§ 37. Робота 3 базами даних

У цьому параграфі описано, як засобами VBA можна створити таблиці для бази даних, що опрацьовуватимуться програмою MS Access, і як таблиці, створені в MS Access, можна доопрацьову­вати за допомогою VBA.

1. Постановка задачі. Нехай є база даних, що зберігається в таб­лиці з назвою «Таблиця_1», яка була створена програмою MS Ac­cess. Таблиця зберігається у файлі myfilel.mdb на диску d:. По­трібно отримати доступ до даних цього файлу засобами VBA, занес­ти записи з файлу бази даних в оперативну пам’ять, опрацювати їх певним чином і занести результати в інший файл myfile2.mdb, який згодом може бути зчитаний і опрацьований програмою MS Access.

Нехай таблиця містить три поля з назвами «Номер», «Прізви­ще», «Бал», заповнені даними про групу студентів. У myfile2.mdb треба помістити таблицю з назвою «Таблиця_2» із записами про студентів, які претендують на стипендію, тобто мають рівно чи більше, ніж 71 бал. Записи в новій таблиці повинні складатися ли­ше з двох полів: «Номер», «Прізвище».

2. Об’єктна модель DAO (Data Access Object). Модель DAO дає змогу працювати з файлами баз даних, створеними різними про­грамами (не тільки MS Access). Зауважимо, що для роботи з об’єк­тами DAO мають бути під’єднані бібліотеки об’єктів Microsoft Offi­ce Object Library і Microsoft DAO Object Library, що роблять коман­дами Tools => References з головного меню VBA.

Ієрархія класів у DAO визначається ієрархією даних у теорії баз даних: база даних на логічному рівні складається з таблиць і запи­тів. Таблицям і запитам на фізичному рівні відповідають набори за­писів. Запис складається з полів. Поле — це найменший структур­ний елемент у базі даних (як клітинка — найменший структурний елемент в електронній таблиці). Це треба брати до уваги під час розв’язування задач, де одночасно використовуються електронні таблиці та бази даних і де дані з поля запису треба записувати в клітинку електронної таблиці і навпаки.

Розглянемо три класи об’єктів, які застосовують для роботи з базами даних: DataBase (база даних); RecordSet (набір записів бази даних); DAO.Field (поле).

Об’єкт класу DataBase спочатку описують, а потім створюють ко­мандою Set так:

Set <Назва об’єкта> = OpenDatabase(<адреса ресурсу>).

Наприклад,

Dim Mybaza As DataBase

Set Mybaza = OpenDatabase("d:\myfile2.mdb").

Об’єкти класу DataBase володіють методами Close (закрити), OpenRecordset (відкрити записи), NewPassword (змінити пароль) тощо. З ін­шими методами і властивостями об’єктів класу DataBase чи іншого класу можна ознайомитися за допомогою Object Browser (F2), на­бравши в коді назву об’єкта і крапку.

Об’єкт MyRecords класу RecordSet спочатку описують, а потім створюють так:

Dim MyRecords As RecordSet

Set MyRecords = Mybaza.OpenRecordset(<джерело даних>)

Джерелом даних може бути таблиця «Таблиця_1» з бази даних MS Access, запит «Запит_1», чи вираз SQL-запиту, наприклад, "SE­LECT ALL * FROM Таблиця_1";.

Об’єкт MyRecords дає змогу працювати з поточним записом. Пе­реміщатися між записами, створювати нові та вилучати записи да­ють змогу, зокрема, такі методи цього об’єкта:

MoveFirst — перейти до першого запису;

MoveLast — перейти до останнього запису;

MoveNext — перейти до наступного запису;

Delete — вилучити запис;

AddNew — створити новий запис;

Update — занести новий запис у файл.

Компонентами класу RecordSet (об’єкта MyRecords) є об’єкти Fields(0), Fields(1),... — поля поточного запису, які нумеруються від нуля, а та­кож низка функцій, зокрема, функція RecordCount (кількість запи­сів) і функція EOF, яка визначає кінець набору записів (кінець файлу).

Об’єкти Fields(0), Fields(1), ... мають властивості Value (значення), Name (назва поля) та ін. Наприклад, значенням функції MyRecords.Field(0).Name є назва відповідного поля «Номер».

Нові таблиці є об’єктами класу DAO.TableDef.

3. Код проекту. Код складається з двох частин. У першій части­ні забезпечимо доступ до файлу бази даних і відкриємо доступ до записів першої таблиці. Записи складаються з трьох полів. Записів є п. Оголосимо три масиви, куди помістимо дані з трьох полів про всіх студентів. Для візуального контролю виведемо ці дані на екран командою Debug.Print.

У другій частині коду оголосимо намір створити і створимо нову таблицю, яка в програмі MS Access називатиметься «Таблиця_2». Оголосимо об’єкт класу «поле» і за його допомогою в новій таблиці створимо два поля з назвами «Номер» і «Прізвище» відповідних типів. Додамо нову таблицю у базу даних. Відкриємо доступ до запи­сів нової таблиці. Аналізуємо масив, що містить бали студентів, і додаємо в нову таблицю лише ті відомості, які відповідають крите­рію пошуку (умові задачі).

Розгляньте код проекту, який створимо у деякому модулі.

Option Base 1

Sub LastCod()

Dim Mybaza As DataBase

Set Mybaza = 0penDatabase("d:\myfile1.mdb")

Dim MyRecords As RecordSet

Set MyRecords = Mybaza.OpenRecordset(“Таблиця_1”)

MyRecords. MoveFirst n = MyRecords.RecordCount

ReDim a(n) As Integer 'ReDim оголошує масиви,

ReDim b(n) As String 'розміри яких задані

ReDim c(n) As Integer 'параметром n

Dim і As Integer і = 1

While Not MyRecords.EOF

a(i) = MyRecords.Fields(0)

b(i) = MyRecords. Fields(1)

c(i) = MyRecords.Fields(2)

MyRecords.MoveNext

Debug.Print a(i), b(i), c(i)

і = і + 1

Wend

'Друга частина проекту

Dim Newtable As DAO.TableDef

Set Newtable = Mybaza.CreateTableDef(“Таблиця_2”)

Dim f1 As DAO.Field

Setf1 = newtable.CreateFieldf(“Hoмep”, dblnteger)

Newtable.Fields.Append f1

Set ft = Newtable.CreateField("Прізвище", dbText)

Newtable.Fields.Append f1

Mybaza.TableDefs.Append Newtable

Dim MyRecords2 As RecordSet

Set MyRecords2 = Mybaza.OpenRecordset("Таблиця_2")

For і = 1 To n

MyRecords2.AddNew

If c(i) >= 71 Then

MyRecords2.Fields(0) = a(i)

MyRecords2.Fields(1) = b(i)

MyRecords2.Update

End If

Next і

End Sub

Зауваження 1. Ознайомившись з мовою SQL, зокрема, з коман­дою SELECT і її частиною WHERE, ви зможете виконувати пошук у базах даних швидким способом. Наприклад, доступ до потрібних даних у цій задачі відразу дає така команда:

Set MyRecords = Mybaza.OpenRecordset ("SELECT ALL Номер, Прізвище _

FROM Таблиця_1 WHERE Бал >= 71";)

Висновок. Ви навчились опрацьовувати і створювати таблиці ба­зи даних програмним шляхом. Можете застосувати форму для вве­дення даних спочатку у форму, а потім у файл бази даних у форма­ті програми MS Access чи іншої програми, які підтримують об’єкт­ну модель DAO чи подібну до неї.

Вправи

1. Модифікуйте код, використавши для зберігання даних в оперативній пам’яті замість трьох масивів один масив записів.

2. Модифікуйте код, зважаючи на зауваження 1.

3. Проаналізуйте, як можна спростити код, якщо в MS Access заздале­гідь створити порожню таблицю «Таблиця_2» потрібної структури (за­повнити її треба буде програмним шляхом).