Вспомните детство, когда алфавитный суп был вашим любимым блюдом. Хорошо, что у вас сохранились эти воспоминания — сейчас мы покажем вам, как приготовить своего рода алфавитный суп из базы данных. В следующих разделах изложены основы работы с базой данных: доступ с помощью строки подключения и через программный интерфейс базы данных на языке Access VBA.
В этой статье
Использование драйвера ODBC или поставщика OLE DB
Программное обращение к SQL Server из Access
Общие сведения о версиях драйвера 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 для обеспечения обратной совместимости. Дополнительные сведения см. в статье Компоненты доступа к данным Майкрософт или 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) предоставляет абстрактный интерфейс базы данных. Объекты доступа к данным (DAO) — это собственная объектная модель программирования, которая позволяет получить доступ и SQL Server для создания, удаления, изменения и перечисления объектов, таблиц, полей, индексов, связей, запросов, свойств и внешних баз данных.
Дополнительные сведения см. в статье Справочник по объектам Доступа к данным Майкрософт.
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 или 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 (версия 17) и Функции драйвера 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 | Скачать | SQL Server см. в OLE DB Driver for SQL Server заметках о функциях и выпусках драйвера Microsoft OLE DB. |
| 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 | Имя базы данных. Описание Назначение подключения. Имя драйвера, возвращенное SQLDrivers. |
| DSN | Имя существующего пользователя или системного источника данных ODBC. Encrypt Указывает, следует ли шифровать данные перед отправкой по сети (Да или Нет). |
| Failover_Partner | Имя сервера-партнера отработки отказа, который будет использоваться при сбое подключения к основному серверу. |
| FailoverPartnerSPN | Имя субъекта-службы для партнера отработки отказа. |
| Fallback | Устаревшее ключевое слово. |
| FileDSN | Имя существующего файлового источника данных ODBC. Язык Язык 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 распознает только часть этих ключевых слов.
| Ключевое слово | Описание |
|---|---|
| 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Управление связанными таблицами