Office
Подключение Access к SQL Server

Подключение 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 нельзя открыть диалоговое окно Свойства связи с данными, в проводнике можно создать пустой файл TXT, изменить его тип на , а затем дважды щелкнуть файл. После создания строки подключения снова измените тип файла на 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 и OLEDB.

В 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 and Microsoft ActiveX Data Objects (ADO).

Какую технологию использовать?

В решениях Access, где применяется код VBA, можно использовать DAO, ADO или и то, и другое. DAO, как и раньше, используется в Access по умолчанию. Например, во всех формах, отчетах и запросах Access используется DAO. Но при миграции на SQL Server рекомендуется использовать ADO, чтобы сделать решение более эффективным. Далее приведены общие рекомендации по выбору DAO или ADO в разных ситуациях.

Используйте DAO, если требуется:

  • создать связанную форму для чтения и записи без использования VBA;

  • обращаться к локальным таблицам;

  • загружать данные во временные таблицы;

  • использовать запросы к серверу в качестве источников данных для отчетов или форм в режиме "только для чтения";

  • определить и использовать объект TableDef или QueryDef в 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 Driver

Ограничения драйвера 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 (SQLOLEDB)

Устарел, не используется

К началу страницы

Сводка ключевых слов 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-адрес или псевдоним Configuration Manager.

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-адрес или псевдоним Configuration Manager.

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.

×