Быстрое обновление данных Access с данными Excel с помощью Excel VBA

Вопрос:Под "быстрым" я имею в виду использование SQL-запроса UPDATE, а не цикл через каждый набор записей. Здесь Я нашел этот приятный запрос: ''Batch update (faster) strSQL = "UPDATE [;Database=c:DocsDBFrom.mdb;].Table1 t " _ & "INNER JOIN [Sheet7$] s " _ & "ON s.id=t.id " _ & "SET t.Field1=s.Field1 " _ & "WHERE s.Field1t.Field1 " cn.Execute strSQL

Вопрос:

Под “быстрым” я имею в виду использование SQL-запроса UPDATE, а не цикл через каждый набор записей.

Здесь Я нашел этот приятный запрос:

»Batch update (faster) strSQL = «UPDATE [;Database=c:DocsDBFrom.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:TempTomTom.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

Я получаю сообщение об ошибке Runtime Automation на cn.Execute strSQL, потому что я понимаю, что мой strSQL должен быть недействительным.

testSheet – это имя, имя листа и кодовое имя для листа.
ExternalData_1 – именованный диапазон.
testQuery – это имя запроса (просмотра) в Access, которое я хочу обновить.

Ответ №1

Я думаю, что вы ищете такой код:

Dim db As Object Dim engine As Object Set engine = CreateObject(«DAO.DBEngine.120») Set db = engine.OpenDatabase(«C:yourdatabase.accdb») Dim sql As String sql = «UPDATE AccTable AS acc » & _ » INNER JOIN (SELECT * FROM [NamedRange] IN «»C:yourexcelfile.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, но все же это не работает. И, насколько я знаю, это относится ко всем возможным подходам для связывания Excel-листа с таблицей доступа в одном выражении SQL.

В качестве workaoaund вы можете либо попробовать импортировать данные Excel в таблицу базы данных Access (я не знаю, работает ли это по-прежнему!), а затем связать две таблицы Access для обновления, или вам придется прибегать к циклизации и обновлению отдельных записей.

Оцените статью
Добавить комментарий