Fast update of Access data with Excel data using Excel VBA

Question

By "fast" I mean using the UPDATE SQL query as opposed to looping through every recordset.

Here I found this nice query:

''Batch update (faster)
strSQL = "UPDATE [;Database=c:\Docs\DBFrom.mdb;].Table1 t " _
    & "INNER JOIN [Sheet7$] s " _
    & "ON s.id=t.id " _
    & "SET t.Field1=s.Field1 " _
    & "WHERE s.Field1<>t.Field1 "
cn.Execute strSQL

However, this example is used while connected from Access VBA to pull data from Excel to Access.

In my case I would need to connect from Excel VBA and using data from that same Excel file (named range without headers) update Access data. The data has exactly the same structure apart from headers.

I cannot seem to understand how to use this UPDATE method, as it uses INNER JOIN of tables which is one in Access and another in Excel. There is only one connection made (cn), so how can it read and join both tables? I guess that it doesn't need explicit connection to its own Access data, therefore there's only one connection made to Excel data. In my case I am in Excel, so I assume I would need to create 2 connections (to Access and to Excel, as Excel is not a DB)? Am I able to use this batch update method in my situation (I would add headers in Excel if it helped)?

My current situation:

Sub test_update()

Dim cn As Object    ''late binding - ADODB.Connection
Dim strSQL As String
Dim strFile As String
Dim strCon As String

Set cn = CreateObject("ADODB.Connection")

strFile = "C:\Temp\Tom\Tom.accdb"

''Consider HDR=Yes, so you can use the names in the first row of the set to refer to columns
''HDR=No;IMEX=1 - imex for mixed data types in a column
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";"
cn.Open strCon

''Batch update (fast)
strSQL = "UPDATE [;Database=" & strFile & ";].testQuery t " _
    & "INNER JOIN [testSheet$ExternalData_1] s " _
    & "ON s.ID=t.ID " _
    & "SET t.col1=s.F2 " _
    & "WHERE t.col1<>s.F2 "

cn.Execute strSQL


Set cn = Nothing

End Sub

I receive a Runtime Automation error on cn.Execute strSQL, because I understand my strSQL must be invalid.

testSheet is both, sheet name and code name for the sheet.
ExternalData_1 is the named range.
testQuery is the name of query (view) in Access that I want to update.

Answer 1

I think you are looking for code like this:

Dim db As Object
Dim engine As Object
Set engine = CreateObject("DAO.DBEngine.120")
Set db = engine.OpenDatabase("C:\your\database.accdb")

Dim sql As String
sql = "UPDATE AccTable  AS acc " & _
    " INNER JOIN (SELECT * FROM [NamedRange] IN ""C:\your\excel\file.xlsx"" ""Excel 12.0 xml;"" ) AS xls " & _
    " ON acc.ID = xls.ID " & _
    " Set acc.SomeField = xls.SomeField "

db.Execute sql

Unfortunately with all current versions of Access/DAO.DBEngine this will raise the error message You cannot edit this field because it resides in a linked Excel spreadsheet. The ability to edit data in a linked Excel spreadsheet has been disabled in this Access release. because Microsoft has deliberately disabled this feature for security reasons.

And, yes, this is nonsense, because you are not even trying to update the data in Excel, but still it does not work anymore. And as far as I know, it applies to all possible approaches to link an Excel-Sheet to an Access-Table in a single SQL statement.

As a workaroaund you could either try to import the Excel-data to an Access database table (I do not know if this still works!) and then link the two Access tables for an update, or you'll have to resort to the looping and updating single records.