Как послать запрос к базе на 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)
Как я уже сказал, мы будем использовать ADP проект, настроенный на работу с MS SQL 2008, в котором я создал тестовую форму и добавил кнопку start с подписью «Выполнить», которая нам понадобится для тестирования нашего кода, т.е. весь код мы будем писать в обработчике события «Нажатие кнопки».
Запросы к базе INSERT, UPDATE, DELETE на VBA
Чтобы долго не тянуть сразу приступим, допустим, нам нужно добавить строку в нашу тестовую таблицу (код прокомментирован)/
В данном случае запрос выполняется с использованием текущих параметров подключения к базе данных. Можем проверить, данные добавились или нет.
Как видим, данные вставились.
Для того чтобы удалить одну строку пишем вот такой код.
Если мы проверим, то увидим, что нужная строка удалилась.
Для обновления данных записываем в переменную sql_query запрос update, надеюсь, смысл понятен.
Запрос SELECT к базе на VBA
Здесь дела обстоят чуть интересней, чем с остальными конструкциями SQL.
Первое, допустим, нам нужно получить все данные из таблицы, и, к примеру, мы их обработаем и выведем в сообщении, а Вы, конечно же, можете использовать их для других целей, для этого мы пишем вот такой код
Здесь мы уже используем циклы VBA Access для того чтобы перебрать все значения в нашем наборе записей.
Но, достаточно часто бывает необходимо получить не все значения из набора записей, а всего лишь одно, например, название месяца по его коду. И для этого использовать цикл как-то накладно, поэтому мы можем просто написать запрос, который вернет всего одно значение и обращаться именно к нему, например, получим название месяца по коду 5
Для универсальности здесь мы уже обратились не по имени ячейки, а по ее индексу, т.е. 0, а это самое первое значение в Recordset, в итоге мы получили значение «Май».
Как видите, все достаточно просто. Если Вам достаточно часто требуется получать конкретное значение из базы (как в последнем примере), то рекомендую вывести весь код в отдельную функцию (Как написать функцию на VBA Access 2003) с одним входящим параметром, например, код месяца (если рассматривать наш пример) и просто, где необходимо вывести это значение, вызывать нужную нам функцию с нужным параметром и все, этим мы значительно уменьшим код VBA и улучшим восприятие нашей программы.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Источник
Джентльменский набор разработчика Access
За время работы разработчиком Access набралась куча полезностей, которую считаю своим долгом выложить на Хабр. Многие из этих сниппетов находятся в сети, а некоторые находятся с трудом или безнадежно затеряны.
1. При работе с Access во время выполнения запроса возникают предупреждающие сообщения. Они довольно полезны во время отладки приложения, но для пользователей, как правило, не нужны. Отключить/включить их можно с помощью небольшого кода VBA:
Указав в виде параметра 0 для отключения и 1 для включения.
2. Для того, чтобы защитить базу данных от посторонних глаз и разграничить права доступа используется файл рабочей группы ( формата .MDW ). Путь к файлу рабочей группы можно указать вручную, но если в сети много пользователей, то гораздо удобнее иметь файлик Access с кнопкой, по нажатию которой отрабатывает одна строка кода, задающая путь к MDW:
3. Не знаю, как бы я работал (наверное, работал бы так же, но гораздо медленнее), если бы не создал себе форму с поиском текста в содержимом запросов или форм. Очень часто необходимо производить какой-то рефакторинг или определять область применения таблицы или поля.
Для поиска по запросам поможет форма с кодом, который выполняет поиск в тексте запросов:
Для поиска по формам код немного объемнее:
4. Для того, чтобы сделать нашу работу чуть более солидной с точки зрения программирования и для возможности поиска ошибок при работе в режиме production на рабочей базе данных очень желательно добавить модуль VBA (tracing модуль) для записи происходящих событий в текстовый файл лога. Простая функция записи в текстовый файл будет очень полезна при отладке.
5. Этот код (из пункта 4) вполне можно вынести в отдельный файл базы данных Access и добавить во все существующие базы данных через References/Ссылки редактора VBA.
Если у вас имеется несколько файлов баз данных Access, то любой повторяющийся код можно вынести в отдельный файл. Единственное изменение, которое может быть необходимо сделать — в случае если в коде используется объект CurrentDb, то заменить его на CodeDb, дабы обращение шло к объектам той базы, которая используется в качестве хранителя общего кода.
6. Зачастую в запросах указывают в качестве параметра значение какого-либо поля открытой формы. Например, таким образом:
Но иногда необходимо указать параметр непосредственно в самом запросе. Сделать это можно так:
И далее из кода Access задать эти параметры и выполнить запрос:
Альтернативой может быть создание модуля VBA и добавление в него глобальной переменной, а также функции, возвращающей эту переменную.
Перед запуском запроса необходимо задать значение глобальной переменной (можно задать при открытии основной формы):
И в самом запросе указать параметром название возвращающей значение функции:
6.1 Этот способ получения параметра запроса можно использовать для частичного ограничения доступа к информации таблицы (в случае использования рабочей группы). При загрузке формы в зависимости от текущего пользователя установим значение глобальной переменной:
Далее установим запрет на просмотр и изменение таблицы demotable, а на запрос установим разрешение. Но так как запрос у нас использует таблицу, на которую нет прав, то данных он нам не вернет. Для того, чтобы он вернул данные необходимо к sql запроса добавить в конце
В результате пользователь Buh сможет иметь доступ ко всем строкам таблицы, а все остальные пользователи ко всем строкам за исключением первой 1000.
7. Для того, чтобы открыть форму из кода используется код:
В качестве «WhereCondition» можно указать условие, на какой записи формы необходимо её открыть (в случае, если форма привязана к данным). Указав, например, «ZakazID=56325», можно открыть форму именно со значением данных ZakazID равным 56325.
В качестве значения «OpenArgs» можно указать какие-либо параметры, которые на открываемой форме можно будет считать
в Private Sub Form_Load() с помощью Me.OpenArgs. Если необходимо передать несколько параметров, то можно передать их в виде текстовой строки, разделив символом. И далее в Private Sub Form_Load() разбить на части:
8. Многие забывают, что Access работает не только таблицами из файлов mdb/accdb, но и с таблицами других баз данных. Для экспорта уже существующих таблиц есть бесплатная утилита SQL Server Migration Assistant, хотя можно воспользоваться и встроенным функционалом или найти стороннее решение.
И напоследок небольшой хинт из разряда «Это знает каждый ребенок, но я вот этого не знал…»:
Знаете ли вы, что для того, чтобы при открытии файла Access не срабатывали макросы и не открывалась форма по умолчанию, необходимо держать нажатой клавишу Shift?
Источник
Инструкция CREATE TABLE (Microsoft Access SQL)
Область применения: Access 2013, Office 2013
Ядро СУБД Microsoft Access не поддерживает использование CREATE TABLE или любых других инструкций DDL с базами данных не на основе ядра СУБД Microsoft Access. Используйте вместо этого методы DAO Create.
Синтаксис
CREATE [TEMPORARY] TABLE таблица (поле1 тип [(размер)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [индекс1] [, поле2 тип [(размер)] [NOT NULL] [индекс2] [, …]] [, CONSTRAINT индекс_набора_полей [, …]])
Инструкция CREATE TABLE включает в себя следующие элементы:
Имя таблицы, которую требуется создать.
Имена полей, которые создаются в новой таблице. Необходимо создать хотя бы одно поле.
Тип данных поля в новой таблице.
Размер поля в знаках (только для полей с типом данных TEXT и BINARY).
Предложение CONSTRAINT, определяющее индекс по одному полю. Дополнительные сведения о создании этого индекса см. в статье, посвященной предложению CONSTRAINT.
Предложение CONSTRAINT, определяющее индекс по нескольким полям. Дополнительные сведения о создании этого индекса см. в статье, посвященной предложению CONSTRAINT.
Примечания
Используйте инструкцию CREATE TABLE, чтобы определить новую таблицу, поля и ограничения полей. Если для поля определено свойство NOT NULL, поле обязательно должно содержать допустимые данные.
Предложение CONSTRAINT накладывает на поле различные ограничения, и с помощью него можно задать первичный ключ. Для создания первичного ключа или дополнительных индексов в существующих таблицах можно использовать инструкцию CREATE INDEX.
Свойство NOT NULL можно задавать для одного поля или внутри именованного предложения CONSTRAINT для одного или нескольких полей. Свойство NOT NULL для поля можно задать только один раз. Попытка определить это свойство повторно приведет к ошибке выполнения.
Таблица, созданная с помощью атрибута TEMPORARY, доступна только в течение того сеанса, во время которого она была создана. Она автоматически удаляется по завершении сеанса. Несколько пользователей могут иметь доступ к временной таблице.
Атрибут WITH COMPRESSION можно использовать только с типами данных CHARACTER, MEMO (другое название — TEXT) и их синонимами.
Атрибут WITH COMPRESSION был добавлен для столбцов с типом данных CHARACTER из-за изменения формата представления знаков Юникода. Каждый знак Юникода всегда занимает два байта. Для существующих баз данных Microsoft Jet, содержащих преимущественно символьные данные, это может привести к почти двукратному увеличению размера при преобразовании в формат ядра СУБД Microsoft Access. Однако представление Юникода для многих наборов символов, которые прежде назывались однобайтовыми кодировками (SBCS), можно без труда сжать до одного байта на символ. Если для столбца с типом данных CHARACTER задать этот атрибут, при сохранении данные автоматически будут сжиматься, а при извлечении из столбца — возвращаться в исходное состояние.
Столбцы с типом данных MEMO также могут содержать сжатые данные. Однако в этом случае существует ограничение. Сжатию могут быть подвергнуты только те поля столбцов с типом данных MEMO, размер которых после сжатия не будет превышать 4096 байт. Остальные поля столбцов с типом данных MEMO останутся в обычном состоянии. Таким образом, в пределах одной таблицы и одного столбца с типом данных MEMO одни данные могут быть подвергнуты сжатию, а другие — нет.
Пример
В этом примере создается новая таблица с именем ThisTable и двумя текстовыми полями.
В этом примере создается новая таблица с именем MyTable с двумя текстовыми поля, полем даты и времени и уникальным индексом, состоящим из всех трех полей.
В этом примере создается новая таблица с двумя текстовыми полями и полем Integer. Поле SSN является первичным ключом.
В этом примере создается новая таблица с именем
Kitsch’n Sync , в которой показаны различные типы полей и индексов. Поле счетчика является первичным ключом.
Источник
Создание таблицы посредством VBA
Скажите пожалуйста, у типов полей: BINARY, TINYINT, INTEGER, TINYINT, DECIMAL нельзя указать ограничение по длине?
Просто, например, в MySQL можно https://dev.mysql.com/doc/refm. rview.html
а что же JetSQL(?) что-то непонятки.
Это нужно, чтобы собрать CREATE TABLE в VBA, например этот кусок:
Ничего умнее как перечислить все BINARY, TINYINT, INTEGER, TINYINT, DECIMAL в условии не придумал. Можно это реализовать красиво? На выходе должны быть правильно сформированные строки типа INTEGER либо VARCHAR(255)
з.ы. За U_C_Type, N_Length — не ругайте, препод так заставляет писать)
Вывод таблицы в Word посредством vba.
Доброго времени суток. Делаю отчет в Access кодом VBA и вывожу в вордовский документ. Подскажите.
Создание таблицы средствами VBA
Здравствуйте, уважаемые пользователи, прошу помощи. Работаю в Аксесе, создал модуль по созданию.
Автоматическое создание формы на основе созданной таблицы в результате импорта данных (VBA)
Доброе время суток, Уважаемые! Подскажите, пожалуйста, решение задачи. Задача: Необходимо после.
Об экспорте данных посредством VBA
Помогите, плз, разобраться в таком вопросе. Передо мной стоит задача экспорта данных в текстовый.
Сообщение было отмечено FloppyDisc как решение
Решение
Может поможет:
Простейший пример кода, который создает таблицу дней недели («tempWeekDays») с порядковым номером в поле «DayID» и названием дня в поле «DayName»
Eugene-LS, и в правду, забыл что можно работать с объектами
Добавлено через 3 минуты
Но все же вопрос не совсем закрыт
Хотя позже переделаю в ООП-шку)
Сообщение было отмечено FloppyDisc как решение
Решение
Сообщение от FloppyDisc
Сообщение от mobile
Сообщение от mobile
Кстати, это вы делали эту основу мне когда-то Если помните, тут вся конструкция собирается из таблицы с описанием
Сообщение от mobile
НО опять же проблема, теперь ведь мне придется определять тип данных
.Fields.Append tbl.CreateField(CreateTable!C_Name, dbText, flength), тут dbText мне не подходит, в таблице с описанием все находится в формате обычной инструкции CREATE TABLE, т.е TEXT, VARCHAR, CHRA, INTEGER.
Так вот, я конечно могу впихнуть много условий или Switch, но все же как сделать это красивее, МБ можно создать какой-то массив со всеми возможными типами и возвращать то нужное значение, если оно есть в этом массиве?
И еще вопрос, т.е тут получится вариант создания длин для полей типа INT, вроде сработало
Добавлено через 9 минут
Eugene-LS, mobile, Особенно порадовало то, что можно задавать значения по умолчанию прямо тут, иначе мне приходилось делать ALTER TABLE для каждого созданного поля
Источник