Ms access vba

Программирование на VBA в СУБД MS Access

VBA (Visual Basic for Application) играет важную роль при разработке баз данных MS Access. С помощью VBA в MS Access можно настроить формы и отчеты, запустить макросы, извлечь данные и т.д.

Модули VBA

VBA может использоваться для выполнения любых действий в ходе работы приложения. Для создания процедур VBA в MS Access используется Построитель программ. Программный код создается в Окне модуля. В зависимости от технологии создания можно различить несколько типов модулей:

1. Стандартный модуль. Для его открытия нужно распахнуть список Макрос, расположенный в группе Другие вкладки Создание ленты инструментов, и нажать кнопку Модуль.

2. Модуль формы или отчета. Для открытия модуля формы или отчета нужно открыть форму или отчет в режиме Конструктора и выбрать кнопку Просмотреть код в группе Сервис контекстного меню Инструменты конструктора форм/отчетов — Конструктор ленты инструментов. MS Access позволяет открыть одновременно и форму/отчет и ее модуль. Модули формы/отчета сохраняются одновременно с формой/отчетом и не перечисляются во вкладке Модули области переходов.

3. Независимый модуль класса. Открывается щелчком мыши по кнопке Модуль класса, появляющейся в распахивающемся списке кнопки Макрос, расположенной в группе Другие вкладки Создание ленты инструментов. После сохранения такого модуля его имя будет находиться на вкладке Модули области переходов вместе со стандартными модулями. Для обозначения стандартных модулей и независимых модулей VBA используются разные пиктограммы (рис. 10.8):

Элементы языка VBA

Коды программы VBA состоят из выражений (statements). В каждой строке кода располагается одно отдельное выражение. Но данное правило имеет исключения:

· несколько выражений могут быть расположены в одной строке и разделены двоеточием;

· использование символа продолжения строки (пробел, за которым следует символ подчеркивания) позволяет создавать выражение, занимающее несколько строк. Не допускается использовать символ продолжения строки только внутри заключенных в кавычки строк. Рекомендуется продолжение каждой строки выделять отступом.

Алфавит языка VBA составляют следующие элементы:

· прописные и строчные буквы латинского алфавита (А – Z);

· прописные и строчные буквы кириллицы (А – Я);

· цифры от 0 до 9;

· символ подчеркивания «_»;

При написании кода VBA следует знать некоторые соглашения, которых следует придерживаться, чтобы избежать ошибок выполнения кода. Первое соглашение – это соглашение по именам. В VBA именуются переменные, константы, процедуры, функции, объекты и т.д. Определим правила, которым следует следовать при выборе имен:

1. Длина имен не должна превышать 255 символов, но VBA учитывает только первые 31 символ от начала имени.

2. Имена должны начинаться с буквы и могут содержать комбинацию букв, цифр и символов.

3. Имена не должны совпадать со стандартными именами VBA.

4. Имена не должны включать точек, пробелов и следующих символов: ! @ # $ %. Символ пробела можно заменить символом подчеркивания _.

Регистр букв в именах не имеет значения. Например, имена PRICE, Price и price эквивалентны.

Типы данных языка VBA

В языке VBA типы данных делятся на простые (или базовые) и структурированные.

К простым типам относятся:

· вещественный (Single, Double, Currency, Decimal);

· логический (Boolean).

К стандартным структурированным типам относятся:

· массив;

· строка;

· тип, определяемый пользователем.

Таким образом,VBA использует 12 встроенных типов данных, представленных в таблице 10.1.

Создание приложений на языке VBA для MS Access

Небольшую и несложную базу данных или приложение Access можно создать в СУБД Access без использования языков программирования SQL и Visual Basic. В СУБД Access имеется достаточно средств (различных мастеров и конструкторов) для визуального проектирования таблиц, запросов, форм и отчетов.

Для решения некоторых задач автоматизации приложений Access можно использовать макросы вместо языка программирования Visual Basic (например, при создании главной и подчиненной кнопочной формы). Но создание коммерческих баз данных в СУБД Access невозможно без применения визуального языка программирования Visual Basic и языка запросов SQL.

Для автоматизации действий над объектами в Microsoft Access и в других приложениях Microsoft Office применяются макросы и модули. Макросы — это небольшие программы на языке макрокоманд (языке сценариев). Модули — это наборы описаний и процедур на языке программирования VBА, т.е. модули — это объекты, содержащие программы на языке Visual Basic.

Основное назначение макросов и модулей — это создание удобного интерфейса приложения, в котором формы и отчеты открывались бы при нажатии кнопок в этих формах или на панелях инструментов. Модули являются более мощным средством создания программных расширений в среде Microsoft Office. Применение модулей требует от пользователей знаний основных принципов объектно-ориентированного программирования.

Попробуй обратиться за помощью к преподавателям

Программирование на VBA в СУБД Access используют в основном разработчики (программисты) в процессе создания приложений (различных баз данных), с которыми работают пользователи.

Для программирования в Access используется не приложение Visual Basic, а встроенная в Microsoft Office система программирования Visual Basic for Applications (VBA). Система программирования VBA является неотъемлемой частью приложений Microsoft Office и предназначена для визуального программирования в таких приложениях как Word, Excel, PowerPoint, Access и т.д. В VBA языком программирования является Visual Basic (VB), а инструментальная среда программирования реализована в виде редактора VB, который может активизироваться из любого приложения MS Office. Редактор Visual Basic является отдельным приложением, поэтому можно переключаться между приложениями Microsoft Office и редактором Visual Basic клавишами $Alt+F11$.

Модули

Система программирования VBA предназначена для написания кода программ модулей, которые хранят текст этих программ.

Задай вопрос специалистам и получи
ответ уже через 15 минут!

Модуль — это совокупность описаний, инструкций и процедур, сохраненных под общим именем. В Access существует два типа модулей: стандартные модули и модули класса. Основное содержание модулей — это процедуры на языке VBA.

Процедура — совокупность описаний и инструкций в модуле, которые выполняются как одна программная единица. В VBA существуют процедуры-подпрограммы Sub и процедуры- функции Function.

Стандартные модули содержат общие процедуры, которые не связаны с конкретным объектом (формой, отчетом). Стандартный модуль — это модуль, в который помещают процедуры Sub и Function, которые должны быть доступны для всех процедур в данном приложении. Стандартные модули могут использоваться другими приложениями Access, так как в общих процедурах нет ссылок на конкретные объекты данного приложения (формы, отчеты). Кроме общих процедур, в стандартных модулях могут содержаться глобальные переменные и функции, а также объекты, которые доступные из других объектов базы данных.

Модуль класса отличается от стандартного модуля тем, что, кроме процедур, он содержит описание объекта и используется для создания классов (объектов). Отдельные модули класса, расположенные на вкладке Модули окна базы данных, содержат описание класса (объекта), созданного пользователем. К модулям класса также относятся модули объектов (форм, отчетов), которые связаны с конкретными формами или отчетами и содержат процедуры обработки событий форм (отчетов) и их элементов управления.

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

Все процедуры событий для формы или отчета хранятся в модуле объекта (форме или отчете). Вновь созданная форма (отчет) не содержит модулей, но их можно создать несколькими способами. Таким образом, формы или отчеты связаны с созданными модулями объектов (форм, отчетов). Если процедура используется только формой или отчетом, то она хранится в коде формы или отчета. Если процедура используется во многих формах и отчетах, то она сохраняется в отдельном модуле.

Первый способ создания пустого модуля: выбрать Да в поле наличия модуля на вкладке Все в окне диалога Форма или Отчет (рис.1). Окно диалога вызывается командой Свойства из контекстного меню, находясь в конструкторе форм или отчетов.

Другой способ создания модуля выполняется кнопкой Программа на панели инструментов в режиме конструктора форм или отчетов. Если щелкнуть мышью на кнопке Программа, то запуститься редактор VB, в котором мы можем вводить текст программы соответствующего модуля (рис.2).

Третий способ осуществляется путем обработки некоторых событий связанных с формой или каким-либо элементом управления формы, находясь в режиме Конструктора форм или отчетов. Для этого необходимо открыть окно редактора Visual Basic, щелкнув на команду Программы в Построителе (рис.3). Построитель вызывается командой Обработка событий из контекстного меню объекта, например кнопки.

Для просмотра модулей класса, которые связаны с конкретными формами или отчетами и содержатся в модулях объектов, нужно выделить форму или отчет в окне базы данных на вкладке формы или отчеты и щелкнуть на пиктограмме Программа на панели инструментов в главном окне Microsoft Access. Чтобы создать отдельный модуль класса или стандартный модуль, нужно выбрать пункт Модуль класса или Модуль в меню Вставка. Стандартный модуль можно создать, например, путем преобразования макроса. Модули отображаются в окне базы данных на вкладке Модули. Для просмотра процедуры в стандартном модуле или в модуле класса нужно выделить требуемый модуль в окне базы данных на вкладке Модули и щелкнуть на пиктограмме Программа на панели инструментов в главном окне Microsoft Access или щелкнуть на кнопке Конструктор в окне базы данных. Откроется редактор Visual Basic, на панели редактора кода которого отображаются процедуры.

Объекты и события

В СУБД Access объектами являются таблицы, формы, запросы, окна, меню, кнопки, линии прокруток и т.д., в том числе и приложение Access. Для каждого объекта определены возможные события. Некоторые события возникают от действия пользователей (щелчков мыши, нажатия клавиш клавиатуры и др.), а другая часть событий происходят в результате свершения других событий, например открытия окна. Каждое событие проявляется в определенных действиях программы.

Существует две группы действий на события:

1. Действия первой группы определены свойствами объекта, и их изменить в процессе программирования на VBA нельзя. Эти свойства устанавливаются ОС Windows и системой программирования VBA. Примером может быть свертывание окна при щелчке на кнопке Свернуть.
2. Вторая группа действий (отклика) на события определяется программистом. Для этой группы действий программист может создать процедуры VBA для каждого возможного события, но на практике программист заполняет кодом процедуры только для тех событий, которые требуются для данного приложения Access.

Так и не нашли ответ
на свой вопрос?

Просто напиши с чем тебе
нужна помощь

Программирование на VBA Access 2003 – Встроенные функции

Пришло время поговорить и о программировании на VBA Access, так как мы с Вами рассматривали уже достаточно много примеров реализации тех или иных задач в Access 2003, но мы не затрагивали основы VBA, и сегодня мы начнем со встроенных функций. Рассмотрим часто используемые функции и конечно разберем примеры их использования, для лучшего освоения языка программирования VBA для Access.

Если говорить о том, что именно мы уже рассматривали в Access 2003 то, например это Загрузка данных из текстового файла на VBA Access или Выгрузка данных из Access в шаблон Word и Excel. А сегодня мы будем рассматривать именно основы VBA и как уже сказалось выше встроенные функции.

Встроенные функции VBA Access

Если Вы уже немного знакомы с какими-нибудь языками программирования, например, Вы, знаете основы JavaScript или основы Visual Basic, то Вы должны понимать что такое встроенные функции, а если нет то я могу привести свое определение данного термина.

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

И так хватит теории, давайте приступим к рассмотрению встроенных функций VBA Access.

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

Функция msgbox – простая функция для отображения сообщения на экране в виде диалогового окна.

И теперь сохраните, откройте форму и нажмите на нашу кнопку и у Вас отобразится это сообщение:

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

Примечание! Далее я буду писать все примеры без указания начала процедуры и ее конца т.е. без Private Sub Кнопка1_Click() и End Sub, подразумевается что все примеры мы будем писать в обработчике одной кнопки, Вы просто вставляйте код ниже между этим строками.

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

Функция Mid – данная функция относится к строковым функциям Access и она вырезает указанное число знаков строки. Имеет три параметра, первый это собственно сама строка, второй это начальная позиция, и третий это число символов.

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

Функция Len – также относится к строковым функциям и она просто считает количество символов в строке. Давайте объединим предыдущий пример с функцией len, так как порой неизвестно (да и не удобно) сколько символов необходимо вырезать.

И результат будет таким же, как и предыдущем примере.

Функция LCase – строковая функция vba которая преобразует все буквы строки в строчные. Для закрепления давайте рассмотрим все на том же примере, только сейчас обрамим наше выражение еще и функцией LCase.

И результат будет таким же, как и раньше, но все заглавные буквы станут строчными, в нашем случае это первая буква «Н», результат:

Функции left и right – это строковые функции vba, которые вырезают указанное количеств символов слева и справа соответственно. Принцип такой же, как и, например, в Excel функциях левсимв и правсимв.

Функция right работает аналогично, только вырезает символы справа.

Функции date, time, now – это функции, с помощью которых можно получить текущую дату и время. По названию я думаю понятно, какая функция для чего нужна, если нет то date текущая дата, time текущее время, now текущая дата и время. Данные функции запускаются без параметров.

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

Функции hour, minute, second – эти функции возвращают соответственно часы, минуты и секунды. Принимают один параметр это текущее время.

Функция isnull – проверяет значение на NULL.

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

Функция Nz — данная функция возвращает значение не NULL. Принимает один обязательный параметр с типом variant и один не обязательный. Она работает следующим образом, если входящий параметр NULL она преобразует его в другое значение, для предотвращения использования NULL значений в выражении. Также если указан второй параметр, то в случае если первый параметр NULL то она вернет его, т.е. второй параметр.

Пример предотвращения NULL значения в выражении:

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

Пример возвращения значения, если входящий параметр равен NULL:

Здесь просто мы смотрим если первый параметр NULL, то возвращаем второй в виде строки.

Приведу несколько функций для работы с числами.

Функции Int, Fix, Abs – функции которые работают с числами, все они принимают один параметр и делают следующее:

• Int – принимает значение типа Double и возвращает число меньшее или равное исходному числу, в случае если число отрицательное;
• Fix — принимает значение типа Double и возвращает число большее или равное исходному числу, в случае если число отрицательное;
• Abs – возвращает абсолютное значение числа переданное во входящем параметре.

Примечание! Для перевода на новую строку мы использовали константу vbNewLine.

Пока это все, надеюсь, данные простые примеры помогут Вам освоить и понять что такое встроенные функции, а мы с Вами продолжим изучать VBA Access в следующих статьях. Удачи!

12. Программирование в Access

Создание приложений VBA в Access, типичные ситуации

Программирование в Access сильно отличается от программирования в Word, Excel и других приложениях Office. Главное принципиальное отличие заключается в том, что Word, Excel, PowerPoint, Project и т.п. предназначены прежде всего для непосредственной работы с ними пользователя, без какой-либо доработки со стороны разработчиков предприятия. Access иногда также используется как конечное приложение пользователями, но чаще он все-таки применяется как платформа для создания своих приложений разработчиками.

Второе отличие заключается в том, что в Access встроено свое собственное ядро для работы с данными. Фактически Access — это полноценная система управления базами данных, поэтому для полного использования его возможностей необходимы знания о принципах работы с базами данных: что такое таблицы и отношения между таблицами (система ключей), что такое нормализация данных, типы данных и ограничения целостности и т.п. Очень часто пользователи на предприятиях такими знаниями не обладают.

Кроме того, существуют разные варианты использования Access с точки зрения архитектуры приложения. Иногда Access (файл MDB) используется просто как ядро, которое управляет данными, находящимися с таблицами. Пользователи работают с этими данными через внешние приложения, созданные разработчиками, например, на Visual Basic, Delphi или C++. В других ситуациях Access, наоборот, используется только для предоставления пользовательского интерфейса для работы с данными, которые физически расположены на серверах баз данных, например, SQL Server, Oracle, IBM D2 и т.п.

В Access предусмотрен встроенный язык запросов JET SQL, который активно используется разработчиками для работы с данными в базах данных Access.

Подводя итоги, можно сказать, что программирование средствами VBA в Access, которое будет рассмотрено в этой главе — это лишь малая часть возможностей Access. Очень многие возможности работы с Access (например, его язык запросов или проектирование и создание таблиц) останутся за пределами этой книги. По этим возможностям существует отдельная литература. Заметим, что во многих книгах по Access за рамками остается как раз язык VBA и объектная модель самого Access, так что эта глава может послужить дополнением к ним.

Какие задачи на предприятии чаще всего решаются средствами автоматизации в Access?

Сразу же скажем, что поскольку Access — это система управления базами данных, то он очень часто используется как контейнер для хранения данных. Данные при этом могут быть самыми разными, например, обычные данные о заключенных договорах или клиентах нашего предприятия, или цифровые фотографии, или шаблоны Word или Excel, которые используются для генерации отчетов из баз данных. То, что в Access все это вместе с графическим интерфейсом можно «упаковать» в один файл MDB, позволяет сделать приложение очень компактным и удобным для переноса с компьютера на компьютер.

Еще одно важное назначение Access — обеспечение клиентского интерфейса для работы с данными, которые хранятся на мощных клиент-серверных системах, таких, как MS SQL Server, Oracle, IBM DB2. Согласно достаточно условной градации от Microsoft рекомендуется использовать настольные системы (такие, как Access, FoxPro, Paradox и т.п.), если к данным одновременно будут обращаться не более 10 пользователей. Если пользователей может быть больше (или, к примеру, самих данных будет очень много — гигабайты), то рекомендуется использовать более сложные, но и более функциональные клиент-серверные системы. А уже в рамках обеспечения доступа к данным (на клиент-серверных системах или прямо в базах данных Access) решаются более узкоспециализированные задачи приложений:

• создание обычных форм — программных интерфейсов для занесения/изменения/просмотра данных в базе данных и Web-форм (они называются страницами доступа к данным);
• создание отчетов к базам данных, в том числе параметризованных;
• создание программной логики приложения обычным способом — на VBA (модули) и для начинающих пользователей — макросы (макросы всегда можно преобразовать в модули);
• вспомогательные действия — печать, экспорт и преобразование данных (хотя для преобразования данных обычно удобнее использовать объектную модель DTS), загрузка данных, репликация и т.п.

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