Green-sell.info

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

Access sql запрос с параметрами

Параметрические SQL запросы в СУБД Access

Параметрические SQL запросы или запросы с параметрами в СУБД Access 2003 и 2007

В данной статье рассмотрим параметрические SQL запросы или запросы с параметрами на выборку данных из таблиц БД Access. Для создания SQL запроса откроем базу данных sql_training_st1_calcul.mdb.

Известно, что для извлечения информации, хранящейся в базе данных БД Access 2003 или 2007, можно применить запрос SELECT на выборку данных из таблиц. Что касается запроса с параметрами, то он извлекает данные лишь в том случае, когда в диалоговом окне «Введите значение параметра» будет введено имя параметра отбора.

Составим параметрический SQL запрос (инструкцию SQL), для этого в открытой БД sql_training_st1_calcul.mdb на вкладке «Создание» выберем команду «Конструктор запросов». Откроется активное окно диалога «Добавление таблицы» на фоне неактивного окна «Запрос1», далее надо закрыть окно диалога «Добавление таблицы». Затем на контекстной вкладке «Конструктор» выберем режим SQL, выполнив команду SQL, в результате в окне редактирования будет отображаться оператор SELECT.

Вводим с клавиатуры следующую инструкцию SQL:
SELECT Группы.Название, Студенты.Фамилия, Дисциплины.Название, Успеваемость.Оценка
FROM (Группы INNER JOIN Студенты ON Группы.КодГруппы = Студенты.КодГруппы) INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины = Успеваемость.КодДисциплины) ON Студенты.КодСтудента = Успеваемость.КодСтудента
WHERE ((Студенты.Фамилия)=[Введите фамилию])
ORDER BY Студенты.Фамилия;

Эта инструкция состоит из четырех предложений «SELECT. . .», «FROM. . .», «WHERE. » и «ORDER BY. «.
Первое предложение содержит оператор SELECT и идентификатор «Группы.Название, Студенты.Фамилия, Дисциплины.Название, Успеваемость.Оценка». Выбор данных осуществляется из четырех полей четырех таблиц. Чтобы однозначно идентифицировать поля в запросе, перед именами полей указаны имена таблиц (например, Группы.Название).

Второе предложение содержит оператор FROM и идентификатор «(Группы INNER JOIN Студенты ON Группы.КодГруппы=Студенты.КодГруппы) INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины=Успеваемость.КодДисциплины) ON Студенты.КодСтудента=Успеваемость.КодСтудента».

FROM — определяет таблицы «Группы», «Студенты», «Дисциплины» и «Успеваемость», которые содержат поля, указанные в предложении SELECT, и обеспечивает взаимосвязи таблиц через ключевые поля таблиц с помощью конструкции INNER JOIN . ON. Следует отметить, что операторы: SELECT и FROM всегда присутствуют в запросах на выборку.

Третье предложение содержит оператор WHERE и идентификатор «((Студенты.Фамилия)=[Введите фамилию])», определяющий условия отбора.
Четвертое предложение содержит оператор ORDER BY и идентификатор «Студенты.Фамилия». ORDER BY не является обязательным оператором и применяется для сортировки выходных данных.

На рисунке 1 представлен скриншот параметрического SQL запроса на выборку данных, сохраненный с именем «Запрос_с_параметрами».

В результате выполнения команды «Сохранить» в «Области переходов» появится объект — «Запросы: Запрос_с_параметрами».

После сохранения параметрического SQL запроса на выборку данных необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить» (рис.2).

Для выполнения запроса введем имя параметра отбора (Воронина) в диалоговое окно «Введите значение параметра» и щелкнем на кнопке «OK».

Результаты выполнения команды «Выполнить» представлены на рис. 3.

Запросы с параметрами

Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы демонстрировали до сих пор, содержали конкретные значения дат, названий, имен и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором пользователь может ввести конкретное значение и затем получить нужный результат.

Покажем, как создавать запросы с параметрами на примере запроса «Отсортированный список товаров», который мы создавали ранее. Теперь мы с помощью этого запроса попробуем отобрать товары, поставляемые определенным поставщиком. Для этого:

  1. Откройте данный запрос в режиме Конструктора.
  2. Чтобы определить параметр запроса, введите в строку Условие отбора (Criteria) для столбца «Название» (CompanyName) вместо конкретного значения слово или фразу и заключите их в квадратные скобки, например [Поставщик:]. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.
  3. Если вы хотите, чтобы Access проверяла данные, вводимые в качестве параметра запроса, нужно указать тип данных для этого параметра. Обычно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text). Если же данные в поле запроса представляют собой даты или числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой кнопкой мыши на свободном поле в верхней части запроса и выберите из контекстного меню команду Параметры (Parameters) или выполните команду меню Запрос, Параметры (Query, Parameters). Появляется диалоговое окно Параметры запроса (Query Parameters), представленное на рис. 4.31.
Читать еще:  Майкрософт офис 365 что это

Рис. 4.31. Диалоговое окно Параметры запроса

  1. В столбец Параметр (Parameter) нужно ввести название параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования через буфер обмена), только можно не вводить квадратные скобки. В столбце Тип данных (Data Type) выберите из раскрывающегося списка необходимый тип данных. Нажмите кнопку ОК.
  2. Нажмите кнопку Запуск (Run) на панели инструментов, чтобы выполнить запрос. При выполнении запроса появляется диалоговое окно Введите значение параметра (Input Parameter Value) (рис. 4.32), в которое нужно ввести значение, например Tokyo Traders. Результат выполнения запроса представлен на рис. 4.33. В него попадают только те товары, которые поставляются данным поставщиком.

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

Рис. 4.32. Диалоговое окно Введите значение параметра

Рис. 4.33. Результат выполнения запроса с параметром

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Помимо простого перебора доступны несколько видов поиска. Но в основном используются два.

Первый вид поиска реализован в виде методов FindFirst, FindLast, FindNext и FindPrevious объекта RecordSet.

Первые два устанавливают курсор на соответственно первую и последнюю записи, соответствующие условию поиска. Условие поиска передаётся в качестве параметра в виде строки. Само условие задаётся по аналогии с предложением WHERE в обычном SQL запросе.

Методы FindNext и FindPrevious устанавливают курсор на следующую и предыдущую записи в соответствии с условием поиска, если такие записи существуют. Условие поиска для этих методов задаётся также, как и для FindFirst и FindLast.

Обратите внимание, что эти четыре метода именно устанавливают указатель на нужную запись, а не производят фильтрацию выборки. Таким образом количество записей выбранных SQL запросом остаётся неизменным.

В следующем примере перебор записей начнётся с записи, у которой id равен 234, так как курсор был перемещён к ней до начала обхода набора записей в цикле.

Как использовать параметры в VBA в разных контекстах Microsoft Access?

Я много читал о SQL-инъекции и использовании параметров из таких источников, как bobby-tables.com. Тем не менее, я работаю со сложным приложением в Access, у которого много динамического SQL с конкатенацией строк во всех местах.

Читать еще:  Как активировать офис 7

У меня есть следующие вещи, которые я хочу изменить, и добавьте параметры, чтобы избежать ошибок и разрешить мне обрабатывать имена с одинарными кавычками, такими как Jack O’Connel.

  • DoCmd.RunSQL выполнить команды SQL
  • Наборы DAO
  • Наборы ADODB
  • Формы и отчеты, открытые с помощью DoCmd.OpenForm и DoCmd.OpenReport , используя конкатенацию строк в аргументе WhereCondition
  • Агрегаты домена, такие как DLookUp , которые используют конкатенацию строк

Запросы в основном структурированы следующим образом:

Каковы мои параметры для использования параметров для этих разных видов запросов?

Этот вопрос предназначен как ресурс, для частых способов использования комментариев параметров для разных сообщений

sql vba access-vba ms-access

2 ответа

6 Решение Erik von Asmuth [2018-03-27 12:47:00]

Существует много способов использования параметров в запросах. Я попытаюсь предоставить примеры для большинства из них и где они применимы.

Во-первых, мы обсудим решения, уникальные для Access, такие как формы, отчеты и агрегации доменов. Затем мы поговорим о DAO и ADO.

Использование значений из форм и отчетов в качестве параметров

В Access вы можете напрямую использовать текущее значение элементов управления для форм и отчетов в вашем коде SQL. Это ограничивает необходимость в параметрах.

Вы можете обращаться к элементам управления следующим образом:

Forms!MyForm!MyTextbox для простого управления на форме

Forms!MyForm!MySubform.Form!MyTextbox для управления надстройкой

Reports!MyReport!MyTextbox для управления в отчете

Пример реализации:

Это доступно для следующих целей:

При использовании DoCmd.RunSQL , обычных запросов (в графическом интерфейсе), форм и источников отчетов, форм и отчетов, агрегатов доменов, DoCmd.OpenForm и DoCmd.OpenReport

Это недоступно для следующих целей:

При выполнении запросов с использованием DAO или ADODB (например, открытие наборов записей CurrentDb.Execute )

Использование TempVars в качестве параметров

TempVars in Access — глобально доступные переменные, которые могут быть установлены в VBA или с использованием макросов. Они могут использоваться повторно для нескольких запросов.

Пример реализации:

Доступность для TempVars идентична доступности значений из форм и отчетов: недоступна для ADO и DAO, доступных для других целей.

Я рекомендую TempVars для использования параметров при открытии форм или отчетов по ссылке на управляющие имена, так как если закрытие объекта закрывается, TempVars остаются доступными. Я рекомендую использовать уникальные имена TempVar для каждой формы или отчета, чтобы избежать странности при обновлении форм или отчетов.

Использование пользовательских функций (UDF) в качестве параметров

Как и TempVars, вы можете использовать пользовательскую функцию и статические переменные для хранения и получения значений.

Пример реализации:

Кроме того, для обеих настроек и получения значения частной статической переменной может быть создана одна функция с необязательным параметром:

Чтобы установить значение:

Чтобы получить значение:

Использование DoCmd.SetParameter

Использование DoCmd.SetParameter довольно ограничено, поэтому я буду краток. Он позволяет установить параметр для использования в DoCmd.OpenForm , DoCmd.OpenReport и некоторых других операторах DoCmd , но он не работает с DoCmd.RunSQL , фильтрами, DAO и ADO.

Пример реализации

Использование DAO

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

В моем примере я собираюсь использовать неявные типы параметров. Если вы хотите сделать их явными, добавьте PARAMETERS объявление в ваш запрос.

Пример реализации

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

Использование ADO

Вы можете использовать параметры в ADO с помощью объекта ADODB.Command . Используйте Command.CreateParameter для создания параметров, а затем добавьте их в коллекцию Command.Parameters . ADO требует, чтобы вы были более явными, чем любой из предыдущих способов использования параметров. Хотя некоторые аргументы (такие как длина) иногда могут быть опущены, я не рекомендую его.

Пример реализации:

Те же ограничения, что и открытые наборы DAO, применяются. Хотя этот способ ограничивается выполнением запросов и открытием наборов записей, вы можете использовать эти наборы записей в другом месте вашего приложения.

Читать еще:  Как проверить ключ офиса

Я построил довольно простой класс построителя запросов, чтобы обойти беспорядок конкатенации строк и обработать отсутствие именованных параметров. Создание запроса довольно просто.

Результат метода ToString() выглядит так:

SELECT * FROM tblEmployees ГДЕ 1 = 1 И (StartDate > # 3/29/2018 # ИЛИ StatusChangeDate > # 3/29/2018 #) И (StatusIndicator IN (‘A’, ‘L’) ИЛИ Grade > 10 ) И (Зарплатa > 9999.99) И (Пенсионный = Неверно) ЗАКАЗАТЬ ПО ID ASC;

Каждый предикат обернут в parens для обработки связанных предложений AND/OR, а параметры с тем же именем должны быть объявлены один раз. Полный код находится на моем github и воспроизведен ниже. У меня также есть версия для запросов Passthrough Oracle, в которых используются параметры ADODB. В конце концов, я хотел бы обернуть оба в интерфейсе IQueryBuilder.

Лекция по информатике на тему «Создание SQL-запросов»

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Создание SQL – запросов

Запрос представляет собой специальным образом сформулированное требование, определяющее состав производимых операций по выборке или изменению данных, хранящихся в таблицах. Для подготовки .запросов, как правило, используются два основных языка описания запросов:

• язык запросов по образцу QBE (Query By Example);

• структурированный язык запросов SQL (Structural Query Language).

Указанные языки обладают практически одинаковыми возможностями. Главное отличие между ними заключается в способе формулирования запросов: язык QBE предлагает ручное заполнение таблицы QBE, часто называемой бланком QBE или бланком запросов, в то время как использование SQL означает программирование запроса.

В Access могут быть созданы следующие типы запросов:

Запрос на выборку. Извлекает данные из одной или нескольких таблиц и отображает их в таблице.

Помимо этого, пользователю предоставляется возможность создавать дополнительное вычисляемое поле с данными, полученными в результате проведения математических или логических операций с другими полями, и вводить параметры (числа, тексты) запроса.

Запрос на изменение. Изменяет или перемещает данные. К этому типу относятся запросы на добавление или удаление записей, на создание или обновление таблицы.

Итоговый запрос. Служит для вычисления итоговых значений (сумма, среднее значение, количество по группам данных). Разновидностью итоговых запросов является перекрёстный запрос, позволяющий представить итоговые данные в виде таблицы, напоминающей электронную. Обычно перекрёстный запрос применяется к повторяющимся данным и связанных с ними датами, чтобы получить более наглядную, сгруппированную по интервалам времени (месяц, квартал) информацию.

Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access. Когда пользователь создает запрос в режиме Конструктора запроса, Microsoft Access автоматически создает эквивалентную инструкцию SQL. Пользователь имеет возможность просматривать и изменять инструкции SQL в режиме SQL. Изменения, внесенные в запрос в режиме SQL, приведут к соответствующим изменениям в режиме Конструктора и наоборот.

Некоторые запросы либо исключительно сложны, либо вообще не могут быть определены. Это запросы к серверу, управляющие запросы и запросы на объединение. Для создания таких запросов требуется ввести инструкцию SQL непосредственно в окно запросов в режиме SQL. Инструкции SQL могут быть использованы в Microsoft Access в тех ситуациях, когда требуется указать имя таблицы, запроса или поля. Ниже приведены основные обобщенные выражения и примеры SQL-запросов.

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

SELECT [ALL | DISTINCT] список полей

FROM имена таблиц

WRERE (критерий отбора)

ORDER BY столбцы сортировки [ASC | DESC]

Где SELECT — команда, определяющая запрос на выборку и содержащая спи-сок полей, в котором указываются поля, подлежащие выводу;

ALL,DISTINCT — предикаты, включающие все строки удовлетворяющие принятым условиям, исключающие строки с повторяющимися данными;

FROM_имена таблиц — определяет имена таблиц, у которых запрос должен отобрать данные.

WHERE — (критерий отбора) — определяет условия для отбора записей указанных таблиц;

ORDER BY столбцы_сортировки — определяет порядок сортировки записей по возрастанию (ASC) или убыванию (DESC); по умолчанию производится сортировка по возрастанию.

Пример 1. Запрос на выборку, позволяющий получить из таблицы СТУДЕНТ данные (таблицу с полями «ФИО», «Дата рождения», «Группа») о студентах мужского пола, родившихся до 1976 г.

SELECT СТУДЕНТ.ФИО,СТУДЕНТ.[Дата рождения],

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