sql - 使用 Excel VBA 使用 Excel 数据快速更新 Access 数据

Question

“快速”是指使用 UPDATE SQL 查询，而不是遍历每个记录集。

Here 我发现了这个很好的查询:

''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

但是，此示例在从 Access VBA 连接时用于将数据从 Excel 提取到 Access。

在我的情况下，我需要从 Excel VBA 连接并使用来自同一个 Excel 文件(没有标题的命名范围)的数据更新 Access 数据。除了标题之外，数据具有完全相同的结构。

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

我现在的情况:

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 一定是无效的。
testSheet 是工作表的工作表名称和代码名称。 ExternalData_1 是命名范围。 testQuery 是 Access 中我要更新的查询( View )的名称。

Answer 1

我认为您正在寻找这样的代码:

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，这将引发错误消息 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.因为 Microsoft 已故意禁用此功能 出于安全原因。

而且，是的，这是无稽之谈，因为您甚至没有尝试更新 Excel 中的数据，但它仍然无法正常工作。据我所知，它适用于在单个 SQL 语句中将 Excel-Sheet 链接到 Access-Table 的所有可能方法。

作为一种解决方法，您可以尝试 进口 Excel 数据到 Access 数据库表(我不知道这是否仍然有效!)，然后链接两个 Access 表以进行更新，否则您将不得不求助于循环和更新单个记录。