• 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

    Hui...

  • 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

shantanu17

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.
 
This is the code I am using, let me know if you need the file as well or will it work?

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

            .AddNew

            .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")

            .update

        Next n

        .Close

    End With



 





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

rs.Close

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

Conn.Close



End Sub
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

MOD EDIT: Added Code tag.
 
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...
Code:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

If you are only adding values to specific column(s) in a table...
Code:
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.
https://blogs.msdn.microsoft.com/selvar/2007/11/10/msdasql-oledb-provider-for-odbc-drivers/

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.
 
Back
Top