Как подключить бд sql к access

Как связать Access с MySQL

В статье рассказывается как связать MS Access с базой данных MySQL, чтобы данные хранились не в файле mdb, а в таблицах MySQL.

1) Нужно поставить MySQL сервер, скачав по адресу mysql.com. Ставится легко, главное указать пароль к серверу и не забыть его.

2) Нужно установить ODBC driver, скачав по адресу mysql.com. Без него Access не сможет подключаться к MySQL.

Созданная база MySQL

3) Access умеет работать со связанными (linked) таблица, но они уже должны быть уже созданы! На рисунке видно что создана база данных (database) my_access с таблицей mytable1. В mytable1 созданы 2 поля id и FIO для примера. Создавать базу данных и таблицы можно любым доступным способом, я использовал MySQL Query Browser из mysql-gui-tools-5.0-r17-win32.msi. Этот этап называется проектированием базы данных и тут подробно расписывать не буду, это отдельная история. Смысл один! Нужно создать базу данных и нужные таблицы с нужными полями.

Связь с таблицами через ODBC

4) Создание связей. Запустите Access и в нем создайте базу данных и сохранитесь. Не создавайте таблиц и что либо. Откройте меню Файл — Внешние данные — Связь с таблицами. В окне связь укажите ODBC Databases () как на рисунке

MySQL ODBC Driver

5) В появившемся окне Выбор источника данных переключитесь на закладку Источник данных компьютера и нажмите кнопку Создать. Выберите MySQL ODBC Driver как на рисунке.

Настройки доступа к MySQL

6) В окне от MySQL заполните поля Data Source Name, укажите Server — в это поле нужно вписать адрес сервера в сети, если Access и MySQL находятся на одном компьютере, то можно вписать localhost (это адрес 127.0.0.1). Если MySQL находится на другом компьютере, то нужно указать адрес того компьютера, для этого понадобится помощь администратора сети и/или баз данных. Укажите User и Password, Имя и Пароль для доступа к базам данных, эти данные должны быть запомнены при установке MySQL или выданы администратором. Обязательно укажите к какой базе данных подсоединятся (на примере my_access созданная на этапе 3. Нажмите кнопку Test для проверки правильности подключения, ответ должен быть успешным Connection successful.

7) После указания параметров подключения вы вернетесь на этап Выбор источника данных. Выберите только что созданное соединение с MySQL (у меня это access2mysql), укажите таблицы для связи.

Access связан с MySQL

8) Можно работать со связанными таблицами как с обычными, только данные будут хранится не в файле mdb, а в базе данных MySQL. На рисунке показано, что введённые в таблицу mytable1 в Access, хранятся в MySQL и это показывает MySQL Query Browser

Дата последней правки: 2013-08-29 14:13:07

Подключение к SQL Server (AccessToSQL)

Чтобы перенести базы данных Access в SQL Server, необходимо подключиться к целевому экземпляру SQL Server. При подключении SSMA получает метаданные о базах данных в экземпляре SQL Server и отображает метаданные базы данных в обозревателе метаданных SQL Server. SSMA хранит сведения о том, к какой экземпляру SQL Server вы подключены, но не хранит пароли.

Подключение к SQL Server остается активным, пока не закройте проект. При повторном открытии проекта необходимо повторно подключиться к SQL Server, если требуется активное подключение к серверу. Вы можете работать в автономном режиме, пока не загружаете объекты базы данных в SQL Server и переносите данные.

Метаданные о экземпляре SQL Server не синхронизируются автоматически. Вместо этого, чтобы обновить метаданные в обозревателе метаданных SQL Server, необходимо вручную обновить метаданные SQL Server. Дополнительные сведения см. в разделе «Синхронизация метаданных SQL Server» далее в этом разделе.

Обязательные разрешения SQL Server

Учетная запись, используемая для подключения к SQL Server, требует разных разрешений в зависимости от действий, выполняемых учетной записью:

Чтобы преобразовать объекты Access в синтаксис Transact-SQL, обновить метаданные из SQL Server или сохранить преобразованный синтаксис в скрипты, учетная запись должна иметь разрешение на вход в экземпляр SQL Server.
Чтобы загрузить объекты базы данных в SQL Server, учетная запись должна быть членом роли базы данных db_ddladmin .
Чтобы перенести данные в SQL Server, учетная запись должна быть членом роли базы данных db_owner .

Установка подключения к SQL Server

Перед преобразованием объектов базы данных Access в синтаксис SQL Server необходимо установить подключение к экземпляру SQL Server, в котором требуется перенести базы данных Access.

При определении свойств подключения также указывается база данных, в которой будут перенесены объекты и данные. Это сопоставление можно настроить на уровне базы данных Access после подключения к SQL Server. Дополнительные сведения см. в разделе «Сопоставление источников» и «Целевые базы данных».

Перед подключением к SQL Server убедитесь, что экземпляр SQL Server запущен и может принимать подключения.

Чтобы подключиться к SQL Server, выполните приведенные действия.

В меню «Файл» выберите «Подключиться к SQL Server«. Если вы ранее подключились к SQL Server, имя команды будет повторно подключено к SQL Server.
В поле «Имя сервера» введите или выберите имя экземпляра SQL Server.
- При подключении к экземпляру по умолчанию на локальном компьютере можно ввести localhost или точку ( . ).
- Если вы подключаетесь к экземпляру по умолчанию на другом компьютере, введите имя компьютера.
- Если вы подключаетесь к именованным экземплярам, введите имя компьютера, обратную косую черту и имя экземпляра. Например: MyServer\MyInstance .
- Чтобы подключиться к активному экземпляру пользователя SQL Server Express, подключитесь с помощью протокола именованных каналов и укажите имя канала, например \\.\pipe\sql\query . Дополнительные сведения см. в документации по SQL Server Express.
Если экземпляр SQL Server настроен на прием подключений к порту, отличному от по умолчанию, введите номер порта, используемый для подключений SQL Server в поле порта Сервера. Для экземпляра SQL Server по умолчанию номер порта по умолчанию — 1433. Для именованных экземпляров SSMA попытается получить номер порта из службы браузера SQL Server.
В поле «База данных» введите имя целевой базы данных для переноса объектов и данных. Этот параметр недоступен при повторном подключении к SQL Server. Имя целевой базы данных не может содержать пробелы или специальные символы. Например, можно перенести базы данных Access в базу данных SQL Server с именем abc . Но нельзя перенести базы данных Access в базу данных SQL Server с именем a b-c . Вы можете настроить это сопоставление для каждой базы данных после подключения. Дополнительные сведения см. в разделе «Сопоставление источников и целевых баз данных»
В раскрывающемся меню проверки подлинности выберите тип проверки подлинности, используемый для подключения. Чтобы использовать текущую учетную запись Windows, выберите проверку подлинности Windows. Чтобы использовать имя входа SQL Server, выберите проверку подлинности SQL Server, а затем укажите имя пользователя и пароль.
Для безопасного подключения добавляются два элемента управления: флажок шифрования подключения и флажок TrustServerCertificate . Только если флажок «Шифрование подключения» установлен флажок TrustServerCertificate , отображается. Если флажок «Шифрование подключения » (true) и TrustServerCertificate не установлен (false), будет проверять SSL-сертификат SQL Server. Проверка сертификата сервера является частью SSL-подтверждения и гарантирует, что для подключения выбран правильный сервер. Чтобы убедиться, что сертификат должен быть установлен на стороне клиента, а также на стороне сервера.
Щелкните Подключить.

Хотя вы можете подключиться к более поздней версии SQL Server, по сравнению с версией, выбранной при создании проекта миграции, преобразование объектов базы данных определяется целевой версией проекта, а не версией SQL Server, к которому вы подключены.

Синхронизация метаданных SQL Server

Если после подключения схемы SQL Server изменяются, можно синхронизировать метаданные с сервером.

Чтобы синхронизировать метаданные SQL Server, обозреватель метаданных SQL Server, щелкните правой кнопкой мыши базы данных и выберите команду «Синхронизировать с базой данных«.

Повторное подключение к SQL Server

Процедура повторного подключения к SQL Server аналогична процедуре установления подключения.

Дальнейшие действия

Если вы хотите настроить сопоставление между исходными и целевыми базами данных, см. статью «Сопоставление исходных и целевых баз данных» В противном случае необходимо преобразовать объекты базы данных в синтаксис SQL Server с помощью преобразования объектов базы данных.

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

Вспомните детство, когда алфавитный суп был вашим любимым блюдом. Хорошо, что у вас сохранились эти воспоминания — сейчас мы покажем вам, как приготовить своего рода алфавитный суп из базы данных. В следующих разделах изложены основы работы с базой данных: доступ с помощью строки подключения и через программный интерфейс базы данных на языке Access VBA.

Компоненты доступа к данным

В этой статье

Использование драйвера 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.

Объект доступа к данным (data access object, DAO) предоставляет абстрактный интерфейс базы данных. Объекты доступа к данным Microsoft (DAO) — это встроенная модель программных объектов, которая позволяет проникнуть в суть Access и SQL Server для создания, удаления, изменения и перечисления объектов, таблиц, полей, индексов, связей, запросов, свойств и внешних баз данных.

Объекты данных ActiveX (ADO) предлагают высокоуровневую программную модель и доступны в Access по ссылке на стороннюю библиотеку. Модель ADO проста в изучении и позволяет клиентским приложениям получать данные из различных источников, в том числе Access и SQL Server, и управлять ими. Ее основные преимущества — удобство использования, высокая скорость, малое потребление памяти и места на диске. Кроме того, в 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-разрядные компоненты.

Драйверы ODBC

Новые возможности

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

Перевод БД в формате MS Access в формат MS SQL Server

1. Открываем базу данных в формате Access программой MS Access. Если используется MS Access версии 2007 и выше, то он может отключить часть содержимого. Его необходимо включить, нажав на кнопку «Параметры».