Open a secure database through code.

[Guest]

I have a database that contains an autoexec macro. Essentially it takes the
results of a query and appends them into a table. I run this manually once a
week because the database I am updating is secured with an mdw file so I have
to sign in. Is there a way using VBA that I can open this database passing in
my user id and password fields so that this can be automated. I've searched
through the boards and found out how to create a connection (works great) but
not actually open a secured db.

I have tried this but I get and error saying that it is not a valid account
or password.

Private Sub MyTest()
Dim DBdaAgg As Database
Dim wrkSec As Workspace
DBEngine.SystemDB = "I:\Resource.MDW"

Set wrkSec = DBEngine.CreateWorkspace("master", "somecut", dbUseJet)
Set DBdaAgg = wrkSec.OpenDatabase("C:\Documents and
Settings\E09461\Desktop\Temp\SYSTTimesheet.mdb;")

DBdaAgg.Close
wrkSec.Close

Set DBdaAgg = Nothing
Set wrkSec = Nothing
End Sub

As always thanks in advance for any help you can give me.

m

 

[Douglas J. Steele]

Your CreateWorkspace statement appears to be incorrect.

The syntax for CreateWorkspace is:

Set workspace = CreateWorkspace(name, user, password, type)

If your UserID is master, and your Password is somecut, you should be using:

Set wrkSec = DBEngine.CreateWorkspace("", "master", "somecut", dbUseJet)

or

Set wrkSec = DBEngine.CreateWorkspace(user:="master", password:="somecut",
type:=dbUseJet)

 

[Guest]

Thanks Douglas - When I use the first method I get the same invalid
account/password error. When I use the second version I get a named argument
not found on the user argument.

Hmmm - I'm sure I'm missing something but for the life of me I don't know
what it is.
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Your CreateWorkspace statement appears to be incorrect.

The syntax for CreateWorkspace is:

Set workspace = CreateWorkspace(name, user, password, type)

If your UserID is master, and your Password is somecut, you should be using:

Set wrkSec = DBEngine.CreateWorkspace("", "master", "somecut", dbUseJet)

or

Set wrkSec = DBEngine.CreateWorkspace(user:="master", password:="somecut",
type:=dbUseJet)

 

[Douglas J. Steele]

Sorry: my mistake. That's what I get for not looking things up. <g>

Try:

Set wrkSec = DBEngine.CreateWorkspace(username:="master",
password:="somecut", usetype:=dbUseJet)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas - When I use the first method I get the same invalid
account/password error. When I use the second version I get a named
argument
not found on the user argument.

Hmmm - I'm sure I'm missing something but for the life of me I don't know
what it is.

 

[Guest]

OK, It liked the syntax this time but now I'm getting the Invalid
Account/Password.

I'm wondering if I need to full qualify the Network Path to the MDW file
rather than using my local mapping letter...

 

[Douglas J. Steele]

It shouldn't make a difference, but why not try it.

You're sure that's the correct MDW?

 

[Guest]

Yep - This is the shortcut I use to open the database:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Documents and
Settings\E09461\Desktop\Temp\SYSTTimesheet.mdb" /wrkgrp "I:\Resource.MDW"

I tried fully qualifying the path but it didn't help... I'm at a loss...

 

[Douglas J. Steele]

What line of code generates the error: the CreateWorkspace call, or the
OpenDatabase call?

(BTW, the semi-colon at the end of the database name in OpenDatabase is
incorrect)

 

[Guest]

It's the create workspace call.

 

[Douglas J. Steele]

Looking in the Help file, it looks as though you must provide a name for the
workspace. I don't believe what you choose as the name makes any difference.

 

[Guest]

Tried that before I sent the last reply.

Set wrkSec = DBEngine.CreateWorkspace(Name:="Test", UserName:="master",
Password:="somecut", usetype:=dbUseJet)

 

[Douglas J. Steele]

Sorry: looks as though I missed something. According to
http://support.microsoft.com/support/access/content/secfaq.asp you need:

Private Sub MyTest()
Dim DBdaAgg As Database
Dim wrkSec As Workspace

DBEngine.SystemDB = "I:\Resource.MDW"
DBEngine.DefaultUser = "master"
DBEngine.DefaultPassword = "somecut"

Set wrkSec = DBEngine.Workspaces(0)
Set DBdaAgg = wrkSec.OpenDatabase("C:\Documents and
Settings\E09461\Desktop\Temp\SYSTTimesheet.mdb")

DBdaAgg.Close
wrkSec.Close

Set DBdaAgg = Nothing
Set wrkSec = Nothing
End Sub

Sorry!

 

[Guest]

Works like a charm!

Thanks a million!