Visual Basic for Applications в 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), загрузка данных, репликация и т. п.

Объект Application, его свойства и методы.

Объектная модель Access по своей архитектуре сильно отличается от объектных моделей Word и Excel. Возможно, это объясняется тем, что Access — не «родной», как Word и Excel, а приобретенный продукт третьей фирмы.

Один из немногих моментов, в котором программирование в Access похоже на программирование в Word и Excel — это наличие объекта Application, который находится на вершине иерархии объектной модели Access. Он точно так же может использоваться для программного запуска Access из других приложений, и его свойства и методы доступны из любой части кода. Запуск Access из другого приложения может выглядеть так:

Dim appAccess As Object.

Set appAccess = CreateObject («Access.Application»).

appAccess.Visible = True.

MsgBox appAccess.Name.

Если не поставить свойство Visible в True, то по умолчанию Access откроется в невидимом окне (увидеть его можно будет только в списке процессов в Task Manager). Если запустить такой код из процедуры Word или Excel, то по умолчанию по завершении работы этой процедуры объект будет удален из оперативной памяти (поэтому здесь и поставлено окно сообщения, чтобы этот процесс задержать).

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

На практике программным образом запускать Access приходится достаточно редко — поскольку обычно удобнее всего оболочку приложения, запускающего Word, Excel и т. п. делать именно в Access. Открывать Access для доступа к данным базы данных в файле MDB не рекомендуется — для этой цели лучше использовать объекты ADO, более простые и удобные и менее ресурсоемкие.

Теперь — о свойствах и методах объекта Application. Как можно убедиться, их набор в Access мало похож на соответствующий набор в Word и Excel. Вначале — о наиболее важных свойствах:

• · AutomationSecurity — позволяет определить уровень безопасности при открытии базы данных. По умолчанию используется значение msoAutomationSecurityByUI — использовать то, что настроено на графическом экране через меню Макрос -> Безопасность. Можно вообще запретить открытие файлов баз данных с макросами (msoAutomationSecurityForceDisable), но чаще используется значение msoAutomationSecurityLow, которое позволяет открыть файл данных без лишних вопросов;
• · BrokenReference — возможность проверить, есть ли разорванные ссылки (когда ваше приложение не может найти модуль dll или другую базу данных). Наличие конкретной ссылки можно проверить при помощи объекта Reference;
• · CodeContextObject — очень полезное свойство, которое позволяет определить, из какого объекта базы данных (формы, отчета и т. п.) был запущен модуль/макрос. Это свойство можно, например, использовать для обнаружения источника ошибок;
• · CodeData — еще одно важнейшее свойство. Оно позволяет получить доступ к коллекциям AllDatabaseDiagrams, AllFunctions, AllQueries, AllStoredProcedures, AllTables и AllViews. Правда, в этих коллекциях находятся одни и те же объекты AccessObject. Возможностей у них на первый взгляд не так много, но на самом деле при помощи этого объекта мы получаем возможность настраивать десятки свойств для таблиц, запросов, диаграмм и других объектов базы данных Excel. Пример применения свойства CodeData для получения информации о всех таблицах в базе данных может выглядеть так:

For Each oTable In CodeData.AllTables.

Debug.Print oTable.Name.

Next.

• · CodeProject — используется для тех же целей, что и CodeData, но предоставляет доступ к коллекциям программных модулей AllForms, AllReports, AllMacros, AllModules и AllDataAccessPages.
• · свойство CommandBars возвращает коллекцию объектов CommandBar — то есть панелей инструментов Access. Эту коллекцию можно использовать для настройки пользовательского интерфейса приложения, построенного на основе Access.
• · CurrentData действует аналогично CodeData и CodeProject. Это свойство позволяет получать доступ к тем же коллекциям, включая полученные с внешнего источника данных (SQL Server). Аналогично работает свойство CurrentProject.
• · свойства CurrentObjectName и CurrentObjectType позволяют определить, какой объект на момент запуска процедуры находился в фокусе (из какого объекта был вызван данный код). Эти свойства, конечно, удобно использовать для проверок, когда один и тот же код может быть вызван разными способами. При этом свойство CurrentObjectType ведет себя несколько неожиданно. Оно возвращает значение из перечисления в обычных ситуациях, но если вызвавшего объекта уже нет (объектная ссылка установлена в Nothing) или информацию о нем получить не удалось, это свойство почему-то возвращает True.
• · свойство DataAccessPages позволяет получить ссылку на одноименную коллекцию, в которой находятся объекты всех Web-форм базы данных (они называются страницами доступа к данным) — объектов DataAccessPage.
• · свойство DBEngine позволяет получить ссылку на объект DBEngine, при помощи которого можно просмотреть или настроить свойства ядра Jet, на котором работает Access. Например, при помощи этого свойства можно сжать базу данных, настроить для нее пароль, определить используемую кодировку и т. п.
• · свойство DoCmd позволяет получить доступ к еще одному очень важному объекту — DoCmd, при помощи которого можно выполнить множество важных операций. Фактически этот объект — основная «рабочая лошадка» Access с точки зрения VBA. Он будет рассмотрен в отдельном разделе. Сам объект DoCmd создавать нет необходимости — он и так всегда доступен через свойство объекта Application, например:

Application.DoCmd.RunSQL «Delete from table1» .

· свойство Forms позволяет вернуть ссылку на коллекцию объектов Form. От уже рассмотренной коллекции AllForms эта коллекция отличается двумя моментами:

— в ней находятся только открытые в настоящий момент формы;

— в ней находятся не объекты AccessObject, а объекты Form с гораздо богатым набором свойств и методов.

• · свойство MenuBar позволяет не совсем стандартным образом настроить пользовательское меню (уровня всего приложения, если для формы предусмотрено свое меню, оно будет иметь приоритет), вернуться к показу встроенного меню или вообще отключить показ меню. Для нестандартной работы с контекстным меню предусмотрено свойство ShortcutMenuBar.
• · свойство Modules действует аналогично свойству Forms. Оно позволяет получить доступ к одноименной коллекции с объектами Module, представляющими стандартные модули и модули классов. У объектов Module намного больше свойств и методов, чем у объекта AccessObject;
• · свойства Printers и Printer возвращают все установленные в системе принтеры и принтер по умолчанию соответственно. При помощи объекта Printer можно настроить множество свойств принтера, которые будут использоваться при печати.
• · свойство References позволяет получить ссылку на коллекцию объектов Reference (ссылки на библиотеки типов). Можно использовать для проверки работоспособности ссылок или для добавления ссылок программным способом.
• · свойство Reports работает аналогично свойствам Forms и Modules, позволяя получить доступ к коллекции объектов Report.
• · Screen — это свойство позволяет получить доступ к специальному объекту Screen, при помощи которого можно получить доступ к активным элементам Access — формам, отчетам, элементам управления и т. п. (В Word и Excel свойства Active… встроены непосредственно в объект Application). При помощи этого объекта можно также изменить вид курсора мыши и вернуться к предыдущему элементу управления.
• · свойство UserControl позволяет определить, как именно был запущен Access — вручную пользователем или программным способом из другого приложения (в зависимости от этого, к примеру, можно решить, закрывать Access автоматически или положиться на пользователя).
• · свойство Version позволяет вернуть версию Access (например, для дополнительных проверок работоспособности приложений). Для Access 2003 эта версия — 11.0.
• · свойство Visible позволяет сделать Access видимым для пользователя (или наоборот, спрятать). По умолчанию Accesss, запущенный программным способом, невидим для пользователя, и для этого свойства необходимо установить True.

Методов у объекта Application также очень много (плюс часть методов достаточно искусственно перенесена в объект DoCmd). Ниже представлены самые важные из них:

• · AccessError () — очень важное свойство для обработки ошибок. Оно позволяет получить описание тех ошибок, для которых стандартное Err. Description возвращает «Application-defined or object-defined error» — ошибок библиотек Access и DAO.
• · метод BuildCriteria () позволяет очень быстро и удобно сконструировать критерий отбора записей, который может применяться в SQL-запросах, фильтрах для формы и отчетов, и т. п. Возвращает правильно сконструированное строковое значение.
• · CloseCurrentDatabase () — возможность закрыть текущую базу данных без закрытия Access. Обычно применяется для того, чтобы затем открыть следующую базу данных без запуска нового экземпляра Access.
• · CodeDb () — возвращает объект DAO. Database, представляющий базу данных, в которой в настоящее время выполняется код (обычно используется, когда у вас есть специальная библиотечная база данных с программными модулями, выполняющими различные операции с другими базами данных). Ссылку на такой же объект для текущей базы данных можно получить при помощи метода CurrentDb ().
• · CompactRepair () — возможность сжать/починить базу данных Access и вернуть код ошибки (можно также записать протокол). Сжимаемая/ремонтируемая база данных должна быть в настоящий момент закрыта.
• · ConvertAccessProject () — возможность выполнить еще одну служебную операцию, на этот раз по преобразованию версии базы данных Access. Возможны варианты от acFileFormatAccess2 до acFileFormatAccess2002.
• · метод CreateAccessProject () позволяет программным образом создать проект Access (программный интерфейс для доступа к SQL Server). Для того, чтобы его сразу создать и открыть, можно использовать метод NewAccessProject (), а чтобы просто открыть существующий — OpenAccessProject ().
• · CreateAdditionalData () — возможность создать объект AdditionalData, который можно использовать вместе с методом ExportXML () при экспорте родительской таблицы в файл XML. Применение этого объекта позволяет экспортировать набор таблиц.
• · CreateControl () — возможность программным образом создать элемент управления на форме. Принимает множество параметров, которые определяют данный элемент управления. Для создания элемента управления в отчете используется метод CreateReportControl (). Удаление — при помощи соответственно DeleteControl () и DeleteReportControl ().
• · CreateForm () — возможность также программным образом создать форму Access (и получить ссылку на объект созданной формы). Затем можно настроить свойства этой формы, добавить для нее элементы управления и т. п. Создание таким же образом отчета можно произвести при помощи метода CreateReport ().
• · CreateGroupLevel () — возможность программным образом создать группировку в отчете (или отсортировать записи). Принимает имя отчета, столбец, по которому производится сортировка, формулы для создания верхнего и нижнего колонтитула групп.
• · CreateNewWorkgroupFile () — возможность программным способом создать файл рабочей группы с разрешениями для пользователей. На графическом экране эту операцию можно выполнить из меню Сервис->Защита->Администратор рабочих групп.
• · CurrentUser () — этот метод позволяет получить в виде строкового значения имя текущего пользователя базы данных. По умолчанию работа производится от имени пользователя Admin.
• · методы, которые начинаются на D…, очень удобны для выполнения различных операций, не прибегая к коду SQL — напрямую из Access:

DAvg (), DSum (), DCount (), DMax (), DMin () и т. п. позволяют применить агрегатные функции к столбцу (или набору записей) в таблице или представлении;

• · DLookup () — исключительно удобный метод, который позволяет найти и вернуть нужное вам значение из таблицы или представления (включая двоичные объекты, например, шаблоны Word). Точно также принимает в качестве параметров имя таблицы или представления, имя столбца и фильтр. Если условию фильтра удовлетворяет несколько значений, то возвращается первое.
• · DFirst () и DLast () — несмотря на свои названия, эти методы работают одинаково, возвращая случайное значение из столбца таблицы или представления.
• · Echo () позволяет перерисовать экран Access и в качестве бесплатного приложения вывести текст в строку состояния. Очень удобен во многих ситуациях метод Eval (). Он позволяет произвести над текстовой строкой операции, как будто эта текстовая строка встретилась в коде VBA. Этот метод возвращает значение типа Variant — чтобы уместились любые возвращаемые значения. Например,

Eval («1+1»).

вернет 2, а.

Eval («МояФункция ()»).

вернет то, что возвращает эта функция. Eval () очень удобно использовать, чтобы избежать громоздких проверок и преобразований типов, например, когда мы принимаем разные значения, вводимые пользователем. Методы ExportXML () и ImportXML () позволяют экспортировать и импортировать наборы таблиц с данными (включая информацию о ключах, индексах, кодировках и т. п.) в XML-совместимый текстовый файл. При этом экспорт и импорт из Access при помощи этих методов можно производить не только для баз данных Access, но и баз данных SQL Server начиная с версии 6.5.

· методы GetOptions () и SetOptions () позволяют получить информацию/установить те десятки настроек, которые доступны через меню Сервис->Параметры. Например, чтобы по нажатии клавиши в таблице производился переход не вправо (по умолчанию), а вниз, можно использовать код.

Application.SetOption «Move After Enter», 2.

• · hWndAccessApp () — очень нужный метод для тех, кто работает с Windows API. позволяет вернуть указатель на окно Access.
• · метод NewCurrentDatabase () позволяет создать и сразу открыть новую базу данных Access. Для открытия существующей базы данных можно использовать метод OpenCurrentDatabase ().
• · Nz () — исключительно удобная функция для практической работы. Позволяет возвращать пустую строку или другое значение, если значение в данном столбце таблицы не определено (Null). Опытные разработчики очень часто используют эту функцию, чтобы избежать ошибок при обращении к пустым значениям (любым, включая Memo), например, при поиске по таблице.
• · Quit () — понятно. Может ничего не сохранять, сохранять все или спрашивать у пользователя.
• · RefreshDatabaseWindow () — возможность обновить окно базы данных. Обычно применяется после программного создания форм, отчетов и т. п.
• · метод Run () позволяет вызвать процедуру или функцию VBA из кода и передать ей до 30 параметров. Может использоваться для вызова пользовательских или встроенных функций, но, поскольку их можно вызвать и стандартными способами. Чаще всего используется при вызове процедуры Access из внешней откомпилированной программы, например, DLL или EXE.
• · RunCommand () — возможность выполнить одну из десятков встроенных команд Access (практически все, что есть в панелях управления и встроенных меню). Например, чтобы максимизировать окно Access, можно воспользоваться командой

RunCommand acCmdAppMaximize.

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