• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pushing data to DB via ODBC connection from excel


New Member
Hey ,
I have a requirement where I need to pull data via ODBC connection and which is having nearly 200 columns with few hundreds of row, after updating the rows in excel all records should be written back to table. May you suggest VBA code for same.


New Member
This is the code I am using, let me know if you need the file as well or will it work?

Sub VBA_SnowFlake_Connect()

Dim sSQLQry As String

Dim ReturnArray

Dim Conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sconnect As String

Set Conn = New ADODB.Connection

'You can provide the full path of your external file as shown below "ODBC; DSN=snf;Database=COMMON_DB_DEV;Schema=STAGE"

'DBPath ="C:\InputData.xlsx"

'---------------------------------- Connection String

'Change the DSN name and snowflake credentials

sconnect = "Provider=MSDASQL.1;DSN=DSN=snf;HDR=Yes';Database=xxx;Schema=xxx"

Conn.Open sconnect

'---------------------------------- SnowFlake Table Name

tblname = "yyy"

Set rs = New ADODB.Recordset

rs.ActiveConnection = Conn

'---------------------------------- Your SQL Statement


   With rs

        .Open tblname, Conn, 1, 3

        For n = 1 To 3


            .Fields("FROM_SOURCE") = Cells(n, "A")

            .Fields("ORDER_NUMBER") = Cells(n, "B")

            .Fields("ORDER_ITEM_POSITION") = Cells(n, "C")

            .Fields("ORDER_ITEM_KEY") = Cells(n, "D")

            .Fields("TEST_VARCHAR") = Cells(n, "E")

            .Fields("TEST_NUMBER") = Cells(n, "F")

            .Fields("TEST_DATE") = Cells(n, "G")

            .Fields("UNION_SOURCE_CAPTURE_TS") = Cells(n, "H")

            .Fields("ADJUSTED_TS") = Cells(n, "I")

            .Fields("ADJUSTMENT_STATUS") = Cells(n, "J")

            .Fields("ADJUSTMENT_COMMENTS") = Cells(n, "K")

            .Fields("ADJUSTMENT_USER") = Cells(n, "L")


        Next n


    End With


'---------------------------------- Close Recordset


'---------------------------------- Close Connection


End Sub

MOD EDIT: Added Code tag.


Excel Ninja
To update source table using ADO, I'd simply use INSERT INTO statement.

Ex: If you are adding values for all the column(s) into a table...
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
If you are only adding values to specific column(s) in a table...
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);
But since, MSDASQL is deprecated, I'd recommend using some other provider...

I'd recommend detailing out more info (such as version of SQL Server you are connecting to).

See below for detail on MSDASQL.

Note: Personally, I don't like pushing data from Excel to db using ADO. For data integrity and security, I prefer to export csv from Excel into designated network folder. Then use Job etc on server side to consume the data.