Технологій на базі СУБД Access.

САМОСТІЙНА РОБОТА № 23

Особливості використання інформаційних

технологій на базі СУБД Access.

Література:

6. М.М.Скопень Комп’ютерні інформаційні технології: Навч. посібник.- К.: КОНДОР, 2005.-302 с.

Система управління базами даних (СУБД) Access - додаток з ідеальним середовищем для створення комп’ютерних інформаційних систем управління підприємством. У порівнянні з додатком Excel СУБД Access також використовується для автоматизації господарської діяльності. Однак при побудові великих розмірів баз даних в середовищі Access значно зростає швидкість відбору та обробки економічної інформації, а створений інтерфейс користувача є більш естетичним і досконалим.

За допомогою СУБД Access можна будувати будь-якої складності бази даних, вводити, переглядати, редагувати інформацію БД, здійснювати вибірку необхідної інформації, зберігати її, формувати звіти, управляти БД. Створена користувачем інформаційна система в середовищі додатка Access може включати сім видів об'єктів (які зберігаються в одному файлі з розширенням MDB), а саме:

- реляційні таблиці для введення та зберігання даних — основні об'єкти БД (при створенні мають стовпчики (поля) та рядки (записи) як у додатку Excel);

- запити у вигляді сформованих вимог на відбір, додавання, вилучення, оновлення необхідної інформації при виконанні дій над полями;

- форми — об'єкти для організації діалогу користувача з інформаційною системою (з використанням елементів управління), введення, перегляду та редагування інформації БД;

- звіти — об'єкти для друкування у зручному вигляді даних;

- сторінки - об'єкти для організації доступу до даних у вигляді Web- сторінок;

- макроси - програми, які сформовані з однієї або декількох макрокоманд для автоматизації виконання дій;

- модулі - програми, які написані на мові Visual Basic для обробки даних.

СУБД Access дозволяє виконати такі важливі функції, як:

1) імпорт у базу даних реляційних таблиць із додатка Excel;

2) експорт реляційних таблиць в середовище додатків Word та Excel, а також в інші бази даних;

3) створення реляційних таблиць;

4) сортування даних реляційної таблиці;

5) створення запитів на відбір, додавання, вилучення, оновлення необхідної інформації в реляційних таблицях та обчислення додаткових показників;

6) побудова діалогових електронних форм на базі реляційних таблиць та запитів з використанням власних запитів на відбір даних і виконанням обчислень допоміжних показників для візуалізації інформації;

7) формування звітів на базі реляційних таблиць і запитів з використанням власних запитів на відбір інформації та виконанням обчислень підсумків;

8) створення сторінок, для публікації даних у вигляді статистичних та динамічного доступу Web — сторінок;

9) побудова макросів і програмних модулів.

Додаток Access забезпечує обмін даними з іншими додатками. Наприклад, при імпорті в базу даних реляційних таблиць із додатка Excel необхідно: встановити закладку „Таблиці", натиснути кнопку „Створити", виконати команду меню „Імпорт таблиць", встановити тип файлу „Excel" і в режимі діалогу із визначеної папки імпортувати таблицю. При імпорті таблиці із додатка Word її спочатку потрібно скопіювати в середовище Excel.

При експорті реляційної таблиці в середовище додатка Word потрібно виділити курсором миші ярлик визначеної реляційної таблиці і виконати команду „Зв'язки з Office / Публікація в Microsoft Word" панелі інструментів „База даних". При експорті реляційної таблиці в середовище додатка Excel потрібно виділити курсором миші ярлик визначеної реляційної таблиці і виконати команду головного меню „Файл/Експорт". Встановити тип файлу „Excel" і натиснути кнопку „Експорт".

Реляційні таблиці створюються в режимі таблиці, конструктора та майстра таблиць. На практиці поширеним є режим конструктора для побудови структури таблиці (рис.5.1), яка включає імена полів (до 64 символів довжиною) і типи даних: текстовий (до 255 символів), поле MEMO (до 65535 символів), числовий (до 8 байт), дата/час (8 байт), грошовий (8 байт), лічильник (4 байти), логічний (1 біт), поле об'єкту OLE (до 1 Гбайту), гіперпосилання (до 2048 символів), майстер підстановок (створює поле, у якому пропонується вибір постійних значень із списку).

При створенні в режимі конструктора нової таблиці можна будь-яке її поле зв'язати (на закладці „Підстановка") з полем вже створеної таблиці, звідки буде здійснюватися вибір даних (у вигляді списку) для їх введення курсором миші в нову створену таблицю.

Для нової таблиці слід встановити ключове поле (рис.5.1), яке ідентифікує записи. Якщо цього не робити, тоді додаток Access самостійно пропонує встановлення ключа і вигляді поля з ім'ям „Код" і типом даних „Лічильник". Для встановлення простого ключа (визначає зв'язок між двома таблицями "один до багатьох" або "один до одного") необхідно виділити курсором поле і через контекстне меню встановити ключ. Для встановлення складеного ключа (визначає зв'язок між двома таблицями "багато до багатьох") необхідно виділити курсором декілька полів (які повинні знаходиться поруч) і через контекстне меню встановити ключ.

При роботі з реляційними таблицями можна сортувати данні шляхом виділення курсором миші визначеного заголовка стовпчика та виконання через контекстне меню команди „Сортування за зростанням" або „Сортування за зменшенням".

Зв'язки між реляційними таблицями (рис.5.2) встановлюються для забезпечення цілісності бази даних і, перш за все, для формування запитів і звітів з використанням декількох таблиць. Для встановлення зв'язків (формування схеми даних) необхідно: виконати команди головного меню „Сервіс/ Схема даних"; у діалоговому вікні „Додавання таблиць" вибрати таблиці; перетягнути курсором миші ключове поле (виділено напівжирним шрифтом) однієї таблиці на те ж саме не ключове поле іншої таблиці; у діалоговому вікні „Зв'язки" встановити прапорець „Забезпечення цілісності даних" і натиснути кнопку „Створити".

Важливим об'єктом інформаційної системи, яка створюється користувачем у середовищі додатку Access, є запит. Розрізняють: простіші запити, які створюються за допомогою команди „Фільтр"; прості запити на вибірку визначених полів зв'язаних реляційних таблиць; складні запити на додавання, вилучення, оновлення необхідної інформації в реляційних таблицях та обчисленням додаткових показників.

Простіші запити формуються для створених таблиць, форм і запитів наступним чином. Наприклад (табл.5.1), необхідно дізнатися, який індекс міста Києва у довіднику „Міста України". Довідник зберігається у вигляді реляційної таблиці. Для цього, потрібно відкрити таблицю кнопкою „Відкрити". На заголовку таблиці через контекстне меню або на панелі інструментів „Таблиця в режимі таблиці" виконати команду „Змінити фільтр". У полі „Місто" ввести з клавіатури "Київ" (можна ввести без лапок) і виконати команду „Застосувати фільтр". Потрібний запис буде знайдено. Якщо назва дуже довга, тоді можна ввести з клавіатури перші літери в кінці із зірочкою — "Киї*". Таке саме правило діє при пошуку чисел.

Аналогічним чином організовується фільтр при використанні діалогової форми. Однак при цьому необхідно відкрити форму, встановити курсор у відповідне поле пошуку даних і через контекстне меню ввести з клавіатури у вікно "Фільтр для" умову. Наприклад, "Львів" і виконати команду „Фільтр за виділеним". При зміні фільтра необхідно встановлений фільтр вилучити командою „Вилучити фільтр".

При формуванні простого запиту на вибірку визначених полів зв'язаних таблиць спочатку встановлюється закладка „Запити", натискується кнопка „Створити" та встановлюється режим „Простий запит", а потім вибираються із зв'язаних таблиць визначені поля та формується запит. Простий запит можна відредагувати в режимі конструктора.

Складні запити формуються в режимі конструктора. Припустимо, що необхідно створити запит "Вибір проводок із журналу господарських операцій (ЖГО)" в інтервалі визначених дат (рис.5.3). Для цього:

- в режимі конструктора формується бланк запиту, на який добавляється таблиця "ЖГО";

- перетягується курсором миші на рядок "Поле" бланка запиту поле таблиці "*" (зірка), тобто одночасно вибираються всі поля таблиці "ЖГО";

- перетягується курсором миші у чергове "Поле" бланка поле "Дата";

- у рядку "Умова відбору" вводиться з клавіатури умова: Between [Дата початку] And [Дата кінця];

- за допомогою команди головного меню „Запит / Параметри" вводяться з клавіатури параметри у діалогове вікно "Параметри запиту". Типи даних вибираються із списку (рис.5.4).

- зберігається запит з відповідним ім'ям.

Формули обчислення додаткових показників будуються в режимі конструктора на бланку запиту. Для цього необхідно встановити курсор на чергове поле бланка і через контекстне меню виконати команду „Побудувати". В діалоговому вікні будується формула, яка може включати імена полів, оператори, константи, функції. Наприклад, для розрахунку премії (50% від окладу, який зберігається в таблиці „Оклади") формула буде мати вигляд:

Премія: Іп1:(Оклади!Оклад*0,5)

Слід зазначити, що вданій формулі використовується функція ,,Int()" для округлення результату обчислення до цілого.

При побудові в режимі конструктора запиту на додавання формується запит, структура полів якого повинна співпадати із структурою реляційної таблиці, куди будуть додаватися записи. При цьому запит на додавання створюється командами головного меню „Запит/Додавання". У діалоговому вікні „Додавання" задається ім'я таблиці.

При побудові в режимі конструктора запиту на вилучення записів із реляційної таблиці на бланку запиту встановлюється саме таблиця, де передбачається вилучення записів і виконуються команди головного меню „Запит/Вилучен­ня".

При побудові в режимі конструктора запиту на створення таблиці на бланку запиту формується визначений перелік полів із вихідних реляційних таблиць і виконуються команди головного меню „Запит/Створення таблиці". У діалоговому вікні „Створення таблиці" задається ім'я таблиці. Крім того, необхідно активізувати закладку „Таблиці" і створити в режимі конструктора таблицю, структура якої співпадає з переліком полів на бланку запиту.

В режимі конструктора можна будувати запити з використанням структурованої мови SQL (Structured Query Language). Мова SQL була створена IBM для управління реляційними базами даних. Одночасно з цим компанія Oracle Corporation створила власну мову Oracle. Однак американський національний інститут стандартів (ANSI) прийняв мову SQL за основу стандартів.

Ядром мови SQL є інструкція SELECT (Що вибирати?). Вона використовується для відбору стовпчиків із реляційних таблиць і містить три основні речення: FROM (Звідки вибирати?), WHERE (За якою умовою?), ORDER BY (Як сортувати?).

При формуванні запиту на SQL обов'язковими у використанні є SELECT та FROM. Програмний модуль закінчується знаком „;".

Наприклад, на базі реляційної таблиці „Заробітна плата", структура якої має поле „ПІБ" (тип даних „Текстовий") і „Зарплата" (тип даних „Числовий") і за допомогою мови SQL необхідно побудувати запити підрахунку загальної кількості співробітників, а також загальної та середньої суми зарплати.

Якщо встановити курсор на заголовок бланка запиту і через контекстне меню відкрити "Режим SQL", тоді можна за допомогою клавіатури підготувати програмні модулі. В даному випадку запит підрахунку загальної кількості співробітників буде мати вигляд:

SELECT Сount([ПІБ]) AS [Кількість співробітників]

FROM [Заробітна плата];

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

SELECT Sum([3apплата]) As [Зарплата всього]

FROM [Заробітна плата];

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

SELECT Avg([Зарплата]) As [Середня зарплата]

FROM [Заробітна плата];

Діалогові електронні форми будуються в таких основних режимах, як режим конструктора, майстра форм, автоформи у стовпчик, автоформи стрічкової. Джерелами їх створення є реляційні таблиці та запити. В окремих випадках при побудові форм (наприклад, кнопкової форми) джерело даних не вибирається.

Спрощеними режимами для побудови форми є режими майстра форм і автоформ. Майстер форм дозволяє вибрати поля, вид форми (в стовпчик, стрічкова, таблична), її кольорову палітру. В режимі автоформ обраний вид форми створюється в автоматичному режимі.

Універсальним режимом побудови форми є режим конструктора. При використанні даного режиму необхідно перейти на закладку „Форми", натиснути кнопку „Створити", вибрати режим „Конструктор" і джерело даних. У вікні побудови діалогової електронної форми оптимально підбирається розмір бланка форми та місце розташування на екрані. Потім за допомогою панелі елементів, яка знаходиться на стандартній панелі „Конструктор форм", будуються на бланку курсором миші такі елементи управління, як: надписи, рамки, поля, кнопки, малюнки тощо.

Формі можна придати естетичний вигляд (рис.5.5) з використанням при піднятих, утоплених або іншого вигляду рамок, шрифтів тексту, кольору фону та літер. Рамки з прийнятним виглядом створюються кнопкою „Прямокутник" на панелі елементів і кнопками „Оформлення" на панелі „Формат (форма/звіт)". Колір фону вибирається через контекстне меню, а колір літер — на панелі „Формат (форма/звіт)". При виборі кольору фону можна також використовувати кнопку „Автоформат" на стандартній панелі „Конструктор форм". При необхідності на бланк форми встановлюється будь-який малюнок. Для цього потрібно на панелі елементів управління натиснути кнопку „Малюнок" і знайти його у визначеній папці.

Для побудови полів на стандартній панелі „Конструктор форм" натискується кнопка „Список полів". З відкритого вікна списку полів (наприклад, з таблиці, яка служить джерелом даних) перетягуються і формуються необхідні поля в прийнятних місцях бланка форми.

Слід зазначити, що поле, яке створюється на формі, складається з двох частин: лівої (відображує ім'я поля) та правої (відображує значення поля). Для оптимального розміщення поля розтягнути ці дві частини можна в тому випадку, якщо встановлений на полі курсор миші приймає вигляд вказівного пальця. Якщо курсор приймає вигляд долоні — обидві частини поля взаємопов'язані і переміщаються на формі одночасно. При необхідності можна встановити колір шрифтів та фону полів.

При виконанням обчислень допоміжних показників для візуалізації інформації на діалоговій формі необхідно: натиснути кнопку „Поле" на панелі елементів; встановити курсором миші у визначеному місці нове поле і оптимально його розмістити; клацнути курсором в лівій частині поля і ввести його ім'я; клацнути курсором в правій частині поля і ввести формулу (табл.5.2).

Примітка: Для розрахунку показника „Прибуток турфірми" створюється поле з системним ім'ям „Поле19", тому при обчисленні допоміжного показника „Рентабельність турфірми" використовується саме це ім'я поля.

При побудові діалогових електронних форм можна створювати запити на відбір даних. Наприклад, на базі реляційної таблиці „Довідник працівників турфірми" потрібно побудувати діалогову форму перегляду працівників за визначений рік. Для цього необхідно відкрити створену форму в режимі конструктора, натиснути кнопку „Властивості" на панелі інструментів „Конструктор форм", активізувати закладку „Подія" та режим „Відкриття". У діалоговому вікні „Побудувач" вибрати режим „Програми" і за допомогою клавіатури підготувати програмний модуль:

В даному випадку перед відкриттям форми на екрані ПК буде з'являтися діалогове вікно (формується за допомогою інструкції Dim), куди користувач повинен ввести з клавіатури рік трудової діяльності працівника. Відкрита діалогова форма дасть можливість переглянути дані (які будуть відсортовані по полю „ПІБ" за зростанням) щодо працівників тур­фірми за визначеній рік.

На формі, яка створюється, можна встановити такі елементи управління як кнопки. Для цього потрібно натиснути на панелі елементів кнопку „Кнопка". Далі вибрати визначену категорію (переходи по записам, обробка записів, робота з формою; робота зі звітом, додаток, різне), а потім їх дії. Наприклад, при роботі з формою можна вибрати такі дії, як: відкриття та закриття форми, друк форми та ін.

Характерні особливості має побудова кнопкової форми. Тут джерело даних не обирається, а список полів на панелі інструментів блокується. У подальшому встановлюються на формі кнопки по категоріям. Побудувати кнопкову форму можна також за допомогою диспетчера командами головного меню „Сервіс /Службові програми /Диспетчер кнопкових форм". Для цієї мети необхідно спланувати, які і де будуть розташовані кнопки, а потім активізувати диспетчера кнопкової форми. Спочатку створюються електронні сторінки кнопкової форми. Далі необхідно встановити по черзі на кожну сторінку курсор і створити відповідні кнопки. Наприклад, на головній кнопковій формі (при виконанні команд „Змінити", а потім „Створити") можна сформувати кнопки: „Перегляд і введення інформації", „Запити", „Звіти", „Зміна кнопкової форми", „Вихід із бази даних". Для перших трьох кнопок робляться посилання на відповідні сторінки. Тобто, при їх натискання буде активізована відповідна сторінка з власними кнопками. Для останніх двох встановлюються відповідні команди їх виконання.

Побудована таким чином діалогова електронна форма при її відкритті має зайві елементи. Це такі елементи, як: смуги прокрутки, область виділення, поле номера запису, кнопка віконного меню, розмірів вікна та закриття тощо. Щоб встановити їх відсутність необхідно в режимі конструктора вибрати через контекстне меню режим „Властивості форми", а потім закладку „Макет".

Звіти будуються в таких основних режимах, як режим конструктора, майстра звітів, автозвіту у стовпчик, авто-звіту стрічкового. Джерелами їх створення є реляційні таблиці та запити. В окремих випадках джерелом даних можуть бути форми. На практиці часто використовується режим майстра звітів, який дозволяє вибрати: доступні поля, рівні групування, макет звіту та його орієнтацію на сторінці (книжковий, альбомний), стиль шрифтів. Після того, як звіт буде готовий можна перейти до режиму конструктора і зробити деякі зміни: курсором миші розтягнути ширину полів для повного перегляду назв, оптимально розмістити поля, ввести формули виконанням обчислень підсумків. Наприклад, для створення підсумку записів по полю „Ціна" необхідно активізувати панель елементів, натиснути кнопку „Поле" і встановити його в області „Примітка звіту" під стовпчиком „Ціна". В ліву частину поля ввести назву (наприклад, "Разом:"), а в праву частину ввести формулу =Sum([Ціна]).

Слід зауважити, що при побудові звіту за допомогою мови Visual Basic можна підготувати запит на відбір інформації при її друкуванні.. Наприклад, необхідно друкувати звіт на базі таблиці "Заробітна плата" за визначений місяць. Для цього необхідно відкрити створений звіт в режимі конструктора, натиснути кнопку „Властивості" на панелі інструментів „Конструктор звітів", активізувати закладку „Подія" та режим „Відкриття". У діалоговому вікні „Побудувач" вибрати режим „Програми" і за допомогою клавіатури підготувати програмний модуль:

Private Sub Report_Open(Cancel As Integer)

Dim Ввести_місяць As Variant

Me.RecordSource = "select*from[Заробітна плата]where[Місяць]= Ввести_місяць And [Зарплата]

> 0 Order by [ПІБ]"

End Sub

В даному випадку перед відкриттям звіту на екрані ПК буде з'являтися діалогове вікно (формується за допомогою інструкції Dim), куди користувач повинен ввести з клавіатури місяць нарахування зарплати працівнику. Відкритий звіт дасть можливість переглянути дані (відсортовані по полю „ПІБ" за зростанням) щодо працівників, яким була нарахована зарплата (умова відбору записів [Зарплата] > 0) за визначений місяць.

Простішими режимами підготовки звіту є автозвіт у стовпчик і стрічковий. Тут без втручання користувача автоматично формується звіт. Але і в цих випадках звіт потребує редагування в режимі конструктора.

Web — сторінки призначені для роботи в мережах, де застосовується технологія „клієнт — сервер". „Клієнт" — це ПК користувача, який використовує ресурси мережі, „сервер" — це ПК, який надає ресурси в мережу. В середовищі додатка Access на базі ПК - клієнта можна створювати Web — сторінки, а потім розміщати їх на ПК - сервері. Слід зауважити, що при використанні технології „клієнт-сервер" інформація запитується та надсилається у вигляді стандартних протоколів:

- HTTP (Hyper Text Transfer Protocol) — застосовується для пересилання по мережі гіпертекстових документів, в яких виділені (кольором) слова. В будь-який час користувач може розгортати ці слова, при встановленні курсору миші на слово та натисканні лівої кнопки миші. Таким чи­ном можна отримати додаткову інформацію щодо цих слів, у вигляді тексту, малюнків, файлів тощо;

- FTP (File Transfer Protocol) — застосовується для пересилання файлів від одного ПК до іншого.

При цьому Web- сторінки оформлюються за допомогою мови гіпертекстової розмітки HTML (Hyper Text Markup Language).

Створення Web- сторінок на базі додатку Access здійснюється двома способами:

1. Публікація даних у вигляді статичних Web - сторінок;

2. Створення Web- сторінок динамічного доступу до даних за допомогою майстра.

Для публікації даних у вигляді статичної Web — сторінки потрібно відкрити базу даних, виділити курсором миші реляційну таблицю, запит або звіт та виконати команду головного меню „Файл / Експорт". При цьому у списку вибрати тип файлу „Документи HTML" і зберегти у визначеній папці.

Для створення Web-сторінки динамічного доступу до даних потрібно відкрити базу даних, активізувати закладку „Сторінки", двічі клацнути курсором на режимі „Створення сторінок доступу до даних за допомогою майстра", вибрати реляційну таблицю або запит, поля, рівень групування, порядок сортування. Після завершення роботи майстра з'являється вікно конструктора та панель елементів управління, за допомогою якої можна встановити необхідні елементи управління (надписи, поля зі списками, кнопки, малюнки тощо).

Для публікації створених Web-сторінок на Web-сервері потрібно знати ім'я каталогу їх розміщення, а також ім'я та пароль сервера.

В середовищі додатку Access багато дій можна викону­вати за допомогою макросів та програмних модулів.

Макрос — це об'єкт БД, який складається з однієї або декількох типових макрокоманд виконання дій. Для створення макросу необхідно активізувати закладку „ Макроси" і натиснути кнопку „Створити". В клітинці стовпчика

„Макрокоманда" необхідно розкрити список макрокоманд і, клацнувши мишею, вибрати ім'я визначеної макрокоманди. Макрокоманди вводяться послідовно в клітинки і виконуються у порядку їх розміщення. За допомогою макрокоманд можна виконувати такі дії, як відкриття, закриття, друк об'єктів бази даних і т.п. Запускати макрос можна за допомогою кнопки „Запуск" або за допомогою ярлика на робочому столі ПК. Щоб створити ярлик, необхідно виділити курсором макрос і через контекстне меню виконати команду „Створити ярлик".

СУБД Access дає можливість користувачу створювати на Visual Basic програмні модулі. Наприклад, можна сформувати програмний модуль автоматичного виклику будь-якої форми за допомогою попередньої форми. Зробимо ілюстрацію даної технології на наступному прикладі. Припустимо, що при побудові інформаційної системи створена перша форма "Заставка", яка повинна бути на екрані 3 секунди, а потім автоматично викликати другу (головну) кнопкову форму. Для цього потрібно відкрити форму "Заставка" в режимі конструктора, натиснути кнопку "Властивості" панелі інструментів „Конструктор форм", у списку знайти рядок "Таймер" і натиснути на кнопку "...". Далі ввести з клавіатури програмний модуль на мові Visual Basic (перший і останній рядок вводяться автоматично):

Потім у рядок "Інтервал таймера" діалогу "Властивості" вводиться з клавіатури число 3000(1000 дорівнює 1 секунді), а форма "Заставка" зберігається.

Якщо СУБД Access завантажений, тобто готовий до роботи, тоді в його середовищі можна використовувати наступні інформаційні технології (табл. 5.3).