Office

Підключення Access до сервера SQL Server

Згадайте дитинство, коли алфавітний суп був вашою улюбленою стравою. Добре, що у вас збереглися ці спогади — зараз ми покажемо вам, як приготувати свого роду алфавітний суп із бази даних. У наступних розділах окреслено основи роботи з базою даних, доступ через рядок підключення і через програмний інтерфейс бази даних мовою Access VBA.

Компоненти доступу до даних

У цій статті

Використання драйвера ODBC або постачальника OLE DB

Програмний доступ до SQL Server з Access

Зведення версій драйвера ODBC

Зведення версій постачальника OLE DB

Зведення ключових слів ODBC

Зведення ключових слів OLE DB

Використання драйвера 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).

Драйвери ODBC

Версія

Завантаження

Нові можливості

Драйвери ODBC 17.0 – 17.3

SQL Server 2017

Завантажити

Драйвер ODBC 17.3

Використання служби Azure Active Directory з драйвером ODBC

Обмеження драйвера ODBC за використання Always Encrypted

Використання транзакцій XA

Драйвер ODBC 17.2

Використання Always Encrypted з драйвером ODBC для SQL Server

Класифікація даних

Параметри сортування для кодування UTF-8 на сервері та підтримка Юнікоду

Драйвер ODBC 17.1

Використання Always Encrypted з драйвером ODBC для SQL Server

Драйвер ODBC 17.0

Always Encrypted

UseFMTONLY    Для використання застарілих метаданих в особливих випадках, коли потрібні тимчасові таблиці. Див. статтю Нотатки про випуск ODBC для SQL Server у Windows

Відмінності в разі використання керованого екземпляра (версія ODBC 17)

Драйвер ODBC 13.1

SQL Server 2016 SP1, SQL Azure

Завантажити

Always Encrypted

Azure Active Directory

Групи доступності AlwaysOn

Групування підключень із підтримкою драйверів у драйвері ODBC для SQL Server

Драйвер ODBC 13.0

SQL Server 2016

Завантажити

Міжнародне доменне ім'я (IDN)

Драйвер ODBC 11.0

SQL Server 2005 – 2012

Завантажити

Групування підключень із підтримкою драйверів

Стійкість підключення у драйвері ODBC для Windows

Асинхронне виконання

Імена учасників служби (SPN) у клієнтських підключеннях (ODBC)

Можливості драйвера Microsoft ODBC для SQL Server у Windows

На початок сторінки

Зведення версій постачальника 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 розпізнає лише частину цих ключових слів.

Ключове слово

Description

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

Ідентифікатор робочої станції — мережеве ім'я комп'ютера, на якому розміщено програму.

На початок сторінки

Див. також

Адміністрування джерел даних ODBC

Керування зв’язаними таблицями

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×