Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel запрос в access

Как послать запрос к базе на VBA Access

Данный урок посвящен SQL запросам к базе данных на VBA Access. Мы рассмотрим, как на VBA осуществляется запросы INSERT, UPDATE, DELETE к базе данных, а также научимся получать конкретное значение из запроса SELECT.

Те, кто программируют на VBA Access и работая при этом с базой данных SQL сервера, очень часто сталкиваются с такой простой и нужной задачей как посыл SQL запроса к базе данных, будь то INSERT, UPDATE или простой SQL запрос SELECT. А так как мы начинающие программисты мы тоже должны уметь это делать, поэтому сегодня займемся именно этим.

Мы уже затрагивали тему получения данных с SQL сервера, где как раз на VBA писали код для получения этих данных, например в статье про Выгрузку данных в текстовый файл из MSSql 2008 или также немного затрагивали в материале Выгрузка данных из Access в шаблон Word и Excel, но так или иначе там мы рассматривали это поверхностно, а сегодня предлагаю поговорить об этом чуть более подробней.

Примечание! Все примеры ниже рассмотрены с использованием ADP проекта Access 2003 и базы данных MSSql 2008. Если Вы не знаете что вообще такое ADP проект то это мы рассматривали в материале Как создать и настроить ADP проект Access

Исходные данные для примеров

Допустим, у нас есть таблица test_table, которая будет содержать номера и названия месяцев в году (запросы выполнены с использованием Management Studio)

Как я уже сказал, мы будем использовать ADP проект, настроенный на работу с MS SQL 2008, в котором я создал тестовую форму и добавил кнопку start с подписью «Выполнить», которая нам понадобится для тестирования нашего кода, т.е. весь код мы будем писать в обработчике события «Нажатие кнопки».

Запросы к базе INSERT, UPDATE, DELETE на VBA

Чтобы долго не тянуть сразу приступим, допустим, нам нужно добавить строку в нашу тестовую таблицу (код прокомментирован)/

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

Читать еще:  Конвертер музыки из видео ютуб

Как видим, данные вставились.

Для того чтобы удалить одну строку пишем вот такой код.

Если мы проверим, то увидим, что нужная строка удалилась.

Для обновления данных записываем в переменную sql_query запрос update, надеюсь, смысл понятен.

Запрос SELECT к базе на VBA

Здесь дела обстоят чуть интересней, чем с остальными конструкциями SQL.

Первое, допустим, нам нужно получить все данные из таблицы, и, к примеру, мы их обработаем и выведем в сообщении, а Вы, конечно же, можете использовать их для других целей, для этого мы пишем вот такой код

Здесь мы уже используем циклы VBA Access для того чтобы перебрать все значения в нашем наборе записей.

Но, достаточно часто бывает необходимо получить не все значения из набора записей, а всего лишь одно, например, название месяца по его коду. И для этого использовать цикл как-то накладно, поэтому мы можем просто написать запрос, который вернет всего одно значение и обращаться именно к нему, например, получим название месяца по коду 5

Для универсальности здесь мы уже обратились не по имени ячейки, а по ее индексу, т.е. 0, а это самое первое значение в Recordset, в итоге мы получили значение «Май».

Как видите, все достаточно просто. Если Вам достаточно часто требуется получать конкретное значение из базы (как в последнем примере), то рекомендую вывести весь код в отдельную функцию (Как написать функцию на VBA Access 2003) с одним входящим параметром, например, код месяца (если рассматривать наш пример) и просто, где необходимо вывести это значение, вызывать нужную нам функцию с нужным параметром и все, этим мы значительно уменьшим код VBA и улучшим восприятие нашей программы.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую пройти наши курсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Канал в Telegram

Вы здесь

Импорт и Экспорт данных из mdb (Access) в Excel на VBA

Программные продукты MS Access и MS Excel относятся к одному пакету MS Office, но из-за лицензионных ограничений, не на все рабочие станции может быть установлен Access. Может возникнуть такая ситуация, что сотруднику, который работает только с Excel, потребуются некоторые данные, которые содержатся в базе Access. Как быть? Можно установить копию Access, но т.к. эта надобность может быть разовой или очень редкой, то приобретение лицензии экономически невыгодно. Можно попросить разработчика mdb создать отчет, который бы экспортировался в Excel. А можно, зная структуру таблиц БД Access, написать небольшой макрос (а можно и большой) который бы импортировал данные в книгу Excel и обрабатывал их особым образом. Есть еще один способ, это использовать инструменты Excel — «Импорт внешних данных«, но о нем в других статьях. А пока рассмотрим пример на VBA.

Читать еще:  Добавление в массив vba excel

Для импорта/экспорта будем использовать библиотеку MS DAO 3.6 Object Library, которая поставляется вместе с VBA. Включите ее в новом проекте. Для этого в редакторе VBA (Alt+F11) откройте Tools — References, найдите в списке «Microsoft DAO 3.6 Object Library» и поставьте галочку.

Например, у нас есть некая база данных комплектующих к ПК, прайс лист проще говоря. Таблица называется «tbl_прайс» и имеет следующую структуру:

    >Можете создать и наполнить данными базу mdb, а можете взять используемую базу в примерах ниже здесь.

Итак, база есть, например, нам необходимо полностью прочитать таблицу БД («tbl_прайс») и вывести результат на лист Excel. Cоздаем новый модуль и добавляем в него процедуру следующего содержания:

Sub ReadMDB()
‘переменная хранящая результат запроса
Dim tbl As Recordset
‘строка запроса SQL
Dim SQLr As String
‘переменная хранящая ссылку на подключенную БД
Dim dbs As Database

‘подключаемся к mdb
Set dbs = DAO.OpenDatabase(«E:price.mdb»)

‘составляем строку SQL запроса
SQLr = «SELECT * FROM tbl_прайс»

‘отправляем запрос открытой БД
‘результат в виде таблицы сохранен в tbl
Set tbl = dbs.OpenRecordset(SQLr)

‘вставляем результат в лист начиная с ячейки A1
Cells(1, 1).CopyFromRecordset tbl

‘Закрываем временную таблицу
tbl.Close

‘Очищаем память. Если этого не сделать, то таблица
‘так и останется висеть в оперативке.
Set tbl = Nothing

‘Закрываем базу
dbs.Close
Set dbs = Nothing
End Sub

Логика работы этой и всех последующих процедур чтения(записи) данных в БД проста. Сначала мы открываем БД, затем отправляем SQL запрос, получаем результат запроса в виде таблицы, закрываем БД, освобождаем память.

В данном варианте мы использовали метод CopyFromRecordset ячейки листа т.е. вставили результат запроса в лист так как есть, но что делать если результат нужно еще обработать некоторым образом который невозможно описать в запросе!? Ниже код демонстрирует построчное чтение результата запроса в цикле Do While (как работает цикл Do While описано в этой статье):

Читать еще:  Формула index excel

Sub ReadMDB_построчно()

Dim tbl As Recordset
Dim SQLr As String
Dim dbs As Database
Dim i As Integer

Set dbs = DAO.OpenDatabase(«E:price.mdb»)

SQLr = «SELECT * FROM tbl_прайс»
Set tbl = dbs.OpenRecordset(SQLr)
i = 1

‘выполняем цикл пока не конец tbl
Do While Not tbl.EOF
‘присваиваем каждой ячейке значение из полей таблицы
Cells(i, 1) = tbl.Fields(«ID»)
Cells(i, 2) = tbl.Fields(«Вид»)
Cells(i, 3) = tbl.Fields(«Производитель»)
Cells(i, 4) = tbl.Fields(«Модель»)
Cells(i, 5) = tbl.Fields(«Количество»)
Cells(i, 6) = tbl.Fields(«Цена»)
‘и для примера получим сумму (цена*кол-во)
Cells(i, 7) = tbl.Fields(«Количество») * tbl.Fields(«Цена»)

i = i + 1
tbl.MoveNext ‘переход к следующей записи

Loop

tbl.Close
Set tbl = Nothing

dbs.Close
Set dbs = Nothing
End Sub

Обратите внимание, второй вариант выводит результат на лист заметно медленнее, чем первый! Поэтому рекомендую по возможности использовать первый вариант.

Метод OpenRecordset позволяет только считывать данные из таблиц БД с помощью запросов. Для того чтобы выполнить запросы на изменение, добавление или удаление записей в таблицах используется метод Execute. Смотрим пример, который позволяет добавить запись в таблицу (при соответствующем SQL запросе можно изменить, удалить записи):

Sub ReadMDB_добавить_запись()

Dim tbl As Recordset
Dim SQLr As String
Dim dbs As Database
Dim kol As Long

Set dbs = DAO.OpenDatabase(«E:price.mdb»)

Set tbl = dbs.OpenRecordset(«tbl_прайс»)
‘метод RecordCount позволяет получить кол-во записей
‘Kol хранит ID для новой записи
kol = tbl.RecordCount + 1

SQLr = «INSERT INTO tbl_прайс (ID,Вид,Производитель, Модель,Количество, Цена)» _
& «Values (» & kol & «,’ОЗУ’,’Hyndai’, ‘DDR3’, 123, 600)»

tbl.Close
Set tbl = Nothing

dbs.Close
Set dbs = Nothing
End Sub

В этих примерах показаны основные моменты работы с БД mdb, которые помогут организовать обмен данными между Excel и Access, но эти способы не являются единственно верными и правильными. На этом все. До встреч!

Ссылка на основную публикацию
Adblock
detector