DBEngine.SystemDB Question???

I am trying to open a Secured front-end database that uses a workgroup file using the following code.  myID and myPSWD are valid in mySecured.mdw but the code errors out each time indicating that the user and password is invalid.  It seems like the  DBEngine.SystemDB = "C:\MyFolder\mySecured.mdw"  is not working because when I mouse over this line in the debug window it is always referencing the original  C:\Program Files\.......\System.mdw.


Dim rst As Recordset
Dim dbs As database
Dim wks As Workspace
Dim varLastMod As Date

DBEngine.SystemDB = "C:\MyFolder\mySecured.mdw"

Set wks = CreateWorkspace("NewWks", "myID", "myPSWD")
Set dbs = wks.OpenDatabase("C:\MyFolder\MyDatabase.mdb")

Set rst = dbs.OpenRecordset("SetupGeneral", dbOpenDynaset)
rst.MoveFirst
varLastMod = rst!LAST_MODIFIED

MsgBox varLastMod
   
End Sub


Any help will be appreciated.
LVL 19
Eric ShermanAccountant/DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't change the SystemDB of your current Access session. You can try open a new dbengine and use that;

Dim dbg As DAO.DBEngine
Dim rst As Recordset
Dim dbs As database
Dim wks As Workspace
Dim varLastMod As Date

Set dbg = New DAO.DBEngine
dbg.SystemDB = "C:\MyFolder\mySecured.mdw"

Set wks = dbg.CreateWorkspace("NewWks", "myID", "myPSWD")
Set dbs = wks.OpenDatabase("C:\MyFolder\MyDatabase.mdb")

Set rst = dbs.OpenRecordset("SetupGeneral", dbOpenDynaset)
rst.MoveFirst
varLastMod = rst!LAST_MODIFIED

MsgBox varLastMod
Eric ShermanAccountant/DeveloperAuthor Commented:
Tried it but same problem.  It is still pointing to the original System.mdw.

I need a way to be able to run the original code but without opening a session of Access.

Thanks,

ET

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you mean when you say "it's still pointing to your original system.mdw"? Do you mean that you can't open the recordset, database, etc? Are you getting any errors?

Try using this:

Set dbg = New PrivDBEngine

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Eric ShermanAccountant/DeveloperAuthor Commented:
It still errors out and when I mouse over the dbg.SystemDB = "C:\MyFolder\mySecured.mdw" it shows where dbg.SystemDB = C:\Program Files\.......\System.mdw.

I think I need to convert this VB code to an EXE.  Probably will need the Professional or Stand Alone version of Visual Basic as I don't see any options to Make EXE from the current modules that's open.

ET


Eric ShermanAccountant/DeveloperAuthor Commented:
LSM ... This worked.

Set dbg = New PrivDBEngine

What is the difference, comparing that code to ....

Set dbg = New DAO.DBEngine

Thanks,

ET






Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The PrivDBEngine is a "private" db engine ... DAO sometimes has trouble building a new dbengine, which is why MS provided the Private dbengine ... BTW, this is not documented, so be forewarned ... it's been in every version of Access since 2000, but there are no guarantees for the future.
Eric ShermanAccountant/DeveloperAuthor Commented:
Ok ....Much Appreciated.

One last question..... The version of Visual Basic that opens up with Access 2000 is not capable of creating an EXE file of the code, correct???  Would I need the Professional version installed in order to accomplish this???

Thanks,

ET
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You are correct - Access uses VBA (which is a subset of the VB language) and you cannot create .exe files. You would have to have a full version of VB to do this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.