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

[Stockton.S]

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

[parkes]

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

UserRs.Open strSQLUser, UserCon, , , adCmdText

[Stockton.S]

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

[parkes]

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

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

[Stockton.S]

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

Simon

[parkes]

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

[Stockton.S]

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

Thanks for your help

Simon

[Samuel]

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