В Microsoft Office Access поддерживается выполнение SQL запросов к текущей базе данных из программного кода на VBA. Рассмотрим, как это можно использовать при разработке приложений баз данных.
Так как запрос выполняется к той же самой базе данный в которой выполняется модуль VBA, для выполнения SQL запроса нет необходимости работать со стандартными в подобных случаях интерфейсами ADO, ODBC и т.д. Всё можно сделать при помощи объектной модели Access.
Как известно SQL запросы можно условно разделить на две большие группы:
- Запросы, не возвращающие данных (INSERT, UPDATE, DELETE и т.д.);
- Запросы возвращающие данные (запросы на выборку (SELECT)).
Запросы, не возвращающие данных
Для выполнения запросов к текущей базе данных служит метод Execute объекта CurrentDb. Этот метод принимает в качестве параметра строку с SQL запросом, который необходимо выполнить.
Например:
|
1 2 3 |
Dim sql As String sql = "DELETE FROM Table1" CurrentDb.Execute sql |
Это простейший запрос. Но, как быть с запросами, которые манипулируют теми или иными данными?
Такие запросы тоже можно выполнить. Для этого нужно просто сформировать соответствующую строку.
Так, например, может быть реализовано добавление записей в таблицу:
|
1 2 3 4 5 6 7 |
Dim field1Value As String Dim field2Value As String Dim sql As String field1Value = "test1" field2Value = "test2" sql = "INSERT INTO Table1 (field1, field2) VALUES ('" & field1Value & "','" & field2Value & "')" CurrentDb.Execute sql |
А, так их обновление:
|
1 2 3 4 5 6 7 |
Dim id As Integer Dim field2Value As String Dim sql As String id = 1 field2Value = "test2 variant 2" sql = "UPDATE Table1 SET field2 = '" & field2Value & "' WHERE id =" & id CurrentDb.Execute sql |
Выполнение другие типов запросов не возвращающих данных осуществляется по аналогичным принципам.
Если вам необходимо запустить операционную систему с флешки, то необходимо знать как отключить secure boot , так как из-за него система может не запуститься. Это мы рассмотрели в отдельном материале.
Запросы на выборку
Метод Execute объекта CurrentDb не поддерживает выполнение запросов на выборку. Поэтому для них нужен другой подход.
Чтобы работать с выборкой данных необходимо воспользоваться объектом RecordSet, который можно создать при помощи метода OpenRecordSet объекта CurrentDb. Этот метод также принимает в качестве параметра строку с SQL запросом.
После создания RecordSet мы можем перебрать все записи, как это показано ниже.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
' Создаём RecordSet для запроса Dim sql As String sql = "SELECT * FROM Table1" Dim record_set As Recordset Set record_set = CurrentDb.OpenRecordset(sql) ' Перебираем одну за одной записи в ранее созданном Recordset Do Until record_set.EOF ' Выводим данные из записи в окно сообщения Dim recordString As String recordString = record_set("id").Value & " " & record_set("field1").Value & " " & record_set("field2").Value Dim dialog_result As Integer dialog_result = MsgBox(recordString, vbOKOnly, "Test") record_set.MoveNext Loop |
Помимо простого перебора доступны несколько видов поиска. Но в основном используются два.
Первый вид поиска реализован в виде методов FindFirst, FindLast, FindNext и FindPrevious объекта RecordSet.
Первые два устанавливают курсор на соответственно первую и последнюю записи, соответствующие условию поиска. Условие поиска передаётся в качестве параметра в виде строки. Само условие задаётся по аналогии с предложением WHERE в обычном SQL запросе.
Методы FindNext и FindPrevious устанавливают курсор на следующую и предыдущую записи в соответствии с условием поиска, если такие записи существуют. Условие поиска для этих методов задаётся также, как и для FindFirst и FindLast.
Обратите внимание, что эти четыре метода именно устанавливают указатель на нужную запись, а не производят фильтрацию выборки. Таким образом количество записей выбранных SQL запросом остаётся неизменным.
В следующем примере перебор записей начнётся с записи, у которой id равен 234, так как курсор был перемещён к ней до начала обхода набора записей в цикле.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
' Создаём RecordSet для запроса Dim sql As String sql = "SELECT * FROM Table1" Dim record_set As Recordset Set record_set = CurrentDb.OpenRecordset(sql) record_set.FindFirst = "id = 234" ' Перебираем одну за одной записи в ранее созданном Recordset Do Until record_set.EOF ' Выводим данные из записи в окно сообщения Dim recordString As String recordString = record_set("id").Value & " " & record_set("field1").Value & " " & record_set("field2").Value Dim dialog_result As Integer dialog_result = MsgBox(recordString, vbOKOnly, "Test") record_set.MoveNext Loop |
Второй вид поиска можно задействовать при помощи свойства Filter того же объекта RecordSet. Условие поиска здесь также задаётся аналогично предложению WHERE в обычном SQL запросе.
Отличие данного вида поиска от предыдущего состоит в том, что в наборе данных остаются только записи, соответствующие условию поиска. Если их нет, результирующий набор данных будет пуст.
К сожалению, отбор по свойству Filter не выполнятся автоматически. После изменения этого свойства. Необходимо создать новый объект RecordSet при помощи метода OpenRecordSet (без параметров), который уже будет содержать результаты отбора.
В приведённом ниже примере в RecordSet будет только одна запись (у которой id равен 234).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Dim sql As String sql = "SELECT * FROM Table1" Dim record_set As RecordSet Set record_set = CurrentDb.OpenRecordset(sql) record_set.Filter = "id = 2" Dim filtered_record_set As Recordset Set filtered_record_set = record_set.OpenRecordset Do Until filtered_record_set.EOF Dim filteredRecordString As String filteredRecordString = filtered_record_set("id").Value & " " & filtered_record_set("field1").Value & " " & filtered_record_set("field2").Value Dim filtered_dialog_result As Integer filtered_dialog_result = MsgBox(filteredRecordString, vbOKOnly, "Test") filtered_record_set.MoveNext Loop |
Как мы видим механизмы для работы с запросами на выборку в VBA не очень удобны. Но, при разработке баз данных Access со сложной бизнес-логикой или сложным интерфейсом пользователя нужно быть готовым их применить.
В принципе возможностей методов Execute и OpenRecordset объекта CurrentDb достаточно для практически любых манипуляций с базой данных, но всё же стоит применять их разумно не пренебрегая и «визуальными» средствами Access.
О уважаемый автор! Как тебе благодарен! Очень полезная статья. Выполняю тестовое задание, чтобы попасть в интересную мне компанию. На удивление заставили поработать с Access.