Sql запросы access vba: Создание инструкций SQL, которые включают переменные и элементы управления

Содержание

Создание инструкций SQL, которые включают переменные и элементы управления

Статья

04/07/2022

Чтение занимает 3 мин

При работе с объектами доступа к данным (DAO) или объектами данных ActiveX (ADO) может потребоваться создать инструкцию SQL в коде. Иногда это называется «встраиванием» кода SQL.

Например, если создается новый объект QueryDef, необходимо присвоить его свойству SQL допустимую строку SQL. Но если вы используете объект Recordset ADO, необходимо присвоить его свойству Source допустимую строку SQL.

Чтобы сформировать инструкцию SQL, создайте запрос на бланке запроса, переключитесь в режим SQL, скопируйте и вставьте соответствующую инструкцию SQL в код.

Часто требуется создавать запрос на основе значений, предоставляемых пользователем, или значений, изменяющихся в разных ситуациях. В этом случае в запрос нужно включить переменные или значения элементов управления. Ядро СУБД Access обрабатывает все инструкции SQL, но не переменные или элементы управления. Поэтому необходимо создавать инструкцию SQL таким образом, чтобы Access сначала определял эти значения и затем объединял их в инструкции SQL, которая передается ядру СУБД Access.

В приведенном ниже примере показано, как создать объект QueryDef с простой инструкцией SQL. Этот запрос возвращает все заказы из таблицы Orders (Заказы), размещенные после 31 марта 2006 г.

Public Sub GetOrders() 
 
   Dim dbs As DAO.Database 
   Dim qdf As DAO.QueryDef 
   Dim strSQL As String 
 
   Set dbs = CurrentDb 
   strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-31-2006#;" 
   Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL) 
 
End Sub

В следующем примере создается такой же объект QueryDef с помощью значения, хранящегося в переменной. Следует помнить, что знаки номера (#), обозначающие значения дат, необходимо включить в строку, чтобы они были объединены со значением даты.

Dim dbs As Database, qdf As QueryDef, strSQL As String 
Dim dteStart As Date 
dteStart = #3-31-2006# 
Set dbs = CurrentDb 
strSQL = "SELECT * FROM Orders WHERE OrderDate" _ 
    & "> #" & dteStart & "#;" 
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

В приведенном ниже примере создается объект QueryDef с помощью значения в элементе управления OrderDate (Дата_заказа) в форме Orders (Заказы). Помните, что нужно предоставить полную ссылку на элемент управления и включить знаки номера (#), обозначающие дату в строке.

Dim dbs As Database, qdf As QueryDef, strSQL As String 
Set dbs = CurrentDb 
strSQL = "SELECT * FROM Orders WHERE OrderDate" _ 
    & "> #" & Forms!Orders!OrderDate & "#;" 
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

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

В приведенном ниже примере кода показано, как создать объект QueryDef с простой инструкцией SQL. Этот запрос возвращает все заказы из таблицы Orders (Заказы), размещенные после 31 марта 2006 г.

Dim dbs As Database, qdf As QueryDef, strSQL As String 
Set dbs = CurrentDb 
strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-31-2006#;" 
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

Dim dbs As Database, qdf As QueryDef, strSQL As String 
Dim dteStart As Date 
dteStart = #3-31-2006# 
Set dbs = CurrentDb 
strSQL = "SELECT * FROM Orders WHERE OrderDate" _ 
    & "> #" & dteStart & "#;" 
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

В приведенном ниже примере кода создается объект QueryDef с помощью значения в элементе управления OrderDate (Дата_заказа) в форме Orders (Заказы). Помните, что он предоставляет полную ссылку на элемент управления и включает знаки номера, обозначающие дату в строке.

Dim dbs As Database, qdf As QueryDef, strSQL As String 
Set dbs = CurrentDb 
strSQL = "SELECT * FROM Orders WHERE OrderDate" _ 
    & "> #" & Forms!Orders!OrderDate & "#;" 
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Метод DoCmd.RunSQL (Доступ) | Microsoft Learn

Twitter

LinkedIn

Facebook

Адрес электронной почты

Статья

04/07/2022

Чтение занимает 2 мин

Метод RunSQL выполняет действие RunSQL в Visual Basic.

Синтаксис

выражения. RunSQL (SQLStatement, UseTransaction)

выражение: переменная, представляющая объект DoCmd.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
SQLStatement	Обязательный	Variant	Выражение строки, которое является допустимым SQL для запроса действий или запроса определения данных. Он использует ВСТАВКА, УДАЛЕНИЕ, ВЫБОР… INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX or DROP INDEX STATEMENT. Включай пункт IN, если вы хотите получить доступ к другой базе данных.
UseTransaction	Необязательный	Variant	Используйте True (1), чтобы включить этот запрос в транзакцию. Если вы не хотите использовать транзакцию, используйте false (0). Если оставить этот аргумент пустым, предполагается значение по умолчанию (True).

Используйте действие RunSQL для выполнения запроса действий Microsoft Access с помощью соответствующего SQL. Можно также выполнить запрос определения данных.

Этот метод применяется только к базам данных Access.

Максимальная длина аргумента SQLStatement — 32 768 символов (в отличие от аргумента действия SQLStatement в окне Макрос, максимальная длина которого составляет 256 символов).

Пример

В следующем примере обновляется таблица Сотрудников , меняя название каждого менеджера по продажам на региональный менеджер по продажам.

Public Sub DoSQL() 
 
    Dim SQL As String 
     
    SQL = "UPDATE Employees" & _ 
          "SET Employees.Title = 'Regional Sales Manager'" & _ 
          "WHERE Employees.Title = 'Sales Manager'" 
 
    DoCmd.RunSQL SQL 
     
End Sub

Поддержка и обратная связь

Метод DoCmd.RunSQL (доступ) | Microsoft Узнайте

Редактировать

Твиттер

Фейсбук

Эл. адрес

Статья
22.01.2022
2 минуты на чтение

Метод RunSQL выполняет действие RunSQL в Visual Basic.

Синтаксис

выражение . RunSQL ( SQLStatement , UseTransaction )

выражение Переменная, представляющая объект DoCmd .

Параметры

Имя	Требуется/дополнительно	Тип данных	Описание
SQLStatement	Обязательно	Вариант	Строковое выражение, являющееся допустимым оператором SQL для запроса действия или запроса определения данных. Он использует оператор INSERT INTO, DELETE, SELECT…INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX или DROP INDEX. Включите предложение IN, если вы хотите получить доступ к другой базе данных.
ИспользованиеТранзакции	Дополнительно	Вариант	Используйте True (1), чтобы включить этот запрос в транзакцию. Используйте False (0), если вы не хотите использовать транзакцию. Если оставить этот аргумент пустым, предполагается значение по умолчанию ( True ).

Используйте действие RunSQL для выполнения запроса действия Microsoft Access с помощью соответствующего оператора SQL. Вы также можете запустить запрос определения данных.

Этот метод применим только к базам данных Access.

Максимальная длина аргумента SQLStatement составляет 32 768 символов (в отличие от аргумента действия SQLStatement в окне макроса, максимальная длина которого составляет 256 символов).

Пример

В следующем примере обновляется таблица «Сотрудники «, меняя должность каждого менеджера по продажам на «Региональный менеджер по продажам».

 Общедоступная подпрограмма DoSQL()
 
    Dim SQL как строка
     
    SQL = «ОБНОВЛЕНИЕ сотрудников» и _
          "SET Employees.Title = "Региональный менеджер по продажам"" & _
          "ГДЕ Сотрудники. Должность = "Менеджер по продажам""
 
    DoCmd.RunSQL SQL
     
Конец сабвуфера

Поддержка и отзывы

У вас есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы Office VBA, чтобы узнать, как вы можете получить поддержку и оставить отзыв.

Советы и приемы запросов Microsoft Access с SQL и кодом VBA

Самые простые запросы Select извлекают указанные вами записи из таблицы. Вы можете выбрать поля из таблицы для отображения,
и указать критерии отбора записей. В большинстве случаев при просмотре результатов запроса вы можете изменить
данные и обновить исходные записи. Эти обновляемые представления чрезвычайно эффективны.

Выбор таблицы и полей

Первым шагом в создании запроса является указание используемой таблицы или таблиц и отображаемых полей. Выбор столов прост.
Просто выберите таблицу из списка при первом создании запроса или используйте команду «Добавить таблицу» из окна «Запрос».
меню. Выбранная таблица помещается в верхнюю часть окна конструктора запросов. Оттуда вы можете выбрать поля для запроса
дважды щелкнув по ним или выбрав несколько полей (используя Shift-Click или Ctrl-Click) и перетащив их в
нижняя часть: сетка запросов по примерам (QBE). Убедитесь, что опция Показать отмечена, чтобы отобразить поле.

Сортировка и изменение порядка полей

После размещения полей в сетке QBE вы можете изменить порядок полей, щелкнув столбец и перетащив его в нужное место.
Чтобы отсортировать результаты, укажите параметр «Сортировка» под полями для сортировки. Вы можете выбрать По возрастанию или
В порядке убывания. Обратите внимание, что вы можете отключить параметр «Показать» и выполнить сортировку по полю, которое не отображается на дисплее.

Переименование полей

Очень приятной особенностью запросов Microsoft Access является возможность переименовывать поля. Ваши данные могут храниться в именах полей, которые
не легко понять пользователям. Используя выражение запроса, вы можете изменить имя поля, которое видит пользователь.
Например, поле с именем «CustID» можно изменить на «Customer ID», поместив новое имя, за которым следует двоеточие и
исходное имя в ячейке поля QBE: Customer ID:[CustID].

Использование вычисляемых полей (выражений)

Помимо извлечения полей из таблицы, запрос Select также может отображать вычисления (выражения).
Конечно, выражения не могут быть обновлены, так как они не существуют в исходной таблице. Выражения чрезвычайно эффективны и позволяют вам
легко отображать сложные расчеты. Существует построитель выражений, упрощающий выбор полей и функций. По умолчанию,
поля выражений называются «Expr1», «Expr2» и т. д.; поэтому вы обычно хотите переименовать их во что-то более понятное.

Поля выражений также полезны для сортировки записей. Вот пример использования вычисляемого поля для
Сортировка по нескольким полям даты (или числовым) с пустыми значениями в запросе Microsoft Access.

Настройка свойств запроса

При разработке запроса можно выбрать Вид | Свойства или щелкните правой кнопкой мыши верхнюю часть запроса и выберите Свойства, чтобы просмотреть и изменить свойства запроса.

Описание

Это свойство позволяет предоставить описание запроса, чтобы помочь вам вспомнить его назначение.

Представление по умолчанию

Показать результаты в виде таблицы, сводной диаграммы или сводной таблицы.

Вывод всех полей

Для этого параметра обычно установлено значение Нет. Если изменить его на Да, будут показаны все поля всех таблиц в запросе.
Как правило, вы должны оставить это свойство в покое и указать нужные поля в сетке QBE.

Верхние значения

Вместо извлечения всех записей можно указать первые n записей или n процентов, где n — указанное здесь значение.

Уникальные значения

По умолчанию установлено значение Нет, и извлекаются все записи. Если это изменить на Да, каждая извлекаемая запись будет содержать уникальные значения.
(SQL использует команду SELECT DISTINCT). То есть нет идентичных извлеченных записей.

Например, вы можете выполнить запрос для поля «Состояние» таблицы «Пациент». Если для этого параметра установлено значение «Нет», результатом будет запись для каждого пациента.
Если установлено значение Да, отображается только список уникальных состояний. Если задано значение Да, запрос не подлежит обновлению.

Уникальные записи

По умолчанию установлено значение Нет, и извлекаются все записи. Для запросов к одной таблице это свойство игнорируется.

Для многотабличных запросов, если установлено значение Да (аналогично использованию DISTINCTROW в операторе SQL), извлекаются только уникальные записи в базовых таблицах.

Свойства Уникальные записи и Уникальные значения связаны, и только одно из них может иметь значение Да (оба могут быть Нет).
Если для уникальных записей задано значение «Да», для параметра «Уникальные значения» автоматически устанавливается значение «Нет». Если для обоих свойств задано значение «Нет», возвращаются все записи.

Разница между DISTINCT и DISTINCTROW

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

DISTINCT проверяет результаты запроса и удаляет повторяющиеся строки. Эти запросы (уникальные значения = Да) не подлежат обновлению.
Они представляют собой моментальный снимок ваших данных и не отражают последующие изменения данных пользователями. Это похоже на
запуск итогового запроса (например, с использованием предложения Group By).

DISTINCTROW проверяет все поля в таблице, а затем удаляет повторяющиеся строки.
Результаты запроса с DISTINCTROW (Unique Records = Yes) могут быть обновлены и отражают изменения в извлеченных записях (но запрос не
автоматически запускаться снова, если данные изменяются для извлечения других строк).

Разница в том, что DISTINCT проверяет только поля в результатах, а DISTINCTROW проверяет все поля в базовых таблицах.
Если ваш запрос объединяет несколько таблиц и отображает записи только из одной, параметр DISTINCTROW позволяет просматривать и редактировать результаты.

Дополнительные сведения см. на странице Distinct и DistinctRow в запросах Microsoft Access.

Необновляемые запросы

Некоторые запросы не подлежат обновлению. Для получения дополнительной информации прочитайте нашу статью, посвященную предупреждающему сообщению, которое вы видите:
Этот набор записей не подлежит обновлению: работа с необновляемыми запросами в Microsoft Access

Свойства SQL Server

Существует несколько свойств, связанных с таблицами SQL Server, которые носят более технический характер и редко нуждаются в изменении.
Для получения дополнительной информации обратитесь к интерактивной справочной системе Microsoft Access.

Фильтровать, Упорядочивать, Фильтровать при загрузке, Упорядочивать при загрузке

Как и в форме, для запроса можно указать параметры Фильтр и Упорядочить. Однако обычно это часть оператора SQL запроса.
При использовании свойств «Фильтровать» и «Упорядочивать по» у вас есть дополнительное преимущество, заключающееся в указании свойств «Фильтровать при загрузке» и «Упорядочивать при загрузке», чтобы применять их или нет.

Имя подтаблицы, поля связи и размер

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

Настройка свойств поля

В дополнение к свойствам запроса каждое поле также имеет свойства, которые можно установить. Перейдите к полю в сетке QBE и щелкните правой кнопкой мыши.
В зависимости от типа поля доступны различные свойства. Наиболее важные свойства относятся к числовым полям и полям даты.
Вы можете указать, как форматируются поля при выполнении запроса.