VBA to update SQL table

MacSapper

New Member
Joined
Oct 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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!!

GPCustIUDComIDRMCCustIDLegalname
SDU-NAT001
SDUNAT001hhhhhh
SOZ-AMP100
SOZAMP100jjjjjujuj
RUS-JPM001RUSJPM001sadlfsdf'
SDU-2345SDU2345sadfdfgffg

MANY THANKS!!!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,396
Messages
5,769,837
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top