I've read through postStackoverflow 14814098 and would like to know (2) things.

1. Can you update MS SQL Tables from Excel by creating a string with an Update statement that refers to an Excel Table. Below is a rough idea in VBA of what I mean.

2. If you add the SQL Statement to the server, how do you call it from Excel using VBA?

Background: I'm attempting to pull a table from the MS SQL Server, Load results into Excel Sheet as an Excel Table where I can exit the sheet and Update all changes back to the server table.

• I set up a class and worksheet module to update the server after individual cells are changed in the worksheet, but now I would like to update all the changes at once. Is there a better way to go about getting the result?

Sub UpdateSqlWithExcelTableJoin()
    
    Dim cmd    As ADODB.Command
    Dim strSQL As String

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "DRIVER=SQL Server;SERVER=MYSERVERNAME;DATABASE=MYDATABASENAME;Trusted_Connection=Yes"
    Set cmd = New ADODB.Command
    
    cnn.Open
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText
    
    Call setString2

    cmd.CommandText = strSQLUpdate
    cmd.Execute
    cnn.Close

    Set cmd = Nothing
    Set cnn = Nothing
    
End Sub

    Sub setString2()
strSQLUpdate = _
    "Update test.profile " & vbNewLine & _
    "Set test.profile.Field = ExcelTable.Field " & vbNewLine & _
    "    test.profile.Profile_Name = ExcelTable.Profile_Name " & vbNewLine & _
    "From test.profile " & vbNewLine & _
    "INNER JOIN OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0;Database=C:\Users\USERNAME\ONEDRIVE - FOLDER\SQL_VBA_b.xlsm;', 'Select ID, Profile_Name' " & vbNewLine & _
    "From '[Sheet3$]') As ExcelTable " & vbNewLine & _
    "ON test.profile.ID = ExcelTable.ID " & vbNewLine & _
    "WHERE (test.profile.ID = ExcelTable.ID " & vbNewLine & _
    "    AND test.profile.Profile_Name = ExcelTable.Profile_Name)"
    
Debug.Print strSQLUpdate
End Sub

CodePudding user response：

I always found this to be easier to run this through an MS-Access connection than to connect directly to SQL Server.

1. Set up an Acess data base with two ODBC connections. A. Define the Excel data as a table to Access. B. Define the SQL Server table as a table to Access.
2. In Excel VBA change your ADOdb connection to connect to the Access db.
3. Now you can run a single update statement in ODBC SQL that looks like this: Update SQLServerTable Set SQLServerColumn = ExcelColumn From SQLServerTable S Inner Join ExcelTable E Where sqlServerkey = ExcelKey

This is more flexible than trying to do it directly because if the update relationship grows more complex you can always code the FROM clause as an Access saved query, and that is the only good way to do nested queries in ODBC (queries that use other queries in FROM).

CodePudding user response：

You can't use select at that position it will always take the whole sheet.

Also you are missing a comma after Field

Update test.profile 
Set test.profile.Field = ExcelTable.Field, 
    test.profile.Profile_Name = ExcelTable.Profile_Name 
From test.profile 
INNER JOIN OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0;Database=C:\Users\USERNAME\ONEDRIVE - FOLDER\SQL_VBA_b.xlsm;', [Sheet3$]) As ExcelTable 
ON test.profile.ID = ExcelTable.ID 
WHERE (test.profile.ID = ExcelTable.ID 
    AND test.profile.Profile_Name = ExcelTable.Profile_Name)

CodePudding user response：

1. ado to retrieve records from SQL
2. save excel file
3. add excel file and SQL Table in Access
4. create Update Query using linked tables (sql and excel)
5. Open workbook / update data / close workbook / run query.