Обновить данные в таблице 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

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

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

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

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

   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

у меня лучше

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

@Марина020696 1 / 1 / 0 Регистрация: 21.02.2015 Сообщений: 54
		1
	Обновить данные в таблице Access на основе данных из Excel 31.07.2017, 20:38. Показов 15490. Ответов 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	3
	Извините, но куда это всё вписывать, и как это будет выглядеть код в VBA по нажатию кнопки. Есть ли у кого пример. Я что то поставлял в кнопку, вроде постарался адаптировать под свою задачу, но в итоге не вышло что-то. 0

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

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

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

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

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

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

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

@~~Eugene-LS~~ Заблокирован
	30.11.2021, 23:09	13
	Сообщение от OrestBerserker за очередные примеры красивого кода. Приятно читать. Спасибо за добрые слова - Успехов! 0

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

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

@~~Eugene-LS~~ Заблокирован
	01.12.2021, 12:19	18
	Сообщение от Capi то совершенно нечитаемо. ... на вкус и цвет ...- ну вы знаете ... Ку! 0

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