Как послать запрос к базе на 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)

   
   CREATE TABLE [dbo].[test_table](
        [id] [int] NOT NULL,
        [name_mon] [varchar](50) NULL
   ) ON [PRIMARY]
   GO

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

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

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

 
 Private Sub start_Click()
 'Объявляем переменную для хранения строки запроса
 Dim sql_query As String
 'Записываем в нее нужный нам запрос
 sql_query = "INSERT INTO test_table (id, name_mon) VALUES ('6', 'Июнь')"
 'Выполняем его
 DoCmd.RunSQL sql_query
 End Sub

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

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

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

 
 Private Sub start_Click()
 'Объявляем переменную для хранения строки запроса
 Dim sql_query As String
 'Записываем в нее запрос на удаление
 sql_query = "DELETE test_table WHERE id = 6"
 'Выполняем его
 DoCmd.RunSQL sql_query
 End Sub

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

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

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

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

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

 
 Private Sub start_Click()
 'Объявляем переменные
 'Для набора записей из базы
 Dim RS As ADODB.Recordset
 'Строка запроса
 Dim sql_query As String
 'Строка для вывода итоговых данных в сообщении
 Dim str As String
 'Создаем новый объект для записей
 set RS = New ADODB.Recordset
 'Строка запроса
 sql_query = "SELECT id, name_mon FROM test_table"
 'Выполняем запрос с использованием текущих настроек подключения проекта 
 RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 'Циклом перебираем записи
 While Not (RS.EOF)
        'Заполняем переменную для вывода сообщения
     str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline                      
 'переход к следующей записи                                
 RS.MoveNext
 Wend
 'Вывод сообщения
 msgbox str
 End Sub

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

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

 
 Private Sub start_Click()
 'Объявляем переменные
 'Для набора записей из базы
 Dim RS As ADODB.Recordset
 'Строка запроса
 Dim sql_query As String
 'Строка для вывода итогового значения
 Dim str As String
 'Создаем новый объект для записей
 set RS = New ADODB.Recordset
 'Строка запроса
 sql_query = "SELECT name_mon FROM test_table WHERE id = 5"
 'Выполняем запрос с использованием текущих настроек подключения проекта 
 RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 'Получаем наше значение
 str = RS.Fields(0)
 msgbox str
 End Sub

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

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

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

На сегодня это все. Удачи!