CODEWITHSUNDEEP
mysqlvbaexcel
Jimjebus
Jimjebus

Reputation: 109

Updating SQL Database Using VBA ADODB Connection

I've been using ADODB for SQL queries to return data and copy it from a recordset to a workbook for a while and a new task is to update records but I have no clue on how to do update a record.

This is an example of my code:

Dim con As ADODB.Connection
Dim rec As ADODB.Recordset

Set con = New ADODB.Connection
Set rec = New ADODB.Recordset

Dim sql As String

With con
    .Provider = "MSDASQL"
    .ConnectionString = "DSN=ukfast"
    .Open
End With

sql = "UPDATE crm_clients " & _
      "SET cheque_number = '" & chqNo & "' " & _
      "WHERE id = '' "

For Selecting data it was as easy as recordset.copyFromRecordset, but I have no clue about pushing an update back up to the database. I tried the .update method but that only works for the record set itself not the database. I've also looked for some sort of execute method but come up short.

What is the correct approach for updating a record using VBA?

Upvotes: 2

Views: 9986

Answers (1)

cboden
cboden

Reputation: 823

You can use the Execute method of the connection object for that:

con.Execute(sql)

Upvotes: 4

Related Questions