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

[nexus]

your right your odbc provider dosnt support this!

your will need to change to an updated povider or chage to a database with a provider that will support it ie sqlserver7

ado has a generic object structure and donsnt tailor it self for individal providers needs so methods will be present regardless,even if the provider soports them or not. its down to the provider to do the restiction of what can and what carnt be done


sorry i carnt be more helfull but thats the way it is and it sucks

there are lists on ms web site for what the different providers can do and not do!!!