Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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
    Cocuments and SettingsMARK DApplication DataMicrosoftAccessSystem1.mdw

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

    <hr>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.<hr>

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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)....

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •