VBA Excel ADO SQL Update Query Not Working

Question

I am new to VBA and Excel Scripting, however, I am trying to use it to connect to an SQL Server I have created. I have built a generalized query from a userform, and created a successful SELECT statements that fill my sheet.

However, when I try to update this information in the database I am unsuccessful. The code throws no errors, but I cannot find my changes in the database. Here is my attempt:

Private Sub dbUpdate(Query)

Dim conn As ADODB.Connection
Dim recset As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn As String

'Create the connection string
strConn = "Provider=SQLNCLI11;Server=IP-Address;Database=Info;Trusted_Connection=yes;DataTypeCompatibility=80;"

'Create the connection and recordset objects
Set conn = New ADODB.Connection
Set recset = New ADODB.Recordset

'Open the connection
conn.Open strConn

'Open the recordset with the query
'Previous attempt, no errors
'recset.Open Query, conn

'Execute the recordset
Set cmd = New ADODB.Command
'The below execution of a query throws errors I believe
cmd.CommandText = Query
Set recset = cmd.Execute

'Close things up
Set recset = Nothing
'recset.Close
conn.Close
Set conn = Nothing

End Sub

I am pretty sure the query is correct, but I will update tomorrow if I still can't figure it out.

Your code does nothing to try to update the data. You didn't include the contents of Query, so it's pretty difficult to figure out what might be wrong. — Ken White

ASH ASH · Accepted Answer · 2018-07-13T19:23:09

Here is one example that could work for you.

Sub ImportDataFromExcel()
    Dim rng As Range
    Dim r As Long
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "C:\Users\Ryan\Desktop\Coding\Integrating Access and Excel and SQL Server\Access & Excel & SQL Server\" & _
        "EXCEL AND ACCESS AND SQL SERVER\Excel & Access\Select, Insert, Update & Delete\Northwind.mdb"
    Set conn = New ADODB.Connection
    conn.Open strConn

    With Worksheets("Sheet1")
        lastrow = .Range("A2").End(xlDown).Row
        lastcolumn = .Range("A2").End(xlToRight).Column
        Set rng = .Range(.Cells(lastrow, 1), .Cells(lastrow, lastcolumn))
    End With

        'therow = 1

        For i = 2 To lastrow
            'r = rng.Row
            'If r > 1 Then
                strSQL = "UPDATE PersonInformation SET " & _
                    "FName='" & Worksheets("Sheet1").Range("B" & i).Value & "', " & _
                    "LName='" & Worksheets("Sheet1").Range("C" & i).Value & "', " & _
                    "Address='" & Worksheets("Sheet1").Range("D" & i).Value & "', " & _
                    "Age=" & Worksheets("Sheet1").Range("E" & i).Value & " WHERE " & _
                    "ID=" & Worksheets("Sheet1").Range("A" & i).Value
                conn.Execute strSQL
            'End If
            'r = r + 1
        Next i


    conn.Close
    Set conn = Nothing
End Sub

There are so, so, so many different versions of this. Hopefully you can adapt this example to fit your specific needs.

VBA Excel ADO SQL Update Query Not Working

1 Answers