We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

DBEngine.SystemDB Question???

Eric Sherman
Eric Sherman asked
on
Medium Priority
1,498 Views
Last Modified: 2008-01-09
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.
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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/Developer
CERTIFIED EXPERT

Author

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

Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Eric ShermanAccountant/Developer
CERTIFIED EXPERT

Author

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/Developer
CERTIFIED EXPERT

Author

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 (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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/Developer
CERTIFIED EXPERT

Author

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 (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.