Програмування в Access з використанням мови SQL

 

Призначення мови SQL. Мова SQLпризначена для програмування запитів в Access, управління реляційними базами даних.

Можливості мовиSQL:

· Створення запитів.

· Управління реляційними базами даних.

· Є стандартом для спілкування програмних продуктів (СКБД) різних виробників.

Характеристика SQL. SQL це скорочення від Structured Query Language.

Мова SQL була розроблена фірмою IBM на початку 70 ^х років у рамках проекту побудови систем управління реляційними базами даних.

Особливість мови SQL. Деякі запити не можуть бути визначені в бланку запиту. Це запити до сервера, що керують запитами. Для створення таких запитів потрібно ввести інструкцію SQL безпосередньо у вікно запиту в режимі SQL.

Структура мови SQL. Коли користувач створює запит у режимі конструктора запиту (користуючись графічними можливостями QBE – Query By Example запиту за зразком) Microsoft Access автоматично створює еквівалентну інструкцію SQL. Користувач має можливість переглядати і змінювати інструкції SQL у режимі SQL. Текст SQL-запиту можна переглянути, якщо в конструкторі запиту обрати команду SQL…з підменю Виду. Якщо змінити текст SQL-запиту, то автоматично обновиться відповідний запит у вікні конструктора.

Запити вSQL будуються за допомогою спеціальних інструкцій (директив, операторів, предикатів) які вказують, що потрібно зробити з вхідним набором даних (таблицею або іншим запитом) для генерації вихідного набору даних.

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

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

Запитна об’єднання комбінує поля (стовпці) з однієї або декількох таблиць або запитів в одне поле в результатах запиту. Наприклад, якщо шість постачальників щомісяця посилають нові списки устаткування то за допомогою запиту на об’єднання ці списки можна об’єднати в один, а потім помістити результати в нову таблицю, створену за допомогою запиту на створення таблиці, заснованого на запиті на об’єднання.

Запитдо сервера відправляє команди безпосередньо в бази даних ODBC, такі як Microsoft FoxPro, причому використовуються тільки команди, підтримувані сервером. Наприклад, запит до сервера можна використовувати для заван­та­ження записів або зміни даних. Керуючі запити створюють, видаляють і змі­нюють таблиці або створюють індекси в базах даних, таких як таблиці Microsoft Access або Microsoft FoxPro. Підлеглий запит являє собою інструкцію SQL SELECT, вкладену в запит на вибірку або запит на зміну. Щоб ви­зна­чити нове поле, дану інструкцію можна ввести в рядок Поле в бланку запиту. Щоб указати для даного поля умову добору, введіть інструкцію в рядок Условие отбора. Підлеглі запити використовуються для:

 

· перевірки наявності результатів підлеглого запиту (використовуються зарезервовані слова EXISTS або NOT EXISTS);

· пошуку значень в основному запиті, що рівні, перевищують або менше значень тих, які повертаються підлеглим запитом (використовуються зарезервовані слова ANY, IN або ALL);

· створення підлеглих запитів у середині підлеглих запитів (вкладені запити).

Розглянемо деякі інструкції мови SQL:

ü Оператори:
SELECT - вибрати

ü Операції:
INNER JOIN – внутрішнє поєднання

ü Директиви:
FROM – від куди;
WHERE - де;
IN - у;
GROUP BY - групуючи по;
HAVING - що мають;
ORDER BY - упорядковуючи по.

ü Предикати (логічне судження про предмет):

ALL – усі;
DISTINCT – відмінність;
DISTINCTROW – різні ряди;
TOP – верхній.

 

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

Введення виразів SQL у значення властивостей і аргументів. Інструкції SQL можна вводити як значення деяких властивостей і аргументів. Найпростіше скопіювати і вставити вираз з вікна режиму SQL. Наприклад, вираз SQL можна використовувати як аргумент «Условие отбора» макрокоманд «ОткрытьФорму»(OpenForm) і«ПрименитьФильтр»(ApplyFilter), як аргументи, що визначають підмножину або умови в статистичних функціях по підмножині, а також як значення властивостей Источник записей (RecordSource) або Источник строк (RowSource) форм і звітів. Щоб створити запит мовою SQLна об’єднання необхідно створити запит в режимі конструктора не додаючи таблиць до бланку запиту. Далі закривають вікно Добавление таблиц. В меню Запрос вибирають команду Запрос SQL, а потім команду Объединение. Можна також вибрати Режим SQL піктографічного меню Вид.

Приклади виразів в інструкціях SQL. Вирази використовуються в різних компонентах інструкціїSQL, деякі приклади наведені втабл. 7.8.

 

Таблиця7.8. Приклади виразів

 

Вираз	Результат
SELECT [Ім’я], [Прізвище] FROM [Співробітники] WHERE [Прізвище]=" Сидоров";	Виводить значення полів «Ім’я» і «Прізвище» для співробітників, що мають прізвище Сидоров.
SELECT [КодТовара], [Марка] FROM [Товари] WHERE [КодТипа]=Forms! [Нові товари]! [КодТипа];	Виводить значення полів «КодТовара» і «Марка» з таблиці «Товари» для записів, у яких значення поля «КодТипа» збігається зі значенням елемента керування «КодТипа»у відкритій формі «Нові товари».
SELECT Avg([Відпускна ціна]) AS [Середня відпускна ціна] FROM [Відомості про замовлення] WHERE [Відпускна ціна]> 1000;	Обчислює середню відпускну ціну для замовлень, у яких значення в полі «Відпускна ціна» перевищує 1000, і виводить їх у поле «Середня відпускна ціна».
SELECT [КодТипа], Count([КодТовара]) AS [КодКількостіТоварів] FROM [Товари] GROUP BY [КодТипа] HAVING Count([КодТовара])> 10;	Виводить у полі «КодКількостіТоварів» загальну кількість товарів для типів, що мають більше 10товарів.

Створення запиту на об’єднання даних полів з використанням мови SQL. Створимо запит на об’єднання даних полів Номер залікової книжки, Прізвище, Ім’я з таблиць Студенти1 та Студенти2 з використання мови SQL:

SELECT [Номер залікової книжки], [Прізвище], [Ім’я]

FROM [Студенти1]

UNION

SELECT [Номер залікової книжки ], [Прізвище], [Ім’я]

FROM [Студенти2].

Створення запиту на об’єднання з додатковою умовою. Створимо запит на об’єднання даних полів [Прізвище], [Ім’я], [Дата народження], [Адреса] з таблиць Студенти1 та Студенти2 у яких прізвище починається після літери Ж:

SELECT [Прізвище], [Ім’я], [Дата народження], [Адреса]

FROM [Студенти1]

WHERE [Прізвище]> ”Ж”

UNION

SELECT [Прізвище], [Ім’я], [Дата народження], [Адреса]

FROM [Студенти2]

WHERE [Прізвище]> ”Ж”.

В результаті отримаємо запит на об’єднання полів Прізвище, Ім’я, Дата народження, Адреса з таблиць Студенти1 та Студенти2 за умови, що прізвища студентів починаються з літери, наступної після літери Ж.

7.3.16. Створення форм і звітів

 

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

 

· проста форма;

· підлегла форма – є частиною іншої форми;

· кнопкова форма – для відкриття різних об’єктів бази даних.

 

Формамає три режими зображення:

 

· режим конструктора;

· режим форми;

· режим табличний.

 

Перемикання між зазначеними режимами виконується за допомогою перелічених команд з підменю Вид або піктограм.

Зв’язок між формою і джерелом даних для неї створюється за допомогою графічних об’єктів, які мають назву елементи управління, що є інструментами пакета Visual Basic.

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

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

Зовнішнє звітимають багато спільного з формами; також співпадають методи їх створення.

 

7.3.17. Способи створення форм і звітів

 

Форму/звіт можна створити такими способами:

 

1. за допомогою конструктора (самостійне створення);

2. за допомогою майстра;

3. за допомогою автоформи або автозвіту.

 

Для створення форми/звіту за допомогою конструктора необхідно виконати:

 

· вибрати вкладку Форма або Отчет і натиснути кнопку Создать;

· у вікні, що з’явиться, (Новая форма/отчет) вибрати Конструктор (рис.7.15);

· вибрати джерело даних – таблицю або запит;

· клацнути по кнопці ОК; у результаті відкриється вікно конструктора форми/звіту.

 

При створенні форми/звіту за допомогою майстра у вікні Новая форма/отчет варто вибрати Майстра; далі слідувати його вказівкам (рис.7.16).

 

Створення автоформи/звіту, 1 спосіб (за допомогою команд БД): Форма/Отчет ► Создать у вікні Новая форма/отчет варто вибрати один з видів автоформи/автозвіта:

 

· в столбец;

· ленточная;

· табличная (тільки для форми).

 

Створення автоформи/звіту, 2 спосіб (за допомогою команд СУБД):

 

· на вкладці Таблиці виділити таблицю, на підставі якої створюється форма/звіт;

· активізувати команду Автоформа/Автоотчет з підменю Вставка.

 

У результаті Access створить форму/звіт.

 

Рисунок 7.15. Діалогове вікно Новая форма

 

Рисунок 7.16. Вікно майстра форм

 

7.3.18. Вікно конструктора форми/звіту

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

Для роботи з конструктором форм необхідна Панель элементов і вікно Список полей. Вони необхідні щоб створювати на бланку форми чи звіту відповідні елементи управління (рис. 7.17).

Перш за все це створення полів таблиць, запитів на бланку форми чи звіту.

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

Поля до бланку форми можна додати як із списку полів який відкривається після створення форми в режимі конструктора методом перетягування зі списку або можна створювати поля з панелі елементів.

Список полів можна встановити самостійно за допомогою піктограми Список полей або за допомогою команди Вид ► Список полей.

Якщо створюється поле з панелі елементів тоді треба вказати відповідне ім’я поля таблиці чи звіту.

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

 

 

Рисунок 7.17. Вікно конструктора форми з елементами управління

 

7.3.19. Панель елементів

Панель елементів містить інструменти пакета Visual Basic (рис. 7.18, рис.7.19).

 

Рисунок 7.18. Панель елементів

 

Рисунок 7.19. Елементи управліннята їх призначення

 

Усі дані у формі (або звіті) містяться в елементах управління. Елементи управлінняуявляють собою об’єкти у формах, звітах або на сторінках доступу до даних, які використовуються для відображення даних, виконання дій або як елементи дизайну.

Елементи управління– це інструменти пакета Visual Basic. Елементи управління можуть бути:

зв’язаними, вільними або такі що обчислюються.

Зв’язаний елемент управління - приєднаний до поля базової таблиці або запиту. Такі елементи управління використовуються для відображення, введення або відновлення значень з полів таблиць бази даних.

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

Можна зробити й іншу класифікацію елементів управління:

· елементи управління для введення, відображення і відбору даних; · елементи управління для аналізу даних та елементи управління для зв’язування з Web-сторінками; · елементи управління для графіки та анімаційного тексту; · елементи управління для користувальницького настроювання; · елементи управління для організації даних.

Нижче наведений розширений список для елементів управління.

Елементи управліннядля введення, відображення і відбору даних.

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

Елементи управліннядля аналізу даних.

Діаграми Office, зведені списки Office, електронні таблиціOffice Елементи управліннядля зв’язування з Web-сторінками. Гіперпосилання і приєднані гіперпосилання.

Елементи управліннядля графіки та анімаційного тексту.

Гіпермалюнки, малюнки, вільні і зв’язані рамки об’єкта, рядок що біжить.

Елементи управліннядля користувальницького настроювання це елементи Active.

Елементи управліннядля організації даних. Елементи розгортання, лінії, розриви сторінок, кнопки переходу по записах, прямокутники, вкладки.

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

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

Значення, обране або введене в приєднане поле зі списком, вставляється в поле, до якого приєднане поле зі списком.

У полі зі списком список складається з рядків з даними. Рядки містять один або кілька стовпців, із заголовками або без заголовків. Якщо поле зі списком, що складається з декількох стовпців, є приєднаним, то Microsoft Access зберігає значення одного зі стовпців.

Вільне поле зі списком дозволяє зберігати значення, з іншого елементу керування. Поля зі списком мають властивість Ограничиться списком (LimitToList), що визначає, чи допускається введення в поле будь-яких значень або тільки співпадаючих з одним зі значень списку. Якщо у формі досить вільного місця і потрібно, щоб список постійно знаходився на екрані, а також якщо потрібно обмежити дані, що вводяться, наявним списком, замість поля зі списком можна використовувати список.

Кнопки. Кнопки у формі або на сторінці доступу до даних використовуються для виконання дії або набору дій. Наприклад, можна створити кнопку, що відкриває іншу форму. Щоб визначити дію для кнопки у формі, варто написати макрос або процедуру обробки події і вкласти її у властивість кнопки Нажатие кнопки (OnClick). За допомогою майстра кнопок можна створювати більше 30 різних типів кнопок.

На кнопці у формі можна відобразити текст, задавши значення властивості Подпись (Caption), і малюнок, задавши значення для властивості Рисунок (Picture). На сторінці доступу до даних текст на кнопці можна відобразити за допомогою властивості Value.

Списки, що розкриваються. У багатьох випадках швидше і легше вибрати значення зі списку, чим запам’ятовувати значення, що потрібно ввести. Список, що розкривається, дозволяє вибрати значення зі списку, використовуючи не занадто багато місця на сторінці доступу до даних. Список, що розкривається, на сторінці доступу до даних виглядає, як поле зі списком у формі. Як і поле зі списком, що розкривається список показує тільки один запис доти, поки його вміст не буде розгорнуто; однак у список, що розкривається, можна вводити нові значення.

Якщо список, що розкривається, у якому користувач вибирає значення, є приєднаним, це значення вставляється в поле, до якого приєднаний список.

Список, що розкривається, складається з рядків даних. Рядки можуть складати тільки один стовпець, відображуваний без заголовка.

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

Написи. Написи використовуються у формах, звітах або на сторінках доступу до даних для відображення описового тексту. Написи не відображають значення з полів або виразів вони завжди є вільними і не змінюються при переході від одного запису до іншої.

Напис може бути приєднаний до іншого елемента. Напис, створений за допомогою інструмента «Надпись», розміщується окремо і не приєднується ні до якого елемента управління. Такі написи використовуються для відображення різних відомостей, наприклад, заголовків форми або звіту, а також для виводу тексту, що пояснює. Написи, які не приєднані до елементів керування, не відображаються в режимі таблиці.

Списки. У багатьох випадках зручніше вибрати значення зі списку, чим вводити конкретне значення по пам’яті. Крім того, вибір зі списку дозволяє бути впевненим, що введене значення є припустимим.

Список складається з рядків з даними. У формі список може містить один або кілька стовпців з заголовками. Якщо список з декількох стовпців є приєднаним, то Microsoft Access зберігає значення одного зі стовпців. На сторінках доступу до даних списки складаються з одного стовпця без заголовка.

Вільний список дозволяє зберігати значення, використовуване в іншому елементі управління. Наприклад, за допомогою вільного списку можна обмежити значення, що відбираються в іншому списку або в користувальницькому діалоговому вікні. Вільний список застосовується також для пошуку запису за допомогою значення, обраного в списку.

У формах замість списків можна використовувати поля зі списком; вони займають менше місця і поряд з вибором значень зі списку допускають введення значень, відсутніх у списку.

Перемикачі. Перемикач можна використовувати у формах, звітах або на сторінках доступу до даних як окремий елемент керування для відображення логічного значення з базового джерела записів. При виборі або очищенні перемикача, приєднаного до поля логічного типу в базі даних Microsoft Access, значення з базової таблиці відображається у відповідності зі значенням властивості Формат поля(Format) цього поля (Да/Нет, Истина/Ложь або Вкл/ Выкл).У проекті Access перемикач приєднується до стовпця з типом даних Bit. Значення 1 у цьому стовпці еквівалентно умовам Да, Вкл або Истина. Значення 0 у цьому стовпці означає умову Нет, Выкл або Ложь. Перемикачі можна також використовувати в групах параметрів для відображення набору значень що вибираються.

Елемент керування «Поле». Поля використовуються у формах, звітах або на сторінках доступу до даних для відображення даних із джерела записів. Поле такого типу називають приєднаним, тому що воно зв’язано з даними в полі. Крім того, існують вільні поля. Наприклад, можна створити вільне поле для відображення результатів обчислень або для прийому даних, що вводяться користувачем. Уміст вільного поля ніде не зберігається.

Групи параметрів. Групи параметрів можна використовувати у формах, звітах або на сторінках доступу до даних для відображення обмеженого набору альтернатив. Для вибору значення в групі параметрів досить його клацнути. У кожен момент часу в групі може бути обраний тільки один параметр. У формі або звіті група параметрів складається з рамки групи і набору прапорців, перемикачів і вимикачів. На сторінці доступу до даних група перемикачів складається з рамки групи і набору перемикачів.

При приєднанні групи до поля до нього приєднується тільки рамка групи, а не прапорці що знаходяться в ній. Користувач не повинний визначати властивість Данные (ControlSource) для кожного елемента управління в групі параметрів. Замість цього у властивості Значення параметра (Option Value) для форм і звітів або у властивості Значение (Value) кожного прапорця, вимикача або перемикача варто задати число, що є припустимим для поля, до якого приєднана рамка групи. При виборі параметра в групі Microsoft Access вводить у поле значення, рівне значенню властивості Значение параметра (Option Value) або Значение (Value) обраного елемента.

 

7.3.20. Підлеглі форми та звіти

 

Підлегла форма - це форма, що знаходиться усередині іншої форми. Первинна форма називається головною формою, а форма яке є вкладеною називається підлеглою формою. Комбінацію «форма/підлегла форма» часто називають також ієрархічною формою або комбінацією «батьківської» і «дочірньої» форм.

Підлеглі форми особливо зручні для виводу даних з таблиць або запитів, зв’язаних з відношенням «один-до-богатьох». Головна форма і підлегла форма в цьому типі форм зв’язані таким чином, що в підлеглій формі виводяться тільки ті записи, що зв’язані з поточним записом у головній формі.

При використанні форми з підлеглою формою для введення нових записів поточний запис у головній формі зберігається при вході в підлеглу форму. Це гарантує, що запису з таблиці на стороні «багато хто» буде відповідати зв’язаний запис у таблиці на стороні «один». Це також автоматично зберігає кожен запис, що додається в підлеглу форму.

Головна форма може відображатися тільки як проста форма. Головна форма може мати будь-яке число підлеглих форм, якщо кожна підлегла форма вбудовується в головну форму. Мається також можливість створювати підлеглі форми до семи рівнів вкладеності. Це означає, що можна мати підлеглу форму усередині головної форми, а іншу підлеглу форму усередині цієї підлеглої форми і т.д.

Підлеглим звітом називають звіт, вставлений в інший звіт. При комбінуванні звітів один зі звітів є головним. Головний звіт може бути як приєднаним, так і вільним, тобто не заснованим на таблиці, запиті або інструкції SQL.

7.4. Автоматизація роботи з базою даних

 

7.4.1. Загальні відомості про макроси

 

Макросом називають записані в потрібній послідовності макрокоманди (дії, комбінації клавіш, рис.7.20), які необхідні для автоматизації виконання операцій, які повторюються (таких як відкриття форм, друк звітів та ін.), шляхом натиснення кнопки будь-якої кількості разів.

Макрокоманда – це основний компонент макросу (замкнута інструкція, самостійно або в комбінації з іншими макрокомандами) який визначає дії в макросі.

Макрокоманда має аргумент, що дає додаткову інформацію про макрокоманду яка виконується.

Види макросів. У СУБДAccess можна створювати різні макроси:

 

- макроси з простими макрокомандами;

- макроси з макрогрупами;

- макроси з умовними макрокомандами;

- макрос Autokeys;

- макрос Autoexec

 

Родинні макроси (тобто дуже схожі) можна об’єднати в макрогрупу і привласнити їй ім’я. Для встановлення імен макрогрупи досить у меню Вид вікна макросу обрати команду Имена макросов. Існують умовні макрокоманди. Для виведення стовпця Условия слід у вікні макросу в меню Вид обрати команду Условия.