What's with ADOX

[Gary.Lowe]

AAHHHHHH!!

I am having a nightmare trying to add a user to a database.

The code so far is as follows.

Public Function fn_ConnectDB() As Boolean
On Error GoTo ConnectErr

            Set cnnMIS = New ADODB.Connection
            With cnnMIS
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Properties("Jet OLEDB:System database") = GetSetting("WIPWOS", "Database", "System_DB", "Err")
                .Open GetSetting("WIPWOS", "Database", "DBPath", "Err"), strUser, strPWD
            End With
    fn_ConnectDB = True
    Exit Function
    
ConnectErr:
    Select Case Err
        Case Err
            lErrNumber = Err.Number
            sErrDescription = Err.Description
            sTitle = "Error in function fn_ConnectDB"
            fn_ConnectDB = False
            Err.Clear
    End Select
End Function


Private Sub cmdAddUser_Click()
On Error GoTo AddErr
    Set cat = New ADOX.Catalog
    
    '\\Validate the relevant input boxes
    If IsNull(txtUserName) Or txtUserName = "" Then
        MsgBox "please input a User Name" _
            , vbOKOnly
        txtUserName.SetFocus
        GoTo Exit_AddErr
    End If

    If IsNull(txtPassword) Or txtPassword = "" Then
        MsgBox "please input a password," _
            , vbOKOnly
        txtPassword.SetFocus
        GoTo Exit_AddErr
    End If
    
    If IsNull(cboGroup) Or cboGroup = "" Then
        MsgBox "please choose a Group," _
            , vbOKOnly
        cboGroup.SetFocus
        GoTo Exit_AddErr
    End If


    '\\open database connection
    If Not DBUtils.fn_ConnectDB Then
        If sTitle = "" Then sTitle = "User error!"
        MsgBox lErrNumber & " " & sErrDescription, vbOKOnly, sTitle
        GoTo Exit_AddErr
    End If
    
    '\\Set catalog active connection to the DB connection
    cat.ActiveConnection = cnnMIS
    
    '\\Append the user to the database
    cat.Users.Append "Gary Lowe", "newpass"
    cat.Users(txtuser).Groups.Append cboGroup

    '\\Add user to the users table
    cnnMIS.BeginTrans
        cnnMIS.Execute " INSERT INTO tblUsers " _
          & "(User_Name, Password, Group ) VALUES " _
          & "('" & txtUserName & "' , '" & txtPassword & "', '" _
          & cboGroup & "');"
    cnnMIS.CommitTrans
    


Exit_AddErr:

    Exit Sub

AddErr:
    Select Case Err
        Case Err
            cnnMIS.RollbackTrans
            Resume Exit_AddErr
    End Select
End Sub

Everytime the code hits the

cat.Users.Append "Gary Lowe", "newpass"

The error 3251 : Object or provider is not capable of performing requested operation.

From what I have read in the ADO guide this means it can't actually do this (So why is it there).

If anyone knows another way round this can you please help.

Thanks

[Jimbob]

I'm struggling with exactly the same problem, too.