none
Excel bidirectional data link to sql server. RRS feed

  • Question

  • Hello,

    I have an excel wookbook pulling data from an MSSQL server via office data connection. How can i push changes made to the pulled data back to the sql server to update the row? ie

    Tom opens excel workbook and the data refreshes, Tom updates the daily earnings field, which automatically updates the database server with the updated daily earnings data. 

    Thoughts?

    Thank you.

    **Sorry if I posted in the wrong area.

    Monday, March 2, 2015 3:50 PM

Answers

  • Hi,

    Are you talking about an Odata feed because that is not bidirectional, it's only one way. If you make a connection via odbc then you have to use pass through queries to get this done. In that case you can try using MS Query. Connections to Excel tend to be one way only.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    Monday, March 2, 2015 10:24 PM
  • You need to open an ADO connection to your SQL DB and then execute an "Insert Into" statement with a JetDB connection to your local data in the from clause. I know that sounds insanely complicated but it's really not. I'm posting from my phone. I'll post example code later tonite or tomorrow. You have to have write permissions for you SQL server. "Insert Into" will just create a new table on the server with your data. That's step one. Step two is to execute a Merge query on the SQL server to put your data into the database table. Merge let's you update existing records and add new ones. Once you get it set up, it works great, your user clicks a button to query the database for existing data, edits whatever, and then clicks another button to post the changes. Your macro will then copy up the whole table and then merge on the changes... I'll post example code later... J
    Tuesday, March 3, 2015 4:20 AM
  • Here's some sample code that pushes data from Excel to SQL Server.

    Sub Rectangle1_Click()
    'TRUSTED CONNECTION
        On Error GoTo errH
       
        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim strFirstName, strLastName As String
       
        Dim server, username, password, table, database As String
       
       
        With Sheets("Sheet1")
               
                server = .TextBox1.Text
                table = .TextBox4.Text
                database = .TextBox5.Text
               
               
                If con.State <> 1 Then
           
                    con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                    'con.Open
           
                End If
                'this is the TRUSTED connection string
               
                Set rs.ActiveConnection = con
               
                'delete all records first if checkbox checked
                If .CheckBox1 Then
                    con.Execute "delete from tbl_demo"
                End If
           
                'set first row with records to import
                'you could also just loop thru a range if you want.
                intImportRow = 10
               
                Do Until .Cells(intImportRow, 1) = ""
                    strFirstName = .Cells(intImportRow, 1)
                    strLastName = .Cells(intImportRow, 2)
                   
                    'insert row into database
                    con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"
                   
                    intImportRow = intImportRow + 1
                Loop
               
                MsgBox "Done importing", vbInformation
               
                con.Close
                Set con = Nothing
       
        End With
       
    Exit Sub
    
    errH:
        MsgBox Err.Description
    End Sub
    

    You can even use a Where clause if you want!!

    Sub UpdateTable()
    
        Dim cnn As Object
        Dim wbkOpen As Workbook
        Dim objfl As Variant
        Dim rngName As Range
        Workbooks.Open "C:\Users\Excel\Desktop\Excel_to_SQL_Server.xls"
        Set wbkOpen = ActiveWorkbook
        Sheets("Sheet1").Select
        Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown))
        rngName.Name = "TempRange"
        strFileName = wbkOpen.FullName
        Set cnn = CreateObject("ADODB.Connection")
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
        nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=Excel-PC\SQLEXPRESS;Database=[Northwind].[dbo].[TBL]]"
        nJOIN = " SELECT * from [TempRange]"
        cnn.Execute nSQL & nJOIN
        MsgBox "Uploaded Successfully"
        wbkOpen.Close
        Set wbkOpen = Nothing
        
    End Sub
    
    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 = "Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;"
    
    'Create a new Command object
    Set cmd = New adodb.Command
    
    'Open the connection
    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 = 2013-01-13 WHERE EMPID = 2"
    
    'Pass the SQL to the Command object
    cmd.CommandText = strSQL
    
    'Open the Connection to the database
    cnn.Open
    
    '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
    
    
    

    Finally, here's a link with a bunch of other samples that show how to move data from Excel to SQL Server, and from SQL Server to Excel.

    http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 6, 2015 2:27 PM

All replies

  • Hi,

    Are you talking about an Odata feed because that is not bidirectional, it's only one way. If you make a connection via odbc then you have to use pass through queries to get this done. In that case you can try using MS Query. Connections to Excel tend to be one way only.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    Monday, March 2, 2015 10:24 PM
  • You need to open an ADO connection to your SQL DB and then execute an "Insert Into" statement with a JetDB connection to your local data in the from clause. I know that sounds insanely complicated but it's really not. I'm posting from my phone. I'll post example code later tonite or tomorrow. You have to have write permissions for you SQL server. "Insert Into" will just create a new table on the server with your data. That's step one. Step two is to execute a Merge query on the SQL server to put your data into the database table. Merge let's you update existing records and add new ones. Once you get it set up, it works great, your user clicks a button to query the database for existing data, edits whatever, and then clicks another button to post the changes. Your macro will then copy up the whole table and then merge on the changes... I'll post example code later... J
    Tuesday, March 3, 2015 4:20 AM
  • Here's some sample code that pushes data from Excel to SQL Server.

    Sub Rectangle1_Click()
    'TRUSTED CONNECTION
        On Error GoTo errH
       
        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim strFirstName, strLastName As String
       
        Dim server, username, password, table, database As String
       
       
        With Sheets("Sheet1")
               
                server = .TextBox1.Text
                table = .TextBox4.Text
                database = .TextBox5.Text
               
               
                If con.State <> 1 Then
           
                    con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                    'con.Open
           
                End If
                'this is the TRUSTED connection string
               
                Set rs.ActiveConnection = con
               
                'delete all records first if checkbox checked
                If .CheckBox1 Then
                    con.Execute "delete from tbl_demo"
                End If
           
                'set first row with records to import
                'you could also just loop thru a range if you want.
                intImportRow = 10
               
                Do Until .Cells(intImportRow, 1) = ""
                    strFirstName = .Cells(intImportRow, 1)
                    strLastName = .Cells(intImportRow, 2)
                   
                    'insert row into database
                    con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"
                   
                    intImportRow = intImportRow + 1
                Loop
               
                MsgBox "Done importing", vbInformation
               
                con.Close
                Set con = Nothing
       
        End With
       
    Exit Sub
    
    errH:
        MsgBox Err.Description
    End Sub
    

    You can even use a Where clause if you want!!

    Sub UpdateTable()
    
        Dim cnn As Object
        Dim wbkOpen As Workbook
        Dim objfl As Variant
        Dim rngName As Range
        Workbooks.Open "C:\Users\Excel\Desktop\Excel_to_SQL_Server.xls"
        Set wbkOpen = ActiveWorkbook
        Sheets("Sheet1").Select
        Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown))
        rngName.Name = "TempRange"
        strFileName = wbkOpen.FullName
        Set cnn = CreateObject("ADODB.Connection")
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
        nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=Excel-PC\SQLEXPRESS;Database=[Northwind].[dbo].[TBL]]"
        nJOIN = " SELECT * from [TempRange]"
        cnn.Execute nSQL & nJOIN
        MsgBox "Uploaded Successfully"
        wbkOpen.Close
        Set wbkOpen = Nothing
        
    End Sub
    
    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 = "Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;"
    
    'Create a new Command object
    Set cmd = New adodb.Command
    
    'Open the connection
    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 = 2013-01-13 WHERE EMPID = 2"
    
    'Pass the SQL to the Command object
    cmd.CommandText = strSQL
    
    'Open the Connection to the database
    cnn.Open
    
    '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
    
    
    

    Finally, here's a link with a bunch of other samples that show how to move data from Excel to SQL Server, and from SQL Server to Excel.

    http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 6, 2015 2:27 PM