Работа с объектами и коллекциями

В VBA можно работать с объектами и коллекциями Access, библиотекой доступа к данным DAO (Data Access Objects), библиотекой прямого доступа к данным баз данных ODBC (ODBC Direct), библиотекой доступа к данным ADO (ActiveX Data Objects) и другими библиотеками. В Access, DAO и ADO все объекты расположены внутри коллекций и доступны в VBA. Каждый объект имеет свойства и методы. Все библиотеки организованы в виде иерархии объектов. Объекты имеют коллекции (семейства) подчиненных объектов и т.д. В Access имеется восемь базовых объектов; их иерархия представлена на Рис.7.13.

Объекты MS Access:

1. Application – активное приложение;

2. Control – элемент управления;

3. DoCmd – объект вызова макрокоманд в VBA коде;

4. Form – открытая форма;

5. Module – объект, ссылающийся на стандартные модули;

6. Reference – объект, содержащий ссылки на объекты;

7. Report – открытый отчет;

8. Screen – ссылка на экран;

Названия семейств формируются путем возведения в множественное число названия соответствующего объекта. В свою очередь большинство объектов имеют присоединенные коллекции свойств (Properties), а формы и отчеты – коллекции разделов и т.д. Так как все объекты в Access хранятся внутри иерархически связанных коллекций, то доступ к объекту на нижней ступени иерархии можно получить, указав все имена коллекций, разделенных точкой, начиная от корневого объекта. Например, Appliction.Forms("Заказы").Controls(0).Properties(0). Большинство коллекций, к примеру, коллекции форм и отчетов, являются глобальными. Тогда к объекту этой коллекции можно обращаться напрямую: Forms("Заказы") или Forms!Заказы.

Рис.7.12. Иерархия объектов Access

Поскольку библиотека DAO всегда поставляется с Access, рассмотрим ее структуру и основные методы более подробно (Рис.).

Объекты библиотеки DAO:

Database – открытая база данных;

DBEngine – ссылка на Microsoft Jet (ядро БД);

Error – объект ошибок;

Field – поле в таблицах, запросах, динамических наборах и т.д.;

Index – индекс;

Parameter – параметр запроса;

QueryDef – сохранённый запрос;

Recordset – динамический набор данных;

Relation – связь между таблицами;

TableDef – сохраненная таблица;

Workspace – активная сессия.

Рис.7.13. Иерархия объектов DAO

В программах на VBA имеется набор свойств объектов, которые возвращают ссылки на подчиненные объекты:

Me – ссылка на активную форму или отчет (доступна в присоединенном модуле);

ActiveControl – ссылка на активный элемент управления;

ActiveForm – ссылка на активную форму (доступна в объекте Screen);

ActiveReport – ссылка на активный отчет (доступна в объекте Screen);

Application – ссылка на открытое приложение;

Parent – ссылка на родительский объект, то есть на коллекцию;

DBEngine – возвращает ссылку на Application.

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

Me![Полная цена] = Me![Цена] * Me![Количество]

Me.Filter = "[Адрес] Like '*" & Me![ПолеУсловияПоиска] & "*'"

Me.FilterOn = True

Если есть желание читать и менять данные в некотором поле базового набора, но не видеть его на экране, то достаточно поместить элемент управления Поле на форму и задать свойство Visible в False. Но прямой доступ к базовому набору в Access невозможен. Можно лишь создать динамическую копию базового набора и синхронизировать все производимые действия с ней с базовым набором самой формы. Для прямого доступа к записям и полям используется объект Recordset. Имеются четыре типа Recordset объектов – table, dynaset, snapshot и forwarrd-only:

1. Table: Может быть создан только на основе существующей или присоединенной таблицы. Предоставляет доступ ко всем методам и свойствам таблицы, а также к индексам, что дает намного более быстрый метод поиска (метод Seek);

2. Dynaset - может быть создан на основе таблицы или запроса. Позволяет обновлять данные в многотабличных запросах и в запросах к внешним БД. Обновление Dynaset объекта приводит к автоматическому обновлению всех участвующих в нем таблиц;

3. Snapshot - создает статическую копию и существует только в то время, когда он создан. Последующие изменения таблиц на него не воздействуют;

4. Forward-only создает статическую копию с просмотром только в прямом порядке.

Для создания объекта типа Recordset используется метод OpenRecordset:

Set variable = database.OpenRecodset (source [type, options, lockedits]), или

Set variable = object.OpenRecodset ( [type, options, lockedits]),

где database – это переменная типа Database; object – переменная типа TableDef или QueryDef; source – ссылка на объект типа TableDef или QueryDef; type – тип динамического набора (может принимать следующие значения: dbOpenTable, dbOpenDynaset, dbOpenSnspshot dbOpenForwardOnly); options может принимать следующие значения: dbAppendOnly, dbReadOnly, dbForwardOnly, …; lockedits – аргумент, определяющий разрешение конфликтов в многопользовательских БД (может принимать следующие значения: DbReadOnly, dbPessimistic, dbOptimistic). Например:

Dim db As Database

Dim rst As Recordset

Set db = CurrentDb()

Set rst = db.OpenRecordset("Клиенты", dbOpenDynaset)

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

Dim rstOrders As Recordset

Set rstOrders = Forms![Заказы].RecordsetClone

или просто Me.RecordsetClone

Recordset можно создать, также основываясь на строке SQL

Set rst = db.OpenRecordset("SELECT * FROM Товары WHERE Цена > 1000", dbOpenDynaset, dbReadOnly)

После завершения работы с динамическим набором его необходимо закрыть. Существуют два общих способа закрытия объектов в VBA. Первый заключается в вызове метода Close, второй – в присвоении соответствующей объектной переменной значения Nothing. Например, rst.Close или Set rst = Nothing.

Recordset имеет текущее приложение – current position. Для синхронизации текущего положения динамического набора с текущей записью формы можно использовать свойство Bookmark (необходимо всегда помнить, что при обращении к динамическому набору данные берутся именно из текущей записи). Для перемещения по динамическому набору имеется ряд методов: MoveFirst, MoseLast, MoveNext, MovePrevions, Move[n]. Например:

Dim rst As Recordset

Set rst = Me.RecordsetClone

rst.MoveNext

Me.Bookmark = rst.Bookmark

Для определения начала и конца набора можно использовать свойства BOF (before of file – начало файла) и EOF (end of file – конец файла). Если в наборе нет записей, то BOF и EOF равны True. Если в наборе есть записи, то при открытии набора курсор обычно устанавливается на первой записи и BOF и EOF = False. Если курсор находится перед первой записью набора, то BOF = True, и если курсор находится после последней записи, то EOF = True.

Число записей в динамическим наборе можно получить с помощью свойства RecordCount. Это свойство возвращает реальное число записей только для динамического набора, основанного на таблице, то есть имеющего тип dbOpenTable. После открытия динамических наборов любых других типов число записей в нем будет неизвестно до тех пор, пока курсор не будет перемещен на последнюю запись. Для непосредственной проверки на наличие в наборе записей лучше проверить свойство EOF. Если EOF будет равно True, то RecordCount также будет равен 0.

Public Function RecCount() As Long

Dim rstCount As Recordset

Dim dbs As Database

Set dbs = CurrentDB()

Set rstCont = dbs.OpenRecordset("Заказы")

If rstCount.Eof Then

RecCount = 0

Else

rstCount.MoveLast

RecCount = rstCount.RecordCount

End If

rstCount.Close

Set dbs = Nothing

End Function

Поиск определенной записи.Для наборов, основанных на таблице, можно использовать метод Seek, который ищет запись, основываясь на сравнении данных некоторого индекса с заданными значениями (самый быстрый метод). Для примера запишем программу, которая будет проверять дублирование значений ключевого поля таблицы Клиенты перед сохранением записи. Для проверки результатов поиска в объекте Recordset имеется свойство NoMatch. Если NoMatch равно False, то запись с заданными условиями поиска отсутствует в наборе.

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("Клиенты", dbOpenTable)

rst.Index = "PrimaryКey"

rst.Seek "=", Me![КодКлиента]

If Not rst.NoMatch Then

MsgBox "Клинт с таким табельным номером уже существует в базе"

DoCmd.GoToControl "КодКлиента"

End If

rst.Close

Метод Seek всегда начинает поиск с начала набора, поэтому поиск по одному и тому же условию будет всегда приводить к одной и той же записи. Для поиска по динамическим наборам остальных типов можно применять также методы FindFirst, FindLast, FindNext и FindPrevions. Например, подсчитаем количество заказов определенного клиента:

intCount = 0

rstOrders.FindFirst "КодКлиента=" & Me![КодКлиента]

Do While rstOrders.NoMatch

rstOrders.FindNext "КодКлиента=" & Me![КодКлиента]

intCount = intCount + 1

Loop

Для поиска записей можно также применять сортировку и фильтрацию. Для сортировки записей лучше всего открыть новый динамический набор, основанный на запросе SQL с оператором ORDER BY. Для фильтрации можно задать свойство Filter объекта Recordset и затем обновить набор с помощью метода OpenRecordset. Ниже приведен текст программы, осуществляющей фильтрацию произвольного набора:

Function FilterField(rstTemp As Recordset, strField As String, strFilter As String) As Recordset

rstTemp.Filter = strField & " = '" & strFilter & "'"

Set FilterField = rstTemp.OpenRecordset

End Function

Обновление динамических наборов.Обновление данных возможно только при работе с динамическими наборами типов dbOpenTable и dbOpenDynaset. Для редактирования некоторой записи необходимо вначале установить курсор на ней, перевести динамический набор в режим редактирования (метод Edit), а затем сохранить изменения с помощью метода Update.

rst.Edit

rst![Товар] = "Pentium 100"

rst![Цена] = 100

rst.Update

Метод Cancel отменяет внесенные изменения. Для удаления текущей записи существует метод Delete. Этот метод удаляет запись без выдачи каких-либо предупреждений и не меняет положения курсора. Для перехода на следующую запись необходимо вызвать метод MoveNext. Для добавления новой записи служит метод AddNew. После заполнения новой записи значениями ее необходимо сохранить с помощью метода Update, потому как если после добавления новой записи перейти к другой записи без сохранения или просто закрыть динамический набор, то добавляемая запись будет потеряна. Следующий пример изменяет значения полей Цена и Количество таблицы Заказы, отфильтрованной для определенного клиента.

Dim rst As Recordset

Dim dbs As Database

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("Заказы", dbOpenDynaset)

rst.Filter = "[КодКлиента]=" & Me![Код клиента]

Set rst = rst.OpenRecordset

If Not rst.EOF Then

With rst

Do Until .EOF

.Edit

![Цена] = rst![Цена] * 1.2

![Количество] = ![Количество] * 2

.Update

.MoveNext

Loop

.Close

End With

End If

dbs.Close

Работа с полями.Получить доступ к значениям и свойствам полей некоторой таблицы можно, открыв соответствующий динамический набор. Коллекция полей Fields является коллекцией по умолчанию для объекта типа Recordset. Тогда получить доступ к некоторому полю можно по его имени или по индексу в коллекции, например: rst.Fields(Field1”), rst.Fields(4) или rst![Field1]. После получения ссылки на объект типа Field можно читать и изменять его свойства, например:

Dim fld As Field

fld.DefaultValue = 1

fld.ValidationRule = "BETWEEN 1 AND 1000"

fld.Value = 100

Упражнения

7.1. Какую модель данных поддерживает Access.

7.2. Представьте на схеме основные объекты Access и их концептуальные взаимосвязи.

7.3. Перечислите типы данных, поддерживаемые Access.

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

7.5. Что такое маска ввода.

7.6. Какие виды индексов поддерживаются в Access.

7.7. Что такое фильтр и чем он отличается от запроса.

7.8. Какие виды фильтров можно создать в Access.

7.9. Какие средства создания запросов предоставляет Access.

7.10. Для чего предназначены формы в Access.

7.11. Какие элементы управления можно использовать при создании форм и отчетов.

7.12. Что такое составная форма.

7.13. Какие объекты Access могут служить для организации интерфейса пользователя и как производится автоматизация приложения.

7.14. Учитывают или нет в Access отчеты сортировку и группировку, определенные в базовым запросе отчета.

7.15. Какие действия можно выполнять с помощью макросов.

7.16. Что принципиально невозможно сделать с помощью макросов.

7.17. Напишите ссылку на свойство Visible элемента управления поле «КодКлиента» в форме «Клиенты».

7.18. Что такое событие. Какие виды событий вы знаете.

7.19.Какие события и в какой последовательности происходят при открытии формы.

7.20. Дайте общую характеристику СУБД Access.

Задание

1. На основе атрибутов, перечисленных в Таблице 1, разработать схему данных. Провести нормализацию данных с учетом требований первых трех нормальных форм. Если нормализация проведена правильно, у вас должно получиться 6 таблиц, связанных отношениями 1 – ∞ (атрибуты Адрес и Телефон предполагаются однозначными).

Таблица 1

2. Разработать полученную схему данных в систему Access. Разработку таблиц проводить с помощью режима конструктора (для облегчения ввода отдельных полей можно воспользоваться построителем). Продумать и указать типы и размеры полей, а также задать необходимые ограничения целостности. Определить обязательные и ключевые поля. Задать:

- формат вывода для атрибутов Дата рождения, Дата заказа, Дата поставки (полный формат даты); Время оформления (краткий формат времени); Цена, Отпускная цена (фиксированный); Пол (пользовательский формат, в виде “мужской”/“женский”);

- маску ввода для атрибутов Дата рождения, Дата заказа, Дата поставки (краткий формат даты), Время оформления (краткий формат времени), Телефон, Факс, Паспорт (пользовательская маска ввода);

- условия на значение для атрибутов Паспорт, Адрес электронной почты, Цена, Отпускная цена, Количество;

- значение по умолчанию для атрибутов Дата заказа, Время оформления, Количество, Цена, Отпускная цена.

3. Установить связи между таблицами. Задать обеспечение ссылочной целостности данных.

4. Задать индексы (минимум по одному вторичному для каждой таблицы и один составной).

5. Произвести ввод данных.

6. Научиться применять встроенные средства Access по отбору информации (поиск, сортировку, все виды фильтров).

7. Разработать запросы, необходимые для решения задач базы данных, а также следующие виды запросов. Разработку производить с помощью конструктора (последние четыре – на SQL):

o многотабличный запрос выборки, сортировки и отбора данных,

o запрос c применением вычислений (создать вычисляемые поля Возраст и Полное имя клиента)

o запрос выборки с внешним объединением (отображать всех клиентов и сделанных ими заказах),

o параметрический запрос,

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

o запрос на добавление,

o запрос на обновление,

o запрос на удаление,

o запрос на объединение UNION.

8. Разработать формы необходимые (на ваш взгляд) для решения задач базы данных, а также следующие формы:

o простые формы для каждой из таблиц;

o многостраничную форму для ввода/вывода данных по клиентам;

o составные формы для ввода/вывода данных по клиентам и заказам, по производителям и товарам, по поставщикам и поставкам.

Требования: разрабатывать формы в режиме конструктора; применить разнообразное форматирование и оформление данных; использовать все возможные элементы управления (кнопки, переключатели, списки, поля со списком и т.д.); внешние поля подчиненных таблиц представить в виде комбинированных списков, подставляющих информацию из базовых таблиц.

9. Разработать отчеты, необходимые для решения задач базы данных, а также:

o простой отчет в виде таблицы, представляющий информацию по товарам, поставщикам и производителям;

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

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

10. На основе созданных форм разработать интерфейс приложения. Работа с базой данных должна производиться только через созданный интерфейс (окно базы данных должно быть скрыто от пользователей). Для этого:

- создать главную (переключательную) форму;

- задать параметры запуска;

- автоматизировать работу приложения с помощью макросов и программирования на VBA. Требования: научиться динамически менять свойства объектов, выполнять операции открытия и закрытия объектов, поиска и фильтрации;

- добавить в отдельные формы элементы поиска в виде поля ввода условия и кнопок “Найти”, “Фильтровать”, а также организовать поиск с помощью выпадающего списка (с помощью мастера создания комбинированных списков);

- связать две простые формы по отображению данных (например, форму по товарам и по производителям). При открытии/закрытии формы по товарам должна автоматически открываться/закрываться форма по производителям. Данные по производителю должны синхронно меняться при переходе от одного товара к другому.

Литература к части I «базы данных»

Основная литература

1. Змитрович А.И. Базы данных. Учебное пособие для вузов. – Мн.: Университетское, 1991. - 271 с.
2. Горев А. Эффективная работа с СУБД. СПб.: Питер, 1997. – 704 с.
3. Вейскас Дж. Эффективная работа с MS Office Access 2003. Питер. 2005. 1168 с.
4. Горсиа-Молина Гектор, Ульман Джефри Д., Уидом Дженнифер. Системы баз данных. Полный курс.: Пер. с англ. – М.: Издательский дом «Вильямс», 2003. – 1088 c.
5. В.Михеева и И. Харитонова. Microsoft Access 2003. СПб, БХВ, 2004. – 1072 с.
6. Дж. Дейт. Введение в системы баз данных. 8-ое издание.: Пер. с англ. – М.: Издательский дом «Вильямс», 2005. – 1328 с.
7. Томас Конолли. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. Москва, СПб, Киев: Издательский дом «Вильямс», 2000. – 1120 с.

Дополнительная литература

1. Дж. Мартин. Организация баз данных в вычислительных системах. М.: Мир, 1991. – 664 с.
2. Сеннов А. Access 2003. Практич. разработка баз данных: Учеб. курс. Питер. 2005. 256 с.
3. Хомоненко А. Д. Базы данных. Учебное пособие. СПб.: Корона, 2002. – 672.
4. R.Elmasri and S.B.Navathe. Fundamentals of Database systems. Adisson-Wesley, 2000. – 1038 с.
5. L.G. Hernandez, A.I. Zmitrovich. Ed. Pucbloy Educacion. Habana, 1988. – 245 p.