Юрчишин В.М., Клим Б.В. і інші. Організація баз даних

Подождите немного. Документ загружается.

If умовний_вираз Then інструкція

або

If умовний_вираз Then інструкція1 Else інструкція2 End If

Приклад введення числа та його аналізу (фрагмент програми):

Dim oplata As Single, slovo As String

oplata = InputBox(“Введіть число”, “Вивід результату”, “100”)

If oplata < 300 then slovo = ”мало” Else slovo = ”небагато”

MsgBox “Введено число: ” & oplata & “ Висновок: ” + slovo

Команда умовного переходу

GoTo мітка

Тут мітка – ідентифікатор. Вона ставиться перед командою, на

яку здійснюється перехід, і відокремлюється від нього двокрапкою.

Команда циклу з регулярною зміною параметра

For ім’я_змінної = початкове To кінцеве_значення Step крок

тіло циклу

Next ім’я_змінної

Тіло циклу може містити один або декілька операторів Exit For –

передчасний вихід з циклу. Приклад видачі чисел від 1 до 10:

Dim i As Integer

For i=1 To 10

MsgBox “№ виконання циклу: ” & i

Next i

Команда циклу перерахункового типу

While умовний вираз

тіло циклу

Wend

Приклад видачі чисел від 1 до 10:

Dim i As Integer

i=0

While i < 11

i=i+1

MsgBox “№ виконання циклу: ” & i

Wend

2.2.5 Підпрограми

Мова VBA має два типи підпрограм: процедуру і функцію.

Опис процедури має вигляд:

Кваліфікатор Sub ім’я (список параметрів, кваліфікаторів і типів)

тіло процедури

End Sub

Тіло процедури може містити оператор Exit Sub – передчасний

вихід з підпрограми

Звернення до процедури виконується за допомогою команди:

Call ім’я процедури(список параметрів)

Опис функції має такий вигляд:

Кваліфікатор Function ім’я (список параметрів, кваліфікаторів і типів) As тип

поверненого значення

тіло функції

End Function

Для передчасного виходу з функції служить команда Exit

Function, яка може міститися в її тілі.

При звертанні до функції називають її ім’я, після якого в

дужках подають список параметрів.

Приклад. Скласти програму для одержання за допомогою

процедури zal найменшого цілого залишка z від ділення числа x на

3 і його подвоєнного значення за допомогою функції dwa.

Option Compare Database

Option Explicit

Sub pryklad() ’Головна програма

Dim z As Integer, x As Integer , s As String

s = InputBox("Введіть х", "Ввід числа", "10")

x = Val(s)

Call zal(x, z)

MsgBox "Задане число="& x &"Залишок="& z &" Подвоєне число=" & dwa(x)

End Sub

Sub zal(x1 As Integer, z1 As Integer) ’Процедура-підпрограма

z1 = x1 Mod 3

End Sub

Function dwa(p As Integer) As Integer ’Процедура-функція

dwa = 2 * p

End Function

У прикладі використана проміжна змінна s типу String для вводу

даних з вікна InputBox. Її значення перетворюється в тип Integer за

допомогою функції Val.

Якщо програма містить коментарі, то вони починаються знаком

апостроф ( ’ ). У вищенаведеному прикладі застосовуються

коментарі для опису типу програм.

Під час виготовлення, налагодження та випробування процедур

може виявитися корисним кваліфікатор Optional, який дозволяє

присвоювати значення вхідному параметру за замовчуванням.

Аргументи, які має кваліфікатор Optional, повинні знаходитися

наприкінці списку параметрів процедури.

Процедура може мати кваліфікатор Private, він її визначає, як

локальну, використовується для обмеження області доступу до неї.

Кваліфікатор ByVal дозволяє передати значення параметра за

значенням, будучи зміненим у процедурі, воно не зміниться в

головній програмі. Кваліфікатор ByRef передасть параметр за

посиланням. Його застосування ідентичне передачі адреси змінної,

така змінна набуває властивості глобальної.

Нижче поданий приклад оголошення локальної процедури

pryklad, параметр якої slovo переданий за значенням, а – expo має

кваліфікатор Optional.

Private Sub pryklad(ByVal slovo As Single, Optional expo as Double =

2.71828182836405)

2.2.6 Об’єкти VBA

Об’єкт DoCmd дозволяє виконати команду. Розглянемо його

окремі методи.

Метод Open – відкрити об’єкт.

Приклад. Відкрити таблицю Oplaty і представити її до

редагування:

DoCmd.OpenTable "Oplaty", acViewNormal, acEdit

Метод FindRecord – пошук запису за зразком.

Приклад. У відкритій таблиці знайти і представити до

редагування запис, поточне поле якого містить значення “Дрозд”:

DoCmd.FindRecord ("Дрозд")

Метод GoToRecord – перехід на запис.

Приклад. У відкритій таблиці Oplaty представити до

редагування 4-й запис:

DoCmd.GoToRecord , "Oplaty ", acGoTo, 4

Метод GoToRecord може містити такі параметри:

acFirst – попередній запис;

acGoTo – заданий запис;

acLast – останній запис;

acNewRec – новий запис;

acNext default – наступний запис;

acPrevious – початок набору даних.

Метод Rename – змінити ім’я об’єкта.

Приклад. Змінити ім’я таблиці Kadry на Klienty:

DoCmd.Rename "Klienty", acTable, "Kadry"

Метод RunSQL – виконати запит (вираз SQL).

Приклад 1. Знищити всі записи відкритої таблиці Gazpr, поле

Nazpr яких містить значення “Смодне – Майорка”:

S = "DELETE * FROM Gazpr WHERE Nazpr='Смодне – Майорка'"

DoCmd.RunSQL S

Приклад 2. У всіх записах відкритої таблиці Gazpr змінити

значення поля Nazpr на “Гаваї”, якщо воно дорівнює “Канари”:

p = "UPDATE Gazpr SET Nazpr = 'Гаваї' WHERE Nazpr = 'Канари'"

DoCmd.RunSQL p

Метод RunMacro – виконати макрос.

Приклад. Виконати макрос Perewirka:

DoCmd.RunMacro "Perewirka"

Метод Close – закрити об’єкт.

Приклад. Закрити таблицю Oplaty з занесенням у пам’ять

виконаних змін:

DoCmd.Close acTable, "Oplaty", acSaveYes

Метод Quit – вихід з MS Access та закриття всіх його вікон.

Об’єкт Recordset призначений для обробітку наборів даних.

Набір даних являє собою таблицю, яка містить дані з однієї або

декількох таблиць. Цей об’єкт є найбільш вживаним під час

програмного редагування таблиць. Його оголошення має вигляд:

Dim нд As New ADODB.Recordset

Тут нд – ім’я набору даних, змінна об’єктного типу.

У процесі розвитку VBA об’єкт Recordset був розроблений у

декількох варіантах, з них два останні – стандартів DAO i ADO

підтримуються версією MS Access 2002. Стандарт ADO новіший,

його об’єкти мають назви попередника, але дещо змінені

властивості, кількість та типи параметрів методів, внесені нові

додаткові засоби, зокрема, для роботи з зовнішніми даними,

такими, наприклад, як масмедіа. Якщо використовується стандарт

ADO, то при оголошенні набору даних необхідно додавати

описувач ADODB.

Об’єктній змінній нд можна присвоїти значення об’єкта за

допомогою команди Set:

Set нд = New ADODB.Recordset

Нижче подані окремі методи набору даних.

Метод Open служить для відкриття набору даних, він має вигляд:

нд.Open "Джерело", назва з’єднання з БД, параметри

Приклад. Створити та відкрити набір даних aRS на основі

таблиці Gazpr:

aRS.Open "Gazpr",CurrentProject.Connection,adOpenKeyset,adLockOptimistic

Тут CurrentProject.Connection – з’єднання з поточним проектом.

Параметри adOpenKeyset і adLockOptimistic визначають тип курсора та

умови колективного доступу до набору даних. Курсор – це

допоміжна (скрита) таблиця, яка служить як тимчасове хранилище

набору даних у оперативній пам’яті. Більш детально курсори

будуть розглянені в розділі 4.6.3.

Перечислимо та коротко охарактеризуємо окремі такі методи:

Метод AddNew – додати запис.

Метод Delete(adAffectCurrent) – знищити запис (поточний);

Метод Move(k) – перейти на k-й запис;

Метод Update – змінити дані та перенести їх у таблицю-джерело;

Метод Filter – фільтрація набору даних за умовою;

Метод Close – закрити набір даних.

Після закриття набору даних, який не передбачено надалі

використовувати в програмі, слід звільнити пам’ять, яку він

займав, за допомогою команди:

Set нд = Nothing

Якщо пам’ять залишиться незвільненою, то її неможливо буде

використати до кінця виконання програми. Це особливо стосується

великих наборів даних, які можуть розтранжирити всю пам’ять.

Часто виникає потреба у тимчасовому знищенні тих наборів

даних, які порівняно рідко використовуються. Тоді одна й та ж

область пам’яті може бути використана різними наборами даних.

Оскільки на кожне формування набору даних витрачається час, то

виникає задача оптимізації за певними критеріями: ефективне

використання пам’яті або мінімум часу на виконання програми.

Об’єкт Recordset має такі властивості:

Name – ім’я набору даних;

Type – тип набору даних;

CursorType – тип курсора;

Edit – редагування набору даних;

Bof, Eof – початок, кінець набору даних;

RecordCount – кількість записів;

нд(“ім’я поля”).Value – значення поля, можуть бути ще такі варіанти:

нд(№ поля).Value – значення поля, № починається з 0 або

нд.Fields(№ поля).Value – значення поля, № починається з 0;

AbsolutePosition – № поточного запису.

Об’єкт Err вигідний тим, що його можна використати для

програмної обробки помилок. Розглянемо декілька його таких

властивостей:

Number – номер помилки;

Description – системне повідомлення;

AccessError() – масив системних повідомлень.

Їх використання покажемо на прикладі. Масив системних

повідомлень містить короткі описи помилок англійською або

російською мовою. Їх можна переглянути за допомогою такої

програми:

Option Compare Database

Option Explicit

Sub Zmist_pomylok()

Dim i As Integer

For i = 1 To 50000

MsgBox "№: " & i & "Текст помилки: " & AccessError(i)

Next i

End Sub

Для видачі повідомлень державною мовою варто створити їх

масив. У прикладі, текст якого поданий нижче, застосувується

масив Powidomle(5000) для обробки заздалегідь помилкової команди.

Відомо, що команда DoCmd.FindNext в даній програмі спричинить

виникнення помилки з кодом 2143, яка видає на екран

повідомлення: “Не был указан образец поиска в команде НайтиЗапись”.

Його замінено на інший такий: “Тут команда пошуку запису

недопустима”.

Option Compare Database

Option Explicit

Private Sub Demo_pomylky()

Dim Powidomle(5000) As String

Powidomle(2143) = "Тут команда пошуку запису недопустима"

On Error GoTo r

DoCmd.FindNext ‘Заздалегідь недопустима команда

x: Exit Sub

r: MsgBox " Помилка: " & Powidomle(Err.Number)

Resume x

End Sub

Запитання для перевірки

Перечисліть та коротко охарактеризуйте програмні засоби

VBA для роботи з базами даних.

Перечисліть і коротко охарактеризуйте види модулів VBA.

3. Чим відрізняється модуль від макроса?

Чи можна модуль класу назвати макросом?

5. Назвіть програмний засіб, який забезпечить незмінність

константи в програмі.

6. Знайдіть спосіб передачі функцією декількох змінених нею

значень у головну програму.

Чи може процедура мовою VBA мати змінну кількість

параметрів? Наведіть приклад.

Що таке набір даних і для чого він використовується?

9. Чому після закриття набору даних його необхідно

знищувати? Що станеться, якщо цього не зробити?

10.

Як можна використати закритий набір даних?

11.

Яка різниця між повторним відкриттям одного й того ж за

змістом щойно закритого і знищеного набору даних?

12.

Чи може програма, складена мовою VBA, виконати команду,

написану мовою SQL?

3. Приклад побудови об’єктів БД

3.1 Постановка задачі

Задача 1. Спроектувати та утворити базу даних

Transgas

для

зберігання та обробки інформації, зразки якої подані в таблиці А7

додатка А. Використовуючи графічний редактор Paint, виготовити

логотипи підприємств та карти-схеми газопроводів.

Задача 2. Підготувати і занести в таблиці контрольні дані.

Задача 3. Виготовити запити, де відібрати дані для звітів і форм.

Задача 4. Виготовити звіт про сумарний об’єм газу за поточний

рік по кожному підприємству для показаного на формі

газопроводу. До заголовка звіту додати назву газопроводу, слова

“станом на” і встановити поточну дату.

Задача 5. Побудувати графік сумарної кількості газу за кожний

рік протягом останніх 8 років підприємством, назва якого

знаходиться на відкритій формі.

Задача 6. Виготовити звіт про розподіл кількості газу на

кожному газопроводі по підприємствах у поточному році.

Навпроти назв газопроводів установити значення кількості для

кожного підприємства. Передбачити підсумки по вертикалі і по

горизонталі.

Задача 7. Виготовити форми для перегляду вмісту таблиць з

даними та доповнення БД відомостями про рух газу. Передбачити

перегляд на формі таких даних: назва підприємства, логотип

підприємства, назва газопроводу, кількість ниток газопроводу,

діаметр газопроводу, карта газопроводу, дата та кількість

перепомпованого газу. Забезпечити форму пояснювальними

написами, засобами запуску звітів та внесення в базу нових даних

про добову кількість перепомпованого газу.

Задача 8. Виготовити поштову наклейку з відомостями про

діючі газопроводи (назва, кількість ниток, діаметр однієї нитки,

мм), передбачити видачу відповідних повідомлень для

газопроводів, кількість ниток яких більша 3. Вказати сумарну

кількість газу в м куб. протягом останніх 10 років.

Задача 9. Скласти програму для формування і видачі звіту за

умовою задачі 4. Відредагувати макрос кнопки, яка запускає звіт з

форми, за її допомогою забезпечити видачу обох звітів:

виготовленого майстром і програмою.

3.2 Розробка та обгрунтування структури БД

При розробці структури БД слід брати до уваги такі фактори:

– база даних – це модель об’єкта, для якого вона розроблена,

тому вона повинна точно і адекватно відображати всі його

матеріальні та фінансові ресурси, а також зв’язки між ними;

– забезпечення виконання всіх необхідних робіт та видачі

потрібних документів і звітів;

– особливості програмного середовища, в якому розробляється

база даних. Як правило, відомі системи управління базами

даних (СУБД) забезпечують виконання всіх потрібних

функцій, однак кожна з них реалізована по-різному;

– максимальне використання стандартних програмних засобів,

виготовлених висококваліфікованими програмістами;

– надійність збереження даних, запобігання втрат інформації

(ураження вірусами, захист від несанкціонованого доступу та

некваліфікованого користування, стан технічних засобів);

– на ведення БД повинно затрачатися якомога менше

інтелектуальних та технічних ресурсів.

На рисунку 3.1 показана структура БД

Transgaz

для нашого

прикладу, в ній будуть знаходитися такі об’єкти:

– три таблиці:

Підприємства

Газопроводи

Облік газу

, вони вже

були визначені в розділі 1.3. Назвемо їх відповідно:

Pidpr

Gazpr

Oblik

;

– шість таких запитів:



D_form

для форми

Forma

;



Grafik

для діаграми;



Naklejka

для поштових наклейок;