Основы Access VBA

Основные понятия Access
Access является программой для работы с реляционными базами данных. Термин реляционный подразумевает, что работа ведется не только с отдельными таблицами, а и со связями между двумя и большим количеством таблиц базы данных. Связь создается, когда две таблицы имеют одно или более общих полей. Предположим, что существует некая таблица базы данных склада, в которой, среди прочих, одно поле содержит номер, который идентифицирует поставщика для данного товара. В этой же базе данных есть другая таблица, в которой содержится информация обо всех поставщиках. Каждая запись в ней содержит имя поставщика, его адрес и т.п., а также поле номера поставщика. Эти две таблицы связаны наличием общего поля номера поставщика. Эта связь позволяет как определять поставщика для конкретного товара, так и создавать список веек товаров, поставляемых некоторым поставщиком. Отдельный набор записей, которые содержат одни и те же поля, называется таблицей базы данных. В приведенном примере мы имели дело с двумя таблицами Invencory (Склад) и Suppliers (Поставщики). Набор всех связанных таблиц называется базой данных или файлом базы данных.
Работа с базой данных обычно разделяется на три этапа:
ввод и редактирование данных;
выполнение запросов к базе данных (то есть получение информации);
создание отчетов на основе данных.
Для каждой из этих задач приложение Access имеет мощный набор средств. Как и другие приложения пакета Office, Access можно использовать самостоятельно для выполнения различных задач в прикладных приложениях. Однако чаще всего это приложение используется вместе с другими программами пакета Office для создания интегрированных решений. Например, можно использовать Access для получения выборки необходимых данных из громадной таблицы базы данных, использовать средства создания диаграмм Excel для построения сводных графиков на основе этих данных, после чего включить последние в отчет, отформатированный в Word. Однако заметим, что многие средства Access, особенно те, которые связаны с доступом и модификацией информации в базе данных, доступны программам на языке VBA непосредственно из компонент ADO. Таким образом, для некоторых прикладных решении совсем не обязательно использовать Access, достаточно положиться только на ADO (средства ADO будут описаны далее в этой главе).
Как программист вы часто будете встречаться с ситуацией, когда базы данных, с которыми вашему приложению необходимо работать, уже существуют. Если же нет вам придется их создавать самостоятельно. Процесс создания базы данных подразумевает определение ее таблиц, полей, содержащихся в каждой таблице, связей между таблицами, отчетов и форм, которые потребуются пользователю и т.п. В общем, для выполнения этих задач, а также для заполнения базы данными используются средства, заложенные в приложении Access. После этого готовые базы данных, формы и т.п. включаются в состав прикладного приложения. Конечно, формы, базы данных и прочие подобные объекты можно создать и с помощью автоматизации, однако, такой подход является чрезвычайно трудоемким, и может лишь изредка использоваться в исключительных ситуациях, например, при создании мастеров.
На протяжении всей этой главы мы будем ссылаться на базу данных Northwind и использовать ее в приводимых примерах. Эта база данных содержится в примерах из пакета Microsoft Office.

SQL это структурированный язык запросов (Structured Query Language), созданный компанией IBM и фактически ставшим стандартом для доступа к базам энных и работы с ними. SQL является непроцедурным языком, который не имеет ни операторов управления ходом выполнения программы, ни именованных процедур. В SQL вы можете задавать все, что хотите получить. Например, аналогом запроса на русском языке "Получить список всех записей в адресной книге (Address), где штатом является Калифорния (California), отсортированный по фамилиям.", на языке SQL будет следующая конструкция:
SELECT * FROM Address WHERE State='CA' ORDER BY LastName
Основной синтаксис этого типа выражений SQL:
SELECT Fields FROM Tablename WHERE Condition ORDER BY SortField
Рассмотрим его подробнее.
Fields список полей, которые нужно получить в результате работы выражения. Символ подстановки * используется для получения всех полей таблицы.
Tablename имя базы или набора данных, используемого при запросе.
Condition условие критерия для записей. Операторы AND и OR используются для создания составных критериев. Если конструкция WHERE Condition опущена, возвращаются все записи таблицы.
SortField поле, по которому производится сортировка результата запроса, Если конструкция SORT BY SortField опущена, порядок в результате будет соответствовать порядку записей в таблице или активному ее индексу (если таковой существует).
Заметим, что это чрезвычайно упрощенные основные сведения о языке SQL. Некоторые подробности будут рассмотрены далее в этой главе.
Технологии доступа к данным
Модель доступа к данным в операционной системе Windows является немного более сложной, чем объектные модели, используемые другими приложениями Office, Например, в приложениях Word и Excel существует прямая связь между программой и данными. Word предоставляет средства для работы с файлами документов, a Excel для работы с файлами электронных таблиц.
При работе с приложением Access и с базами данных все гораздо сложнее. Прямые манипуляции и изменение базы данных выполняется механизмом базы данных (database engine), а не непосредственно приложением Access или другой программой. Причиной такого подхода является многообразие стандартов форматов баз данных. Механизм базы данных можно рассматривать как интерпретатор, который переводит общие команды (типа "Сортировать эту таблицу в алфавитном порядке") в специальные команды, требуемые конкретным форматом базы данных, с которой проводится работа. В обратном направлении также производится перевод механизм базы данных принимает сообщения или данные из файла базы, а затем передает их приложению.
Используемый механизм базы данных зависит, в первую очередь, от формата базы данных. Например, для формата файлов Access (MDB) используется механизм Microsoft Jet, а для баз данных ADB механизм Microsoft SQL Server. Существуют также и другие механизмы. К счастью, разработчику крайне редко приходится вдаваться в детали используемого механизма, кроме тех случаев, когда необходимо выбирать, какой из них использовать. Работа механизма базы данных большей частью скрыта от глаз разработчика и пользователя.
По мере эволюции операционной системы Windows технологии доступа к данным также изменились. Долговременная стратегия компании Microsoft с названием Universal Data Access (UDA) теперь поддерживается в операционных системах Office 2000 и Office ХР. Эта стратегия внедрена на двух уровнях. Компоненты архитектуры низкоуровневого доступа к данным (составная часть UDA, взаимодействующая непосредственно с файлом базы данных) имеют название OLE DB. Интерфейс программирования высокого уровня, который лежит между OLE BD и программами приложений, получил название ADO (Active Data Objects) (иногда его еще называют ActiveX Data Objects). Любой язык программирования может использовать ADO, если только этот язык поддерживает модель COM (Component Object Model). Эту модель поддерживают следующие языки: Visual Basic Scripting Edition, Visual C++, Visual J++, Visual Basic, а также Visual Basic for Applications.
Пакет установки Office содержит последние версии OLE DB и ADO. Если у вас нет веских причин для обратного, рекомендуется при создании новых проектов работы с базами данных использовать эти технологии. Это является именно тем случаем, когда самое новое является самым лучшим. UDA предлагает более простой, и одновременно более мощный программный интерфейс, а также поддержку большего количества форматов файлов баз данных, чем его предшественник DAO (Data Access Objects). Пакет Office продолжает поддерживать DAO, поэтому сохраняется возможность интегрировать в свои прикладные приложения Office существующие приложения DAO.
ADO может использоваться без запуска приложения Access. В общем случае, если вашему приложению необходим лишь доступ к данным в базе и не требуется использование таких объектов Access, как формы и отчеты, можно обойти Access и использовать напрямую ADO (это будет описано далее).
Объектная модель Access
В вершине объектной модели Access находится объект Application. На следующем уровне иерархии находятся три объекта, с которыми в основном и придется работать. Хотя эти объекты и имеют много общего, но разработаны они для разных целей.
Объект Form используется для ввода и редактирования данных.
Объект Report используется для отображения отформатированных данных, которые не могут редактироваться.
Объект DataAccessPage соединяет в себе средства форм и отчетов для ввода, редактирования и отображения данных. Он разработан для использования в Web-броузере.
В большинстве случаев формы, отчеты и страницы доступа, с которыми вам придется работать, будут уже существовать, будь они созданы вами или кем-либо другим. Вашей задачей как разработчика является автоматизация этих существующих объектов для выполнения поставленной в приложении задачи. Хотя и можно использовать язык VEA для создания таких объектов с нуля, это особый подход, который в настоящем конспекте рассматриваться не будет.
Открытие и закрытие приложений в Access
При автоматизации Access из других приложений Office обычно требуется открытие и, возможно, закрытие приложения Access или базы данных, с которой ведется работа. Для открытия существующей базы данных типа .MDB используется метод OpencurrentDatabase. Его синтаксис:
AccessApp.OpenCurrentDatabase dbname, exclusive
В этом примере предполагается, что AccessApp является ссылкой на объект Access.Application, dbname является строкой, содержащей полное (включающее путь) имя открываемой базы данных. Необязательный аргумент exclusive определяет, будет ли база данных открываться в монопольном режиме (True) или в режиме совместного доступа (False, по умолчанию). В следующем фрагменте открывается база данных Northwind и печатается отчет с именем "Products by Category". Обратите внимание, что стандартным действием команды OpenReport является вывод отчета на печать. Именно поэтому в этом фрагменте не существует явной команды печати:
Dim AccessApp As Access.Application
Set AccessApp=New Access.Application
Const PATH= _
"C:\Program Files\Microsoft Office\office\samples\"
With AccessApp
.OpenCurrentDatabase PATH & "northwind.mdb"
.DoCmd.OpenReport "Product by Category" End With AccessApp.Quit
Для того чтобы открыть файл ADP, используется метод OpenAccessProject:
AccessApp.OpenAccessProject projname
где projname полное (включающее путь) имя файла ADP.
Для того чтобы закрыть открытую базу данных (все равно .MDB или ADP), используется метод CloseCurrentDatabase объекта Application. Этот метод не имеет аргументов.

Файлы ADP и MDB
Файл ADP (Access Data Project) осуществляет доступ к базе данных Microsoft SQL Server с помощью технологии OLE DB. Файл ADP содержит только программу (или основанные на HTML объекты баз данных, такие как формы и отчеты) и не содержит фактических данных или объектов определения данных, таких как таблицы и хранимые процедуры. Файл MDB {Microsoft DataBase) содержит не только объекты, такие как отчеты и форма, но и фактические данные, а также использует механизм Jet для доступа к базе данных. База данных MDB может содержать все соответствующие данные, но связанные данные могут также существовать и во внешних источниках, например, в файле Excel или базе данных dBase.

Объект AccessObject
AccessObject это объект общего типа, который используется в Access для отслеживания объектов разных типов. Например, Form, DataAccessPage и Query три особых типа объектов, которые подразумеваются под общим названием AccessObject. Следующие коллекции Access, которые доступны как свойства объектов CurrentData и CurrentProject (они будут описаны позже), содержат свойство AccessObject:
AllDataAccessPages (объект CurrentProject)
AllDataAccessDiagram (объект CurrentData)
AllForms (объект CurrentProject)
AllMacros (Объект CurrentProject)
AllModules (Объект CurrentProject)
AllQueries (Объект CurrentProject)
AllReports (Объект CurrentProject)
AllStoredProcedures (объект CurrentData)
AllTables (объект CurrentData)
AllViews (объект CurrentData)
Каждая из этих коллекций содержит объект AccessObject для каждого элемента, находящегося в их составе. Каждый объект AccessObject, в свою очередь, содержит объект особого типа. Например, в коллекции AllDataAccessPages каждый объект AccessObject содержит ссылку на объект DataAccessPage.
Нумерация во всех этих коллекциях начинается с нуля. Все они содержат существующие экземпляры объектов соответствующего типа. Это значит, что нельзя использовать стандартный синтаксис коллекций для добавления и удаления объектов из этих коллекций; вместо этого эти коллекции используются для доступа к уже существующим объектам (если такие имеются) заданного типа.
Объект AccessObject имеет свойства, перечисленные в табл. 1.

Таблица 1. Свойства объекта AccessObject
Свойство Описание
IsLoaded Принимает значение True, если объект загружен (открыт в каком-либо представлении); в противном случае False
Name Имя объекта
Properties Возвращает коллекцию AccessObject Properties, содержащую все свойства объекта
Туре Возвращает значение acob j ectType, определяющее тип объекта (например, acDataAccessPage, acQuery или acReport). Детали можно увидеть в Object Browser

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

Листинг 1. Процедура вывода списка всех доступных отчетов в базе данных Борей
Public Sub ListAllReports()
Dim AccessApp As Access.Application
Dim ao As AccessObject
Dim buf As String
Const DB_PATH = _
"C:\Program Files\Microsoft Office\Office10\Samples\"
Set AccessApp = New Access.Application
AccessApp.OpenCurrentDatabase DB_PATH & "борей.mdb"
For Each ao In AccessApp.CurrentProject.AllReports
buf = buf & ao.Name
If ao.IsLoaded Then
buf = buf & " загружена."
Else
buf = buf & " не загружена."
End If
buf = buf & vbCrLf
Next
MsgBox buf
AccessApp.Quit
End Sub
Коллекции All***" содержат информацию о разных объектах, но не позволяют ничего с ними сделать, кроме как узнать, существуют и загружены ли они, а также установить или считать дополнительные их свойства. Перед тем как реально использовать объект, его нужно загрузить. Это будет рассмотрено в следующем разделе.
Обратите внимание, что эти коллекции ссылаются на объекты, которые содержат данные, но не реализуют доступ к этим данным. Для реальной работы с данными следует использовать ADO или DAO, как будет описано далее в этой главе.

Открытие отчетов, форм и страниц доступа к данным
Перед тем, как работать с таким объектом, как отчет или форма, необходимо его загрузить. Для этой цели (а также и для других целей, описываемых далее в этой главе) используется объект DoCmd. Для открытия формы используется его метод QpenForm. Его упрощенный синтаксис:
DoCmd.OpenForm formname, view, filtername, wherecondition
Рассмотрим его подробнее.
FormName единственный обязательный аргумент, задающий имя формы.
View необязательная константа, задающая способ открытия формы. Возможными значениями являются: acDesign (представление конструктоpa), acForinos (табличное представление данных), acNormal (обычное представление по умолчанию) и acPreview (предварительный просмотр печати).
Filtername имя запроса в текущей базе данных, который должен быть применен в форме к данным.
Wherecondition SQL-условие WHERE, но без самого ключевого слова WHERE.
В одно и то же время аргументы Filtername и Wherecondition не применяются, так как имеют одну цель. Например, в следующем фрагменте открывается база данных Northwind, после этого открывается форма Employees в обычном представлении, показывающая только те записи, в которых в поле Title содержится строка Sales Representative. В заключение, приложение становится видимым, позволяя пользователю работать с формой. В этом фрагменте подразумевается, что gAccessApp является глобальной переменной типа Access.Application.
Const DB_PATH= _
"с:\program_files\microsoft office\office\samples\"
Set gAccessApp=New AccessApplication
GaccessApp.OpenCurrentDataabase _
DB_PATH & "борей.mdb"
GAccessApp.DoCmd.OpenForm FormName:-"Employees" _
WhereCondition:= "Title='Sales Representative'"
GAccessApp.Visible=True
Метод OpenReport используется для открытия отчета. Его синтаксис:
DoCmd.OpenReport reportname, view, filtername, Wherecondition
Рассмотрим его подробнее.
Reportname единственный обязательный аргумент, задающий имя отчета.
View необязательная константа, задающая способ открытия формы. Возможными значениями являются: acDesign (представление конструктора), acNormal (обычное представление по умолчанию) и acPreview (предварительный просмотр печати).
Filtername имя запроса в текущей базе данных, который должен быть применен в форме к данным.
Whereconditionусловие WHERE, но без самого ключевого слова WHERE.
При работе с отчетами под термином обычное представление подразумевается совсем не то, что вы ожидаете. Отчеты немедленно выводятся на печать сразу после их отображения на экране. Для отображения отчета без его последующей печати используется режим предварительного просмотра. Следующий фрагмент открывает отчет с именем Invoice для предварительного просмотра, отображая в нем только счета-фактуры для клиента с идентификатором RATTS:
Const DB_PATH=
"с:\program files\microsoft office\o
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·Для открытия страницы доступа к данным используется метод OpenDataAccessPage. Его синтаксис:
DoCmd.OpenDataAccessPage datapagename, datapageview
Рассмотрим его подробнее.
Datapagename единственный обязательный аргумент, задающий имя открываемой страницы доступа к данным.
DataPageView необязательная константа, задающая способ открытия страницы доступа. Возможными значениями являются: acDataPageBrowse (табличное представление, по умолчанию) и acDataPageDesign (представление конструктора).
В следующем фрагменте открывается и отображается на экране страница доступа к данным с именем Analyze Sales:
Const DB_PATH= _
"c:\program files\microsoft office\office\samples\"
Set gAccessApp=New AccessApplication
GAccessApp.OpenCurrentDatabase _
DB_PATH & "northwind.mdb"
GAccessApp.DoCmd.OpenDataAccessPage _
Datapagename:="Analyze Sales"
GAccessApp.Visible=True
Метод DoCmd используется также для открытия запросов, таблиц, хранимых процедур и других объектов Access. Общий подход аналогичен только что описанному для форм, отчетов и страниц доступа к данным. Детали же можно узнать в онлайновой справке по VBA.
Для закрытия объектов используется метод Close. Его синтаксис:
DoCmd.Close objecttype, objectname, save
Рассмотрим его подробнее.
objecttype константа, задающая тип закрываемого объекта. Возможными значениями являются: acDataAccessPage (страница доступа к данным), acDefault (по умолчанию), acDiagram (диаграмма), acForm (форма), асМасго (сценарий), acModule (модуль), acQuery (запрос), acReport (отчет), acServerView, acStoredProcedure (хранимая процедура) и асTable (таблица).
Objectname имя закрываемого объекта. Если задан аргумент objecttype, то имя объекта должно соответствовать этому типу.
Save действителен только для объектов, в которых могут быть изменены данные или конструкция. Возможными значениями являются: асSaveNo (не сохранять), acSavePrompt (запрос на сохранение) и acSaveYes (сохранить).
Если первые два аргумента опущены, закрывается объект в активном окне. Если вы попытаетесь закрыть неоткрытый или вообще не существующий объект, ошибки не возникнет.

Ссылки на открытые объекты
Access предоставляет несколько коллекций, содержащих открытые объекты разных типов. Например, коллекция Reports содержит все открытые в настоящий момент отчеты. Наряду с ним существуют коллекции Forms и DataAccessPages. Эти коллекции доступны как свойства объекта Application и отличаются от коллекций ALL**** лишь тем, что содержат открытые объекты, а не все существующие объекты определенного типа. Вспомним, чтобы определить, открыт ли объект, можно с помощью свойства IsLoaded объекта в соответствующей коллекции All****.
Как и во всех коллекциях, доступ к членам коллекций Forms, Reports и DataAccessPages можно осуществить по индексному номеру объекта или по его имени. Индексный номер используется в основном только при переборе коллекции, так как вследствие операций удаления и добавления индекс отдельного элемента коллекции может измениться. В следующем фрагменте закрываются и сохраняются все открытые страницы доступа к данным (здесь переменная АсcessApp является ссылкой на объект Асе ess. Application):
Dim Index As Integer
With AccessApp
For Index=0 To .DataAccessPages.Count-1
.DoCmd.Close AcDataAccessPage, _
.DataAccessPages(Index).Name, acSaveYes
Next Index
Для проверки того, открыта ли форма Employees, и для получения ссылки на ее набор записей (recordset), используется следующий фрагмент:
Dim rsEmployees As ADODB.Recordset
With AccessApp
If .AllForms("Employees").IsLoadeed Then
Set rsEmployees=.Forms("Employees").recordset
End If
End With

Объект CurrentProject
Объект CurrentProject возвращает ссылку на текущий проект. Этот объект используется для доступа и манипуляций с компонентами приложения проекта баз данных. Вы уже немного знакомы с некоторыми свойствами этого объекта, такими как коллекции All****, рассмотренные ранее в этой главе. В табл. 2 перечислены свойства объекта CurrentProject.
Объект CurrentData
Объект CurrentData используется для ссылки на элементы данных текущей базы. Эти элементы являются составными частями самой базы данных, в отличие от элементов частей приложения (доступ к которым осуществляется с помощью объекта CurrentProject). Объект CurrentData имеет только пять свойств: AllDatabaseDiagrams, AllQueries, AllStoredProcedures, AllTables и AllViews. Каждое из этих свойств ссылается на коллекцию доступных объектов определенного типа. Эти коллекции уже рассматривались ранее в этой главе при описании объекта AccessObject.
Объект Screen
В приложении Access объект Screen используется для получения ссылки на форму, отчет или элемент управления, на котором в настоящее время находится фокус. Объект Screen, доступный с помощью свойства Screen объекта Application, имеет следующие свойства, каждое из которых возвращает ссылку на текущий активный объект определенного типа:
ActiveControl
ActiveDataAccessPage
ActiveDatasheet
ActiveForm
ActiveReport
Если попытаться прочитать одно из этих свойств, а объект соответствующего типа не будет в это время активен, возникнет ошибка. Свойство MousePointer объекта Screen управляет внешним видом указателя мыши на всем пространстве экрана. Возможные установки представлены далее.
0 По умолчанию. Внешний вид определяется приложением Access
1 Обычное выделение (стрелка) 3 Выделенный текст (значок I)
7 Изменение размеров по вертикали (двунаправленная стрелка)
9 Изменение размеров по горизонтали (двунаправленная стрелка)
11 Ожидание (часики)

Таблица 2. Свойства объекта CurentProject
Свойство Описание
AllDataAccessPages Коллекции ссылок, содержащие объекты Accessobject для всех доступных объектов определенного типа (форм, отчетов, и т.п.)
AllForms
AllMacros
AllModules
AllReports
BaseConnectionString Возвращает строку соединения с базой данных, которая была использована. Доступно только для чтения
Connection Возвращает ссылку на текущий объект ADO Connection. Только для чтения
FullName Полное имя (содержащее путь) текущего проекта баз данных. Только для чтения
IsConnected Принимает значение True, если текущий проект подключен к своему источнику данных. В противном случае False
Name Возвращает имя файла (без пути) текущего проекта
Path Путь (без имени файла) к текущему проекту
ProjectType Возвращает тип текущего проекта: acADP (для проектов ADP) или acMDB (для проектов MDB)
Properties Возвращает ссылку на коллекцию AccessObjectProperties, содержащую дополнительные свойства объектов
Объект DoCmd
Объект DoCmd позволяет управлять действиями приложения Access из программы VBA. Действия выполняют множество задач, таких как открытие отчетов и страниц доступа к данным, закрытие окон, работа с элементами управления. Объект DoCmd доступен в качестве одноименного свойства объекта Application. Его синтаксис:
AccessApp.DoCmd.action [argl, arg2, ...]
Здесь AccessApp является ссылкой на объект Access.Application. Аргумент Action является именем поддерживаемого метода. Argl и т. д., которые представляют собой аргументы конкретного вызываемого метода. Объект DoCmd имеет множество доступных ему методов (или действий), и в случае необходимости мы будем их использовать.
Параметры доступа
Когда используется приложение Access, параметры, которые влияют на его работу, можно изменить в диалоговом окне Options. Они управляют отображением панели состояния, границами печати, каталогом для файлов данных и шрифтом по умолчанию для таблиц данных. Для работы с этими параметрами в программе используются методы GetOption и SetOption объекта Application. Их синтаксис имеет следующий вид:
Aplication.GetOption (OptionName)
Application.SetOption {OptionName, Setting)
Здесь OptionName является именем параметра, a Setting новым его значением. Метод GetOption возвращает текущее значение заданного параметра, имеющее тип Variant. Способ, которым возвращается параметр, определяется его типом.
Да/Нет. Возвращается значение True или False, в зависимости от установки параметра.
Заданная пользователем строка или число. Строка содержит значение свойства таким, каким оно отображается в диалоговом окне.
Выбор из списка или из группы параметров. Число задает позицию текущей установки в списке или группе начиная с нуля.
Значения, передаваемые при установке параметров, работают таким же образом. Если в программе VBA есть необходимость изменить значения параметров Access, хорошей практикой считается сохранение их исходных значений и восстановление перед завершением программы. Для того чтобы посмотреть список имен Access, откройте справку Visual Basic и щелкните на ссылке Set Options from Visual Basic (Установка параметров из программ Visual Basic).
Параметры запуска
Приложение Access имеет еще одну категорию параметров, называемую параметрами запуска. Вместо управления самим приложением Access, эти параметры управляют внешним видом приложения при запуске. Например, параметры запуска содержат заголовок приложения, его пиктограмму, начальную форму или страницу, отображаемую при запуске. В приложении Access параметры запуска можно просмотреть в диалоговом окне Startup (выберите в меню команду Tools->Startup). Параметры запуска хранятся в коллекции Properties базы данных. В базах данных MDB эта коллекция является свойством объекта Database; в базах данных ADP свойством объекта CurrentProject. Перед тем как установить значение параметра запуска, следует добавить его в коллекцию Properties. Это выполняется установкой значения параметра. Если не возникнет ошибок, значит, этот параметр уже был в коллекции Properties и изменение его значения прошло успешно. Если этого параметра в коллекции не было, возникнет ошибка, которую следует исправить, и создать нужный параметр. Коллекцией Properties можно добавить собственный параметр в проекте базы данных.
Фрагмент программы, приведенный в листинге 2, содержит процедуру, которая устанавливает значение дополнительного параметра и создает его, если он не существует. Это свойство определяется в объекте Property, который нужно сначала создать, а потом уже добавить в коллекцию Properties. В этом фрагменте предполагается, что свойство было добавлено как текстовая строка, что выражено во втором аргументе метода CreateProperty dbText. Свойства могут иметь многие типы, допустимые в Access, например, тип даты (dbDate), тип валюты (dbcurrency) или булево значение (dbBoolean). Полный список типов и им соответствующих констант содержится в онлайновой справке по свойству Туре.
Листинг 2. Установка дополнительного параметра запуска
Public Function SetCustomProperty (PropertyName As String, _
PropertyValue As String) As Boolean
Dim db As Object
Dim prop As Property
' Обработка ошибки "property not found".
On Error GoTo PropNotFound
' Установка текущей базы данных.
Set db = CurrentDb
' Пробуем установит значение свойства
db.Properties(PropertyName) = PropertyValue
' В случае успеха возвращаем True.
SetCustomProperty = True
Done:
Exit Function
PropNotFound:
If Err = 3270 Then ' Код ошибки "property not found"
' Создаем свойство.
Set prop = db.CreateProperty (PropertyName, _
dbText, PropertyValue)
' Добавляем его к коллекции Properties
db.Properties.Append prop
Resume
Else
SetCustomProperty = False
Resume Done
End If
End Function
Работа с формами и отчетами
Формы и отчеты в Access подобны тем, что отображают данные пользователю. Отличаются же они тем, что формы допускают редактирование и ввод пользователем данных, а отчеты нет. Они позволяют только просмотр данных. Например, если пользователь хочет ввести некоторую информацию в базу данных, необходимо разработать форму. Для печати счетов-фактур с целью последующей отправки заказчикам следует использовать отчеты. Отчеты позволяют снимать с них снимки (snapshot), чтобы отчет можно было просмотреть и вне Access. После того, как снимок снят, отчет перестает быть динамическим. Это означает, что он больше не отражает изменения, происходящие в данных.
Существует два шага при использовании форм или отчетов в прикладном офисном приложении. Сначала форму или отчет необходимо создать. Несмотря на то, что можно создать форму и отчет непосредственно в программе VBA, все же легче (и это чаще всего применяется) создать их в приложении Access, а после этого использовать в проекте VBA. Описание деталей создания отчетов и форм выходит за рамки настоящего конспекта, однако, попытаемся изложить некоторые базовые концепции, которые следует знать для эффективного использования отчетов и форм в прикладных приложениях.
Для создания формы или отчета запустите Access и откройте проект базы данных, с которым вы работаете. В меню Вставка выберите команду Форма или Отчет. Диалоговое окно Новый отчет показано на рис. 1. Диалоговое окно Новая форма аналогично ему. В любом случае Access предоставляет некоторые средства для создания новых отчетов и форм, в том числе и мастера. В этом месте также можно выбрать из списка, содержащегося в диалоговом окне, источник данных, который будет использовать эта форма. Этот список позволит вам выбрать из всех таблиц и запросов, которые существуют в проекте.

Рис. 1. Диалоговое окно Новый отчет первый шаг в разработке нового отчета в Access
Приложение Access предоставляет мощные средства для разработки форм и отчетов. Например, на рис. 2 показан новый, пустой отчет, открытый в конструкторе отчетов. Вся белая область с сеткой представляет сам отчет с областями заголовка, колонтитулов и деталей самого отчета. В одном из маленьких окон перечислены все поля, которые существуют в источнике данных, выбранном для данного отчета.
Запросы
Запрос это способ получения подмножества записей из таблицы базы данных.
Например, можно создать запрос, основанный на таблице Address, который возвращает только людей, живущих в Калифорнии. Запросы имеют некоторое сходство с SQL-конструкцией WHERE, однако, они гораздо мощнее. Например, запрос может выполнить вычисления с данными, отобразить итоговые значения, изменить данные в таблицах и т.д. Часто формы и отчеты основываются на запросах. Запрос отбирает данные и манипулирует с ними, а формы и отчеты их отображают. Запросы также играют важную роль при работе с реляционными базами данных, позволяя отбирать и работать с данными из двух или более связанных таблиц.

Рис. 2. Создание отчета в конструкторе отчетов
Панель инструментов позволяет выбрать любые элементы управления из числа предлагаемых приложением Access. Эти элементы предоставляют большую гибкость при компоновке и отображении данных в отчете. Наиболее часто используемые элементы представлены ниже.
Текстовое поле. Отображает текст, который пользователь может редактировать.
Надпись. Отображает нередактируемый текст.
Флажок. Отображает данные, которые могут иметь значения True или False.
Список. Позволяет пользователю производить выбор из списка значений.
Рисунок. Позволяет отображать в отчете графические объекты.
Панель инструментов отображает только те элементы управления, которые входят в Access. Кнопка в правом нижнем углу предоставляет некоторые дополнительные элементы. Щелкнув по ней, можно увидеть полный список доступных в системе элементов управления. Достоинством работы с элементами управления является то, что их можно связать с данными. При помощи установки свойства элемента управления можно задать, из какого источника данных будут автоматически отображаться данные в этом элементе. Элемент управления может отображать текст, рисунки и т.п., а также сводные данные.
В результате получается, что формы и отчеты в Access большей своей частью автоматизированы. Ссылки или связи между элементами и источниками данных, встроенные в форму или отчет, работают автоматически, чаще всего без вмешательства со стороны программиста VBA. Для отчетов это своего рода дорога с односторонним движением. Данные поступают из источника в отчет и отображаются элементами управления. В форме движение идет в обе стороны. Данные поступают из источника, отображаются в форме, а затем новые или отредактированные данные отправляются назад, в источник данных.
Практически в любом случае формы и отчеты (в меньшей степени) также содержат программы VBA. Большая часть этих функций работает внутри формы или отчета, выполняя такие задачи, как счет, или проверка данных в форме. Некоторые программы VBA могут взаимодействовать с внешними элементами, например, открывать другую форму при закрытии данной. Потребность в таком программировании зависит от цели использования формы. Если коллекция форм или отчетов разработана для запуска в Access, вам, скорее всего, в формах потребуется внутренняя программа, управляющая перемещением пользователя от одной формы или отчета к другой. С другой стороны, если формы разработаны для того, чтобы быть автоматизированными с помощью программ VBA в прикладных приложениях, вам, скорее всего, потребуется возложить управление перемещением пользователя на программу, а не на внутренние функции форм и отчетов. Если вы заняты адаптацией существующих форм и отчетов к прикладному приложению VBA, вы не должны пропустить такие встроенные программы, чтобы избежать непредвиденных результатов.
Использование элементов управления
Несмотря на то, что большая часть вещей в формах и отчетах Access происходит автоматически, иногда следует работать напрямую с элементами управления форм и отчетов. Отдельные элементы управления доступны посредством коллекции Controls, которая состоит из отдельных объектов для каждого элемента управления формы или отчета. Как и во всех коллекциях, доступ к отдельным элементам можно получить по свойству Name или по их индексу, который в коллекции Controls находится в диапазоне от 0 до Controls.Count-1. Чаще всего вам придется использовать цикл For Each для перебора всех элементов в коллекции.
Каждый элемент управления имеет свойство ControlType, в котором содержится тип элемента. Это свойство нельзя изменить, его можно только прочитать в тексте программы. Возможные значения свойства ControlType перечислены в табл. 3.
Рассмотрим пример использования элемента управления. Следующий фрагмент возвращает значение, содержащееся в текстовом поле с именем txtcompa-nyName в форме с именем Customers:
Buf=Forms!Customers!txtCompanyName.Value
Текст программы, приведенный в листинге 3, является примером цикла, который проходит по всем элементам управления формы, работая только с элементами некоторого типа. В качестве аргумента процедуре передается ссылка на форму, а ее текст в цикле по всем элементам управления вставляет во все текстовые поля значение пустой строки.
Таблица 3. Возможные значения свойства ControlType
Константа Тип элемента управления

AcBoundObjectFrame Рамка объекта
AcCheckBox Флажок
AcComboBox Комбинированный список
AcCommandButton Кнопка команды
AcCustomControl Дополнительный элемент ActiveX
AcImage Рисунок
AcLabel Надпись
AcLine Линия
AcListBox Список
acObjectFrame Кадр объекта или диаграмма без рамки
acOptionButton Кнопка переключателя
acOptionGroup Группа переключателей
acPage Страница
acPageBreak Разрыв страницы
acRectangle Прямоугольник
acSubForm Подформа
acTabCtl Табуляция
acTextBox Текстовое поле
acToggleButton Кнопка переключения

Листинг 3. Процедура ClearAIITextBoxes устанавливает в пустую строку содержимое всех текстовых полей заданной формы
Sub ClearAIITextBoxes(f As Form)
Dim с As Control
For Each с In f.Controls
If c.ControlType = acTextBox Then c.Value = ""
Next с
End Sub
Коллекция Controls не ограничивается только объектами форм и отчетов. Формы и отчеты состоят из нескольких разделов, таких как заголовок, колонтитулы и разделы детализации. Отдельные разделы доступны посредством свойства Section:
Obj.Section(SectionType)
где Obj является ссылкой на форму или отчет. Аргумент SectionType задает интересующий раздел, согласно спецификации в табл. 4.

Точки и восклицательные знаки в именах объектов
Вы уже видели знаки точек, используемые для обозначения свойств объектов, например: Object.Property. Оператор точки указывает, что все, что следует за ней, является элементом, определенным в Access. Например, в следующей строке идентификаторы Controls и Value являются определенными в Access элементами:
Buf=FORMS("Sales").Controls.("txtName").Value
Оператор ! можно использовать для непосредственного обращения к элементам, определенным пользователем. Таким образом, следующий фрагмент является эквивалентен предыдущему:
Buf=Forms!Sales.Controls!txtName.Value
Продолжая и предполагая, что Controls является свойством по умолчанию объекта Form, вышеуказанный фрагмент можно еще больше упростить:
Buf=Forms!Sales!txtName.Value
Во всех трех фрагментах выполняется одно и то же: осуществляется доступ к значению свойства Value элемента управления txtName формы Sales.

Таблица 4. Константы для задания типа раздела
Константа Тип элемента управления
acDetail Раздел детализации
AcHeader Раздел заголовка
acFooter Раздел окончания
acPageHeader Раздел верхнего колонтитула
acPageFooter Раздел нижнего колонтитула
AcGroupLeveliHeader Раздел заголовка группы уровня 1
AcGroupLevellFooter Раздел окончания группы уровня 1
AcGroupLevel2Header Раздел заголовка группы уровня 2
AcGroupLevel2Footer Раздел окончания группы уровня 2
Последние 4 константы применимы лишь к отчетам.
Следующий фрагмент проходит в цикле по всем элементам управления раздела деталей заданной формы:
Dim с As Control
For Each c In Forms!Personnel.Section(acDetail).Controls
. . .
Next с
Два элемента управления имеют собственные коллекции Controls. Элемент группы, используемый для группировки связанных элементов, имеет коллекцию Controls, который содержит кнопку, флажок, фиксируемую кнопку и (или) элемент ярлыка группы. Элемент раздела Tab, используемый для создания многостраничных диалоговых окон, имеет коллекцию Pages. Объект каждой страницы Page имеет свою собственную коллекцию Controls, содержащую элементы этой страницы.

Me
Ключевое слово Me возвращает ссылку на форму, отчет или модуль класса, в которой содержится запущенная программа. Таким образом, программа, запущенная в форме, очистит все текстовые поля формы, используя процедуру из листинга 3 следующим образом:
Call СlearAllTextBoxes(Me)

Страницы доступа к данным
Технология страниц доступа к данным специально разработана для осуществления доступа к данным со страниц Web. На самом деле страница доступа к данным является документом HTML (Hypertext Markup Language), который содержит дескрипторы и специальные элементы HTML, а также элементы ActiveX. Приложение Microsoft Internet Explorer после 5-ой версии является наиболее привлекательным броузером для работы со страницами доступа к данным. Другие броузеры могут тоже отображать эти страницы, но не позволяют пользователю взаимодействовать непосредственно с данными. На рис. 3 показана страница доступа к данным, отображенная в Internet Explorer. Эта страница показывает использование элемента PivotTable (перекрестная таблица) одного из множества элементов, которые могут присутствовать на страницах доступа к данным.
С некоторой точки зрения, страницу доступа к данным можно представлять как комбинацию формы и отчета, основанную на Web. Эти страницы похожи на отчеты тем, что могут отображать сводные данные и допускают большую гибкость в сортировке, группировке и фильтрации данных. Они аналогичны формам, в которых пользователь может добавлять, удалять и редактировать записи. Хотя страницы доступа к данным и разработаны для Web, их можно просмотреть и непосредственно в Access.
Приемлемы ли страницы доступа к данным для прикладных приложений? Иногда одна-две страницы доступа к данным и станут решением, особенно когда вам необходимо лишь работать с данными в Internet или в локальной сети, а не использовать другие офисные приложения. Так как в таком проекте будет использоваться только Access, это становится вопросом скорее разработки в Access, чем разработки в среде Office, в приложениях которой совместно используются два и больше приложений пакета. Страницы доступа к данным имеют свое применение даже не в основанных на Web проектах. Так как эти страницы можно просмотреть либо в Access, либо в броузере, всегда можно выбирать того, как они могут быть представлены пользователю. Страницы доступа к данным имеют множество возможностей, которые вы можете захотеть использовать в своих проектах, даже если они никак не связаны с Web.

Рис. 7.3. Страница доступа к данным, отображенная в Internet Explorer

Соглашения о безопасности
Страницы доступа к данным поднимают обоюдоострую проблему безопасности. Придется заняться не только защитой баз данных, но и безопасностью в Internet. Вопросы безопасности будут рассмотрены далее.
Создание страниц доступа к данным
Страницы доступа к данным можно создать и программным путем, однако, как и в случае с формами и отчетами, проще для этого воспользоваться представлением конструктора в приложении Access. В данном случае существует три возможности: можно для создания страниц использовать Мастер, можно открыть пустую страницу в представлении конструктора, и можно открыть существующую Web-страницу, которую вы хотите взять за основу в создании страницы доступа к данным. На рис. 4. показана пустая страница доступа к данным в окне конструктора. Как и в случае с формами и отчетами, существует панель инструментов с элементами, которые можно разместить на странице, хотя набор этих элементов при создании страниц доступа к данным несколько отличается. Некоторые элементы предназначены только для Web-страниц, например, гиперссылки, которые связаны с данными. Существуют также специализированные элементы, которые предназначены только для страниц доступа к данным, такие как самостоятельная диаграмма для графического отображения данных и миниатюрная таблица для отображения данных в строках и столбцах.
При разработке страниц доступа к данн
·ым задается база данных, с которой связана эта страница, а также задаются таблицы и (или) поля, с которыми связаны отдельные ее элементы. Когда страница просматривается пользователем либо в Internet Explorer, либо в Access, автоматически устанавливается связь между данными и страницей, и данные переносятся в случае необходимости на страницу и из нее.

Рис. 7.4. Использование конструктора для создания страницы доступа к данным
Использование страниц доступа к данным
Ранее мы уже увидели, как использовать метод OpenDataAccessPage объекта DoCmd для открытия страниц доступа к данным в режиме просмотра или конструктора. Его синтаксис:
DoCmd.OpenDataAccessPage datapagename, datapageview
где datapagename имя открываемой страницы доступа к данным, a datapageview указатель на то, в каком режиме эту страницу открывать: конструктора (acDataPageDesign) или просмотра (acDataPageBrowse, по умолчанию). Хотя и возможно открыть страницу в режиме конструктора и манипулировать ею с помощью программы VBA, чаще всего страницы открываются в режиме просмотра для работы с ними пользователя. В следующем фрагменте открывается база данных Northwind и отображается страница доступа к данным "Review Products" в режиме просмотра:
dim AccessApp As Access.Application
set AccessApp=New Access.Application Const PATH= _
"c:\program files\microsoft office\office\samples\"
AccessApp.OpenCurrentDatabase PATH & "northwind.mdb"
AccessApp.DoCmd.OpenDataAccessPage "Review Products"
AccessApp.Visible=True
Страницы доступа к данным можно также открыть и в Internet Explorer, что иногда предпочтительнее. Это выполняется при помощи команды Shell, которая принимает командную строку в качестве первого аргумента, а в качестве второго индикатор состояния окна. Следующий фрагмент открывает Internet Explorer в максимизированном окне и загружает страницу доступа к данным с именем review orders.htm:
Const IE_PATH= _
"C:\Program Files\Internet Explorer\IEXPLORE.EXE "
Const DAP_PATH= _
"c:\program files\microsoft office\office\samples\"
Const DAP_NAME= "review orders.htm"
Shell IE_PATH & DAP_PATH & DAP_NAME, vbMaximizedFocus
Защита баз данных
Защита баз данных является весьма сложным вопросом, но он должен быть представлен, прежде всего, двумя аспектами. Первый касается безопасности данных. Многие базы данных содержат конфиденциальную информацию, и в современном мире компьютеров, связанных единой сетью, важность защиты этих данных от несанкционированного доступа и искажения данных возрастает с каждым днем. Защита баз данных от несанкционированного вмешательства содержит такие технологии, как установление безопасности на уровне пользователей и шифрование баз данных. Но эти вопросы выходят за рамки данной книги.
Вторым аспектом вопроса о безопасности является защита вашего прикладного решения. Вам необходимо защитить свою интеллектуальную собственность, ограничив доступ других людей к деталям функционирования объектов в вашем приложении. Вы также захотите защитить свое решение от умышленного искажения его логики другими пользователями сети. Существует несколько подходов относительно защиты прикладного решения. Выбор его зависит от характеристик решения и от необходимого уровня безопасности. Заметим, что технологии, используемые для защиты базы данных, такие как безопасность на уровне пользователей, также могут оказать содействие и в защите текста программы и объектов.
Параметры запуска
Когда речь идет лишь о среднем уровне безопасности, можно воспользоваться параметрами запуска Access для ограничения уровней работы с приложением, к которым пользователи имеют доступ. В их числе меню, панели инструментов, окна базы данных и специальные ключи. Эту технологию можно использовать только тогда, когда ваше приложение имеет при запуске форму и дополнительное меню, которое содержит необходимые пользователю команды. Требуемые параметры запуска можно установить из программы VBA, как было описано ранее в этой главе, или непосредственно в Access.
В меню Сервис выберите команду Параметры запуска для открытия диалого вого окна.
Выберите требуемую форму запуска в списке Отображать форму.
В списке Контекстное меню выберите необходимое меню.
Отключите все параметры диалогового окна, за исключением Строка состояния.
В VBA-программе установите свойство AllowBypassKey в значение False, чтобы пользователи не могли использовать клавишу для обхода параметров запуска. Параметры запуска могут использоваться для защиты как файлов MDB, так и файлов ADP.
Пароли к базам данных
Назначая пароль базы данных, можно ограничить доступ к ней. Однако заметим, что пароль не ограничивает пользователя в действиях, которые он может выполнять в приложении, например, просмотреть текст программы, если он открыл файл. Если база данных снабжена паролем, он запрашивается у пользователя при попытке ее открыть. Ниже описаны действия по установке пароля для базы данных непосредственно в Access. Заметим, что хорошей практикой считается создание резервной копии базы данных перед установкой на нее пароля. Пароли можно использовать только при работе с файлами MDB.
Выберите в меню Файл->Открыть.
Выберите файл базы данных и выделите его имя.
Щелкните на стрелке рядом с кнопкой Открыть и выберите Монопольно.
Выберите Сервис->Защита->Задать пароль базы данных.
В диалоговом окне дважды введите пароль, после чего щелкните ОК. Обратите внимание, что пароли зависят от регистра.
Для того чтобы удалить пароль, выполните действия с 1 до 3, а вместо действия 4 выберите в меню команду Сервис->Защита->Удалить пароль базы данных и введите в окне существующий пароль.
Установка, изменение и удаление пароля базы данных с помощью программы VBA возможны с помощью технологии DAO (Data Access Objects) и невозможны при использовании технологии ADO (Active Data Objects). Многочисленные достоинства технологии ADO предопределяют то, чтобы именно она использовалась в новых проектах, а для установки и изменения паролей придется работать непосредственно в Access, как было описано выше. Однако в программах с использованием ADO защищенные паролем базы данных можно открыть, не вызывая при этом окна запроса пароля. Пароль может быть жестко зашит в саму программу VBA. Также можно в программе запрашивать у пользователя его ввод.
Для того чтобы открыть защищенную паролем базу данных, пароль задается как свойство Jet OLEDB:Database Password (синтаксис этого свойства зависит от производителя конкретной базы данных). Для этого существует два способа: посредством свойства объекта Connection, используемого для установки соединения с базой данных или в качестве части строки соединения, передаваемой в качестве аргумента методу Open. Следующий фрагмент демонстрирует первую технологию для открытия базы данных c:\data\employees.mdb, защищенной паролем BANANA:
Dim adoCon As ADODB.Connection
Set adoCon=New ADODB.Connection
With adoCon
.Provider="Microsoft.Jet.OLEDB.4.0"
.Mode=adModeReadWrite
.Properties ("Jet OLEDB:Database Password")="BANANA"
.Open "с:\data\employees.mdb"
End With
Скрытие текста программы VBA
Приложение Access позволяет сохранять файлы базы данных в формате, в котором программа VBA скомпилирована и может быть запущена, но не может быть просмотрена или изменена. Это касается как файлов MDB, так и файлов ADP. Эта технология может быть использована как самостоятельно, так и совместно с другими формами защиты баз данных. Сохранение базы данных в файлах MDE и ADE предотвращает следующие действия пользователя:
просмотр и редактирование текста программы VBA;
создание и изменение форм, отчетов и модулей;
добавление и удаление ссылок на библиотеки объектов;
открытие Object Browser.
Для сохранения проекта в файле .MDE или .ADE запустите Access и откройте файл MDB или ADP, убедившись, что другие пользователи эти файлы не открыли (если они используются совместно в сети). В меню выберите команду Tools->Database Utilities->Make MDE (ADP) File. Задайте имя и размещение файла, после чего щелкните на Save. Исходный файл останется неизмененным, но сохранится файл .mde или .ade.
Файлы MDE и ADE не могут быть преобразованы назад в файлы MDP и ADP. Если в проект вносятся какие-либо изменения, будь то в программу VBA или в формы или отчеты, работа производится непосредственно с исходными файлами MDB или ADP, после чего следует снова сохранить проект в файле MDE или ADE. Это значит, что исходный файл необходимо хранить.
Создание файлов MDE не считается хорошим подходом, если в файле MDB содержатся таблицы с данными. Если после этого внести изменения в исходный файл MDB, можно столкнуться с проблемой урегулирования нескольких версий данных, так как таблицы в файле MDE, скорее всего, были также изменены. Это значит, что сохранение проекта MDB в файле MDE реально уместно лишь в случае, когда решение было внедрено в качестве последней конфигурации.
Active Data Objects
Технология ADO (Active Data Objects) от Microsoft является самой современной среди доступных разработчику прикладных приложений в среде Office. Прикладной проект в Office может также использовать и более ранние технологии доступа к данным, такие как DAO или RDO, но они применяются лишь для поддержки уже существующих проектов. Технология ADO вырвалась далеко вперед как по своим функциям, так и по своей простоте использования, она считается наилучшим выбором при разработке новых проектов. ADO является частью операционной системы Windows, а не частью приложения Access. Таким образом, прикладные приложения в Office могут использовать ADO вообще без автоматизации Access.
Как и все рассмотренные нами средства разработки, ADO имеет свою объектную модель, которая определяет связи между различными компонентами, входящими в ее состав. По сравнению с другими технологиями баз данных, объектная модель ADO поражает своей простотой. Немного упрощенная ее диаграмма представлена на рис. 5.
Объект Connection представляет соединение между программой и источником данных (файлом, в котором размещены фактические данные). Когда создается объект Connection, следует задать параметры, которые требуются для открытия источника данных, такие как производитель базы данных (по нему идентифицируется тип источника данных), а в случае необходимости имя пользователя, пароль и т.д. Объект Connection также позволяет посредством транзакций вносить изменения в источник данных. В некоторых случаях объект Connection следует создавать в программе явным образом. В других случаях соединение может быть создано неявно. Каждый объект Connection имеет свою коллекцию Errors, содержащую объекты Error для каждой обнаруженной при соединении ошибки (если такие возникнут).
Объект Command используется для запроса на выполнение некоторого действия. С помощью ADO можно выполнять множество действий, не прибегая к услугам объекта Command, но случается и такое, когда он оказывается необходим. Объект Command может представлять выражение SQL, хранимую процедуру или запрос. Он содержит коллекцию Parameters, состоящую из объектов Parameter для каждого аргумента команды.


Рис. 7.5. Объектная модель ADO
Однако, чаще всего придется работать с объектом Recordset. Как и следует из его имени, объект Recordset содержит коллекцию записей из источника данных. Набор записей еще иногда называют результирующим набором (result set). Он может содержать записи из одной таблицы источника данных, а при работе с реляционными базами данных записи, составленные из полей двух и более таблиц источника данных. Набор данных может содержать как все записи из источника данных, так и лишь те из них, которые удовлетворяют некоторому заданному критерию. Каждый объект Recordset содержит коллекцию Fields, содержащую объект Field для каждого поля в наборе данных.
Каждый из четырех основных классов объектной модели ADO (Connection, Command, Recordset и Field) содержит также коллекцию Properties (она на рисунке не показана), содержащую параметры объекта.
Создание соединения
Первым шагом доступа к базе данных из программы VBA является установление с ней соединения. В общем случае это требует выполнения следующих трех действий:
создания экземпляра объекта ADO Connection;
установки его свойств, определяющих требуемое соединение;
выполнения метода Open.
Перед тем как привести пример установления соединения ADO, следует рассмотреть свойства и методы объекта Connection. В табл. 5 и табл. 6 приведены наиболее часто используемые свойства и методы этого объекта. Не претендуя на исчерпывающие данные, из соображений ясности, в них опущены некоторые расширенные свойства и методы, которые редко требуют изменения своих значений, установленных по умолчанию. Обратите внимание, что свойства объекта Connection доступны для чтения и записи только тогда, когда соединение еще не установлено. Когда соединение открыто, они доступны лишь для чтения.

Таблица 7.5. Свойства объекта Connection Свойство Описание
CommandTimeout Время в секундах, в течение которого объект Connection будет пытаться выполнить команду, после чего операция отменяется и возвращается ошибка. Значение по умолчанию 30 секунд
Connect ionstring Строка, содержащая детали соединения с базой данных. Детали описываются в основном тексте раздела
ConnectionTimeout Время в секундах, в течение которого объект Connection будет пытаться установить соединение с базой данных, после чего операция отменяется и возвращается ошибка. Значение по умолчанию 15 секунд
Mode Определяет разрешения на чтение, запись и совместное использование. Допустимые значения приведены в табл. 7
State Возвращает состояние объекта Connection: adStateClosed (значение 0) или adStateOpen (значение 1)

Таблица 7.6. Методы объекта Connection
Метод Описание
Close () Закрывает текущее соединение с базой данных
Execute (command) Выполняет заданную команду SQL. Для некоторых команд
возвращает ссылку на результирующий набор записей
Open (Connectstring, User, Password)
Открывает соединение с источником данных. Если информация о соединении задана в свойстве ConnectionString, метод может быть вызван без аргументов
OpenSchema (QueryType, Criteria, SchemalD)
Возвращает информацию об организации источника данных. Более полная информация о методе приведена в разделе «Получение информации об источнике данных» далее в конспекте

SQL
Структурированный язык запросов (Structured Query Language или SQL) стал де-факто стандартом для доступа и работы с базами данных. Он может показаться невероятно сложным, но его основы чрезвычайно просты для понимания. SQL отличается от VBA тем, что он является непроцедурным языком. Он не имеет операторов для управления ходом выполнения программы или последовательностями выполняемых операторов. Вместо этого SQL позволяет задавать желаемый конечный результат, а механизм базы данных уже сам обеспечивает достижение поставленной задачи. Предположим, что у нас имеется таблица Contacts, которая содержит поля имени, адреса, города, штата, номера телефона и прочих реквизитов клиентов. Если необходимо получить список имен и адресов электронной почты каждого из тех, кто проживает в Калифорнии, причем список должен быть отсортирован в алфавитном порядке согласно имени, можно выполнить следующее выражение SQL:
Select Name, Email From Contacts Where State='CA' Order By Name
Если имя поля содержит в себе пробел, оно должно быть заключено в одинарные кавычки:
Select 'First Name', 'Last Name' From Contacts Where State='CA' Большинство наиболее часто используемых выражений SQL имеет следующую фундаментальную структуру:
Select Fields From Table Where Condition Order By OrderField
Выражение SQL может быть и более сложным, особенно при работе с реляционными базами данных. В книгах, посвященных этой тематике, и практически в любой книге по программированию в базах данных можно найти более полное описание языка SQL.
Создание строки соединения
Строка соединения может принимать две формы. Первая называется Data Source Name или DSN (имя источника данных). Этот тип соединения можно использовать только тогда, когда он установлен в базе данных, к которой открывается доступ. Если у вас не получится сделать это самостоятельно, может вам помочь системный администратор.
Таблица 7.7. Значения свойства Mode объекта Connection

Константа
Значение
Описание

adModeUnknown
0
По умолчанию. Разрешения не установлены

adModeRead
1
Только для чтения

adModeWrite
2
Только для записи

adModeReadWr i t e
3
Чтение и запись

adModeShareDenyRead
4
Запрещение для других открывать соединение в режиме чтения


adModeShareDenyWrite
8
Запрещение для других открывать соединение в режиме записи


adModeShareExclusive
12
Запрещение для других открывать любой тип соединения

adModeShareDenyNone
16
Запрещение для других открывать новое соединение с разрешениями

Второй формой строки соединения является строка с внедренными параметрами, определяющими параметры соединения. Возможные параметры (разделенные запятыми) представлены ниже.
Provider. Имя изготовителя данного OLE DB.
Data Source. Имя источника данных.
ID. Идентификатор пользователя (если необходимо).
Password. Пароль (если необходимо).
File Name. Имя специфичного для изготовителя файла, содержащего информацию о соединении.
Remote Provider. При использовании службы удаленного доступа к данным (RDS, Remote Data Service).
Прочие параметры. Прочие параметры, специфичные для конкретного изготовителя базы данных.
Любая конкретная строка соединения будет содержать только некоторые из этих параметров. Невозможно дать примеры всех возможных типов строк соединения, однако некоторые из них мы все же приведем. В следующей строке соединения открывается соединение с базой данных Microsoft Jet OLE DB версии 4 в файле Sales2001.mdb (формат базы данных, используемый приложением Access):
ProviderMicrosoft.Jet.OLEDB.4.0; Data Source=Sales2001.mdb;
Следующая строка открывает соединение с базой данных Employee в сервере баз данных SQL Server с именем HDQTR1, с именем пользователя peter и паролем grapes:
Driver= {SQL Server}; server=HDQTRl; database=employees; uid=peter; pwd=grapes
Детали создания правильной строки соединения могут вначале показаться сложными. Если вы работаете с созданной самостоятельно базой данных, детали создания строки соединения вы можете отыскать в документации по данной СУБД (системе управления базой данных).
База данных Борей
Все примеры этого раздела работают с базой данных борей.mdb. Это пример базы данных, устанавливаемой вместе с приложением Microsoft Access, и многие пользователи уже имеют ее в своей системе или даже работали с ней. Если же таковой базы у вас нет, вы можете использовать любой другой совместимый с OLEDB источник данных, внося необходимые изменения в строки соединения в примерах.
Листинг 4 приводит пример создания и открытия объекта ADODB Connection. Эта программа не выполняет никаких действий с самими данными, а только демонстрирует основные действия при открытии соединения, проверке ошибок и закрытии соединения. Важность проверки ошибок трудно переоценить. Существует такое количество вероятных источников ошибок, особенно если программа работает с информацией, введенной пользователем, что всегда присутствует вероятность опечатки или другой ошибки. В приводимой процедуре показан общий подход, рекомендуемый для обработки ошибок в ADO: выполнить On Error Resume Next, а затем после каждой операции ADO, которая потенциально может привести к ошибке, проверять объект Err и в случае обнаружения ошибки реагировать соответствующим образом.

Листинг 4. Открытие соединения ADODB
Public Sub List0704()
Dim adoCon As ADODB.Connection
Dim conString As String
Dim msg As String
On Error Resume Next
' Создание строки соединения
conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
conString = conString & "Data Source="
conString = conString & "C:\Program Files\Microsoft Office\Office10\Samples\Борей.mdb;"
' Создание объекта Connection
Set adoCon = New ADODB.Connection
adoCon.ConnectionString = conString
' Открытие соединения
adoCon.Open
'Проверка ошибок и вывод сообщения
If Err = 0 Then
MsgBox "Подключение выполнено."
Else
msg = "Ошибка при подключении к базе данных:" & vbCrLf
msg = msg & "Ошибка: " & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
End If
' Здесь выполняется обработка данных.
' Закрытие соединения
adoCon.Close
End Sub

Наборы данных
Большая часть работы с базами данных будет выполняться с наборами данных. Как вы уже узнали из конспекта, набор записей представлен в ADODB объектом Recordset. В сущности, набор записей позволяет отбирать подмножество данных из таблиц базы данных. Простым примером может стать набор записей, который отбирает из адресной таблицы все записи, содержащие в поле STATE значение California. Правила, которые определяют состав отбираемых в набор записей, называются критерием. За некоторыми исключениями, критерии задаются с помощью конструкций SQL. Перед тем, как вдаваться в детали наборов записей, рассмотрим основные понятия языка SQL.
Основные понятия языка SQL
При создании наборов записей самой общей задачей выражения SQL является определение следующих трех аспектов.
Из какой таблицы будут отбираться данные?
Какие поля из таблицы будут включаться в результат?
Какие записи будут включаться в результат? Общий синтаксис выражения SQL:
SELECT <какие поля> FROM <из какой таблицы> WHERE <какие записи>
Рассмотрим несколько примеров. Следующее выражение SQL определяет набор записей, содержащий все поля из таблицы ADDRESSES, и включающий только те записи, в которых поле STATE содержит значение CALIFORNIA:
SELECT * FROM ADDRESSES WHERE STATE="CALIFORNIA"
Критерий отбора записей может быть настолько сложным, насколько это необходимо. Для создания сложных критериев отбора можно использовать комбинацию простых. Например, для базы данных склада можно отобрать такой набор записей, в котором количество товаров на руках (QOH) меньше пяти, а в поле поставщика SUPPLIER находится Acme Machine Parts. В этом случае выражение SQL будет иметь следующий вид:
SELECT * FROM INVENTORY WHERE QOH<5 AND SUPPLIER="Acme Machine Parts"
Существует два оператора, используемых для объединения простых критериев в составной. (На самом деле их больше, но эти два используются наиболее часто.) Оператор AND требует, чтобы были удовлетворены оба критерия, как в предыдущем примере; а оператор OR чтобы был удовлетворен хотя бы один из них. Например, следующее выражение SQL отбирает те записи, в которых поставщиком является либо Acme Machine Parts, либо Wilson Electronics:
SELECT * FROM INVENTORY WHERE SUPPLIER="Acme Machine Parts" OR
SUPPLIER="Wilson Electronics"
В табл. 8 перечислены наиболее часто используемые операторы сравнения.
Таблица 8. Операторы сравнения выражений SQL Оператор Описание
= Равно
<> Не равно
>= Больше или равно
<= Меньше или равно
> Больше
< Меньше
BETWEEN valuel AND value2 Находится между значениями valuel и value2, включая границы
IN (list) Находится среди списка значений. Список list содержит разделенные запятыми данные
IS null Значение равно null (пусто, не определено)
Критерий необязательно задавать в выражениях SQL. Если критерий опущен, в результате все записи таблицы будут отобраны в набор. Например, следующее выражение создает набор записей, содержащий все записи и все поля из заданной таблицы:
SELECT * FROM ADDRESSES
Оператор NOT
При создании выражения SQL для отбора набора записей иногда проще задать то, что включать в него не следует, чем то, что следует на самом деле. Для этого используется оператор NOT. Помещая его перед критерием, мы указываем, что отбираются записи, не удовлетворяющие заданному критерию. Например, следующее выражение SQL отбирает те записи, где штатом не является Калифорния:
SELECT * FROM ADDRESSES WHERE NOT STATE="California"
Оператор NOT можно использовать перед любым критерием SQL.
Выражение SQL также позволяет определить, какие именно поля из таблицы будут включены в результат. В приведенном выше примере первая часть выражения select * определяет, что в набор записей включаются все поля. Иногда нам необходимы не все поля, и ключевое слово select можно использовать для задания тех полей, которые должны быть включены в набор записей. Например, следующее выражение SQL определяет набор записей, который содержит только поля FIRSTNAME, LASTNAME и PHONE для тех записей, в которых STATE="New York":
SELECT FIRSTNAME, LASTNAME, PHONE FROM ADDRESSES WHERE STATE="New York"
Обратите внимание в этом примере на то, что поля, участвующие в критерии, необязательно должны включаться в результирующий набор записей.
Последний аспект выражений SQL, который мы рассмотрим, будет сортировка набора записей. Если выражение SQL не задает порядок сортировки, набор записей наследовать порядок, в котором записи находятся в исходной таблице. Если необходимо, чтобы в результирующем наборе записи были отсортированы в определенном порядке, в выражении SQL применяется конструкция sort by, определяющая этот порядок. Эта конструкция записывается в конец выражения SQL и определяет поля, по которым производится сортировка. Эти поля вовсе не обязательно должны содержаться в результирующем наборе записей. В следующем примере результат выражения SQL сортируется по полю LASTNAME:
SELECT * FROM ADDRESSES WHERE STATE="Ohio" ORDER BY LASTNAME
По умолчанию производится сортировка в порядке возрастания значений поля. Если нужна сортировка в обратном порядке, после имени поля нужно указать ключевое слово descend:
SELECT * FROM ADDRESSES WHERE STATE="Ohio" ORDER BY LASTNAME DESCEND
Создание набора записей
Для того чтобы создать набор записей, прежде следует установить соединение с источником данных, как было описано выше в этой главе. После этого для создания объекта Recordset можно использовать два способа.
Первым из них является создание вначале пустого экземпляра объекта Recordset, а после этого вызов его метода Open с аргументами, задающими детали данных. Упрощенный синтаксис этого метода:
Recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Рассмотрим его подробнее.
Source выражение SQL, определяющее набор записей.
ActiveConnection ссылка на объект Connection, подключенный к источнику данных.
CursorType необязательный аргумент, задающий тип курсора, используемого при открытии набора записей.
LockType необязательный аргумент, определяющий тип блокировки или совместного использования при открытии набора записей.
Options необязательный аргумент, определяющий интерпретацию аргумента Source.
Последние три аргумента требуют дополнительных пояснений. Термин курсор имеет отношение к характеру связи между источником данных и набором записей. В частности, это касается изменений в данных, производимых другими пользователями (естественно, если речь идет только о среде совместного использования). Возможные значения этого аргумента описаны в табл. 9.

Таблица 9. Значения аргумента CursorType Константа Значение Описание
adOpenForwardonly 0 Значение по умолчанию. Разрешено движение по набору записей только в прямом направлении
adOpenstatic 3 Разрешается движение в прямом и обратном порядке, но изменения, производимые другими пользователями в источнике данных, не отражаются в наборе записей
adOpenKeyset 1 То же, что и adOpenstatic, но действия в источнике, выполняемые другими, отражаются в наборе записей (кроме удаления и добавления данных)
adOpenDynamic 2 То же, что и OpenKeyset, но в наборе записей отражаются также и выполненные другими операции удаления и добавления записей в источнике

Почему существует именно четыре типа курсора? Курсоры с большими возможностями предъявляются к системе более строгие требования, т.е. нуждаются в большем объеме памяти и времени на обработку процессором. Выбирая простейший из курсоров, отвечающих вашим требованиям, вы делаете свою программу максимально эффективной. Если необходимо лишь "пройтись" по данным и отобрать записи, используйте курсор, установленный по умолчанию (adOpenForwardonly). Если по набору записей требуется перемещаться в обоих направлениях, используйте курсор adOpenstatic. Остальные два типа курсора следует использовать только при работе с базой данных в многопользовательском режиме.
Аргумент Locktype определяет, могут ли данные в наборе записей изменяться. Это аргумент также управляет блокировкой записей, что влияет на способность других пользователей изменять запись. Заблокированная запись не может быть изменена другим пользователем. Возможные значения этого аргумента приведены в табл. 10.

Таблица 10. Значения аргумента LockType Константа Значение Описание
adLockReadOnly 1 Значение по умолчанию. Набор записей доступен только для чтения
adLockPessimistic 2 Запись блокируется сразу после начала редактирования
adLockOptimistic 3 Запись блокируется при вызове метода Update
adLockBatchOptimistic 4 Запись блокируется, когда выполняется пакетное обновление

Установки блокировки adLockOptimistic и adLockBatchOptimistic применяются в основном в сложных многопользовательских условиях работы с базой данных. Для большинства сценариев в прикладных приложениях Office будет уместным использование режима adLockReadOnly, если изменение записей не предвидится (или режима adLockPessimistic в противном случае).
Аргумент Options определяет способ интерпретации информации в аргументе Source. Ранее уже говорилось, Source является выражением SQL, которое отвечает нашим целям. Источник Source может также быть объектом другого типа, например, объектом Command, однако, эти тонкости выходят за рамки настоящего конспекта. Но именно это является причиной появления аргумента Options в методе Open. Когда в качестве аргумента источника передается выражение SQL, в качестве аргумента Options задается константа adCmdText (значение единица). Этот аргумент может быть и опущен, и в этом случае сам механизм базы данных проверяет аргумент Source на предмет его типа. При этом немного замедляется производительность.
В листинге 5 показан пример создания набора записей с помощью метода Open.

Листинг 5. Создание набора записей с помощью метода Open
Public Sub List0705()
Dim adoCon As ADODB.Connection
Dim rs As ADODB.Recordset
Dim conString As String
Dim sqlstr As String
Dim msg As String
On Error Resume Next
' Создаем строку соединения.
conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
conString = conString & "Data Source="
conString = conString & "C:\Program Files\Microsoft Office\Office10\Samples\Борей.mdb;"
' Создаем объект Connection.
Set adoCon = New ADODB.Connection
adoCon.ConnectionString = conString
' Открываем соединение.
adoCon.Open
'Проверяем ошибки и выводим сообщение.
If Err <> 0 Then
msg = "Ошибка при подключении к базе данных:" & vbCrLf
msg = msg & "Ошибка:." & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
End If
Err.Clear
' Создаем пустой набор данных.
Set rs = New ADODB.Recordset
' Создаем запрос SQL.
sqlstr = "Select * From Customers Where Country='Мексика'"
' Открываем набор данных
rs.Open sqlstr, adoCon
' Проверяем ошибки и выводим сообщение.
If Err <> 0 Then
msg = "Ошибка при создании набора данных:" & vbCrLf
msg = msg & "Ошибка: " & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
Else
MsgBox "Набор данных создан успешно."
End If
End Sub

В примере в листинге 5 обратите внимание, что критерий Мексика заключен в одинарные кавычки. SQL может работать с любым типом кавычек одинарными и двойными, но так как в двойные кавычки в программах VBA заключается строковое значение, для обрамления текстового критерия в выражениях SQL обычно используются одинарные кавычки.
Вторым методом создания набора записей является вызов метода Execute объекта Connection, с передачей ему выражения SQL в качестве аргумента. Этот метод создает набор записей, определенный выражением SQL, и возвращает на него ссылку. Эта ссылка обычно сохраняется в некоторой переменной, которая затем используется для доступа к содержимому набора записей. Ограничением этой техники является то, что в этом случае данные в наборе записей являются доступными только для чтения, а типом курсора является adForwardOnly. Если требуются другие параметры открытия набора записей, следует применять метод Open объекта Recordset, описанный ранее в этом разделе.
Ниже приведен пример создания набора записей с использованием метода Execute. В этом фрагменте предполагается, что переменная adoCon ссылается на объект connection, ассоциированный с базой данных Борей:
Dim rs As ADODB.Recordset
Dim conString As String
ConString="Select * from Customers Where Country='Мексика'"
Set rs=adoCon.Execute(conString)
После выполнения этого фрагмента rs будет ссылаться на набор, который содержит все записи из таблицы Customers, в которых значением поля Country является Mexico. После этого объект Recordset может использоваться для доступа и манипуляций с данными. В листинге 6, приведенном далее, будет показано создание набора записей с помощью этого метода.
Работа с наборами записей
Как только создан набор, содержащий необходимые записи, для работы с данными можно использовать свойства и методы объекта Recordset. Структура данных внутри набора является такой же, как и в таблице они организованы по строкам (записи) и столбцам (поля). Конечно же, набор содержит только те записи и поля, которые были заданы в выражении SQL при создании набора.
Доступ к данным полей
Практически в любой момент времени набор записей имеет текущую (или активную) запись. Сразу после создания набора записей его первая запись (согласно порядку сортировки) становится текущей. Доступ к данным в текущей записи осуществляется от поля к полю, основываясь на коллекции Fields объекта Recordset. Эта коллекция имеет свойство RecordCount, в котором содержится количество полей в наборе. В нем также существует метод Item, который возвращает содержимое заданного поля. Поле можно задать либо по имени, либо по его относительной позиции. Например, предполагая, что rs ссылается на набор записей, можно использовать следующее выражение:
Rs.Fields.Item(n)
Это выражение вернет значение n-го поля в текущей записи в наборе rs. Значение n может изменяться от 0 до RecordCount-1. Так как метод Item является методом по умолчанию коллекции Fields, его можно не указывать. Следующие две строки программы будут эквивалентными:
Rs.Fields.Item(2)
Rs.Fields(2)
Для получения данных по имени поля, это имя передается в качестве аргумента методу Item, как показано ниже. Здесь снова учитывается, что метод Item принят по умолчанию в коллекции, и его имя можно опустить:
Rs.Fields("FirstName")
Наборы записей ADO поддерживают оператор !, который возвращает данные из заданного поля. Использование этого оператора способствует краткости при программировании. В этой конструкции имя поля не заключается в кавычки, однако, если оно содержит пробел, оно должно быть заключено в квадратные скобки. Например, следующая строка вернет содержимое поля с именем FirstName из текущей записи набора записей:
Rs!FirstName
Перемещение по набору записей
В любой момент времени набор записей имеет некоторую позицию текущей. Обычно эта позиция указывает на некоторую запись, называемую текущей записью. Текущая позиция может также быть установлена перед первой или после последней записи. Эти позиции называют соответственно BOF (Begin Of Pile) и EOF (End Of File). Если позиция установлена в bof или EOF, ни одна запись не является активной.
Многие операции с данными требуют прохождения по всем записям в наборе, изменяя последовательно текущие записи. Объект Recordset для этой цели имеет несколько методов, которые описаны в табл. 7.11.
Таблица 7.11. Методы перемещения объекта Recordset Метод Описание
Move n Перемещает текущую запись вперед (n положительно) или назад (n отрицательно) по набору. Если текущая позиция является первой или последней в наборе вследствие перемещения, текущей позицией станет, соответственно, bof или EOF. Если будет предпринята попытка движения вперед с текущей позиции EOF или назад с текущей позиции BOF, возникает ошибка
MoveFirst Перемещает к первой записи набора
MoveLast Перемещает к последней записи набора
MoveNext Перемещает к следующей записи в наборе. Если метод вызывается
с записи, являющейся последней, метод возвращает EOF
MovePrevious Перемещает к предыдущей записи набора. Если текущей являлась первая
запись, возвращает bof
Объект Recordset имеет несколько свойств, которые могут оказаться полезными при перемещениях по набору записей. Они описаны в табл. 7.12.
Таблица 7.12. Свойства, связанные с перемещением объекта Recordset Свойство Описание
AbsolutePosition Возвращает число, соответствующее позиции текущей записи в диапазоне от 1 до Recordset. RecordCount. Если текущей позицией является EOF или bof, возвращает соответственно константы adPosEOF (-3) или adPosBOF (-2). Если набор записей пуст или текущая позиция в нем неизвестна, возвращает константу adPosUnknown (-1)
BOF Возвращает True, если текущая позиция BOF
EOF Возвращает True, если текущая позиция EOF

Выявление пустых наборов полей
Если существует вероятность того, что создаваемый набор записей окажется пустым, этот факт должен проверяться в тексте программы еще до попыток работы с данными набора. Для определения пустоты набора записей существует два способа: свойство RecordCount должно содержать 0, а свойства EOF и BOF должно одновременно содержать значение True.
В листинге 7.6 показано, как использовать метод MoveNext и свойство EOF для прохождения в цикле по всем записям в наборе и отображения содержимого двух из полей. На рис. 6 вывод этой программы, запущенной в приложении Word.

Листинг 6. Прохождение в цикле по всем записям набора
Public Sub List0706()
Dim adoCon As ADODB.Connection
Dim conString As String
Dim msg As String
Dim rs As ADODB.Recordset
On Error Resume Next
' Создаем строку соединения.
conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
conString = conString & "Data Source="
conString = conString & "C:\Program Files\Microsoft Office\Office10\Samples\Борей.mdb;"
' Создаем объект Connection.
Set adoCon = New ADODB.Connection
adoCon.ConnectionString = conString
' Открываем соединение. adoCon.Open
'Проверяем ошибки и выводим сообщение.
If Err <> 0 Then
msg = "Ошибка при подключении к базе данных:" & vbCrLf
msg = msg & "Ошибка: " & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
End If
Err.Clear
' Создаем набор данных.
Set rs = adoCon.Execute _
("Select * From Клиенты Where Страна='Мексика'")
' При ошибке выводим сообщение и заканчиваем
If Err <> 0 Then
msg = "Ошибка при создании набора данных:" & vbCrLf
msg = msg & "Ошибка: " & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
adoCon.Close
Exit Sub
End If
' Проверяем, что набор данных не пуст.
If rs.EOF And rs.BOF Then
MsgBox "He найдено таких записей."
adoCon.Close
Exit Sub
End If
' Просматриваем набор записей, получаем ContactName
' и ContactTitle для всех записей
msg = ""
Do While Not rs.EOF
msg = msg & rs!ОбращатьсяК & ", " & rs!Должность & vbCrLf
rs.MoveNext
Loop
' Выводим список.
MsgBox msg
' Закрываем соединение.
adoCon.Close
End Sub


Рис. 6. Вывод программы из листинга.6
Изменение данных в наборе записей
В наборе записей можно выполнить три типа модификаций:
изменение данных в существующей записи;
добавление новой записи;
удаление существующей записи.
Для того чтобы изменить набор записей, он должен быть открыт в режиме, допускающем изменения. Это определяется аргументом LockType метода Open, что было описано ранее. Если попытаться изменить набор записей, открытый только для чтения, возникнет ошибка.
Для изменения существующей записи, следует сначала сделать ее текущей. Это можно выполнить путем создания нового набора записей (на основе выражения SQL), содержащего только интересующую запись, либо путем создания набора с множеством записей и последовательного их перебора, пока не будет обнаружена искомая. Как только нужная запись становится текущей, модификация представляет собой просто помещение необходимых данных в соответствующее поле и последующий вызов метода Update для сохранения изменений.
Во фрагменте, приведенном в листинге 7, показано открытие набора, содержащего единственную запись, изменение данных в этой записи. Как видите, в этом фрагменте не проверяется "единственность" записи, возвращаемой заданным критерием SQL. Это пример приложения из реальной жизни.
Листинг 7. Изменение существующей записи в наборе
Public Sub List0707()
Dim adoCon As ADODB.Connection
Dim conString As String
Dim sqlStr As String
Dim msg As String
Dim rs As ADODB.Recordset
On Error Resume Next
' Создаем строку соединения.
conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
conString = conString & "Data Source="
conString = conString & "C:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb;"
' Создаем объект Connection.
Set adoCon = New ADODB.Connection
adoCon.ConnectionString = conString
' Открываем соединение
adoCon.Open
' Проверяем ошибки и выводим сообщение.
If Err <> 0 Then
msg = "Ошибка при подключении к базе данных:" & vbCrLf
msg = msg & "Ошибка: " & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
Exit Sub
End If
Err.Clear
' Создаем набор данных.
Set rs = New ADODB.Recordset
' Создаем запрос SQL.
sqlStr = "Select * From Employees Where"
sqlStr = sqlStr & "LastName='Peacock' And FirstName='Margaret'"
' Открываем набор данных
rs.Open sqlStr, adoCon, adOpenStatic, adLockPessimistic, adCmdText
'Проверяем ошибки и выводим сообщение.
If Err <> 0 Then
msg = "Ошибка при создании набора данных:" & vbCrLf
msg = msg & "Ошибка: " & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
Exit Sub
End If
' Обновляем данные.
rs.Fields("Title") = "Sales Manager"
rs.Update
If Err <> 0 Then
msg = "Ошибка при обновлении набора данных:" & vbCrLf
msg = msg & "Ошибка: " & Err & vbCrLf
msg = msg & "Описание: " & Err.Description
MsgBox msg
Exit Sub
Else
MsgBox "Таблица успешно обновлена."
End If
End Sub
Для добавления новой записи в набор используется метод AddNew. Полагая, что rs является ссылкой на набор записей, его синтаксис будет таким:
Rs.AddNew
Этот метод добавляет одну пустую запись в набор и делает ее текущей. После этого можно добавить в каждое поле новой записи данные, и вызвать метод Update для сохранения новой записи. Вот пример такой операции:
Rs.AddNew
Rs.Fields("FirstName")="Joan"
Rs.Fields("LastName")="Sutherland"
Rs.FieldsC'PhoneNumber")="123-45-6789"
Rs.Update
Для удаления одной или нескольких записей используется метод Delete объекта Recordset. Его синтаксис:
Rs.Delete AffectRecords
Аргумент AffectRecords может быть установлен в следующие константы: adAffectCurrent для удаления текущей записи, или adAf fectAll для удаления всех записей набора.
Резюме
Программирование баз данных является самым сложным аспектом разработки в среде Office. Во многих ситуациях он также является самым важным и опасным. Он важен потому, что многие организации хранят свою информацию именно в базах данных и заказывают у разработчиков прикладных приложений решения, работающие с этими данными. Это опасно, так как данные, с которыми приходится работать, могут быть важными, а порой и вообще уникальными. Уже небольшое их повреждение может потенциально привести к большим проблемам. Поэтому настоятельно рекомендуется избегать больших задач до того, к будут основательно изучены технологии баз данных вообще и программирование в Access и ADO в частности, т.е. пока они не будут опробованы на простых безопасных примерах.
13PAGE 15


13PAGE 14115




M Заголовок 1 Заголовок 2 Заголовок 3 Заголовок 4 Заголовок 5 Заголовок 6 Заголовок 7 Заголовок 8 Заголовок 915

Приложенные файлы

  • doc 26521826
    Размер файла: 543 kB Загрузок: 0

Добавить комментарий