使用Excel VBA快速更新Access数据与Excel数据
“快”我的意思是使用UPDATE
SQL查询,而不是遍历每个记录集。使用Excel VBA快速更新Access数据与Excel数据
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
不过,在使用这个例子中,同时从访问VBA连接到从Excel提取数据访问。
对我来说,我需要从Excel的VBA连接,并使用来自同一Excel文件(命名范围没有头)更新访问数据。该数据有与标题完全相同的结构。
我似乎无法理解如何使用此UPDATE
方法,因为它使用Access中的一个表和Excel中的另一个表中的INNER JOIN
。只有一个连接(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中查询(查看)的名称。
我认为你正在寻找这样的代码:
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.
因为微软故意禁用此功能的安全,这将引发错误信息原因。
而且,是的,这是无稽之谈,因为你甚至没有试图更新Excel中的数据,但它仍然不起作用。据我所知,它适用于在单个SQL语句中将Excel表单链接到访问表的所有可能方法。
作为一个workaroaund,您可以尝试将导入 Excel数据到Access数据库表(我不知道这是否仍然有效!),然后链接两个Access表以进行更新,否则您将不得不求助于循环和更新单个记录。
这被称为异构查询。此外,它也是“客户端”操作,因为Access和Excel都是客户端数据库系统。恕我直言,这可能是可能的,但它将很难找出如何做到这一点,并且它不可能给游标式方法带来任何性能优势。例如,您可以将名为'[testSheet $ ExternalData_1]'的链接表添加到MS Access数据库中,但这是一个非常复杂的方法。 –
我没有看到您打开连接的位置。使用最新的Excel和Access,您最好使用DAO。这里是一个粗略的例子http://*.com/questions/15448338/attempt-to-connect-to-a-valid-database-from-outside-access-outlook-excel-using/15449542#15449542我不开心与日期。 – Fionnuala
@ Nick.McDermaid解决问题并不困难,Access和Excel可以很好地协同工作。 – Fionnuala