Scripting access to an Access file with a security Workgroup file

Question

I am trying to script access to an Access database for use on the command line. The Access database is secured with a workgroup file.

Dim oApp, sWGF,myWS
Set sApp = CreateObject("Access.Application")
set sWGF = "C:\Users\Alan\Documents\Secured.mdw"
oApp.DBEngine.SystemDb = sWGF
WScript.echo "Workgroup " & sWGF
WScript.echo "SystemDb " & oApp.DBEngine.SystemDb
Set myWS = oApp.DBEngine.CreateWorkspace("New","Name","Password")

This code outputs the Secured.mdw filename for the workgroup, but the default System.mdw filename for the SystemDB as the output from the two WScript.echo commands. It also fails to create the workspace saying the Name and Password are wrong (although they ARE correct for the Secured.mdw file)

There are lots of references elsewhere on the net that say you can only do this as the first thing inside an application, but that IS what I am doing.

I am not sure what I am doing wrong. Any ideas

score 2 · Accepted Answer · answered May 13 '11 at 22:49

I think you should be using DAO and Jet directly, instead of automating Access.

  Dim objEngine
  Dim strWorgroup
  dim wrkWorkspace
  Dim db

  Set objEngine = CreateObject("DAO.DBEngine.36")
  objEngine.SystemDB = "C:\Users\Alan\Documents\Secured.mdw"
  Set wrkWorkspace = objEngine.CreateWorkspace("", "Name", "Password")
  Set db = wrkWorkspace.OpenDatabase("C:\MyDatabase.mdb")

This would bypass Access itself and use the Jet database engine directly, which seems simpler to me.

In testing this, I had some difficulty setting the SystemDB as well, but it turned out I just had to make sure I was providing a VALID one for use. The first non-default one I tried didn't work, but when I made a copy of the default one and used that, it worked.

So, I'd look to see if you've got the correct filename/path for your workgroup file, if it's the right version of Jet, and if you've got the appropriate NTFS permissions to open it.

Great that solved it. The reason I am using the Application Object is that I am trying to use the scripts shown at (http://stackoverflow.com/questions/187506/how-do-you-use-version-control-with-access-development), but opening the database with DAO, and then whilst its open using the application object to OpenCurrentDatabase() works (whereas it didn't with the raw scripts). I still have to check the rest works - but I think I am on my way. — akc42, May 14 '11 at 14:41

score 0 · Answer 2 · edited Dec 13 '17 at 02:34

0

A late addition to "I had some difficulty setting the SystemDB as well":

This worked for me:

Set objEngine = CreateObject("DAO.PrivateDBEngine.36")

edited Dec 13 '17 at 02:34

SomeGuyOnAComputer

5,414
6
40
72

answered Dec 12 '17 at 22:36

Werner

1

score 0 · Answer 3 · answered May 12 '11 at 14:03

0

Are sApp and oApp the same thing? You're mixing up variables.

answered May 12 '11 at 14:03

JeffO

7,957
3
44
53

No, I manually typed the code in which is sitting on another machine - its a typo – akc42 May 13 '11 at 21:39

Scripting access to an Access file with a security Workgroup file

3 Answers3