Error 3251 - Trying to edit SQL using VBA code and ADODB connection

I'm trying to edit my SQL but can't find the right method. All I'm getting is an 3251 error. Hope for some help.

Here is the code I'm trying to use:

Sub TestWriteSQL()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ClientID As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString = "Provider=sqlncli; Data Source=MYSERVER; Initial Catalog=Test; Trusted_Connection=yes;"
cn.Open

ClientID = 7555

Set rs = cn.Execute("SELECT CustomFieldValue.Value, CustomRecordset.RecordId, CustomFieldValue.CustomFieldId FROM CustomFieldValue INNER JOIN CustomRecordset ON CustomFieldValue.CustomRecordsetId = CustomRecordset.Id WHERE CustomRecordset.RecordId = '" & ClientID & "' And (CustomFieldValue.CustomFieldId = 296)")

'rs.Edit
rs!Value = 22
rs.Update

Set rs = Nothing
cn.Close

End Sub

Open in new window

ZoodiaqAsked:
Who is Participating?
 
MMTadminConnect With a Mentor Commented:
cn.execute is read only recordset by design

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675023%28v=vs.85%29.aspx

You need to declare a recordset and then use it

recordset.Open Source, ActiveConnection, CursorType, LockType, Options
0
 
ZoodiaqAuthor Commented:
Thx for the link.

I'm a beginner in this area. Could you provide me with an example where I change the CustomFieldValue.Value to 22? If I could get that to work I think I could move on from there.
0
 
Anthony PerkinsConnect With a Mentor Commented:
Something like this should prove the most efficient way to change that value:
Sub TestWriteSQL()

Dim cn As ADODB.Connection
Dim ClientID As String

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=sqlncli; Data Source=MYSERVER; Initial Catalog=Test; Trusted_Connection=yes;"
cn.Open

ClientID = "7555"   ' ClientID is a String and therefore the value assigned should be in double quotes.

cn.Execute "UPDATE cfv SET Value = 22 FROM CustomFieldValue cfv INNER JOIN CustomRecordset cr ON cfv.CustomRecordsetId = cr.Id WHERE cr.RecordId = '" & ClientID & "' AND cfv.CustomFieldId = 296"

cn.Close

End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.