Выполнение SQL запросов к текущей базе данных в среде VBA (Access)

21.12.2018 Admin Комментировать

В Microsoft Office Access поддерживается выполнение SQL запросов к текущей базе данных из программного кода на VBA. Рассмотрим, как это можно использовать при разработке приложений баз данных.

Так как запрос выполняется к той же самой базе данный в которой выполняется модуль VBA, для выполнения SQL запроса нет необходимости работать со стандартными в подобных случаях интерфейсами ADO, ODBC и т.д. Всё можно сделать при помощи объектной модели Access.

Как известно SQL запросы можно условно разделить на две большие группы:

• Запросы, не возвращающие данных (INSERT, UPDATE, DELETE и т.д.);
• Запросы возвращающие данные (запросы на выборку (SELECT)).

Запросы, не возвращающие данных

Для выполнения запросов к текущей базе данных служит метод Execute объекта CurrentDb. Этот метод принимает в качестве параметра строку с SQL запросом, который необходимо выполнить.

Например:

 

Visual Basic

Dim sql As String sql = "DELETE FROM Table1" CurrentDb.Execute sql

 

 

Dim sql As String

sql = "DELETE FROM Table1"

CurrentDb.Execute sql

 

Это простейший запрос. Но, как быть с запросами, которые манипулируют теми или иными данными?

Такие запросы тоже можно выполнить. Для этого нужно просто сформировать соответствующую строку.

Так, например, может быть реализовано добавление записей в таблицу:

 

Visual Basic

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

 

 

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

 

А, так их обновление:

 

Visual Basic

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

 

 

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

 

Выполнение другие типов запросов не возвращающих данных осуществляется по аналогичным принципам.

Запросы на выборку

Метод Execute объекта CurrentDb не поддерживает выполнение запросов на выборку. Поэтому для них нужен другой подход.

Чтобы работать с выборкой данных необходимо воспользоваться объектом RecordSet, который можно создать при помощи метода OpenRecordSet объекта CurrentDb. Этот метод также принимает в качестве параметра строку с SQL запросом.

После создания RecordSet мы можем перебрать все записи, как это показано ниже.

 

Visual Basic

' Создаём 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

 

 

' Создаём 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, так как курсор был перемещён к ней до начала обхода набора записей в цикле.

 

Visual Basic

' Создаём 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

 

 

' Создаём 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).

 

Visual Basic

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

 

 

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

AccessVBAБазы данныхПрограммированиеСтатьи