1

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.

Community
  • 1
  • 1
ZygD
  • 22,092
  • 39
  • 79
  • 102
  • 1
    This is called a heterogenous query. Further to that it is also a 'client side' operation as both Access and Excel are client database systems. IMHO it might be possible but it'll be difficult to work out exactly how to do it, and it's unlikely to give any performance benefits over a cursor style approach. For example you could add a linked table called `[testSheet$ExternalData_1]` to your MS Access database but it's a very convoluted approach. – Nick.Mc Jun 22 '15 at 06:57
  • I do not see where you open the connection. With the newest Excel and Access you are better off with DAO. Here is a rough example http://stackoverflow.com/questions/15448338/attempt-to-connect-to-a-valid-database-from-outside-access-outlook-excel-using/15449542#15449542 I am not happy with the date. – Fionnuala Jun 22 '15 at 08:24
  • @Nick.McDermaid It is not so difficult to work out, Access and Excel play well together. – Fionnuala Jun 22 '15 at 08:26

1 Answers1

3

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.

PhilS
  • 1,634
  • 14
  • 23
  • Depends on the version of Office. – Fionnuala Jun 22 '15 at 10:13
  • What exactly depends on the version of Office? - Or rather how does it matter, unless you want to use an ancient version of Office that has not received any security update in years? – PhilS Jun 22 '15 at 10:23
  • DAO.DBEngine.36 is an old version. – Fionnuala Jun 22 '15 at 11:01
  • 1
    Thank you. You are right, as I use an AccDB-File and Excel 12.0 in the code, I absolutely should have used the DBEngine.120-ProgId. - It does not change the essence of the answer though. – PhilS Jun 22 '15 at 11:09
  • Do I understand it correctly that this piece of code would have worked many years before, but currently, as you say, there's NO WAY to update Access data with Excel data with a single SQL statement? – ZygD Jul 13 '15 at 23:00
  • 1
    @ZygD: Yes you understand correctly. At least I don't know any way to achieve this. – PhilS Jul 14 '15 at 10:11
  • 1
    Using ADODB and the Jet 4.0 provider seems to still work, although that won't support `.xlsx` and `.accdb`. Perhaps using DAO 3.6 might also work, although that would be useless for the OP, as he is using an `.accdb`. – Zev Spitz Sep 07 '15 at 15:21
  • 1
    Wouldn't it be enough to link to the Excel sheet from an Access database, and issue queries on the Access database? – Zev Spitz Sep 09 '15 at 23:27
  • 1
    Yes, with older versions of DAO/Access you can use the code from my answer. In some scenarios it would be enough to link to the Excel sheet, but you have got the exact same problem with updateability in newer versions of Access there. – PhilS Sep 11 '15 at 08:10