CreateWorkspace does not work for me

[nova]

I'm going crazy on the below code not working anymore. I have used this in
the past without any problem. Recently I wanted to implement again and for
one reason or another, it seems the systemDB is not assigned correctly.I'm
getting the error 'Not a valid account name or password'. When debugging, the
SystemDB returns the default Access security group instead of the one I'm
trying to assign.
I'm on Win XP, Office 2003, company laptop, so wondering if this could have
to do with local security?

Dim ws As Workspace
Dim db As Database
DBEngine.SystemDB = "C:\mySecureFile.mdw"
Debug.Print DBEngine.SystemDB
Set ws = DBEngine.CreateWorkspace("New", strUser, strPassword)
Set db = ws.OpenDatabase("C:\myDatabase.mdb")
'... rest of the code
Set db = Nothing
Set ws = Nothing

 

[Douglas J. Steele]

Are you actually providing values for strUser and strPassword before your
call to CreateWorkspace? (Your code doesn't show you doing so...)

 

[Tom van Stiphout]

On Fri, 19 Dec 2008 07:12:59 -0500, "Douglas J. Steele"

(if that was the problem) you need to set:
Option Explicit
at the top of each code module. That will force you to declare your
variables and will hopefully serve as a reminder to give them a value
as well.

-Tom.
Microsoft Access MVP

 

[nova]

Yes, sorry if that wasn't clear. The username and password are declared as
constants. Also, I can use them to log into the secured db using a shortcut
so they're valid.
what's strange to me is that on the debug line, I'm getting the default
workgroup instead of the one that was assigned in the line above.

 

[Douglas J. Steele]

To be honest, I've never had to do something like this. However, the Help
file states "For this option to have any effect, you must set the SystemDB
property before your application initializes the DBEngine object (that is,
before creating an instance of any other DAO object). The scope of this
setting is limited to your application and can't be changed without
restarting your application.", and the examples clearly show setting the
SystemDB property before any other declarations.

Try:

DBEngine.SystemDB = "C:\mySecureFile.mdw"
Debug.Print DBEngine.SystemDB

Dim ws As Workspace
Dim db As Database

Set ws = DBEngine.CreateWorkspace("New", strUser, strPassword)
Set db = ws.OpenDatabase("C:\myDatabase.mdb")
'... rest of the code
Set db = Nothing
Set ws = Nothing

 

[nova]

thanks for looking into this...
hm, strange as I find this exact same code on the ms support pages
(http://support.microsoft.com/kb/163002)
anyway, I've tried your proposal, but no difference. I also tried executing
this code from Outlook as maybe it had to do with the db I was working in,
but again no difference.

 

[nova]

I may have found a workable solution:
when creating my own instance of DBEngine, it seems to work ok, see code
below. However, I cannot run this code simultaniously from different
applications (ex Outlook and Access). Will need to test if different users
log into the same db.
Thanks again...

dim dbe as DAO.DBEngine
Dim ws As Workspace
Dim db As Database
Set dbe = CreateObject("DAO.DBEngine.36")
dbe.SystemDB = "C:\mySecureFile.mdw"
Debug.Print dbe.SystemDB
Set ws = dbe.CreateWorkspace("New", strUser, strPassword)
Set db = ws.OpenDatabase("C:\myDatabase.mdb")
'... rest of the code
Set db = Nothing
Set ws = Nothing
Set dbe = Nothing