使用Excel VBA快速更新使用Excel数据的Access数据 [英] Fast update of Access data with Excel data using Excel VBA

问题描述

快我的意思是使用 UPDATE SQL查询，而不是循环遍历每个记录集。

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

然而，当从访问VBA 连接时，使用此示例将数据从Excel拉到Access。

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

在我的情况下，我需要从 Excel VBA 并使用来自同一Excel文件的数据（命名范围无标题）更新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.

我似乎无法理解如何使用这个 UPDATE 方法，因为它使用Access中的一个表和Excel中的另一个表的 INNER JOIN 。只有一个连接（ cn ），那么它如何读取和连接这两个表？我猜猜它不需要显式连接到自己的Access数据，因此只有一个连接到Excel数据。在我的情况下，我在Excel中，所以我想我需要创建2个连接（Access和Excel，因为Excel不是DB）？我可以在我的情况下使用这个批量更新方法（如果有帮助，我将在Excel中添加标题）？

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)?

我目前的情况：

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

我在 cn.Execute strSQL上收到运行时自动化错误，因为我明白我的 strSQL 必须无效。

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

testSheet 是表格的工作表名称和代码名称。

ExternalData_1 是命名范围。

testQuery 是我要更新的Access中的查询（视图）的名称。

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.

推荐答案

我想你正在寻找这样的代码：

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

不幸的是，Access / DAO.DBEngine的所有当前版本都会引发错误消息您无法编辑此字段，因为它驻留在链接的Excel电子表格中。 由于 Microsoft已故意禁用此功能，因此在此Access版电子表格中编辑数据的功能已被禁用。

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.

而且，是的，这是废话，因为你甚至没有尝试更新Excel中的数据，但仍然不再工作。据我所知，它适用于在单个SQL语句中将Excel表链接到Access-Table的所有可能方法。

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.

作为一个工作空间，您可以或者尝试将Excel数据导入到Access数据库表（我不知道这是否仍然有效），然后链接两个Access表进行更新，否则您将不得不诉诸于循环和更新单个记录。

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.

这篇关于使用Excel VBA快速更新使用Excel数据的Access数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！