Обновить данные в таблице Access на основе данных из Excel

@Марина020696 · Регистрация: 21.02.2015

Author24 — интернет-сервис помощи студентам

Добрый день, подскажите, пожалуйста, как решить задачу:
Есть книга Excel "C:\Data\Новые сотрудники.xls". В этой книге есть лист "output", с таблицей данных (первая строка - название полей).
Есть база Access "Сотрудники". В этой базе есть таблица "test" с данными.

Что надо сделать:
Необходимо создать макрос в Excel либо запрос SQL в Access, который бы вставлял данные из листа output, в таблицу "test", причем если в таблице "test" есть уже такие данные (определяется по четырем ключевым полям), то их необходимо заменить. Т.е. не должно быть дублированых записей в таблице "test". Этим макросом я буду пополнять ежедневно данные в таблице "test", либо заменять уже существующие (на верные или скорректированные).

В чем проблема:
Я не знаю как создать таблицу (объект?) с дынными из Excel, что бы:
a. проверить есть ли такие же записи в таблице "test" (если есть, то удалить их из "test" и залить заново с новыми данными)
b. Вставить их в access.

mobile · 31.07.2017, 21:54

Поскольку запрос к Excel необновляемый (патентные ограничения), то придется делать в 4 запроса:
1. удаление имеющейся временной таблицы
2. создание новой временной таблицы для импорта из Excel
3. обновление совпадающих по ключам записей
4. добавление новых в таблицу Test.

1. Запрос на удаление временной таблицы Temp

SQL    
        
    Скопировано
1
DROP TABLE Temp

2. Создание новой таблицы Temp из данных Excel

SQL    
        
    Скопировано
1
SELECT * INTO Temp FROM [output$] IN 'C:\Data\Новые сотрудники.xls'[excel 8.0; hdr=yes;]

3. запрос на обновление совпадающих по ключам записей

SQL    
        
            
                
                
                
                
            
        
    Скопировано
1
2
3
4
5
UPDATE test INNER JOIN Temp 
ON test.[ключполе1]=Temp.[ключполе1] AND test.[ключполе2]=Temp.[ключполе2] 
   AND test.[ключполе3]=Temp.[ключполе3] AND test.[ключполе4]=Temp.[ключполе4]
SET test.[обновлполе1]=Temp.[обновлполе1], test.[обновлполе2]=Temp.[обновлполе2], ...., 
    test.[обновлполеN]=Temp.[обновлполеN]

4. запрос на добавление новых в таблицу Test. Поскольку есть 4 ключевых поля по которым связываются таблицы, то если на этих полях задан составной индекс из всех 4 ключевых полей, то достаточно просто добавить инсертом данные в Test

SQL    
        
    Скопировано
1
2
3
INSERT INTO Test([ключполе1], [ключполе1], [ключполе1], [ключполе1], [обновлполе1], [обновлполе2], ..., [обновлполеN])
SELECT [ключполе1], [ключполе1], [ключполе1], [ключполе1], [обновлполе1], [обновлполе2], ..., [обновлполеN]
FROM Temp

Весь набор запросов легко вставить в процедуру и выполнять ее одним нажатием кнопки.

@Bercut33 · 01.02.2020, 19:25

Извините, но куда это всё вписывать, и как это будет выглядеть код в VBA по нажатию кнопки. Есть ли у кого пример.
Я что то поставлял в кнопку, вроде постарался адаптировать под свою задачу, но в итоге не вышло что-то.

@alvk · 02.02.2020, 03:31

На вопрос "куда это вставить?" обычно отвечают однозначно. Но не буду здесь озвучивать, думаю дойдёт.
Не извиню. Извиняются, это когда на ногу случайно наступил, а когда проявляют свою полную неграмотность в деле, за которое берутся, тогда извините не прокатит. Заплатите кому надо и вам всё сделают, либо учите хотя бы азы: сначала SQL, потом MS Access для чайников.
Я уже не буду говорить про море примеров на форуме с кодом sql в vba. И не только на форуме.
И уж тем более промолчу, что в сообщении mobile VBA даже не пахнет

@Silur · 02.02.2020, 08:31

Ну, можно, например, создать хранимые запросы и вызывать их из VBA командой CurrentDB.Execute
А вызов посадить на кнопку.

@Eugene-LS · 02.02.2020, 08:54

Сообщение от alvk

И уж тем более промолчу, что в сообщении mobile VBA даже не пахнет

Правильно!

Но так эти запросы надо как то запускать ....
Плюс потребуется:
01. Диалог открытия импортируемого файла ( на форуме их море)
02. Дополнительная предварительная обработка импортированных данных
...
всё на VBA !
...
А чего мы тут обсуждаем??? - ТС-а давно тут уже нет - ненужно уже ...

@alvk · 02.02.2020, 10:57

Сообщение от Eugene-LS

ТС-а давно тут уже нет

суток не прошло - это давно?

@amd48 · 04.02.2020, 11:41

Сообщение от alvk

суток не прошло

тема создана 31.07.2017, 21:38

@alvk · 04.02.2020, 13:05

Сообщение от amd48

тема создана

не важно когда, отвечаем уже другому ТС
Обновить данные в таблице Access на основе данных из Excel

@OrestBerserker · 30.11.2021, 18:29

Не хочу новую тему создавать.
У меня немного другая задача: вставить в таблицу MyTable только те строки из файла Excel, которых там нет.
Программа (Макрос1) ругается на синтаксис SQL, подозреваю, что неверно ссылаюсь на лист Excel.
...конечно, можно завести временную таблицу, туда вставить всё из Excel, а потом делать запрос на обновление, но неужели одним запросом обойтись нельзя?

@Eugene-LS · 30.11.2021, 20:23

Сообщение от OrestBerserker

Программа (Макрос1) ругается на синтаксис SQL, подозреваю, что неверно ссылаюсь на лист Excel.

Попробуйте:

Visual Basic    
        
            
                
                
                
                
            
        
    Скопировано
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub ww()
Dim sPth$, sSQL$ ', appE As Object
    
    sPth = "C:\Users\Roman\YandexDisk\ВДВ\UMO\ssvv.xlsx"
    'sPth = "d:\Temp\ssvv.xlsx"
    
    If Dir(sPth, vbNormal) = "" Then
        MsgBox "Файл:" & vbCrLf & sPth & vbCrLf & _
            "не обнаружен!", vbExclamation, "Внимание!"
        Exit Sub
    End If
    sSQL = "INSERT INTO MyTbl ( ExtId, NzvSpc, Spc, Spz, VUS, VSpc ) " & vbCrLf & _
            "SELECT Q01.id, [Название специализации], " & vbCrLf & _
            "Q01.Spc , Q01.Spz, Q01.VUS, Q01.VSpc " & vbCrLf & _
            "FROM (" & vbCrLf & _
            "   SELECT * FROM [VDV$] AS xlData " & vbCrLf & _
            "   IN '" & sPth & _
                "'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes] " & vbCrLf & _
            ") AS Q01 " & vbCrLf & _
            "LEFT JOIN MyTbl ON Q01.id = MyTbl.ExtId " & vbCrLf & _
            "WHERE (ExtId Is Null);"
 
    Debug.Print sSQL
    
    DoCmd.RunSQL sSQL
    'CurrentDb.Execute sSQL
End Sub

Или так:

Visual Basic    
        
            
                
                
                
                
            
        
    Скопировано
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub ww()
Dim sPth$, sSQL$ ', appE As Object
 
    sPth = "C:\Users\Roman\YandexDisk\ВДВ\UMO\ssvv.xlsx"
    'sPth = "d:\Temp\ssvv.xlsx"
 
    If Dir(sPth, vbNormal) = "" Then
        MsgBox "Файл:" & vbCrLf & sPth & vbCrLf & _
            "не обнаружен!", vbExclamation, "Внимание!"
        Exit Sub
    End If
  
    sSQL = "INSERT INTO MyTbl ( ExtId, NzvSpc, Spc, Spz, VUS, VSpc ) " & vbCrLf & _
        "SELECT id, [Название специализации], Spc, Spz, VUS, VSpc " & vbCrLf & _
        "FROM [VDV$] IN '" & sPth & _
            "'[Excel 12.0;HDR=Yes;Imex=1;] " & vbCrLf & _
        "WHERE (id Not In (SELECT ExtId FROM MyTbl));"
        
    Debug.Print sSQL
    DoCmd.RunSQL sSQL
End Sub

@OrestBerserker · 30.11.2021, 20:59

Сообщение от Eugene-LS

Попробуйте:

Не могу не отозваться, оба примера работают.
Ещё спасибо не только за помощь, но и за очередные примеры красивого кода.
Бесспорно, SQL запрос, разделенный & vbCrLf & в ImmadiateWindow смотрится куда привлекательнее, чем длинная строка.

@Eugene-LS · 30.11.2021, 23:09

Сообщение от OrestBerserker

за очередные примеры красивого кода.

Приятно читать.
Спасибо за добрые слова - Успехов!

shanemac51 · 01.12.2021, 10:41

Сообщение от OrestBerserker

за очередные примеры красивого кода

предпочитаю иную запись, без строк продолжения и с пробелом в начале подстроки
замена же позволяет явно видеть все апострофы

Visual Basic    
        
            
                
                
                
                
            
        
    Скопировано
1
2
3
4
5
6
s1 = "INSERT INTO MyTbl ( ExtId, NzvSpc, Spc, Spz, VUS, VSpc ) " 
   s1=s1 &   " SELECT id, [Название специализации], Spc, Spz, VUS, VSpc " 
   s1=s1 &   " FROM [VDV$] IN '%sPth'[Excel 12.0;HDR=Yes;Imex=1;] "
   s1=s1 &   " WHERE (id Not In (SELECT ExtId FROM MyTbl));"
  s2=replace(s1,%sPth,sPth)
docmd.runsql s2

@OrestBerserker · 01.12.2021, 11:00

shanemac51, вариант замены длинной строки на имя переменной действительно хорош, но имхо "собирание матрешки" с рекурсией (s1=s1 & bla-bla) порождает в Immadiate длинную строку, а разделители & vbCrLf & делают запрос во время отладки более читаемым.

@Capi · 01.12.2021, 11:10

Сообщение от shanemac51

предпочитаю иную запись

А для меня это совершенно нечитаемо.
Не хочу говорить "каша".

shanemac51 · 01.12.2021, 12:14

Сообщение от Capi

А для меня это совершенно нечитаемо

у каждого свои приоритеты в создании кода - для меня основой является читабельность, видимость всех апострофов/пробелов
пара лишних строк - не критичны

@Eugene-LS · 01.12.2021, 12:19

Сообщение от Capi

то совершенно нечитаемо.

... на вкус и цвет ...- ну вы знаете ...

Ку!

shanemac51 · 01.12.2021, 12:22

Сообщение от OrestBerserker

запрос во время отладки более читаемым

читабельность в коде у меня лучше, причем я обычно имею временный запрос, в который при отладке записываю полученную длинную строку

Visual Basic    
        
            
                
                
                
                
            
        
    Скопировано
1
2
3
4
5
6
7
   s1 = "INSERT INTO MyTbl ( ExtId, NzvSpc, Spc, Spz, VUS, VSpc ) " 
   s1=s1 &   " SELECT id, [Название специализации], Spc, Spz, VUS, VSpc " 
   s1=s1 &   " FROM [VDV$] IN '%sPth'[Excel 12.0;HDR=Yes;Imex=1;] "
   s1=s1 &   " WHERE (id Not In (SELECT ExtId FROM MyTbl));"
   s2=replace(s1,%sPth,sPth)
   querydefs("wrem").sql=s2   ''' для поиска сложных ошибок
   docmd.runsql s2

и при необходимости запускаю wrem для поиска ошибки, что бывает крайне редко

@Capi · 01.12.2021, 12:50

Сообщение от Eugene-LS

на вкус и цвет ...- ну вы знаете

Разумеется.
Я ведь и не навязываю.
Просто свое мнение высказываю.

И не говорю

Сообщение от shanemac51

у меня лучше

Новые блоги и статьи Все статьи Все блоги /
Результаты исследования от команды MCM (март 2025 г.) Programma_Boinc 07.04.2025 Результаты исследования от команды MCM (март 2025 г. ) В рамках наших текущих исследований мы продолжаем изучать гены, которые имеют наибольшую вероятность развития рака легких, выявленные в рамках. . .	Рекурсивные типы в Python py-thonny 07.04.2025 Рекурсивные типы - это типы данных, которые определяются через самих себя или в сочетании с другими типами, которые в свою очередь ссылаются на исходный тип. В мире программирования такие структуры. . .	C++26: Объединение и конкатенация последовательностей и диапазонов в std::ranges NullReferenced 07.04.2025 Работа с последовательностями данных – одна из фундаментальных задач, с которой сталкивается каждый разработчик. C++ прошел длинный путь в эволюции средств для манипуляции коллекциями – от. . .	Обмен данными в микросервисной архитектуре ArchitectMsa 06.04.2025 Когда разработчики начинают погружаться в мир микросервисов, они часто сталкиваются с парадоксальным правилом: "два сервиса не должны делить один источник данных". Эта мантра звучит повсюду в. . .	PostgreSQL в Kubernetes: Автоматизация обслуживания с CNPG Mr. Docker 06.04.2025 Администраторы баз данных сталкиваются с целым рядом проблем при обслуживании PostgreSQL в Kubernetes: как обеспечить правильную репликацию данных, как настроить автоматическое переключение при. . .
Async/await в TypeScript run.dev 06.04.2025 Асинхронное программирование — это подход к разработке программного обеспечения, при котором операции выполняются независимо друг от друга. В отличие от синхронного выполнения, где каждая последующая. . .	Многопоточность в C#: Синхронизация потоков UnmanagedCoder 06.04.2025 Многопоточное программирование стало неотъемлемой частью разработки современных приложений на C#. С появлением многоядерных процессоров возможность выполнять несколько задач параллельно значительно. . .	TypeScript: Классы и конструкторы run.dev 06.04.2025 TypeScript, как статически типизированный язык, построенный на основе JavaScript, привнес в веб-разработку новый уровень надежности и структурированности кода. Одним из важнейших элементов этой. . .	Многопоточное программирование: Rust против C++ golander 06.04.2025 C++ существует уже несколько десятилетий и его поддержка параллелизма постепенно наращивалась со временем. Начиная с C++11, язык получил стандартную библиотеку для работы с потоками, а в последующих. . .	std::vector в C++: от основ к оптимизации производительности NullReferenced 05.04.2025 Для многих программистов знакомство с std::vector происходит на ранних этапах изучения языка, но между базовым пониманием и подлинным мастерством лежит огромная дистанция. Контейнер std::vector. . .

@Марина020696 1 / 1 / 0 Регистрация: 21.02.2015 Сообщений: 54

	Обновить данные в таблице Access на основе данных из Excel 31.07.2017, 20:38. Показов 16010. Ответов 23 Метки нет (Все метки) Добрый день, подскажите, пожалуйста, как решить задачу: Есть книга Excel "C:\Data\Новые сотрудники.xls". В этой книге есть лист "output", с таблицей данных (первая строка - название полей). Есть база Access "Сотрудники". В этой базе есть таблица "test" с данными. Что надо сделать: Необходимо создать макрос в Excel либо запрос SQL в Access, который бы вставлял данные из листа output, в таблицу "test", причем если в таблице "test" есть уже такие данные (определяется по четырем ключевым полям), то их необходимо заменить. Т.е. не должно быть дублированых записей в таблице "test". Этим макросом я буду пополнять ежедневно данные в таблице "test", либо заменять уже существующие (на верные или скорректированные). В чем проблема: Я не знаю как создать таблицу (объект?) с дынными из Excel, что бы: a. проверить есть ли такие же записи в таблице "test" (если есть, то удалить их из "test" и залить заново с новыми данными) b. Вставить их в access. 0

@Bercut33 0 / 0 / 0 Регистрация: 13.02.2019 Сообщений: 15
	01.02.2020, 19:25
	Извините, но куда это всё вписывать, и как это будет выглядеть код в VBA по нажатию кнопки. Есть ли у кого пример. Я что то поставлял в кнопку, вроде постарался адаптировать под свою задачу, но в итоге не вышло что-то. 0

@alvk 7455 / 4588 / 302 Регистрация: 12.08.2011 Сообщений: 14,344
	02.02.2020, 03:31
	На вопрос "куда это вставить?" обычно отвечают однозначно. Но не буду здесь озвучивать, думаю дойдёт. Не извиню. Извиняются, это когда на ногу случайно наступил, а когда проявляют свою полную неграмотность в деле, за которое берутся, тогда извините не прокатит. Заплатите кому надо и вам всё сделают, либо учите хотя бы азы: сначала SQL, потом MS Access для чайников. Я уже не буду говорить про море примеров на форуме с кодом sql в vba. И не только на форуме. И уж тем более промолчу, что в сообщении mobile VBA даже не пахнет 0

@Silur 1342 / 263 / 16 Регистрация: 16.01.2014 Сообщений: 832
	02.02.2020, 08:31
	Ну, можно, например, создать хранимые запросы и вызывать их из VBA командой CurrentDB.Execute А вызов посадить на кнопку. 0

@Eugene-LS 11045 / 5683 / 1449 Регистрация: 05.10.2016 Сообщений: 16,072
	02.02.2020, 08:54
	Сообщение от alvk И уж тем более промолчу, что в сообщении mobile VBA даже не пахнет Правильно! Но так эти запросы надо как то запускать .... Плюс потребуется: 01. Диалог открытия импортируемого файла ( на форуме их море) 02. Дополнительная предварительная обработка импортированных данных ... всё на VBA ! ... А чего мы тут обсуждаем??? - ТС-а давно тут уже нет - ненужно уже ... 0

@alvk 7455 / 4588 / 302 Регистрация: 12.08.2011 Сообщений: 14,344
	02.02.2020, 10:57
	Сообщение от Eugene-LS ТС-а давно тут уже нет суток не прошло - это давно? 0

@amd48 842 / 473 / 79 Регистрация: 18.05.2016 Сообщений: 1,256 Записей в блоге: 5
	04.02.2020, 11:41
	Сообщение от alvk суток не прошло тема создана 31.07.2017, 21:38 0

@alvk 7455 / 4588 / 302 Регистрация: 12.08.2011 Сообщений: 14,344
	04.02.2020, 13:05
	Сообщение от amd48 тема создана не важно когда, отвечаем уже другому ТС Обновить данные в таблице Access на основе данных из Excel 0

@OrestBerserker 2 / 1 / 0 Регистрация: 27.01.2015 Сообщений: 179
	30.11.2021, 20:59
	Сообщение от Eugene-LS Попробуйте: Не могу не отозваться, оба примера работают. Ещё спасибо не только за помощь, но и за очередные примеры красивого кода. Бесспорно, SQL запрос, разделенный & vbCrLf & в ImmadiateWindow смотрится куда привлекательнее, чем длинная строка. 1

@Eugene-LS 11045 / 5683 / 1449 Регистрация: 05.10.2016 Сообщений: 16,072
	30.11.2021, 23:09
	Сообщение от OrestBerserker за очередные примеры красивого кода. Приятно читать. Спасибо за добрые слова - Успехов! 0

@OrestBerserker 2 / 1 / 0 Регистрация: 27.01.2015 Сообщений: 179
	01.12.2021, 11:00
	shanemac51, вариант замены длинной строки на имя переменной действительно хорош, но имхо "собирание матрешки" с рекурсией (s1=s1 & bla-bla) порождает в Immadiate длинную строку, а разделители & vbCrLf & делают запрос во время отладки более читаемым. 0

@Capi Модератор 6231 / 2909 / 707 Регистрация: 12.06.2016 Сообщений: 7,839
	01.12.2021, 11:10
	Сообщение от shanemac51 предпочитаю иную запись А для меня это совершенно нечитаемо. Не хочу говорить "каша". 0

shanemac51 Модератор 12158 / 5010 / 803 Регистрация: 07.08.2010 Сообщений: 14,770 Записей в блоге: 4
	01.12.2021, 12:14
	Сообщение от Capi А для меня это совершенно нечитаемо у каждого свои приоритеты в создании кода - для меня основой является читабельность, видимость всех апострофов/пробелов пара лишних строк - не критичны 0

@Eugene-LS 11045 / 5683 / 1449 Регистрация: 05.10.2016 Сообщений: 16,072
	01.12.2021, 12:19
	Сообщение от Capi то совершенно нечитаемо. ... на вкус и цвет ...- ну вы знаете ... Ку! 0

@Capi Модератор 6231 / 2909 / 707 Регистрация: 12.06.2016 Сообщений: 7,839
	01.12.2021, 12:50
	Сообщение от Eugene-LS на вкус и цвет ...- ну вы знаете Разумеется. Я ведь и не навязываю. Просто свое мнение высказываю. И не говорю Сообщение от shanemac51 у меня лучше 0