How do I connect to a database using code that I want to be able to add records to?

I have a database that I can connect to using the following but I want to be able to add records using the .AddNew command and it brings up an "Object or Providers not Capable of preforming requested operation " error.

Any ideas?

Simon

Dim UserCon As ADODB.Connection
Dim UserRs As ADODB.Recordset
Dim UserFld As ADODB.Field

Dim strSQLUser As String
Dim strUserDNS As String

strUserDNS = frmView.userdatasource.ConnectionString
Set UserCon = New ADODB.Connection
UserCon.Open strUserDNS

strSQLUser = "SELECT userdata.viewpic, userdata.viewtip, userdata.viewupdate" & _
" FROM userdata" & _
" WHERE userdata.userlogin = " & Chr(39) & getusr() & Chr(39) & Chr(59)
'frmSearch.mmissdatasource.RecordSource = strSQLstatus & adCmdText

Set UserRs = New ADODB.Recordset
UserRs.CursorType = adOpenForwardOnly
UserRs.Open strSQLUser, UserCon, , , adCmdText

If UserRs.EOF = False Then
'For Each UserFld In UserRs
frmView.mipicss.Checked = UserRs("ViewPic")
frmView.miview.Checked = UserRs("ViewTip")
frmView.miupdate.Checked = UserRs("ViewUpdate")
'Next
Else
'UserRs.MoveLast

UserRs.AddNew
' UserRs("userlogin") = getusr()
' UserRs("viewpic").Value = True
' UserRs("viewtip").Value = True
' UserRs("viewupdate").Value = False
' UserRs.Update
End If

UserRs.Close
Set UserRs = Nothing

UserCon.Close
Set UserCon = Nothing

One suggestion have you try removing the ', , ,adCmdText' from the end

UserRs.Open strSQLUser, UserCon, , , adCmdText

I got that bit working the bit that I cannot get to work is:

UserRs.AddNew
UserRs("userlogin") = getusr()
UserRs("viewpic").Value = True
UserRs("viewtip").Value = True
UserRs("viewupdate").Value = False
UserRs.Update

It errors on userlogin when it tries to go to the user login object however it works on the other fields.

Simon

Could have something to do with your trying to enter text, try this:-

UserRs("userlogin") = "'" & getusr() & "'"

It is more that it cannot recognise that userlogin is a valid field name and erorrs because of that!

Simon

Your select statement doesn't include the userlogin in field

"SELECT USERDATA.USERLOGIN, userdata.viewpic, userdata.viewtip, userdata.viewupdate" & _
" FROM userdata" & _
" WHERE userdata.userlogin = " & Chr(39) & getusr() & Chr(39) & Chr(59)
'frmSearch.mmissdatasource.RecordSource = strSQLstatus & adCmdText

You must include the all field names before the FROM clause or it will not pick up that field

Thanks for spotting that I learn more and more about this everyday.

Thanks for your help

Simon

hi,
you can try this code.
You must give to the recordset the possibility of write over Data base...

StrutturaTabs.Open "Select * from <tab name>", <connectionstring>, , adLockOptimistic