VBA to update SQL table

[MacSapper]

Hi All
I am new to Excel /VBA and have been asked to create a excel workbook that takes data from a SQL table and the user then updates the spreadsheet and then clicks a button to update the table.

I seem to be unable to get the update to work and to me its a very simple task! (Or Should be) the Excel sheet contains 4 columns

On Error GoTo ErrExit
    
    Dim cn_ADO As ADODB.Connection
    Dim cmd_ADO As ADODB.Command
        
    Dim SQLUser As String
    Dim SQLPassword As String
    Dim SQLServer As String
    Dim DBName As String
    Dim DbConn As String
    
    Dim SQLQuery As String
    Dim strWhere As String
    
    'Dim strStatus As String
    Dim i As Integer
    'Dim j As Integer
    Dim jOffset As Integer
    Dim iStartRow As Integer
    'Dim iStep As Integer
    
    'Data Columns
    Dim strEntityID As String
    Dim strGPCustID As String
    Dim strGPCode As String
    Dim strName As String


    'iStep = 100
    jOffset = 1
    iStartRow = 5
    i = iStartRow
    
    SQLUser = "sa"
    SQLPassword = "peretersd "
    SQLServer = "server1"
    DBName = "DEB"
    
    DbConn = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & SQLUser & ";Password=" & SQLPassword & ";Initial Catalog=" & DBName & ";" & _
            "Data Source=" & SQLServer & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
            "Use Encryption for Data=False;Tag with column collation when possible=False"
    
    Set cn_ADO = New ADODB.Connection
    cn_ADO.Open DbConn
    
    Set cmd_ADO = New ADODB.Command
    

    While Cells(i, jOffset).Value > 0

        strGPCustID = Cells(i, 0 + jOffset).Value
        strEntityID = Cells(i, 1 + jOffset).Value
        strGPCode = Cells(i, 2 + jOffset).Value
        strName = Cells(i, 3 + jOffset).Value
        strWhere = "GPCustID = " & strGPCustID
     MsgBox ("GPCustID = " & strGPCustID)
     MsgBox ("ComID = " & strEntityID)
      MsgBox ("RMCCustID = " & strGPCode)
       MsgBox ("Legal name = " & strName)
     
        SQLQuery = "update Customer_Master " & _
                    "set " & _
                    "GPCustID = '" & strGPCustID & "', " & _
                    "ComID = '" & strEntityID & "', " & _
                    "RMCcustID = '" & strGPCode & "', " & _
                    "Legalname = '" & strName & "' " & _
                    "where " & strWhere

    
    
        cmd_ADO.CommandText = SQLQuery
        MsgBox ("SQL Query = " & SQLQuery)
        cmd_ADO.ActiveConnection = cn_ADO
        cmd_ADO.Execute
        
        i = i + 1
        
    Wend
    
    Set cmd_ADO = Nothing
    Set cn_ADO = Nothing
    
    Exit Sub
    
ErrExit:
            MsgBox "Error: " & Err & " " & Error(Err)
            Application.StatusBar = False
            Application.Cursor = xlDefault

            If Not cn_ADO Is Nothing Then
                Set cn_ADO = Nothing
            End If
            If Not cmd_ADO Is Nothing Then
                Set cmd_ADO = Nothing
            End If

End Sub

All the messages appear correct but after the first pass it throws an error where it say "Invalid Column Name" the data in the error is the 4 row down / second column so below i get Invalid Column Name SDU but obviously this is the data!!
so please can someone put me out of my misery and tell me what is wrong!!

GPCustIUD	ComID	RMCCustID	Legalname
SDU-NAT001	SDU	NAT001	hhhhhh
SOZ-AMP100	SOZ	AMP100	jjjjjujuj
RUS-JPM001	RUS	JPM001	sadlfsdf'
SDU-2345	SDU	2345	sadfdfgffg

MANY THANKS!!!

 

[John_w]

Is "GPCustID = '" correct? The sheet shows GPCustIUD.

 

[MacSapper]

Sorry typo ! yes GPCustID is correct.....