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!!!