Password problem

[Joe]

I posted the following question on April 19th:

I use the following code to open another Access database
and open a form, which has code, attached to the open
event. The code uses an update query to pull information
from the first database, and then runs a report that is
dependant on that update. My problem is that access
complains that the user does not have the necessary
permissions to run the update queries, is it possible to
pass user and password details to the second database
logon form from the first database code. I have tried
setting permissions to allow the updates in the second
tables security settings, without success.

Dim objAcc As New Access.
Set objAcc = GetObject("C:\Documents and
Settings\Joe\My Documents\Van
Run05.mdb", "access.application")
objAcc.UserControl = True
objAcc.DoCmd.OpenForm "frmfuelweek"
DoCmd.OpenForm "frminstruct", acNormal, "", "", ,
acNormal

I have been away on holiday and on my return Bill Taylor
had suggested setting the queries run permissions to
owner's instead of user's. Unfortunately this does not
work. I have tried running the two queries on the second
database directly from the first but the database still
complains that the user has no read permissions on the
underlying table.
Any help with this problem much appreciated.

 

[TC]

Joe, you won't get any good answer to this question until you post more
information on how the security has been established for both databases. How
many workgroup files are you using? What users are defined in each file?
Which file was used to create which database?, and so on.

Access security is a very complex topic. RWOP queries >might< be the answer,
but there is waaaaaaay more to those than simply "setting the queries run
permissions to owner's".

HTH,
TC

 

[Joe]

Hi TC
As you suggest database security is perhaps the
underlining problem, which is preventing the queries
running. The open database, which after importing data
attempts to update the second, is not secured, other than
the fact that it opens at the switchboard and the database
window is hidden. The database, which I want to update,
has had the security wizard applied and has two users
accounts both of whom have the necessary permissions to
carry out the required tasks. I have tried making a copy
of the first database and creating accounts the same as in
the second and running the security wizard, which was
unsuccessful.
Where should I go from here?
Thanks Joe

 

[TC]

So, database #1 is secured. It executes the following code:

 

[TC]

So, >secured< database #1 executes code which:
1. opens >unsecured< database #2;
2. runs some forms (in database #2) which:
3. import some data from database #1, then
4. try to update some data in database #1.
Step 4 fails with an error saying that the user does not have sufficient
permissions to perform the update.

Is all that correct?

Does the current user of database #1 have permission to perform the update
in question?

When the code opens database #2, does Access prompt for a username/password?

TC

 

[Joe]

Database #1 is not secured and after data has been
imported into this database I want to open database #2
which is secured, this in turn would pull some data from a
table linked to database #1 and produce a report which
combines data from both tables. I accept that the approach
is not ideal but this is the way the databases have
evolved and it would be a big job to re-write both
databases.

The current user of database #1 has a user account on
database #2 with the required permissions to carry out the
tasks.

The logon screen does appear but this obviously does not
have a reference to the settings normally retrieved when
started from the desktop icon. I.e.

"C:\Program Files\Microsoft Office\2000
\Office\MSACCESS.EXE" "C:\Documents and Settings\Joe\My
Documents\Van Run05.mdb" /WRKGRP "C:\Documents and
Settings\Joe\My Documents\Secured1.mdw"

Your further advice would be appreciated.
Joe

 

[test]

Joe, I'm a little confused by your new description of what you are doing.
Could you please amend the following step-by-step description to ensure it
is correct, and answer the questions "in line" where they occur. Then we can
take it from there. I need the information in this specific, step-by-step
format. A general narrative description is too easy to misunderstand.

Is this correct:

Unsecured database #1 executes code which:
1. opens Secured database #2;
2. runs some forms (in database #2) which:
3. import some data from database #1, then
4. try to update some data in database #1.

Step 4 fails with an error saying that the user does not have sufficient
permissions to perform that update. Correct?

When you start db #1, does it ask for a username/password? If so:
- what username do you enter?
- does that user have permission to perform, >in db #1<, the update which
step 4 tries to perform "remotely"?

When db #1 starts db #2, I gather it asks for a username/passwiord. What
username do you enter?

Cheers,
TC

 

[Guest]

Hi TC
I have attempted to answer your points and hope that it is
clear enough for you to understand. My reply's are in the
brackets adjoining each point. Many thanks for your
ongoing efforts.


This is correct:

Unsecured database #1 executes code which:
1. opens Secured database #2; (the password dialog box
opens at this point it will not accept a password but
accepts the name which is already in the user name box,
database #2 opens.)
2. runs some forms (in database #2) which: (the forms
attempt to run an update query to pull data from a linked
tbl residing in database #2 but linked to a tbl in
database #1 database #2 fails at this point because the
user has no read permissions to read the linked tbl.)
3. import some data from database #1, then
(hopefully the bracketed reply above clears your points 3
& 4. If the query was to run successfully a report would
be printed that reply's on the imported data, database #2
would then be closed.)
4. try to update some data in database #1.

Step 4 fails with an error saying that the user does not
have sufficient
permissions to perform that update. Correct? (step 2
fails. As explained.)

When you start db #1, does it ask for a username/password?
If so:
- what username do you enter? (I use my name "joe" as this
database is unsecured I provide no password. The username
password dialog opens when any database on the system is
opened, is this because I have run the security wizard on
databases in the past?)
- does that user have permission to perform, >in db #1<,
the update which step 4 tries to perform "remotely"?
(Database #1 had no permissions set when I first attempted
this procedure, since then I have attempted to grant the
users permissions in database #1 but this has had no
effect.)

When db #1 starts db #2, I gather it asks for a
username/password. What username do you enter? (The dialog
box opens with the name "joe" already in the username box,
it will not accept the password for this username, failing
if you attempt to enter the password. But it opens with
the name "joe" left in the username box and the password
blank.)

 

[Joe]

Hi TC
I have attempted to answer your points and hope that it is
clear enough for you to understand. My reply's are in the
brackets adjoining each point. Many thanks for your
ongoing efforts.


This is correct:

Unsecured database #1 executes code which:
1. opens Secured database #2; (the password dialog box
opens at this point it will not accept a password but
accepts the name which is already in the user name box,
database #2 opens.)
2. runs some forms (in database #2) which: (the forms
attempt to run an update query to pull data from a linked
tbl residing in database #2 but linked to a tbl in
database #1 database #2 fails at this point because the
user has no read permissions to read the linked tbl.)
3. import some data from database #1, then
(hopefully the bracketed reply above clears your points 3
& 4. If the query was to run successfully a report would
be printed that reply's on the imported data, database #2
would then be closed.)
4. try to update some data in database #1.

Step 4 fails with an error saying that the user does not
have sufficient
permissions to perform that update. Correct? (step 2
fails. As explained.)

When you start db #1, does it ask for a username/password?
If so:
- what username do you enter? (I use my name "joe" as this
database is unsecured I provide no password. The username
password dialog opens when any database on the system is
opened, is this because I have run the security wizard on
databases in the past?)
- does that user have permission to perform, >in db #1<,
the update which step 4 tries to perform "remotely"?
(Database #1 had no permissions set when I first attempted
this procedure, since then I have attempted to grant the
users permissions in database #1 but this has had no
effect.)

When db #1 starts db #2, I gather it asks for a
username/password. What username do you enter? (The dialog
box opens with the name "joe" already in the username box,
it will not accept the password for this username, failing
if you attempt to enter the password. But it opens with
the name "joe" left in the username box and the password
blank.)


..

 

[TC]

Hi Joe, I was busy on the week-end, so I hope you are still reading this.

I think you have some misconceptions about what is a "secured" or
"unsecured" database. It doesn't depend on whether you do or do not have to
enter a password, respectively! You could have a >secured< database in which
certain users >did not< have to enter a password. And conversely, you could
have an >unsecured< database in which certain users >did< have to enter a
password!

To explain this further. When-ever you open a database, you are using a
so-called "workgroup information file". The usernames (Tom, Dick, Joe,
whatever) are defined in the workgroup information file - not in the
database file. Each user (in the workgroup information file) might or might
not have a password, depending on what they have set for themselves. The

database< might or might not have been "secured" by applying the security

wizard or by other means. So, the question of whether you do or do not enter
a password for some user, is >independent< of the question of whether the
database has been secured or not.

Having said that, let's re-describe your problem, using your replies to my
previous questions, but deleting the terms "secured" and "unsecured", since
there is some confusion with those. Sorry if this seems repetetive, but with
security problems, it is critical to have a clear description of what is
actually hapenning.

Is this correct:

Database #1 executes code which does two things:

1. Opens database #2. When that happens, a username/password box is
displayed. It is already displaying the name of the user who logged-in to
database #1 - correct? That username does not have a password, so you just
press Enter, and database #2 then opens.

2. Runs some forms in database #2 which "attempt to run an update query
to pull data from a linked tbl residing in database #2 but linked to a tbl
in database #1. Database #2 fails at this point because the user has no read
permissions to read the linked tbl."

This suggests that the user who logged-on to database #1, does not have
permission to read the relevant table in database #1. This could well be
true, depending on how you set up the security. To follow this up, you would
need to show me the actual code that is causing the error (VBA + SQL), and
the exact text of the message you're getting.

HTH,
TC

 

[Joe]

Hi TC , Thanks for your response.
The syntax of the points you make in your latest posting
are true. The code used to start the update query is as
follows:

Dim objAcc As New Access.Application 'opens database #2
and printsgrouped job report with fuel figures
Set objAcc = GetObject("C:\Documents and Settings\Joe\My
Documents\Van Run05.mdb", "access.application")
objAcc.DoCmd.OpenQuery "qryfuelupdate"
the code fails here with the message:
Run-time error '3112'
Record(s) cannot be read; No read permission
on 'tblmasterfuel'

qryfuelupdate SQL is as follows:

INSERT INTO [tblJobs&Prices] ( Van, Invdate, FuelAmt,
Name, [Group], reports )
SELECT qryweekfuel.Van AS Expr1, [Date]-2 AS invdate,
qryweekfuel.[Sum Of Amt Net] AS Expr2,
qrystaff_dates.Name, qrystaff_dates.Group,
qrystaff_dates.Reports
FROM qryweekfuel INNER JOIN qrystaff_dates ON
qryweekfuel.Van = qrystaff_dates.Vanrun
WHERE (((qryweekfuel.Date) Between [qrystaff_dates].
[sdate] And [qrystaff_dates].[end]))
GROUP BY qryweekfuel.Van, [Date]-2, qryweekfuel.[Sum Of
Amt Net], qrystaff_dates.Name, qrystaff_dates.Group,
qrystaff_dates.Reports
HAVING (((qryweekfuel.Van) Is Not Null))
ORDER BY qryweekfuel.Van, [Date]-2
WITH OWNERACCESS OPTION;

Should the VBA code that starts databse #2
Set objAcc = GetObject("C:\Documents and Settings\Joe\My
Documents\Van Run05.mdb", "access.application")
Not include the path of the security file?
Thanks for your time
Joe

 

[TC]

Hi Joe, glad you're still reading.

We're getting closer to an answer now!

Answers interspersed.

Cheers,
TC

Hi TC , Thanks for your response.
The syntax of the points you make in your latest posting
are true. The code used to start the update query is as
follows:

Dim objAcc As New Access.Application 'opens database #2
Set objAcc = GetObject("C:\Documents and Settings\Joe\My Documents\Van

Run05.mdb", "access.application")

This will start Access using the default workgroup information file - ie.
the workgroup file that was last selected using the workgroup administrator
program. Let's call that workgroup file, 'D' (for default). Even if you
started db #1 from a shorcut that used some other workgroup file 'W' for db
#1, db #2 is being started with the default workgroup file, 'D'. This
clarifies which workgroup file is being used for each database.

*Are* you starting db #1 from a shortcut that specifies a workgroup file?

You could probably delete the 'New' keyword from the 'Dim' statement above.
That is nothing to do with your problem, however.

objAcc.DoCmd.OpenQuery "qryfuelupdate"
the code fails here with the message: Run-time error '3112' Record(s)

cannot be read; No read permission on 'tblmasterfuel'

qryfuelupdate SQL is as follows:

INSERT INTO [tblJobs&Prices] ( Van, Invdate, FuelAmt,
Name, [Group], reports )
SELECT qryweekfuel.Van AS Expr1, [Date]-2 AS invdate,
qryweekfuel.[Sum Of Amt Net] AS Expr2,
qrystaff_dates.Name, qrystaff_dates.Group,
qrystaff_dates.Reports
FROM qryweekfuel INNER JOIN qrystaff_dates ON
qryweekfuel.Van = qrystaff_dates.Vanrun
WHERE (((qryweekfuel.Date) Between [qrystaff_dates].
[sdate] And [qrystaff_dates].[end]))
GROUP BY qryweekfuel.Van, [Date]-2, qryweekfuel.[Sum Of
Amt Net], qrystaff_dates.Name, qrystaff_dates.Group,
qrystaff_dates.Reports
HAVING (((qryweekfuel.Van) Is Not Null))
ORDER BY qryweekfuel.Van, [Date]-2
WITH OWNERACCESS OPTION;

WITH OWNERACCESS OPTION has the following meaning: "When running this query,
do not use the access permissions of the currently logged-on user (U).
Instead, use the access permissions of the user (O) who actually owns the
query."

Normally, user O would have greater permissions to the tables, than user U.
(In fact, user U might have >no< permissions to the tables.) WITH
OWNERACCESS OPTION lets user O >delegate< his greater permissions, to
lower-level user U, for the duration of that query.

So, instead of checking what permissions the >current< user has to
tblmasterfuel in db #1, you need to check what permissions >the owner of
qryfuelupdate in db#2< has to tblmasterfuel in db #1.

To determine the owner of qryfuelupdate in db#2, just log-on to db #2, then
use the Tools:Security options, or enter the following (untested) statement
into the debug window:

? dbengine(0)(0).querydefs![qryfuelupdate].owner

My bet is, that the owner listed above, does >not< have suitable permissions
to tblmasterfuel in db #1.

Should the VBA code that starts databse #2
Set objAcc = GetObject("C:\Documents and Settings\Joe\My
Documents\Van Run05.mdb", "access.application")
Not include the path of the security file?

Can't say at this stage.

HTH,
TC

 

[Guest]

Hi TC

Your continued deliberations are much appreciated. I have
looked at the permissions as you requested and they appear
to be in order, with the qryfuelupdate owner being joe and
all permissions suggesting that joe be allowed read write
and administration rights over this query. I was unable to
run the statement you provided in the immediate window,
which gave the compile error 'member or data member not
found'.
I have managed to get the database #2 to accept the
password for user joe when the logon dialog is opened. I
have also taken out the new reference in the dim
statement.

I still wonder about the reference to the database mdw
file:

"C:\Program Files\Microsoft Office\2000
\Office\MSACCESS.EXE" "C:\Documents and Settings\Joe\My
Documents\Van Run05.mdb" /WRKGRP "C:\Documents and
Settings\Joe\My Documents\Secured1.mdw"

Which is called when database #2 is started from the
desktop shortcut. If I start database #2 by double
clicking on the mdb file and enter the username joe with
the correct password and attempt to run qryfuelupdate I
obtain the same error message as when database #2 is
started from database #1. Or am I still confused.

Regards Joe

 

[TC]

Hi TC

Your continued deliberations are much appreciated.

No probs. But Joe, could you please retain the text of the whole
conversation when you make each post. The old posts rapidly disappear from
my newsserver. So if you cut out the old text, I can not see it any more, to
refer back to it. >All of the previous posts< have already isappeared from
my newsserver :-(

I have
looked at the permissions as you requested and they appear
to be in order, with the qryfuelupdate owner being joe and
all permissions suggesting that joe be allowed read write
and administration rights over this query.

The issue is not whether joe has read/write access to the query in db #2. It
is whether joe has read access to the relevant table in db #1. The message
(as I remember it) implies that joe can not read the table in db #. It does
not imply that joe can not use the query in db #2. In fact, he >is< using
the query in db #2, because it is giving an error when it executes the query
SQL!

So, does joe have read access to the relevant table in db #1?

I was unable to
run the statement you provided in the immediate window,
which gave the compile error 'member or data member not
found'.

Ok, I guess you got the query owner from the Tools:Security options.

I have managed to get the database #2 to accept the
password for user joe when the logon dialog is opened.

Not sure what you mean by that. If joe has a password, you must enter it, or
the open won't proceed. Conversely, if joe >does not< have a password, you
must leave that field blank, or again, the open won't proceed.

I have also taken out the new reference in the dim statement.


I still wonder about the reference to the database mdw file.

No point wondering, until you tell me whether user joe does or does not have
read access to the relevant table in db #1

HTH,
TC

 

[Joe]

Hi TC
Sorry about losing the history, I didn't appreciate that
your access to the group was any different from mine. To
answer your question, I have given 'joe' user rights to
all tables and queries on both databases.

Regards Joe

 

[TC]

Joe, if you are logging-on as a user who has full permissions to the
relevant table, you shouldn't be getting any errors saying that there is no
read access to that table.

I feel you need to double-check this. Please follow these steps precisely -
adding & omitting nothing.

1. Double-click db #2;
2. Log on as joe;
3. Use tools:security to check the owner of the query in question - who is
it?
4. Double click the relevant query - what error occurs?
5. Close db #2;
6. Double-click db #1;
7. Log on as joe;
8. See if the user from step 3., has full permissions to the table in
question.

If the user from step 3. above, has full permissions to the table in
question, I can't see any way that the query (which references that table)
would say that there is no read access to that table.

HTH,
TC

 

[Joe]

1. Double-click db #2;
2. Log on as joe;
3. Use tools:security to check the owner of the query in
question - who is (joe is the current owner of all tables,
queries and forms.)
it?
4. Double click the relevant query - what error occurs?
(no errors occur the query executes as normal.)
5. Close db #2;
6. Double-click db #1;
7. Log on as joe;
8. See if the user from step 3., has full permissions to
the table in
question. (joe is the current owner of all tables, queries
and forms.)


If the user from step 3. above, has full permissions to
the table in
question, I can't see any way that the query (which
references that table)
would say that there is no read access to that table. ( I
hate to be repetitive but I am convinced that the problem
lies in the reference to the security & workgroup file
contained within the desktop shortcut, this is referred to
when the database is started from the desktop, but is not
when the database is started from db #1. the response is
exactly the same as would occur should db #2 be started
from the mdb file in the documents folder. This results in
exactly the same error "record(s) cannot be read no read
permissions on 'tblmasterfuel'", even though the
user 'joe' complete with password is supplied at start-up?)

 

[TC]

1. Double-click db #2;
2. Log on as joe;
3. Use tools:security to check the owner of the query in
question - who is (joe is the current owner of all tables,
queries and forms.)
it?
4. Double click the relevant query - what error occurs?
(no errors occur the query executes as normal.)
5. Close db #2;
6. Double-click db #1;
7. Log on as joe;
8. See if the user from step 3., has full permissions to
the table in
question. (joe is the current owner of all tables, queries
and forms.)

Ok, those tests show that the table in db #1, *can be accessed* by the query
in db #2, without any errors, ***using the system default workgroup file***.

So, one of three things must be true:

(1) You used the existing system default workgroup file, when you secured
the database. That would be an error. You start the securing process by
creating a *new* workgroup file, not using the existing system default one.

Or:

(2) You *did* create a new workgroup file, but you have missed one or more
steps in the process - because, your "secured" database can be referenced
from the *default* workgroup file.

Or:

(3) You did create a new workgroup file, & you have now set that file as the
default workgroup file, using the workgroup administrator program (or option
in later versions of Access). That would not be an "error", as such, but it
would cause problems with all your other databases, so most people would not
go that way.

Joe, I hate to give up on this problem after all the work that we have done
so far! But the above conclusion suggested that you have not set up the
security properly.

These kinds of problems are really, really difficult to diagnose remotely.

At this stage, here is all I can think to suggest.

(1) Get someone local to come over & take a look at it. Or,

(2) *Desecure* both databases, then start again, using a formal reference
such as the Access Security FAQ, following the steps precisely, adding &
omitting nothing.

Finally, if you want to open a second database (from the current one) but
using a different workgroup file, check out the PrivDBEngine object. You'll
find some references to it on the web. The code is something like this
(untested):

dim dbe as PrivDBEngine, db as Database
set dbe = new privdbengine
dbe.systemdb = "path to other workgroup file"
dbe.user = "fred"
dbe.password = "s3cr3t"
set db = dbe.opendatabase( ...)
etc.

Sorry to leave you in limbo, but I really can't do much more by remote
control :-(

HTH,
TC

 

[Guest]

Hi TC
Many thanks for all the work you have put into this issue,
although unresolved your efforts have been very much
appreciated. I will attempt to desecure the databases as
you have suggested.
Many thanks
Joe

 

[TC]

Thanks, Joe. I appreciate your not being disappointed, that we did not get a
final result (after all that effort). Unfortunately, these security problems
are very hard to diagnose remotely. I hope you get a solution, & do not
hestiate to post new questions when required.

Cheers,
TC