Створення запитів за допомогою Microsoft Query
У Microsoft Office входить окрема програма, яка називається Microsoft Query, що надає доступ до баз даних, створених за допомогою таких програмних засобів, як dBASE, Access, Paradox, Microsoft SQL Server і т. П. Ця програма дозволяє відбирати інформацію з бази даних, сортувати , редагувати і копіювати її на робочий лист. Використовуючи Microsoft Query, можна отримати дані на основі заданих користувачем критеріїв. Excel зв'язується з Microsoft Query допомогою механізму DDE (Dynamic Data Exchange - динамічний обмін даними). Microsoft Query працює із зовнішніми джерелами даних через драйвери ODBC (Open Database Connectivity), які є розробленим Microsoft стандартом для роботи з базами даних. Для отримання даних за запитом за допомогою драйвера ODBC використовується мова запитів SQL (Structured Query Language).
Розглянемо послідовність створення запиту за допомогою Microsoft Query на основі простої бази даних про студентів, створеної в Access (рис. 15.1).
Виконайте команду Дані, Зовнішні дані, Створити запит (Data, Get External Data, New Database Query). З'явиться діалогове вікно Вибір джерела даних (Choose Data Source) (рис. 15.2). У цьому вікні виберіть джерело даних, наприклад базу даних, складену в Access 97. Натисніть кнопку Параметри (Options), за допомогою вікна, що з'явилося Джерело даних (Data Source) можна встановити папки, в яких слід шукати джерело даних. Отже, виберіть базу даних, складену в Access 97, в якості джерела і натисніть кнопку ОК.
У діалоговому вікні Select Database (рис. 15.3) в списку Database Name виберіть ім'я файлу бази даних (в даному випадку студенти.mdb) і натисніть кнопку ОК, що призведе до запуску майстра запитів.
Мал. 15.1.База даних про студентів
Мал. 15.2.Діалоговое вікно Вибір джерела даних
Мал. 15.3.Діалоговое вікно Select Database
Відкриється діалогове вікно Створення запиту: вибір стовпців (Query Wizard - Choose Columns) (рис. 15.4), яке дозволяє додавати стовпці або поля в список Стовпці запиту (Query Columns). При натисканні на кнопку>, розташовану в середній частині діалогового вікна Створення запиту: вибір стовпців (Query Wizard Choose Columns), обраний стовпець додається в запит, кнопка <служит для удаления выбранного столбца из списка столбцов запроса, а кнопка « позволяет удалить все столбцы запроса. В рассматриваемом случае в список Столбцы запроса (Query Columns) добавим все поля базы данных, кроме поля номер. Нажмите кнопку Далее> (Next>), переходячи до наступного кроку роботи майстра запитів.
Мал. 15.4.Діалоговое вікно Створення запиту: вибір стовпців
Відкриється діалогове вікно Створення запиту: відбір даних (Query Wizard -Filter Data) (рис. 15.5), яке дозволяє відфільтрувати дані в обраних полях. Для фільтрації даних у списку Стовпці для відбору, виділіть стовпець, по якому буде проводитися фільтрація. У першому списку вибирається операція відбору, а в другому, що містить всі наявні в обраному полі значення, виберіть потрібне або введіть своє. При необхідності ввести додаткову операцію відбору виберіть перемикач і (AND) або чи (OR), і скористайтеся наступними розкриваються списками для введення критеріїв фільтрації і т. Д. Завершивши створення критеріїв фільтрації, натисніть кнопку Далі> (Next>), переходячи до наступного кроку роботи майстра запитів. В даному випадку проведемо фільтрацію по полю група, встановивши критерій дорівнює значенню Економіка, і полю предмет з критерієм дорівнює значенню інформатика, т. Е. Відобразимо тільки інформацію про результати здачі інформатики студентів-економістів. Відзначимо, що візуально вибрані поля відрізняються від невибраних тим, що вони виділяються напівжирним шрифтом.
Мал. 15.5.Діалоговое вікно Створення запиту: відбір даних
Відкриється діалогове вікно Створення запиту: порядок сортування (Query Wizard - Sort Data) (рис. 15.6), яке дозволяє впорядкувати дані в обраних полях в порядку зростання або зменшення. Відсортуємо дані за прізвищами в порядку зростання. Натисніть кнопку Далі> (Next>), переходячи до наступного кроку роботи майстра запитів.
Мал. 15.6.Діалоговое вікно Створення запиту: порядок сортування
Відкриється діалогове вікно Створення запиту: заключний крок (рис. 15.7). При бажанні ще раз скористатися тим же запитом його можна зберегти, використовуючи кнопку Зберегти запит (Save Query). Для завершення створення запиту виберіть прапорець Повернути дані в Microsoft Excel (Return Data to Microsoft Excel) та натисніть кнопку Готово (Finish).
Мал. 15.7.Діалоговое вікно Створення запиту: заключний крок
Мал. 15.8.Діалоговое вікно Повернення даних в Microsoft Excel
Макрорекордер записує перераховані вище дії у вигляді наступного макросу:
Для уніфікації способу робота з базами даних, створеними різними засобами (наприклад, Access, FoxPro, SQL Server і т. Д.), Microsoft розробив інтерфейс доступу до даних, званий протоколом ODBC (Open Database Connectivity - відкритий зв'язок з базою даних). Введення, отримання і робота з даними відбувається за допомогою структурованого мови запитів (SQL, Structured Query Language). Протокол ODBC забезпечує стандарт передачі даних з однієї програми до іншої.
Крім того, він дозволяє створювати, видаляти, редагувати, сортувати і фільтрувати поля, записи і таблиці.
Microsoft Jet - це ядро бази даних, яке забезпечує можливість доступу до даних з усіх програм, OLE Automation, зокрема VBA і Excel, незалежно від інтерфейсу конкретної системи управління базами даних. Ядро Jet зберігає свою інформацію за замовчуванням в файлах з базами даних, що мають розширення mdb. Такі файли містять таблиці, модулі, форми, звіти, запити і індекси. Звичайно, формат mdb - це не єдиний формат баз даних, з якими працює Microsoft Jet. Він підтримує формати баз даних, створених за допомогою FoxPro, dBASE і т. Д.
DАО - об'єктний доступ до даних
Об'єктний доступ до даних (DAO, Data Access Objects) створювався як об'єктно-орієнтований інтерфейс ядра бази даних Jet, т. Е. Для забезпечення взаємодії між додатком, наприклад VBA, і ядром бази даних Jet. У Office 97 включена нова версія DАО - DАО 3.5, яка дозволяє маніпулювати даними в обхід ядра Jet. У DАО 3.5 можна безпосередньо зв'язуватися з джерелами даних через ODBCDirect, що робить програми більш швидкодіючими.
Об'єкти доступу до даних є логічним поданням реляційної бази даних: що входять в неї таблиць, записів, полів, індексів і т. Д.
Усього є 17 типів об'єктів доступу до даних. На рис. 15.10 представлені об'єкти доступу до даних перших чотирьох ієрархічних рівнів. У верхній частині ієрархії об'єктів доступу знаходиться об'єкт DBEngine, безпосередньо процесор ядра бази даних Jet. Об'єкт Error, який є елементом сімейства Errors, зберігає інформацію про помилки, що виникають при об'єктному доступі до даних. Об'єкт DBEngine також містить в собі сімейство workspaces об'єктів workspace, які встановлюють іменовані сеанси роботи користувача. Кожен об'єкт workspace включає сімейство Databases, яке складається з одного або більше об'єктів Database, що є відкритими базами даних. Об'єкт TabieDef, елемент сімейства TabieDefs, являє збережене визначення основної або приєднаної таблиці. Об'єкт QueryDef, елемент сімейства QueryDefs, являє збережене визначення запиту в базі даних Microsoft Jet або тимчасове визначення запиту в робочій області ODBCDirect. Об'єкт Recordset, елемент сімейства Recordsets, представляє набір записів в основній таблиці або набір записів, який виходить в результаті виконання запиту. В об'єктах Container групуються однотипні об'єкти, а об'єкт Relation представляє зв'язок між полями таблиць або запитів.
Порядок роботи при об'єктному доступі до даних
Під час вилучення інформації за допомогою об'єктного доступу до даних треба:
- Створити робочу область (об'єкт Workspace)
- Відкрити базу (джерела) даних (об'єкт Database)
- Створити набір записів (об'єкт Recordset)
- Виконати безпосередню роботу з конкретними записами і полями
Створення робочої області
Робочу область можна створити методом CreateWorkspace об'єкта DBEngine.
Set РабочаяОбласть = CreateWorkspace (Name, UserName, Password, UseType)
Наприклад, для створення робочої області Microsoft Jet можна використовувати наступні дві інструкції:
Dim РабочаяОбласть As CreateWorkspace
Set РабочаяОбласть = DBEngine.CreateWorkspace (Name: = "МояОбласть", UserName: = "admin", Password: = "", UseType: = dbUseJet)
Dim РабочаяОбласть As CreateWorkspace
Set РабочаяОбласть = CreateWorkspace (Name: = "МояОбласть", UserName: = "UID'V Password: =" ", UseType: = dbUseODBC)
Відкриття бази (джерела) даних
Відкрити базу даних можна методом OpenDatabase об'єкта Workspace.
Set БазаДанних = РабочаяОбласть.OpenDatabase (name, options, readonly, connect)
Об'єктна змінна, що представляє відкривається об'єкт Database
Об'єктна змінна, що представляє існуючий об'єкт workspace, який буде містити базу даних. Якщо аргумент РабочаяОбласть не заданий, метод OpenDatabase використовує стандартну робочу область
Повертає значення True, якщо покажчик поточного запису розташований перед першим записом набору записів, і значення False, якщо покажчик поточного запису розташований на першому записі набору або на будь-якому записі після неї повертає значення True, якщо покажчик поточного запису розташований після останнього запису набору, і значення False, якщо покажчик поточного запису розташований на останньому записі набору або на будь-якому записі перед нею
Розглянемо роботу з базою даних на прикладі наступного простого додатка, в якому використовуються всі основні операції з записами. База даних знаходиться в файлі студенти.mdb, створеним в Access, і складається з однієї таблиці ПервийКурс. У таблиці є чотири поля: Прізвище, Група, предмет і Оцінка. При запуску програми на екрані відображається діалогове вікно Студенти першого курсу.
Мал. 15.11.Діалоговое вікно Студенти першого курсу
Наведена нижче програма:
- При активізації діалогового вікна виводить в поля введення інформацію про першому студента і в написі всього записів вказує загальне число записів в таблиці.
- При натисканні кнопок> і <происходит перемещение на одну запись вперед и назад соответственно, а при нажатии кнопок>> і <<— перемещение в конец и начало таблицы соответственно.
При натисканні кнопки знайти відбувається пошук першого запису з таблиці з вказаною прізвищем (якщо такого запису немає, то за допомогою властивості Bookmark покажчик повертається в ту ж позицію, звідки почався пошук). При натисканні кнопки знайти далі шукається подальша запис з тим же прізвищем.
- При натисканні кнопки Видалити видаляється запис.
- При натисканні кнопки новий запис створюється новий запис за даними, введеним в поля введення.
- При натисканні кнопки Редагувати вносяться зміни в запис з бази даних.
- При натисканні кнопки Закрити закриваються база даних і діалогове вікно.
- При виборі перемикача хорошисти і відмінники в діалоговому вікні виводяться дані тільки про хорошистів і відмінників, а при виборі перемикача все - виводиться інформація про всіх студентів.
'Змінні рівня модуля
Dim РабочаяОбласть As Workspace
Dim БазаДанних As Database
Dim Запис As Recordset
Dim ЗапісьДубль As Recordset
Dim Прізвище As String
Dim Критерій As String
Dim Закладка As Variant
Private Sub CommandButton1_Click ()
'Знайти запис на прізвище
'Запам'ятовується на закладці поточна запис
'Зчитується прізвище з поля Прізвище, на її основі
'Створюється критерій пошуку і шукається перший запис з
Критерій = "[Прізвище] =" ' Прізвище " '"
'Якщо запис з вказаною прізвищем знайдена, то вона відображається
'В діалоговому вікні.
'Якщо запис не знайдено, то з'являється повідомлення і
'За допомогою закладки відбувається повернення до запису, з якої
'Було розпочато пошук
If Запісь.NoMatch = False Then
MsgBox "Запис не знайдена", vblnformation, "Студенти"
ПоказатьЗапісь End If
Private Sub CommandButtonlO_Click ()
'Перехід до останнього запису