Порядок организации связи баз данных СУБД Access с базами данных в СУБД SQL Server

26. Порядок организации связи баз данных СУБД Access с базами данных в СУБД SQL Server.

Access предоставляет возможность использовать данные из различных внеш­них источников.

В сетевых системах с файловым сервером при коллективном использовании базы данных Access более чем 15 пользователями и значительных размерах БД производительность становится недопустимо низкой. Это связано с уве­личением объема передаваемых по сети данных, поскольку их обработка производится на компьютере пользователя. Например, если пользователю необходимо получить информацию об одном студенте, то на его компьютер должны быть переданы данные обо всех студентах (тысячи строк), из кото­рых локальная СУБД выберет одну строку. Для построения более эффектив­ной системы обработки общей базы данных целесообразно использовать ар­хитектуру "клиент-сервер".

Программное обеспечение архитектуры "клиент-сервер" состоит из двух час­тей: программного обеспечения сервера и программного обеспечения поль­зователя-клиента.

Программа-клиент выполняется на компьютере пользователя и посылает запросы к программе-серверу, которая работает на компью­тере общего доступа. Основная обработка данных производится мощным сервером, а на компьютер пользователя возвращаются только результаты вы­полнения запроса. В такой архитектуре сервер называется сервером баз дан­ных. Схема взаимодействия клиентских приложений Access с SQL-сервером баз данных приведена на рис. 8.1.

Рис. 8.1. Схема взаимодействия приложений клиентов с SQL-сервером

Сервер баз данных ориентирован на хранение и обработку больших объемов данных, на одновременную работу большого числа пользователей и обеспе­чивает при этом высокую производительность, надежность и защищенность. Доступ и изменение данных производится по запросам пользователей, обра­ботка которых осуществляется на сервере. Пользователю-клиенту, сделав­шему запрос, возвращается только результат выполнения запроса.

Access позволяет использовать интерфейс ODBC для доступа к различным базам данных, в том числе базам данных SQL Server.

Для обеспечения взаимодействия с данными SQL Server в Access, начиная с версии 2000, помимо технологий, ориентированных, на ODBC, используется технология OLE DB, которая обеспечивает универсальный доступ к данным. Для SQL Server технология OLE DB является базовой, OLE DB — это набор OLE-интерфейсов, предназначенных для доступа к данным самых разных источников. Целью создания OLE DB является увеличение скорости и упро­щение доступа к данным, сохраняемым в различных форматах.

Работа Access с SQL Server через ODBC

В Access для использования данных из базы Microsoft SQL Server, как и из других внешних источников, для которых имеется интерфейс ODBC, могут быть применены различные способы.

- Запросы на языке SQL к базам данных SQL Server. С помощью запросов на SQL можно выполнить выборку данных из таблиц, сохраняемых на сервере, добавление, обновление данных и структур таблиц сервера. За­просы к серверу могут содержать инструкции для создания и удаления таблиц в базе данных и даже базе данных на сервере. Запросы передаются и выполняются непосредственно на сервере. Запросы целесообразно использовать для запуска сохраняемых процедур на сервере.

- Связь с таблицами баз данных SQL Server. При этом таблицы сервера отображаются в окне базы данных Access для их просмотра и изменения средствами Access. Данные остаются в формате источника, но представ­лены как объекты Access и могут быть обработаны с помощью инстру­ментальных средств Access.

- Импорт объектов SQL Server в текущую базу данных Access. При им­порте объектов создаются их копии. Исходные объекты источника при этом не изменяются. В Access внешние данные можно использовать при создании запросов, форм, отчетов, причем можно объединить внешние данные с данными из объектов Access.

При взаимодействии Access с сервером локальные таблицы базы данных мо­гут быть экспортированы в общую базу данных сервера. Таким образом, можно, создав таблицу в Access, разместить ее в базе данных сервера.

Для работы в Access с данными на SQL-сервере необходимо подключиться к их источнику через интерфейс ODBC.

Определение внешних источников данных в ODBC

ODBC предназначен для соединения приложения, выполняющегося на рабо­чей станции, с сервером и выполнения процедур, связанных с получением и обработкой данных. Основными компонентами ODBC являются администра­тор и драйвер. Администратор ODBC устанавливает соединение с указанным пользователем внешним источником данных, поддерживает связь между приложением и сервером, обеспечивает приложение информацией и по мере необходимости загружает драйвер. Драйвер управляет взаимодействием при­ложения с сервером, переводит SQL-выражения приложения на диалект ис­точника Данных, перехватывает ошибки. Драйверы ODBC содержатся в ди­намически подключаемых библиотеках — DLL.

Если требуется из приложения Access получить доступ к базам данных SQL, необходимо, чтобы был установлен соответствующий драйвер ODBC. Ряд драйверов ODBC поставляется вместе с Access (в том числе драйвер Micro­soft SQL Server) и они устанавливаются уже при инсталляции Access. Допол­нительные драйверы ODBC, поддерживающие другие форматы данных, мо­гут быть приобретены у независимых поставщиков. Microsoft Access требует 32-разрядных драйверов ODBC, совместимых с протоколом ODBC уровня 1.

Приобретаемые драйверы должны быть проверены в работе с Microsoft Access. Схема взаимодействия приложения Access с различными базами SQL представлена на рис. 8.2.

Рис. 8.2.

Для того чтобы посмотреть, установлен ли нужный драйвер на вашем ком­пьютере, выберите Источники данных (ODBC) (ODBC Data Sources (32bit)) в списке программ Панели управления. В Windows XP ссылка на Источники данных (ODBC) размещена в категории Производительность и обслужи­вание

В открывшемся окне Администратор источников данных ODBC (ODBC Data Source Administrator) на вкладке Драйверы (Drivers) будут представле­ны все установленные в системе ODBC-драйверы. Убедитесь, что на компьютере установлен драйвер SQL Server. Для добавления и удаления драйверов ODBC используются программы настройки соответствующих драйверов. На этой вкладке операции выполняться не могут.

Рис. 8.3. Схема взаимодействия Access с SQL Server через ODBC

Если драйверы ODBC установлены, то на вкладке ленты Внешние данные (External Data) в группах Импорт (Import) и Экспорт (Export) в списке кноп­ки Дополнительно (More) становится доступным элемент Базы данных ODBC (ODBC Database), который позволяет перейти к процедуре подключе­ния к внешнему источнику данных.

Типы источников данных

Для того чтобы получить доступ к базе данных SQL-сервера через интерфейс ODBC, необходимо определять источник данных. Источник данных ODBC — это термин, используемый для ссылки на внешнюю базу данных. Для каждой базы данных сервера, с которой работает Access, должен быть создан источник данных. К источникам данных обращаются по именам. Соз­дать источники данных позволяет программа Администратор источников данных ODBC. Каждый драйвер ODBC предъявляет собственные требования к созданию и настройке источников данных.

Администратор ODBC позволяет настраивать и использовать три типа ис­точников данных.

- Пользовательский DSN (User DSN). Источник данных этого типа может быть использован только одним пользователем, работающем на данном компьютере. Пользовательский источник данных относится только к учетной записи Microsoft Windows, активной в момент создания. В любой другой учетной записи он не отображается. Для приложений, выполняю­щихся на компьютере в качестве служб, он также не всегда видим.

- Системный DSN (System DSN) предназначен для всех пользователей и системных служб на данном компьютере. Системные источники данных отображаются во всех учетных записях клиентского компьютера.

- Файловый DSN (File DSN) может совместно использоваться многими пользователями разных компьютеров сети, на которых установлены оди­наковые драйверы, для получения доступа к базе данных. При определе­нии этого источника данных в отличие от других указывается файл, в ко­тором сохраняется описание источника. Файл имеет расширение dsn. Этот файл должен быть доступен на всех компьютерах сети, пользовате­ли которых могут его использовать. Сведения о других источниках дан­ных хранятся в системном реестре.

Создание источника данных

Рассмотрим создание пользовательского источника данных— Пользова­тельского DSN (User DSN). Процедура создания источников данных других типов практически не имеет отличий. Активизируйте диалоговое окно Ад­министратор источников данных ODBC (ODBC Data Source Administrator) и выберите в нем вкладку Пользовательский DSN (User DSN) (рис. 8.4). Первоначально в нем нет источников данных для SQL Server.

Для создания нового источника данных нажмите кнопку Добавить (Add), в окне Создание нового источника данных (Create New Data Source) (рис. 8.5) выберите драйвер SQL Server, для которого создается источник.

После выбора драйвера и нажатия кнопки Готово (Finish) к работе подклю­чается мастер создания нового источника данных. Окно мастера Создание источника данных для SQL-сервера (Create a New Data Source to SQL Server) представлено на рис. 8.6.

Рис. 8.6. Окно создания нового источника данных на SQL-сервере

В этом окне:

□  Имя (Name) определяет имя нового источника данных, которое будет
использоваться при подключении приложения к базе данных сервера —
Поставка;

□  Описание (Description) содержит комментарий, который поможет определить назначение базы данных, используемой в качестве источника
данных;

□  Сервер (Server) определяет имя SQL Server в сети, с которым нужно со­единиться для получения доступа к базе данных. Если SQL Server уста­новлен на том же компьютере, достаточно указать (local).

В следующем диалоговом окне выбирается способ проверки прав пользова­теля на доступ к SQL-серверу при подключении к нему (рис. 8.7).

К SQL Server можно подключаться, используя два режима регистрации:

- по доверительному соединению, при котором пользователь, успешно за­регистрировавшийся в сети Windows Server и представленный в списках пользователей сервера, не проверяется дополнительно, Сервер доверяет результатам этой проверки. Этот режим задается при выборе переключа­теля проверка подлинности учетной записи Windows NT (With Windows NT authentication using the network login ID). В дальнейшем про­изводится только проверка на право выполнения тех или иных работ с объектами баз данных;

- путем проверки самим SQL Server идентификатора пользователя и паро­ля, передаваемых непосредственно серверу. Этот режим задается при вы­боре переключателя проверки подлинности учетной записи SQL Server (With SQL Server authentication using a login ID and password en­tered by the user). Результаты проверки, выполненной Windows, не учиты­ваются. Пользователь должен быть известен серверу под этим именем.

Рис. 8.7. Выбор способа идентификации пользователя при подключении к серверу I

Идентификатор пользователя и пароль должны быть заранее определены на SQL-сервере администратором и обеспечивают первый уровень защиты дан­ных от несанкционированного доступа.

Далее мастер позволяет ввести имя базы данных сервера, к которой будет по умолчанию осуществляться доступ при использовании создаваемого источ­ника данных (рис. 8.8).

Рис. 8.8. Определение базы данных, к которой по умолчанию открывается доступ при подключении к серверу

После установки в следующем окне еще ряда параметров мастер завершает создание нового источника данных выводом окна Установка ODBC для SQL-сервера Microsoft (ODBC Microsoft SQL Server Setup) с информацией о его параметрах (рис. 8.9).

Нажав кнопку Проверить источник данных (Test Data Source), можно про­извести тестирование нового источника данных. Результаты тестирования отобразятся в соответствующем окне (рис. 8.10).

Выполнив рассмотренные шаги по созданию источника данных и возвратив­шись в окно Администратор источников данных ODBC (ODBC Data Source Administrator), на вкладке Пользовательский DSN (User DSN) в спи­ске увидим строку с именем нового источника данных (рис. 8.11).

На этом создание источника данных завершается. Теперь, указав лишь имя источника данных, можно подключиться к конкретному SQL-серверу и базе данных. Использование этого имени при работе с данными SQL Server из ба­зы данных Access будет рассмотрено далее.

Рис. 8.9. Информация о конфигурации вновь созданного источника данных

Рис. 8.10. Результат тестирования нового источника данных

Связь с таблицами баз данных SQL Server

Другим способом использования в приложениях Access данных из баз дан­ных SQL Server через ODBC является связь с таблицами. Связанные таблицы, оставаясь на сервере в формате источника, отображаются в окне базы данных Access. Эти таблицы могут быть использованы наряду с локальными таблицами базы данных Access при создании запросов, форм, отчетов привычными диалоговыми средствами Access. Связанные таблицы можно просмотреть и в режиме конструктора, однако никакие изменения структуры таблиц невоз­можны.

Создание связи с таблицами сервера

Для создания связи с внешними таблицами предназначена команда База данных ODBC (ODBC Database), размещенная на вкладке ленты Внешние данные (External Data) в группе Импорт (Import) в списке кнопки Дополни­тельно (More).

Рис. 8.16. Окно выбора способа использования данных источника

При выполнении этой команды открывается окно Внешние данные - База данных ODBC (Get External Data - ODBC Database) (рис. 8.16), в котором для создания связи с таблицами из внешних баз данных нужно выбрать переключатель Создать снизанную таблицу для связи с ис­точником данных (Link to the data source by creating a linked table).

Затем в окне (см. рис. 8.12) выбирается источник данных ODBC, обеспечи­вающий подключение к нужному серверу и базе данных. Получив доступ к базе данных сервера, в окне Связь с таблицами (рис. 8.17) выбираются таб­лицы, которые будут использоваться в работе как связанные.

Рис.8.17

Импорт объектов из базы данных SQL-сервера в Access

Запросы на языке SQL к серверу и работа со связанными таблицами сервера возможна только при установленной связи с сервером. При импорте объекты сервера копируются в базу данных Access и сохраняются в ней как собствен­ные, Связь с сервером необходима только на время выполнения операции импорта. При использовании импортированных объектов связь с сервером не нужна.

Для импорта данных из базы данных сервера используется команда База данных ODBC (ODBC Database), размещенная на вкладке ленты Внешние данные (External Data) в группе Импорт (Import) в списке кнопки Дополни­тельно (More).

В окне Внешние данные - База данных ODBC (Get External Data - ODBC Database) (см. рис. 8.16) выбирается переключатель Импорти­ровать данные источника в новую таблицу в текущей базе данных (Im­port the source data into a new table in the current database). Далее выбирается источник данных, определяющий сервер и базу данных, из которой должны импортироваться объекты, если необходимо, производится регистрация на сервере и в окне Импорт объектов (Import Objects) выбирается любое коли­чество нужных объектов. После завершения процедуры полученные объекты отображаются в базе данных Access.

Экспорт объектов Access в базу данных SQL-сервера

Одним из способов создания таблиц в базе данных SQL-сервера является экспорт таблиц из локальной базы данных Access. Для экспорта объектов из базы данных Access в базу данных ODBC применяется команда База данных ODBC (ODBC Database), размещенная на вкладке ленты Внешние данные (External Data) в группе Экспорт (Export) в списке кнопки Дополнительно (More). Объект должен быть предварительно выбран, иначе команда недос­тупна. В окне Экспорт (Export) отобразится имя экспортируемого объекта и задается имя, под которым объект сохранится в базе данных получателя, в нашем примере базе данных SQL-сервера.

Рис. 8.28. Сообщение об экспорте таблицы во внешнюю базу данных

Следующим отобразится окно для выбора источника данных ODBC, опреде­ляющего местоположение и имя внешней базы данных, в которую необходи­мо экспортировать объект. После принятия выбранного источника, если в источнике данных указан способ подключения с регистрацией на сервере SQL, предлагается зарегистрироваться, проверяется идентификатор, пароль и права пользователя на проведение операции. Если пользователь имеет права на проведение операции, объект записывается в базу данных сервера. Связь с сервером после выполнения операции экспорта прерывается.