Open DB with User Security

[XcelSoft]

I have an Access 2k db for which user seurity has been
enabled using a WorkGroup Information File. I am trying
to open the database in Visual Basic from another
maintenance db to set or turn off the "AllowBypassKey"
property of the secured database. I read the article
referred to me by Doug Steele but I'm still having a
problem. The following code is in the second db that
will basically open the secured database so I can then
change the AllowBypassKey property but it doesn't seem to
work because once you open the second DB it appears that
it's SystemDB is always pointed to the original
SYSTEM.MDW file in the common file folder. Seems like
the code below is not being acknowledged.

Dim dbs As Database, myWorkspace As Workspace

DBEngine.SystemDB = "c:\myfolder\secured.mdw"
Set myWorkspace = DBEngine.CreateWorkspace
("New", "userid", "password")

Set dbs = DBEngine.Workspaces("New").OpenDatabase
("c:\myfolder\mydatabase.mdb")

The Error "Not a valid account name or password" pops
up. I looked in the origninal system.mdw and the
secured.mdw and the user name is there.

Any ideas will be appreciated.

 

[Dirk Goldgar]

I have an Access 2k db for which user seurity has been
enabled using a WorkGroup Information File. I am trying
to open the database in Visual Basic from another
maintenance db to set or turn off the "AllowBypassKey"
property of the secured database. I read the article
referred to me by Doug Steele but I'm still having a
problem. The following code is in the second db that
will basically open the secured database so I can then
change the AllowBypassKey property but it doesn't seem to
work because once you open the second DB it appears that
it's SystemDB is always pointed to the original
SYSTEM.MDW file in the common file folder. Seems like
the code below is not being acknowledged.

Dim dbs As Database, myWorkspace As Workspace

DBEngine.SystemDB = "c:\myfolder\secured.mdw"
Set myWorkspace = DBEngine.CreateWorkspace
("New", "userid", "password")

Set dbs = DBEngine.Workspaces("New").OpenDatabase
("c:\myfolder\mydatabase.mdb")

The Error "Not a valid account name or password" pops
up. I looked in the origninal system.mdw and the
secured.mdw and the user name is there.

Any ideas will be appreciated.

This works for me:

DBEngine.SystemDB = "c:\myfolder\secured.mdw"
Set myWorkspace = DBEngine.CreateWorkspace("New", "userid",
"password")
Set dbs = myWorkspace.OpenDatabase("c:\myfolder\mydatabase.mdb")

And this works for me:

DBEngine.SystemDB = "c:\Personnel Management\PMS.mdw"
Set myWorkspace = DBEngine.CreateWorkspace("New", "userid",
"password")
DBEngine.Workspaces.Append myWorkspace
Set dbs =
DBEngine.Workspaces("New").OpenDatabase("c:\myfolder\mydatabase.mdb")

The help file has this to say:

<quote>
You can create new Workspace objects with the CreateWorkspace method.
After you create a new Workspace object, you must append it to the
Workspaces collection if you need to refer to it from the Workspaces
collection. You can, however, use a newly created Workspace object
without appending it to the Workspaces collection.
</quote>

 

[TC]

Well caught.

Surprising that: DBEngine.Workspaces("New") did not fail with a runtime
error, when he wasn't appending it. Does that fail for you?

TC
(off for the day)

 

[Xcelsoft]

Thanks Dirk and TC.

Looks like the Append was needed. Also, the database
that I am using to toggle the "AllowBypassKey" property
on and off in the secured db had to be opened with the
same Workstation Information file. Otherwise, the
DBEngine.SystemDB would default to the SYSTEM.MDW that's
used when opening Access by default. I created another
DB that used the same secured.mdw file and it worked ok.

Thanks for your input.

 

[Dirk Goldgar]

Surprising that: DBEngine.Workspaces("New") did not fail with a
runtime error, when he wasn't appending it. Does that fail for you?

Yes, I get a "not in collection" error. I'm not sure why that wouldn't
have appeared for Xcelsoft.

 

[Xcelsoft]

Dirk,

I did eventually get the "Not in Collection" error. I had
two issues/problems going. Once I opened the database
with the correct Workgroup Info File is when the "Not in
Collection" error poped up. I then used the Append
statement to Append the Workspace to the collection and
all worked ok.

Thanks for your help.

 

[TC]

I don't understand. If you're still reading this: When you posted your
original code - which did not have the Append - why was your question not
this: "Why do I get a Not In Collection error in this code?".

Cheers,
TC

 

[Dirk Goldgar]

I don't understand. If you're still reading this: When you posted your
original code - which did not have the Append - why was your question
not this: "Why do I get a Not In Collection error in this code?".

TC -

I've been looking into the matter, and I *think* the problem was along
these lines. Xcelsoft was running the code in a utility .mdb file, and
was not opening that .mdb in the same workgroup as the secured database
in question. AIUI, an application can't have more than one DBEngine
object in existence at a time, and you can't change the SystemDB
property of the DBEngine object after it has been initialized; that is,
after any other DAO object has been created. Since Access, in opening
an .mdb file, always initializes the DBEngine object for its own use, it
was too late for Xcelsoft's code to change the SystemDB to the other,
secure workgroup file. Thus it didn't matter that the username and
password specified were in the secure workgroup file, because they were
being validated against the default System.mdw; hence they were never
valid.

Once Xcelsoft changed things so that the utility .mdb was opened in the
same workgroup, the code could work, because the SystemDB property
wouldn't need to be changed. My code worked without doing that because
I overlooked the fact that he was running the code from Access, and
instead I used a normal VB application, not Access VBA, to run it. So
my results were misleading.

As it happens, I believe there's a way the code could have been made to
work without having to open the utility .mdb in the same workgroup. I
think (without testing) that the PrivDBEngine object could have been
used instead of DBEngine. If I'm right, something like this would do
it:

Dim dbe As PrivDBEngine

Set dbe = New PrivDbEngine
dbe.SystemDB = "c:\myfolder\secured.mdw"
Set myWorkspace = dbe.CreateWorkspace("New", "userid", "password")
Set dbs = myWorkspace.OpenDatabase("c:\myfolder\mydatabase.mdb")

 

[TC]

Hi Dirk

I'm off for the day in a moment, so I'll read this later & reply if
required. Thanks for the info!

TC

 

[Xcelsoft]

Dirk / TC,

In a nutshell what Dirk explained is pretty much what
happened.

Many thanks for your help.

Regards,

Xcelsoft