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]

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