Cannot Create Secured Workspace (AXP (A2K format) Jet 4.0 SP6)

Topic

New Reply

When attempting to create a “secured” workspace from a non-secured .MDB/.MDE file using the DAO CreateWorkspace method, Err 3029, Not a valid account name or password, results. I’ve used identical code in past and it worked fine – even posted an example last year. I’m using same version of Access (XP, 2000 format) and Jet (4.0 SP6) as when code worked OK. The purpose of the sub is to dynamically create a new user account for a user with non-admin permissions from an unsecured db file as part of a setup routine. (The rest of sub works fine – if already logged into secured db….) Example of code used:

Public Sub CreateNewUserAcct(ByVal strWrkgrpPath As String, _
ByVal strUser As String, _
ByVal strGrp As String, _
ByVal strPwd As String)
On Error GoTo Err_Handler

Dim ws As DAO.Workspace
Dim grp As DAO.Group
Dim usr As DAO.User
Dim strPID As String
Dim strMsg As String

strPID = “aBC123xyZ456”

DBEngine.SystemDB = strWrkgrpPath ‘ Full path of Wrkgrp file for secured DB
Set ws = DBEngine.CreateWorkspace(“SecureWS”, “SYSADMIN”, “XabcAc46Z1”, dbUseJet)

The code halts at the “Set ws” line and I get the Err 3029 msg. I tried this with 2 different databases, attempting to connect to 2 different secured db’s, w/same result. I double checked the password, path, etc, all are specified correctly. The problem seems to be with the DBEngine.SystemDB = strWrkgrpPath instruction – it does not appear to work. When code halts & enter command in debug window:

? DBEngine.SystemDB
C:Documents and SettingsMARK DApplication DataMicrosoftAccessSystem1.mdw

Which is the default (non-secured) .MDW on my system. According to Help file for SystemDB property:

[indent]

---

Sets or returns the path for the current location of the workgroup information file (Microsoft Jet workspaces only).

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.

---

[/indent]

There is no other “instance of any other DAO object” created before running this code, AFAIK. It’d make more sense if SystemDB was a property of the Workspace rather than DBEngine object. What’s the use of being able to create a new Workspace object if you can’t specify a different SystemDB along with Username, password, etc?? The same code worked in past, so why not now? If anyone has any clues, plz advise!

Reply | Quote

Replies

Mark,

I think that “can’t be changed without restarting your application” is the key. If you were calling this code from another app (VB6, Excel, …), the Jet Engine would not be running yet, so you could switch to another workgroup file. But when you start Access, you also start the Jet Engine, so if you call this code from Access, you should quit and restart Access for this to take effect, which is clearly not what you want. If you switch to another workgroup interactively from within Access, you’ll notice that Access restarts itself if you look closely. So I don’t think this can be solved within Access (but if someone proves me wrong, I won’t complain.)

Note: If you happen to call this code with the name of your current workgroup file as strWrkgrpPath, it will run without problem.

Reply | Quote

I never did find out why code posted did not work, I’ve used similar code in past — after consulting ADH chapter on Security decided to try Plan B, ditch DAO in favor of ADO/ADOX & plain old SQL. Always avoided ADOX for security due to flakiness (as noted below); SQL provides simplest syntax (must be using A2K or later to use SQL for security). Of course in ADO there is no “Workspace” object; the closest analogy is the ADO Connection object; while the Jet 4.0 OLEDB provider-specific properties are roughly analagous to DAO (Jet) DBEngine properties. Revised procedure:

Public Sub adoCreateNewUserAcct(ByVal strWrkgrpPath As String, _
ByVal strUser As String, _
ByVal strPwd As String, _
ByVal strGrp As String)
On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim strPID As String
Dim strCnn As String
Dim strMsg As String

Set cat = New ADOX.Catalog
Set cnn = New ADODB.Connection
strCnn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“User ID=SYSADMIN;” & _
“Password=Xz095Rwii97;” & _
“Data Source=” & strWrkgrpPath & “MyApp.mdb;” & _
“Jet OLEDB:System database=” & strWrkgrpPath & “App.mdw;”
‘ —————————————————————————–
‘ ADOX (cannot specify PID)
‘ Created new acct but pwd did not work – pwd was blank
‘ cnn.ConnectionString = strCnn
‘ cnn.Open
‘
‘ With cat
‘ .ActiveConnection = cnn
‘ .Users.Append strUser, strPwd
‘ .Users(strUser).Groups.Append “Users” ‘Default Users acct
‘ .Users(strUser).Groups.Append strGrp ‘Security group acct
‘ End With
‘
‘ cnn.Close
‘ —————————————————————————–
‘ SQL:
strPID = “ABC123XYZ456” ‘ 4-20 characters
With cnn
.ConnectionString = strCnn
.Open
.Execute “CREATE USER ” & strUser & ” ” & strPwd & ” ” & strPID
.Execute “ADD USER ” & strUser & ” TO Users”
.Execute “ADD USER ” & strUser & ” TO ” & strGrp
.Close
End With

‘ Test msg:
strMsg = “New User account created: ” & vbCrLf & _
“User: ” & strUser & vbCrLf & _
“Group: Users; ” & strGrp & vbCrLf & _
“User Password: ” & Left(strPwd, 1) & String(Len(strPwd) – 1, “*”)
MsgBox strMsg, vbInformation, “ADO NEW USER ACCT FUNCTION”

Exit_Sub:
Set cnn = Nothing
Set cat = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “ADO NEW USER ACCOUNT FUNCTION ERROR”
Resume Exit_Sub

End Sub

The Jet OLEDB System database property allows you to specify workgroup file for the connection. Note that if using ADOX cannot specify PID for new acct; in addition, the password specified when using the ADOX Users Append method did not “stick” – new User acct was created, but was able to log into secured DB w/o providing password – the password was blank – not desired outcome. So used SQL CREATE USER statement to specify PID and Pwd, this does work correctly in A2K and AXP. So problem resolved (for now)….

Reply | Quote

Is there some reason you have to do this from an unsecured mdb? It seems that the simplest option would be to use a secured mdb in the setup.

Reply | Quote

I guess the concept is, app will be distributed to new users at remote sites, they would not be able to log into a secured db till new account created. I will not know who these users are in advance; for apps distributed locally, when I get a request to grant access to a new user, I simply create a new account “manually” before providing setup files to new user – the updated .MDW file is copied to local PC. We want to avoid using a “default” NewUser acct & password as that would not be very secure; and there may be multiple users at the remote site. Any recommendations for best approach in this case would be appreciated.

Reply | Quote

We use a predefined user login and password to log into the workgroup, not one that belongs to an actual person. That user has the necessary permissions to handle the security. We distribute the workgroup file with the application, so we know the user login is there for this purpose.

Reply | Quote