VBA Update sql from Excel to MS SQL Server 2008 using ListColumns

Question

I am working on an Excel 2010 Workboox where a macro pulls in data from a database table. The users can then update the values of Column06) to a specific value if needed. Once complete, they can run a macro to run a SQL update so Column06 in the database is updated where COLUMN01 and COLUMN02 are in the database table. I know the ADO connection is working as I tried with a very generic sql which worked fine. I know that the table could be of varying lengths, so I knew I probably needed to loop through the rows, and that's where I'm stuck.

I tried setting up a loop similar to another solution I found online, and started getting Run-Time Error 91 "Object variable or with block variable not set". I think is due to the ListObject.ListColumns I'm using in the new Update Statement. I've tried using other examples to declare these, but it usually ends up in other errors. I must be missing something, or doing something wrong. Any help would be greatly appreciated.

Sub Updatetbl_data()
'
' Updatetbl_data Macro
' test
Sheets("Sheet2").Select
Dim cnn As ADODB.Connection
Dim uSQL As String

Set cnn = New Connection
    cnnstr = "Provider=SQLOLEDB; " & _  
    "Data Source=MySource; " & _  
    "Initial Catalog=MyDB;" & _
    "User ID=ID;" & _
    "Password=Pass;" & _
    "Trusted_Connection=No"
cnn.Open cnnstr
' New Update Statement idea based on possible solution found online
Dim row As Range
For Each row In [tbl_data].Rows
uSQL = "UPDATE tbl_data SET Column06 = '" & (row.Columns  (row.ListObject.ListColumns("Column06").Index).Value) & _
"' WHERE Column01 = '" & (row.Columns(row.ListObject.ListColumns ("Column01").Index).Value) & _
"' AND Column02 = '" & (row.Columns(row.ListObject.ListColumns("Column02").Index).Value) & "' "
'Debug.Print (uSQL)
cnn.Execute uSQL
Next

cnn.Close
Set cnn = Nothing
Exit Sub

'
End Sub

JonRo · Answer 1 · 2017-10-07T14:53:32.183

Perhaps row.Columns is not designed for what you want to achieve. You can give this link to another article on stackoverflow a look for some more information. Next, I made some changes to your code which might do the trick.


    ' ... ... ...
    Dim row As Range
    'For Each row In [tbl_data].Rows ==>> has to be replaced by
    'For Each row In [tbl_data] ==>> which returns all cells, perhaps better the following
    Const ColNbr_Column01 As Long = 1
    Const ColNbr_Column02 As Long = 2
    Const ColNbr_Column06 As Long = 6
    ' now, select only the first column of the range [tbl_data]
    For Each row In Range( _
                        [tbl_data].Cells(1, 1).Address, _
                        [tbl_data].Cells([tbl_data].Rows.Count, 1).Address)

    ' now, use offset to reach to the columns in the row
    uSQL = "UPDATE tbl_data SET Column06 = '" & row.Offset(0, ColNbr_Column06).Value & _
    "' WHERE Column01 = '" & row.Offset(0, ColNbr_Column01).Value & _
    "' AND Column02 = '" & row.Offset(0, ColNbr_Column02).Value & "' "
    'Debug.Print (uSQL)
    ' ... ... ...

JonRo, After I reduced the Column constants by 1 each, it worked, e.g. Col_Nbr__Coumn06 As Long = 5. Now I just need to figure out how to throw in an "IF not blank, Then exit for loop, and I should be good to go. — drkevorkiian, Oct 09 '17 at 20:49
Figured out my last challenge, it was easier than I was making it. I just had to modify the code slightly Const ColNbr_Column01 As Long = 0 Const ColNbr_Column02 As Long = 1 Const ColNbr_Column06 As Long = 5 ' now, select only the first column of the range [tbl_data] For Each row In Range( _ [tbl_data].Cells(1, 1).Address, _ [tbl_data].Cells([tbl_data].Rows.Count, 1).Address) If row.Offset(0, Column01).Value = "" Then Exit For ' now, use offset to reach to the columns in the row — drkevorkiian, Oct 09 '17 at 21:02

score 1 · Answer 2 · answered Oct 08 '17 at 12:04

This is the basic concept.

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Database;Data Source=Server_Name"



'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2017-10-08' WHERE EMPID = 1"

'Pass the SQL to the Command object
cmd.CommandText = strSQL


'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

VBA Update sql from Excel to MS SQL Server 2008 using ListColumns

2 Answers2