Trouble with ADODB connection and recordset [resolved]

[tejiri]

I have written this code in VBScript. When I run the form, i get an error saying "current recordset does not support updating. This may br a limitation of the provider or of the selected locktype." What should I add to my connection string so that it allows recordset updating?


Dim myvalue
Dim conn
Dim rec
Dim strconn
Dim tsk
Sub CommandButton1_Click()

set conn = CreateObject("ADODB.Connection")
strconn= "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\nqaam\My Documents\db1.mdb"
conn.Open strconn
Set rec = conn.Execute(" SELECT * FROM TEST ")



rec.AddNew
If Item.UserProperties("Month 2")<>"" Then
rec.Fields("Month 2") = Item.UserProperties("Month 2")
End If


Set myvalue = Item.UserProperties("Month 2")
MsgBox myvalue

rec.Update
rec.Close
Conn.Close

End Sub

[si_the_geek]

The problem is not your connection string, it is the way you are opening the recordset.

Using .Execute returns a read-only forward-only recordset. To have any other options you need to use .Open method of the recordset instead, and specify whatever parameters you like.

For example, instead of the conn.execute line you could have this:

set rec = CreateObject("ADODB.Recordset")
rec.Open " SELECT * FROM TEST ", conn, adOpenDynamic, adLockOptimistic, adCmdText