Згадайте дитинство, коли алфавітний суп був вашою улюбленою стравою. Добре, що у вас збереглися ці спогади — зараз ми покажемо вам, як приготувати свого роду алфавітний суп із бази даних. У наступних розділах окреслено основи роботи з базою даних, доступ через рядок підключення і через програмний інтерфейс бази даних мовою Access VBA.
У цій статті
Використання драйвера ODBC або постачальника OLE DB
Програмний доступ до SQL Server з Access
Використання драйвера ODBC або постачальника OLE DB
Рядки підключення існують уже доволі давно. Рядок підключення певного формату можна задати як в інтерфейсі користувача Access, так і в коді VBA. Рядок підключення (як ODBC, так і OLE DB) передає інформацію, наприклад розташування сервера, ім'я бази даних, тип безпеки та інші корисні параметри, безпосередньо в базу даних. Наприклад:
ODBC;DRIVER=SQL Server;SERVER="MyServer";DATABASE="MyHRdb";TRUSTED_CONNECTION=Yes
Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyHRdb; Integrated Security=SSPI;
Спочатку з’явилася автономна бібліотека SQL Server Native Client (SNAC), що містила технології ODBC і OLED, і вона досі доступна для SQL Server версій 2005 – 2012. SNAC використовувалася в багатьох старих програмах і до цього часу підтримується для забезпечення зворотної сумісності, але ми не радимо використовувати цю бібліотеку для розробки нових програм. Слід використовувати новіші окремі, завантажувані версії драйверів ODBC.
Драйвери ODBC
Open Database Connectivity (ODBC) — це протокол, який використовується для підключення бази даних Access до зовнішнього джерела даних, як-от Microsoft SQL Server. Зазвичай, щоб додати рядок підключення, використовують файлові джерела даних (також звані файлами DSN); у цьому разі в рядку підключення використовується ключове слово FILEDSN, або дані з реєстру; у цьому разі використовується ключове слово DSN. Крім того, такі властивості можна вказувати в коді VBA, використовуючи рядок підключення без DSN.
Розвиток драйверів ODBC відбувався в три етапи:
-
До 2005 р. драйвери ODBC постачалися в складі компонентів доступу до даних Windows (Windows Data Access Components, WDAC), які спочатку називалися компонентами доступу до даних Microsoft (Microsoft Data Access Components, MDAC). Ці компоненти досі постачаються з Windows для забезпечення зворотної сумісності. Докладні відомості див. в статті Компоненти доступу до даних Microsoft або Windows.
-
Драйвери ODBC постачалися з SNAC для версій SQL Server 2005 – SQL Server 2012.
-
Після SQL Server 2012 драйвери ODBC постачаються окремо й містять підтримку нових функцій SQL Server.
Для нових розробок не рекомендовано використовувати драйвери ODBC перших двох етапів, а натомість слід вибирати драйвери ODBC третього етапу.
Постачальники OLE DB
Object Linking and Embedding, Database (OLE DB) — це більш сучасний протокол, який використовується для підключення бази даних Access до зовнішнього джерела даних, як-от Microsoft SQL Server. OLE DB не потребує наявності DSN, а також забезпечує повний доступ до джерел даних ODBC і драйверів ODBC.
Порада Зазвичай, щоб додати рядок підключення OLE DB, використовується діалогове вікно Властивості зв’язку з даними. Хоча в програмі Access немає можливості відкрити діалогове вікно Властивості зв’язку з даними, у Провіднику Windows можна створити пустий файл TXT, змінити тип файлу на. UDL, а потім двічі клацнути цей файл. Після створення рядка підключення знову змініть тип файлу на TXT.
Розвиток постачальників OLE DB відбувався в три етапи:
-
До 2005 р. постачальники OLE DB постачалися в складі компонентів доступу до даних Windows (Windows Data Access Components, WDAC), які спочатку називалися компонентами доступу до даних Microsoft (Microsoft Data Access Components, MDAC).
-
Постачальники OLE DB постачалися з версіями SQL Server 2005 – SQL Server 2017. У 2011 р. їх було оголошено застарілими.
-
У 2017 р. постачальник OLE DB для SQL Server знову було введено в дію.
Наразі рекомендована версія для розробки нових рішень — драйвер OLE DB 18 для SQL Server.
Оптимізація продуктивності за допомогою рядка підключення ODBC
Щоб оптимізувати продуктивність, звести до мінімуму мережевий трафік і скоротити багатокористувацький доступ до бази даних SQL Server, використовуйте якомога менше рядків підключення, поширюючи їх по кількох наборах записів. Хоча Access просто передає рядок підключення на сервер, програма розпізнає та може використовувати такі ключові слова: DSN, DATABASE, UID, PWD і DRIVER, які допомагають звести до мінімуму комунікацію між клієнтом і сервером.
Примітка Якщо підключення ODBC до зовнішнього джерела даних буде втрачено, Access автоматично спробує відновити підключення. У разі вдалої спроби можна продовжувати роботу. Якщо ж відновити підключення не вдається, можна працювати лише з об’єктами, які не використовують підключення. Щоб повторити підключення, закрийте та знову відкрийте Access.
Рекомендації з одночасного використання ODBC і OLE DB
Уникайте сполучення рядка підключення і технологій доступу до бази даних. Використовуйте рядок підключення ODBC для DAO. Використовуйте рядок підключення OLE DB для ADO. Якщо ваша програма містить код VBA, в якому використовуються як DAO, так і ADO, використовуйте драйвер ODBC для DAO та постачальник OLE DB для ADO. Намагайтеся отримати найновіші функцію та підтримку для ODBC та OLE DB відповідно.
В ODBC використовується термін "драйвер", а в OLE DB — термін "постачальник". Ці терміни стосуються одного і того самого типу програмних компонентів, але не є взаємозамінними в синтаксисі рядка підключення. Використовуйте правильне значення терміна згідно з документацією.
Програмний доступ до SQL Server з Access
Існує два основних способи програмного доступу до бази даних SQL Server з Access.
DAO
Об'єкт доступу до даних (data access object, DAO) надає абстрактний інтерфейс бази даних. Об'єкти доступу до даних Microsoft (DAO) — це вбудована модель програмних об'єктів, яка дає змогу дістатися до суті Access і SQL Server для створення, видалення, змінення та перелічення об'єктів, таблиць, полів, індексів, зв'язків, запитів, властивостей і зовнішніх баз даних.
Докладні відомості див. у статті довіднику з об’єктів доступу до даних Microsoft.
ADO
Об'єкти даних ActiveX (ADO) пропонують високорівневу програмну модель і доступні в Access за посиланням на сторонню бібліотеку. Модель ADO проста в вивченні і дає змогу клієнтським програмам отримувати дані з різних джерел, в тому числі Access і SQL Server, і керувати ними. Її основні переваги — зручність використання, висока швидкість, низьке споживання пам'яті та місця на диску. Крім того, в ADO підтримуються основні функції для розробки та веб-програм.
Додаткові відомості див. в довіднику з об’єктів даних Microsoft ActiveX і статті Об’єкти даних Microsoft ActiveX (ADO).
Яку технологію використовувати?
У рішеннях Access, де використовується код VBA, можна використовувати DAO, ADO, або й те, й інше. DAO, як і раніше, використовується в Access за замовчуванням. Наприклад, в усіх формах, звітах і запитах Access використовується DAO. Але в разі перенесення на SQL Server рекомендовано використовувати ADO, щоб зробити рішення ефективнішим. Далі наведено загальні рекомендації з вибору DAO або ADO в різних ситуаціях.
Використовуйте DAO, якщо потрібно:
-
створити зв'язану форму для читання й записування без використання VBA;
-
запитувати локальні таблиці;
-
завантажувати дані в тимчасові таблиці;
-
використовувати запити до сервера як джерела даних для звітів або форм в режимі "лише для читання";
-
визначити й використовувати об'єкт TableDef або QueroDef у VBA.
Використовуйте ADO, якщо потрібно:
-
вдаватися до додаткових можливостей оптимізації, таких як виконання асинхронних операцій;
-
виконувати запити DDL і DML до сервера;
-
напряму звертатися до даних SQL Server за допомогою наборів записів у VBA;
-
писати спрощений код для певних завдань, як-от потокова передача BLOB-об'єктів;
-
напряму викликати збережену процедуру з параметрами, використовуючи об'єкт команди в VBA.
Зведення версій драйвера ODBC
У таблиці нижче наведено основні відомості про версії драйвера ODBC, місця їхнього завантаження й підтримку функцій. Виберіть правильну розрядність версії драйвера (64-розрядну або 32-розрядну) на основі Windows, а не Office. Якщо використовується 32-розрядна версія Access у 64-розрядній версії Windows, інсталюйте 64-розрядні драйвери, які містять необхідні для Access 32-розрядні компоненти.
Додаткові відомості див. в статтях Використання ключових слів рядка підключення для SQL Server Native Client, Нотатки про випуск ODBC для SQL Server у Windows (V17) і Функції драйвера Microsoft ODBC для SQL Server в Windows (V13, 11).
Зведення версій постачальника OLE DB
У таблиці нижче наведено основні відомості про версії постачальника OLE DB, місця їхнього завантаження й підтримку функцій. Виберіть правильну розрядність версії драйвера (64-розрядну або 32-розрядну) на основі Windows, а не Office. Якщо використовується 32-розрядна версія Access у 64-розрядній версії Windows, інсталюйте 64-розрядні драйвери, які містять необхідні для Access 32-розрядні компоненти.
Додаткові відомості див. в статті Використання ключових слів рядка підключення для SQL Server Native Client.
|
Постачальник OLE DB |
Версія |
Завантаження |
Нові можливості |
|
Драйвер OLE DB 18.2.1 (MSOLEDBSQL) |
SQL Server 2017 |
Можливості драйвера OLE DB для SQL Server і Нотатки про випуск драйвера Microsoft OLE DB для SQL Server |
|
|
SQL Server Native Client (SQLNCLI) |
SQL Server 2005 – 2012 |
Застарів, не використовується |
|
|
Драйвер OLE DB (SQLOEDB) |
Застарів, не використовується |
Зведення ключових слів ODBC
У таблиці нижче наведено ключові слова ODBC, які розпізнає SQL Server, і їхнє призначення. Access розпізнає лише частину цих ключових слів.
|
Ключове слово |
Опис |
|
Addr |
Мережева адреса сервера, на якому запущено екземпляр SQL Server. |
|
AnsiNPW |
Указує використання варіантів обробки порівнянь NULL, заповнення символьних даних, попереджень і конкатенації NULL (Yes або No) відповідно до стандарту ANSI. |
|
APP |
Ім'я програми, яка викликає функцію SQLDriverConnect. |
|
ApplicationIntent |
Оголошує тип робочого навантаження програми під час підключення до сервера (ReadOnly або ReadWrite). |
|
AttachDBFileName |
Ім'я основного файлу приєднуваної бази даних. |
|
AutoTranslate |
Указує, чи треба перетворювати на Юнікод рядки символів ANSI, які передаються між клієнтом і сервером (Yes або No). |
|
Database |
Ім’я бази даних. Description Мета підключення. Driver Ім’я драйвера, повернуте функцією SQLDrivers. |
|
DSN |
Ім'я наявного користувача або системного джерела даних ODBC. Encrypt Указує, чи потрібно шифрувати дані, перш ніж надсилати їх мережею (Yes або No). |
|
Failover_Partner |
Ім'я резервного сервера на випадок, якщо не вдається встановити підключення до основного сервера. |
|
FailoverPartnerSPN |
Ім’я учасника служби-резервного сервера. |
|
Fallback |
Застаріле ключове слово. |
|
FileDSN |
Ім'я наявного файлового джерела даних ODBC. Language Мова SQL Server. |
|
MARS_Connection |
Указує використання кількох активних наборів результатів (MARS) у разі підключення до SQL Server 2005 (9.x) або новішої версії (Yes або No). |
|
MultiSubnetFailover |
Указує, чи потрібно підключатися до прослуховувача групи доступності в групі доступності SQL Server або до екземпляра відмовостійкого кластера (Yes або No). |
|
Net |
dbnmpntw — іменовані канали, а dbmssocn — TCP/IP. |
|
PWD |
Пароль для входу до SQL Server. |
|
QueryLog_On |
Вмикає ведення журналу довготривалих запитів (Yes або No). |
|
QueryLogFile |
Повний шлях та ім'я файлу журналу довготривалих запитів. |
|
QueryLogTime |
Символьний рядок цифр, що визначає граничний час у мілісекундах для записування довготривалого запиту в журнал. |
|
QuotedId |
Указує, чи треба використовувати в SQL Server правила ISO про застосування лапок в інструкціях SQL (Yes або No). |
|
Regional |
Указує, чи використовує драйвер ODBC для SQL Server Native Client настройки клієнта під час перетворення значень грошових одиниць, дати або часу на символьні дані (Yes або No). |
|
SaveFile |
Ім’я файлу джерела даних ODBC, в якому зберігаються атрибути поточного підключення в разі успішного підключення. |
|
Server |
Ім'я екземпляра SQL Server: сервер у мережі, IP-адреса або псевдонім диспетчера конфігурацій. |
|
ServerSPN |
Ім’я учасника служби-сервера. |
|
StatsLog_On |
Активує збирання даних про продуктивність драйвера ODBC для SQL Server Native Client. |
|
StatsLogFile |
Повний шлях та ім'я файлу для записування статистики продуктивності драйвера ODBC для SQL Server Native Client. |
|
Trusted_Connection |
Указує, чи можна використовувати режим автентифікації Windows або ім'я користувача та пароль SQL Server для перевірки під час входу (Yes або No). |
|
TrustServerCertificate |
У разі використання з Encrypt вмикає шифрування з використанням сертифіката сервера з власним підписом. |
|
UID |
Ім’я для входу до SQL Server. |
|
UseProcForPrepare |
Застаріле ключове слово. |
|
WSID |
Ідентифікатор робочої станції — мережеве ім'я комп'ютера, на якому розміщено програму. |
Зведення ключових слів OLE DB
У таблиці нижче наведено ключові слова OLE DB, які розпізнає SQL Server, і їхнє призначення. Access розпізнає лише частину цих ключових слів.
|
Ключове слово |
Опис |
|
Addr |
Мережева адреса сервера, на якому запущено екземпляр SQL Server. |
|
APP |
Рядок, який ідентифікує програму. |
|
ApplicationIntent |
Оголошує тип робочого навантаження програми під час підключення до сервера (ReadOnly або ReadWrite). |
|
AttachDBFileName |
Ім'я основного файлу приєднуваної бази даних. |
|
AutoTranslate |
Вмикає перетворення символів OEM/ANSI (True або False). |
|
Connect Timeout |
Час (у секундах), протягом якого очікується завершення ініціалізації джерела даних. |
|
Current Language |
Назва мови SQL Server. |
|
Data Source |
Ім'я екземпляра SQL Server в організації. |
|
Database |
Ім’я бази даних. |
|
DataTypeCompatibility |
Число, яке визначає режим обробки даних. |
|
Encrypt |
Указує, чи потрібно шифрувати дані, перш ніж надсилати їх мережею (Yes або No). |
|
FailoverPartner |
Ім'я резервного сервера, який використовується для дзеркального відображення бази даних. |
|
FailoverPartnerSPN |
Ім’я учасника служби-резервного сервера. |
|
Initial Catalog |
Ім’я бази даних. |
|
Initial File Name |
Ім'я основного файлу (включно з повним шляхом) приєднуваної бази даних. |
|
Integrated Security |
Використовується для автентифікації Windows (SSPI). |
|
Language |
Мова SQL Server. |
|
MarsConn |
Указує використання кількох активних наборів результатів (MARS) у разі підключення до SQL Server 2005 (9.x) або новішої версії (Yes або No). |
|
Net |
Мережева бібліотека, яка використовується для підключення до екземпляра SQL Server в організації. |
|
Network Address |
Мережева адреса екземпляра SQL Server в організації. |
|
PacketSize |
Розмір мережевого пакета. За замовчуванням дорівнює 4096. |
|
Persist Security Info |
Указує, чи ввімкнуто збереження відомостей про безпеку (True або False). |
|
PersistSensitive |
Указує, чи ввімкнуто збереження конфіденційних відомостей (True або False). |
|
Provider |
У SQL Server Native Client це має бути SQLNCLI11. |
|
PWD |
Пароль для входу до SQL Server. |
|
Server |
Ім'я екземпляра SQL Server: сервер у мережі, IP-адреса або псевдонім диспетчера конфігурацій. |
|
ServerSPN |
Ім’я учасника служби-сервера. |
|
Timeout |
Час (у секундах), протягом якого очікується завершення ініціалізації джерела даних. |
|
Trusted_Connection |
Указує, чи можна використовувати режим автентифікації Windows або ім'я користувача та пароль SQL Server для перевірки під час входу (Yes або No). |
|
TrustServerCertificate |
Визначає, чи перевірено сертифікат сервера (True або False). |
|
UID |
Ім’я для входу до SQL Server. |
|
Use Encryption for Data |
Указує, чи потрібно шифрувати дані, перш ніж надсилати їх мережею (True або False). |
|
UseProcForPrepare |
Застаріле ключове слово. |
|
WSID |
Ідентифікатор робочої станції — мережеве ім'я комп'ютера, на якому розміщено програму. |
