Обработка объектов СУБД MS Access

Цель работы –изучение способов обработки объектов СУБД MS ACCESS средствами языка программирования VBA.

После выполнения работы студент должен знать основы обработки объектов СУБД MS ACCESS, уметьсоздавать пользовательские приложения автоматизированной обработки информации средствами СУБД MS ACCESS.

Справочная информация

Для организации обращения к базе данных, созданной средствами СУБД MS Access, могут быть использованы модели данных DAO (Data Access Objects), ADO (ActiveX Data Objects), ODBCDirect (Open Data Base Connectivity Direct). Первая из них является наиболее апробированной и удобнее других описывает доступ к файлам Access.

Для извлечения данных с помощью объектного доступа (DAO) необходимо в общем случае:

1. Создать рабочую область (Объект Workspaсe);

2. Открыть базу данных (объект Database);

3. Создать набор записей (объект Recordset);

4. Выполнить непосредственное обращение к конкретным записям и полям;

5. Закрыть набор данных;

6. Закрыть базу данных;

7. Закрыть рабочую область (завершить сеанс работы).

При запуске программ VBA из среды СУБД Access необходимость в пунктах 1 и 7 отпадает, т.к. СУБД сама выполняет эти действия, а пункты 2 и 6 выполняет пользователь, открывая и закрывая на время сеанса работы ту или иную базу данных. Таким образом, для обработки объектов в программе достаточно обратиться к открытой базе данных методом CurrentDb и выполнить пункты 3, 4 и 5. Например, для обращения к записям таблицы Студенты учебной базы данных Студент.mdb необходимо создать следующий фрагмент кода:

‘База данных Студент.mdb должна быть открыта средствами

‘СУБД Access

‘объявление объектных переменных

Dim База, Запись As Variant

‘присвоение переменной ссылки на открытую (текущую) базу ‘данных

Set База = CurrentDb

‘присвоение объектной переменной ссылки на набор записей ‘таблицы Студенты.mdb

Set Запись = База.OpenRecordSet("Студенты")

‘операторы обработки записей таблицы Студенты.mdb

‘закрытие набора записей таблицы Студенты.mdb

Запись.Close

Перечень используемых объектов, их свойств, событий и методов можно увидеть в окне Object Browser редактора VBA (меню View à Object Browser ). Основные методы и свойства объекта Recordset, которые будут использованы нами в дальнейшем описании, приведены в прилож. 16.

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

Пример программы поиска

Требуется создать процедуру поиска записей в таблице Студенты учебной базы данных. Поиск осуществляется по фамилии студента, значение которой вводится с клавиатуры. Если записи найдены, то выводится номер найденной записи с указанием фамилии, имени и отчества студента. Если подобных записей нет, то выводится сообщение об этом.

'Последовательный поиск по фамилии и вывод на экран

‘Программа не проверяет корректность введенной с клавиатуры фамилии

Sub Поиск()

Dim База, Запись As Variant

Dim p As String * 20, i As Integer, признак As Boolean

Set База = CurrentDb

Set Запись = База.OpenRecordSet("Студенты")

признак = False

'признак характеризует хотя бы одну найденную запись

p = InputBox("Введите фамилию", "Поиск", "Шариков")

If RTrim(p) = Empty Then Exit Sub

With Запись

Do Until Запись.EOF = True 'Свойство "End of File" - признак конца файла

If RTrim(![Фамилия].Value) = RTrim(p) Then

i = i + 1

признак = True

MsgBox str(i) & "-я запись. Студент " & ![Фамилия].Value _ & " " & ![Имя].Value & " " & [Отчество].Value

End If

.MoveNext 'метод перехода к следующей записи

Loop

If признак = False Then MsgBox "Записи с фамилией " _

& p & "отсутствуют"

.Close

End With

End Sub

Пример программы поиска и редактирования записей

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

'Работа с записями таблицы - поиск и редактирование

Sub редактирование()

Dim База, Запись As Variant

Set База = CurrentDb 'семейство объектов текущей базы данных

Set Запись = База.OpenRecordSet("Студенты")

'открытие набора записей текущей базы данных

Do Until Запись.EOF = True

'Свойство "End of File" - признак конца файла

If Запись![Фамилия].Value = InputBox ("Введите _

искомую фамилию") Then

Запись.Edit

'метод блокирует запись и разрешает обновление полей записи

Запись![Фамилия].Value = InputBox ("Введите _

новую фамилию")

Запись.Update 'метод сохраняет изменения полей записи

MsgBox Запись![Фамилия].Value

End If

Запись.MoveNext 'метод перехода к следующей записи

Loop

End Sub

Пример программы поиска и удаления записей

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

'Последовательный поиск по фамилии и удаление

'Программа не проверяет корректность введенной с клавиатуры 'фамилии

Sub удаление()

Dim База, Запись As Variant

Dim p As String * 20, признак As Boolean, удаление As Byte

Set База = CurrentDb

Set Запись = База.OpenRecordSet("Студенты")

признак = False 'признак характеризует хотя бы одну найденную 'запись

p = InputBox("Введите фамилию", "Поиск и удаление")

If RTrim(p) = Empty Then Exit Sub

With Запись

Do Until Запись.EOF = True

If RTrim(![Фамилия].Value) = RTrim(p) Then

признак = True

удаление = MsgBox(RTrim(![Фамилия].Value) & " "

& RTrim(![Имя]) & " " & RTrim(![Отчество]) _

& " " & ![КодСпециальности] & "-" & ![Курс] _

& ![Группа], vbYesNoCancel + vbQuestion, _

"Удалить запись?")

Select Case удаление

Case vbYes

MsgBox RTrim(p) & " отчислен(а)!", vbCritical

.Delete

Case vbNo

MsgBox "Ура! Не отчислили!", vbExclamation

Case vbCancel

Exit Sub

End Select

End If

.MoveNext 'метод перехода к следующей записи

Loop

If признак = False Then MsgBox "Записи с фамилией " _

& RTrim(p) & " отсутствуют"

.Close

End With

End Sub

Пример программы расчета

Требуется создать процедуру расчета и вывода на экран значения среднего возраста всех студентов таблицы Студенты учебной базы данных.

'Работа с записями таблицы - просмотр и расчет среднего возраста

Sub Расчет()

Dim База, Запись As Variant

Dim СрВозраст As Single, СумВозраст As Integer, _

ЧислоЗаписей As Long

Set База = CurrentDb 'семейство объектов текущей базы данных

Set Запись База.OpenRecordSet("Студенты")

'открытие набора записей текущей базы данных

СумВозраст = 0

ЧислоЗаписей = 0

Do Until Запись.EOF = True

ЧислоЗаписей = ЧислоЗаписей + 1

СумВозраст = СумВозраст + (Year(Date) - _

Year(Запись![ДатаРождения].Value))

Запись.MoveNext 'метод перехода к следующей записи

Loop

СрВозраст = СумВозраст / ЧислоЗаписей

'или СрВозраст = СумВозраст /Запись.RecordCount

'сойство RecordCount возвращает число записей

MsgBox "Средний возраст равен " & Format(СрВозраст, "Standard")

End Sub

Пример программы ввода новой записи

Требуется создать процедуру ввода новой записи в таблицу Студенты учебной базы данных.

'Работа с записями таблицы - добавление

'Программа не проверяет корректность вводимых значений,

'не отслеживает нажатие кнопки Cancel,

'не осуществляет поиск дублирующих записей в поле КодСтудента 'таблицы "Студенты"

Sub добавление1()

Dim База, Запись As Variant

Set База = CurrentDb

Set Запись = База.OpenRecordSet("Студенты")

With Запись

.AddNew 'метод добавляет запись

![КодСтудента].Value = InputBox("Введите код", "Ввод", _

"141111")

![Фамилия].Value = InputBox("Введите фамилию студента", _

"Ввод", "Шариков")

![Имя].Value = InputBox("Введите имя", "Ввод", "Полиграф")

![Отчество].Value = InputBox("Введите отчество студента", _

"Ввод", "Полиграфович")

![ДатаРождения].Value = CDate(InputBox("Введите дату _

рождения", "Ввод", "01.02.01"))

![Адрес].Value = InputBox("Введите домашний адрес", "Ввод", _

"Москва, Петровка - 25")

![КодСпециальности].Value = InputBox("Введите код _

специальности", "Ввод", "У")

![Курс].Value = CInt(InputBox("Введите курс", "Ввод", "1"))

![Группа].Value = CInt(InputBox("Введите группу", "Ввод", "1"))

![Подгруппа].Value = CInt(InputBox("Введите подгруппу", _

"Ввод", "1"))

.Update 'метод сохраняет новую запись

.Close

End With

End Sub

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

Option Compare Database

'тип данных Type Студент описывается в декларативной части 'модуля

Private Type Студент

Код As String * 5

Фам As String * 20

Имя As String * 10

Отч As String * 20

Д_Р As Date

Адр As String * 255

КодСпец As String * 1

Курс As Byte

Группа As Byte

Подгруппа As Byte

End Type

Sub добавление2()

Dim Экономист As Студент

Dim q, База, Запись As Variant

Set База = CurrentDb

Set Запись = База.OpenRecordSet("Студенты")

With Экономист

q = InputBox("Введите код", "Ввод", "02111")

'здесь может присутствовать проверка корректности 'введенного значения кода студента

If RTrim(q) = Empty Then Exit Sub Else .Код = q

q = InputBox("Введите фамилию студента", "Ввод", "Шариков")

'здесь может присутствовать проверка корректности 'введенного значения фамилии,

' и т.д.

If RTrim(q) = Empty Then Exit Sub Else .Фам = q

q = InputBox("Введите имя", "Ввод", "Полиграф")

If RTrim(q) = Empty Then Exit Sub Else .Имя = q

q = InputBox("Введите отчество студента", "Ввод", _

"Полиграфович")

If RTrim(q) = Empty Then Exit Sub Else .Отч = q

q = CDate(InputBox("Введите дату рождения", "Ввод", _

"01.02.01"))

If RTrim(q) = Empty Then Exit Sub Else .Д_Р = q

q = InputBox("Введите домашний адрес", "Ввод", "Москва, _

Петровка - 25")

If RTrim(q) = Empty Then Exit Sub Else .Адр = q

q = InputBox("Введите код специальности", "Ввод", "У")

If RTrim(q) = Empty Then Exit Sub Else .КодСпец = q

q = CInt(InputBox("Введите курс", "Ввод", "1"))

If RTrim(q) = Empty Then Exit Sub Else .Курс = q

q = CInt(InputBox("Введите группу", "Ввод", "1"))

If RTrim(q) = Empty Then Exit Sub Else .Группа = q

q = CInt(InputBox("Введите подгруппу", "Ввод", "1"))

If RTrim(q) = Empty Then Exit Sub Else .Подгруппа = q

End With

'Здесь может присутствовать проверка наличия дублирующих 'записей в поле ‘КодСтудента,

'для чего необходимо организовать поиск значения КодСтудента _

'в таблице Студенты,

'аналогичного значению, введенному с клавиатуры

With Запись

.AddNew

![КодСтудента].Value = Экономист.Код

![Фамилия].Value = Экономист.Фам

![Имя].Value = Экономист.Имя

![Отчество].Value = Экономист.Отч

![ДатаРождения].Value = Экономист.Д_Р

![Адрес].Value = Экономист.Адр

![КодСпециальности].Value = Экономист.КодСпец

![Курс].Value = Экономист.Курс

![Группа].Value = Экономист.Группа

![Подгруппа].Value = Экономист.Подгруппа

.Update

.Close

End With

End Sub

Задание

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

2. Создайте программы, осуществляющие ввод, просмотр, поиск, редактирование и удаление записей для одной или нескольких таблиц Вашей личной базы данных. Детальные требования к программам необходимо согласовать с преподавателем.

3. Создайте программу расчета одного из финансово-экономических показателей по данным, хранящимся в таблицах Вашей личной базы данных, например: прибыли, доходов, расходов, рентабельности. Детальные требования к программе согласуйте с преподавателем.

4. Запуск созданных программ обработки записей базы данных организуйте из главной кнопочной формы.

5. * В рассмотренных выше программах интерфейс пользователя организован через стандартные диалоговые окна, реализованные с использованием функций и процедур MsgBox и InputBox. Их применение не всегда удобно, особенно при вводе и выводе большого количества данных. Создайте пользовательские формы, заменяющие стандартные диалоговые окна, и реализуйте их взаимосвязь с разработанными Вами программами.

Контрольные вопросы

1. Объясните принцип обращения к физическим данным, хранящимся в базе.

2. Объясните понятие модели доступа к данным. Какие модели Вы знаете? Чем они отличаются?

3. Какие объекты использованы в приведенных выше примерах программ?

4. Какие свойства и методы этих объектов использованы в приведенных выше примерах программ?

5. Почему в примерах отсутствует обращение к объекту Field?

6. * Предложите вариант программы поиска записей в родительской таблице и вывода значений соответствующих записей из дочерней таблицы.

7. * Предложите вариант программы поиска записей, в которой используются методы FindFirst, FindLast, FindNext, FindPrevious, Seek или свойство Filter.

Литература

1.	Боровиков В.В. Microsoft Access 2002. Программирование и разработка баз данных и приложений. – М: СОЛОН-Р, 2002. – 560 с.
2.	Гарнаев А.Ю. Visual Basic 6.0. . – СПб: БХВ-Петербург, 2001. – 448 с.
3.	Гарнаев А.Ю. Самоучитель VBA. – СПб: БХВ-Петербург, 2001. – 512 с.
4.	Информатика: Базовый курс: Учебное пособие / Под ред. С.В.Симоновича. – СПб: Питер, 2004. – 639 с.
5.	Информатика: Практикум по технологии работы на компьютере: Учеб. пособие/ Под ред. Н.В.Макарова. – М: Финансы и статистика, 2002. – 255 с.
6.	Назаров С.В. Мельников П.П. Программирование на MS Visual Basic: учебное пособие. – М.: Финансы и статистика, 2002. –320 с.
7.	Павлова Н.Г., Румянцев С.С., Сивоволов Н.В. Система управления базой данных MS Access. Конспект лекций. – Н.Новгород: ФГОУ ВПО «ВГАВТ», 2005. – 44 с.
8.	Павлова Н.Г., Румянцев С.С., Соловьева В.Г. Проектирование базы данных. Метод. указания.- Н.Новгород: ВГАВТ, 2001.
9.	Павлова Н.Г., Румянцев С.С. Информатика в экономике. Учебно-методическое пособие. Часть 2. Электронные таблицы. – Н.Новгород: ФГОУ ВПО «ВГАВТ», 2013. – 41 с.
10.	Стандарт предприятия. Организация издательской деятельности в Волжской государственной академии водного транспорта. – Н.Новгород: ИПК ФГОУ ВПО «ВГАВТ», 2007.

Приложение 1

Образец оформления титульного листа

Приложение 2