Глава 10. Работа Access с данными на SQL-сервере

Самоучитель Microsoft Access 2003
Бекаревич Ю.Б., Пушкина Н.В.

Microsoft SQL Server Язык структурированных запросов — SQL Работа Access с SQL Server через ODBC	Access предоставляет возможность использовать данные из различных внешних источников. Внешними источниками данных могут служить таблицы других баз данных Access, Microsoft FoxPro, dBase, Paradox и Microsoft SQL Server, таблицы и списки форматов HTML и НТХ, находящиеся на сервере в локальной, корпоративной сети или в Интернете, данные из других приложений, таких как Microsoft Excel, Microsoft Exchange. Настоящая глава посвящена технологии работы в сети с данными базы коллективного использования, размешенной на Microsoft SQL Server. Microsoft SQL Server В сетевых системах с файловым сервером при коллективном использовании базы данных Access более чем 15 пользователями и значительных размерах БД производительность становится недопустимо низкой. Это связано с увеличением объема передаваемых по сети данных, поскольку их обработка производится на компьютере пользователя. Например, если пользователю необходимо получить информацию об одном товаре, то на его компьютер должны быть переданы данные обо всех товарах (тысячи строк), из которых локальная СУБД выберет одну строку. Для построения более эффективной системы обработки общей базы данных целесообразно использовать архитектуру "клиент-сервер". Программное обеспечение архитектуры "клиент-сервер" состоит из двух частей: программного обеспечения сервера и программного обеспечения пользователя-клиента. Программа-клиент выполняется на компьютере пользователя и посылает запросы программе-серверу, которая работает на компьютере общего доступа. Основная обработка данных производится мощным сервером, а на компьютер пользователя возвращаются только результаты выполнения запроса. В такой архитектуре сервер называется сервером баз данных. Схема взаимодействия клиентских приложений Access с SQL-сервером баз данных приведена на рис. 10.1. Рис. 10.1. Схема взаимодействия приложений клиентов с SQL-сервером Сервер баз данных ориентирован на хранение и обработку больших объемов данных, на одновременную работу большого числа пользователей и обеспечивает при этом высокую производительность, надежность и защищенность. Доступ к данным и их изменение производятся по запросам пользователей, обработка которых осуществляется на сервере. Пользователю-клиенту, сделавшему запрос, возвращается только результат выполнения запроса. Широко известными СУБД, применяемыми в архитектуре "клиент-сервер", являются Microsoft SQL Server, Oracle, Sybase SQL Server и др. Эти СУБД являются реляционными SQL-серверами баз данных. СУБД с архитектурой "клиент-сервер" может включать собственную клиентскую программу. В то же время в качестве клиентов сервера баз данных могут использоваться другие СУБД. Access также может работать в качестве клиента SQL-сервера. Для взаимосвязи клиентов с сервером разработано специальное программное обеспечение. Широко используемыми интерфейсами таких взаимосвязей являются ODBC и OLE DB. Access предоставляет несколько способов взаимодействия приложения с данными сервера на основе интерфейса ODBC. Начиная с версии 2000, Access содержит средства создания клиентских приложений Microsoft SQL Server, которые позволяют не только использовать существующие на сервере базы данных, но и создавать новые, и взаимодействуют с ними на основе интерфейса OLE DB. Данные в базе Microsoft SQL Server организуются в логические компоненты, такие как таблицы, представления, сохраняемые процедуры. Физически база данных сервера хранится в нескольких файлах на диске. SQL Server может сохранять несколько баз данных. Среди них — четыре системные базы данных и одна или несколько баз данных пользователя. Можно иметь только одну базу данных, содержащую данные для всех пользователей, или разные базы данных для каждой группы пользователей. Например, организация может иметь одну базу данных для продаж, другую для платежей, третью для приложения, управляющего документами, и т.д. Приложение может использовать только одну базу данных или иметь доступ к различным базам данных. SQL Server способен обслужить тысячи пользователей, одновременно работающих в многочисленных базах данных на сервере. Все пользователи, которые подключаются к серверу, получают доступ к базам данных в соответствии с определенными правами. Централизованное хранение и управление данными, осуществляемое с помощью SQL Server, позволяет не загружать на компьютер каждого клиента отдельные копии данных. Это гарантирует работу всех пользователей с одними и теми же данными. SQL Server обеспечивает полную защиту при попытках корректировать одновременно одни и те же данные. Сервер эффективно распределяет оперативную и дисковую память среди многочисленных пользователей. SQL Server обеспечивает надежное обслуживание больших баз данных, имеет широкие возможности по администрированию, защите, разграничению доступа к данным. Microsoft SQL Server ориентирован на создание и ведение БД на уровне предприятия. Основное его назначение — работа с крупными корпоративными базами данных емкостью в сотни гигабайт и единицы терабайт. Для администрирования SQL Server используется универсальная консоль администратора, которая может обслуживать различные серверные продукть Microsoft. Из единой консоли администратора можно управлять не толш SQL Server, но и Internet Information Server (IIS), и всеми серверами органи зации. Основным режимом работы SQL Server является работа на мошны серверах под управлением Windows NT или 2000/2003 Server. В то же время SQL Server может устанавливаться и для работы под управлением Win dows NT Workstation", Windows 95/98 или Windows 2000/XP Professional. Microsoft SQL Server 2000 Desktop Engine Microsoft SQL Server 2000 Desktop Engine позволяет разрабатывать и тестировать проекты Access или приложения с архитектурой "клиент-сервер" на персональном компьютере или рабочей станции, не подключаясь к базе данных SQL Server на удаленном сервере. Изменив сведения о подключении проекта Access, можно выполнить окончательное тестирование и ввод его в эксплуатацию. Работа в этом режиме целесообразна в том случае, если базы данных сервера будут использоваться небольшой группой пользователей, но в дальнейшем может возникнуть необходимость в эксплуатации мощного сетевого сервера баз данных. Desktop Engine является минимальным из всех изданий Microsoft SQL Server 2000. Технология Microsoft SQL Server 2000 Desktop Engine обеспечивает локальное хранение данных в формате, совместимом с Microsoft SQL Server 2000, и, кроме того, может использоваться при удаленном хранении данных в качестве ядра обработки данных в архитектуре "клиент-сервер", альтернативного ядру базы данных Jet. для файлового сервера. Предыдущая версия Microsoft SQL Server 2000 Desktop Engine называлась Microsoft Data Engine (MSDE). Microsoft SQL Server 2000 Desktop Engine поставляется как часть Microsoft Office в папке \MSDE2000 компакт-диска (самоизвлекающаяся программа MSDE2KS3.EXE). Эта версия SQL-сервера может устанавливаться на компьютере с операционной системой начиная с Windows 98. При первой установке Microsoft SQL Server 2000 Desktop Engine после завершения выполнения программы самоизвлечения необходимо выполнить команду setup.exe с параметром. Это можно сделать в режиме эмуляции MS-DOS. Для перехода в режим эмуляции MS-DOS выберите в меню Пуск команду Выполнить, введите CMD и нажмите клавишу <Enter>. Затем введите следующие команды: CD \sql2ksp3\msde setup.exe SAPWD="AStrongSAPwd" По умолчанию команда setup.exe после самоизвлечения находится в папке C:\sql2ksp3\msde. При обновлении существующей версии команда setup.exe записывается в следующем виде: setup.exe /upgradesp setup\sqlrun01.msi SAPWD="AStrongSAPwd" В Microsoft SQL Server 2000 Desktop Engine существует ограничение на максимальный размер базы данных (2 Гбайт) и число одновременно обслуживаемых пользователей (до 6), хотя сервер сохраняет производительность и при значительно большем их числе, если компьютер, выполняющий функции сервера в одноранговой сети, обладает достаточными ресурсами (прежде всего памятью). Кроме встраиваемой Microsoft SQL Server 2000 Desktop Engine версии, на локальном компьютере может устанавливаться версия Microsoft SQL Server 2000 Personal Edition. Основным отличием этой версии от MSDE является то, что она включает полный набор инструментов управления: Microsoft Management Console (MMC), SQL Enterprise Manager и другие графические средства, упрощающие создание и управление базами данных; а также обеспечивающие максимальные возможности приложения Microsoft SQL Server Standard Edition. Microsoft SQL Server 2000 Desktop Engine является прекрасным инструментом для изучения средств управления базами данных архитектуры "клиент-сервер". При этом нет необходимости приобретать Windows NT или Windows 2000 и SQL Server, т. к. этот вариант сервера включен в Microsoft Office 2003, и не нужно заниматься настройкой параметров сети.
Microsoft SQL Server Язык структурированных запросов — SQL Работа Access с SQL Server через ODBC	Язык структурированных запросов — SQL Универсальным языком запросов к SQL-серверу различных приложений, в том числе Access, является язык структурированных запросов — SQL (Structured Query Language). В Access язык SQL является основным средством доступа к данным базы. Он используется для выборки, обновления, удаления, добавления данных. Любой запрос в Access реализуется с помощью языка SQL. Большинство запросов можно разработать, используя графические средства конструирования запросов, однако и в этом случае запрос генерируется и хранится в виде инструкции SQL. Для того чтобы увидеть инструкции SQL, реализующие запросы на обработку данных в базе, надо, находясь в окне конструктора запросов, выбрать пункт Вид|Режим SQL (View|SQL View). Каждый раз, когда вы создаете запрос в режиме конструктора, открывайте окно SQL и читайте соответствующую инструкцию SQL. Это позволит вам быстро ознакомиться с основными инструкциями SQL. Язык SQL является языком реляционных баз данных и наиболее распространенным языком управления базами данных в системах архитектуры "клиент-сервер". При взаимодействии приложений Access с SQL Server также используется этот язык. Следует отметить, что язык SQL имеет множество диалектов, порожденных ''различными разработчиками. Это затрудняет работу приложений, созданных в одних СУБД, с базами данных других СУБД, несмотря на то, что они также используют язык SQL при работе с данными. Последний утвержденный на сегодня стандарт SQL для реляционных баз данных, называемый SQL92 или ANSI SQL, является усовершенствованием ранее принятого Американским национальным институтом стандартов (ANSI — American National Standards Institute) и зарегистрированного Международной организацией стандартов (ISO — International Standards Organization) SQL86. Этот стандарт обобщает основные известные реализации языка. Причем ядро стандарта содержит функции, реализованные в большинстве коммерческих версий языка, а полный стандарт включает расширения, которые многими производителями еще не реализованы или реализованы на своем диалекте. Кроме того, некоторые программные продукты были выпущены еще до принятия стандарта и используют язык, синтаксис которого не соответствует стандарту. В связи с этим инструкции SQL, предназначенные для выполнения в одной базе данных, зачастую не могут быть выполнены в другой базе данных и требуют модификации. SQL относится к категории множественно-ориентированных языков, поэтому в него не входят ни средства управления выполнением программы, такие как организация ветвлений или циклов, ни средства организации интерфейса, такие как создание форм или распечатка отчетов. SQL-Server содержит расширенную версию SQL, которая иммет название Transact-SQL или TSQL. Transact-SQL обеспечивает полную поддержку команд стандартного языка SQL. Этот язык включает расширенные средства программирования, такие как дополнительные типы данных, команды кон­троля за выполнением, встроенные функции, средства обеспечения целостности данных, сохраненные процедуры и триггеры, выполняющиеся на сервере. В Access 2002 и более поздних версиях запросы можно создавать в одном из двух несовместимых режимов запроса ANSI SQL: ANSI-89, который описывает традиционный синтаксис Jet SQL или ANSI-92, который близок к спецификации ANSI-92 уровня 1; запросы ANSI-92 могут выполняться с ми­нимальными изменениями в базе данных Microsoft SQL Server. Если предполагается будущее развитие приложения до проекта Access и необходимо разработать запросы, которые будут выполняться в базе данных Microsoft SQL Server, или необходимо воспользоваться преимуществами но­вых средств, отсутствующих в ANSI-89 SQL, должен быть использован режим ANSI-92 SQL. Данный режим устанавливается на вкладке Таблицы и запросы в окне параметров, вызываемом командой Сервис|Параметры. Компании при разработке ANSI SQL-совместимых программ, имея право расширять язык SQL, должны поддерживать стандартный набор основных команд. Для взаимосвязи клиентов с сервером разрабатывается специальное программное обеспечение — интерфейсы. Группой производителей СУБД (SQL Access Group) разработан стандартный интерфейс языка, учитывающий особенности всех основных вариантов SQL. Этот интерфейс был формализован Microsoft и получил название открытого интерфейса подключения к базам данных (Open Database Connectivity - ODBC). Все программные продукты Microsoft могут получать через этот ин терфейс доступ к различным базам данных, использующим SQL. В СУБД других фирм также обеспечивается поддержка стандарта ODBC. Access позволяет использовать интерфейс ODBC для доступа к данным и различных внешних источников, в том числе баз данных SQL Server. Для обеспечения взаимодействия с данными SQL Server в Access, начиная с версии 2000, помимо технологий, ориентированных на ODBC, используете; технология OLE DB, которая обеспечивает универсальный доступ к данным. Для SQL Server технология OLE DB является базовой. OLE DB — это набор OLE-интерфейсов, предназначенных для доступа к данным самых разных источников. Целью создания OLE DB является увеличение скорости и упрощение доступа к данным, сохраняемым в различных форматах.
Microsoft SQL Server Язык структурированных запросов — SQL Работа Access с SQL Server через ODBC	Работа Access с SQL Server через ODBC В Access использование данных из базы Microsoft SQL Server, как и из других внешних источников, для которых имеется интерфейс ODBC, реализуется способами, рассмотренными ниже. 1. Запросы на языке SQL к базам данных SQL Server. С помощью запросов на SQL можно выполнить выборку данных из таблиц, сохраняемых на сервере, добавление, обновление данных и структур таблиц сервера. Запросы к серверу могут содержать инструкции для создания и удаления таблиц в базе данных и даже баз данных на сервере. Запросы передаются и выполняются непосредственно на сервере. Запросы целесообразно использовать для запуска сохраняемых процедур на сервере. 2. Связь с таблицами баз данных SQL Server. При этом таблицы сервера отображаются в окне базы данных Access для их просмотра и изменения средствами Access. Данные остаются в формате источника, но представлены как объекты Access, и могут быть обработаны с помощью инструментальных средств Access. 3. Импорт объектов SQL Server в текущую базу данных Access. При импорте объектов создаются их копии. Исходные объекты источника при этом не изменяются. В Access внешние данные можно использовать при создании запросов, форм, отчетов, причем можно объединить внешние данные с данными из объектов Access. При взаимодействии Access с сервером локальные таблицы базы данных могут быть экспортированы в общую базу данных сервера. Таким образом, можно, создав таблицу в Access, разместить ее в базе данных сервера. Для работы в Access с внешними данными на SQL-сервере необходимо под­ключиться к их источнику через интерфейс ODBC. Определение внешних источников данных в ODBC ODBC предназначен для соединения приложения, выполняющегося на рабочей станции, с сервером и выполнения процедур, связанных с получением и обработкой данных. Основными компонентами ODBC являются администратор и драйвер. Администратор ODBC устанавливает соединение с указанным пользователем внешним источником данных, поддерживает связь между приложением и сервером, обеспечивает приложение информацией и, по мере необходимости, загружает драйвер. Драйвер управляет взаимодействием приложения с сервером, переводит SQL-выражения приложения на диалект источника данных, перехватывает ошибки. Драйверы ODBC содержатся в динамически подключаемых библиотеках формата DLL. Если из приложения Access требуется получить доступ к базам данных SQL, необходимо, чтобы был установлен соответствующий драйвер ODBC. Ряд драйверов ODBC поставляется вместе с Access (в том числе драйвер Microsoft SQL Server), и они могут быть установлены уже при инсталляции Access. Дополнительные драйверы ODBC, поддерживающие другие форматы данных, могут быть приобретены у независимых поставщиков. Microsoft Access требует 32-разрядных драйверов ODBC, совместимых с протоколом ODBC уровня 1. Приобретаемые драйверы должны быть проверены в работе с Microsoft Access. Схема взаимодействия приложения Access с различными базами данных SQL представлена на рис. 10.2. Рис. 10.3. Схема взаимодействия Access с внешними базами данных Схема взаимодействия приложения Access при запросах к SQL-серверу, связях с его таблицами, импорте и экспорте в базы данных сервера представлена на рис. 10.3. Рис. 10.3. Схема взаимодействия Access с SQL-сервером Для того чтобы посмотреть, установлен ли нужный драйвер на вашем компьютере, выберите пункт Источники данных (ODBC) (ODBC Data Sources (32bit)) в списке программ панели управления. В Windows XP ссылка на пункт Источники данных (ODBC) размещена в категории Производительность и обслуживание|Администрирование. Напомним, что в Windows для открытия окна Панель управления надо щелкнуть на кнопке Пуск (Start) и выполнить соответствующую команду. В открывшемся окне Администратор источников данных ODBC (ODBC Data Source Administrator) на вкладке Драйверы (Drivers) будут представлены все установленные в системе ODBC-драйверы. Убедитесь, что на вашем компь­ютере установлен драйвер SQL Server. Для добавления и удаления драйверов ODBC используются программы настройки соответствующих драйверов. На этой вкладке операции выполняться не могут. Если драйверы ODBC установлены, то при выполнении операции с внешними данными становится доступным элемент Базы данных ODBC (ODBC Databases), который и позволяет перейти к процедуре подключения к внешнему источнику данных. Этот элемент отображается в списке Тип файла (Save as Type) при импорте в диалоговом окне Импорт (Import), а при связи с таблицей — в окне Связь (Link). Типы источников данных Для того чтобы получить доступ к базе данных SQL-сервера, необходимо определить источник данных. Источник данных ODBC — это термин, используемый для ссылки на внешнюю базу данных. Для каждой базы данных сервера, с которой работает Access, должен быть создан источник данных. К источникам данных обращаются по именам. Создать источники данных позволяет программа Администратор ODBC. Каждый драйвер ODBC предъявляет собственные требования к созданию и настройке источников данных. Администратор ODBC позволяет настраивать и использовать три типа источников данных. быть использован только одним пользователем, работающим на данном компьютере. системных служб на данном компьютере. которых установлены одинаковые драйверы, для получения доступа к базе данных. При определе­нии этого источника данных, в отличие от других, указывается файл, в котором сохраняется описание источника. Файл имеет расширение dsn. Этот файл должен быть доступен на всех компьютерах сети, пользователи которых будут использовать этот источник данных. Создание источника данных Рассмотрим создание пользовательского источника данных — Пользовательского DSN (User DSN). Процедура создания источников данных других типов аналогична описанной ниже. Активизируйте диалоговое окно Администратор источников данных ODBC (ODBC Data Source Administrator) и выберите в нем вкладку Пользовательский DSN (User DSN) (рис. 10.4). Первоначально в нем нет источников данных для SQL Server. Рис. 10.4. Окно Администратора ODBC при определении пользовательского источника данных Рис. 10.5. Выбор драйвера, для которого создается источник данных Для создания нового источника данных нажмите кнопку Добавить (Add), в окне Создание нового источника данных (Create New Data Source) (рис. 10.5) выберите драйвер SQL Server, для которого создается источник. После выбора драйвера и нажатия кнопки Готово к работе подключается мастер создания нового источника данных. Окно мастера Создание источника данных для SQL-сервера (Create a New Data Source to SQL Server) представлено на рис. 10.6. Рис. 10.6. Окно создания нового источника данных на SQL-сервере В этом окне нужно заполнить следующие поля: Name (Имя), определяющее имя нового источника данных, которое будет использоваться при подключении приложения к базе данных сервера; Description (Описание), содержащее комментарий, который поможет определить назначение базы данных, используемой в качестве источника данных; Server (Сервер), задающее имя SQL-сервера в сети, с которым нужно соединиться для получения доступа к базе данных. Если SQL-сервер установлен на том же компьютере, достаточно указать значение "local". В следующем диалоговом окне выбирается способ проверки прав пользователя на доступ к SQL-серверу при подключении к нему (рис. 10.7). К SQL-серверу можно подключаться, используя два режима регистрации: по доверительному соединению, при котором пользователь, успешно зарегистрировавшийся в сети Windows NT/2000/XP и представленный в списках пользователей сервера, не проверяется дополнительно. Сервер доверяет результатам этой проверки. Этот режим задается при выборе пункта With Windows NT authentication using the network login ID (Проверка подлинности учетной записи Windows NT). В дальнейшем производится только проверка на право выполнения тех или иных работ с объектами баз данных; путем проверки самим SQL-сервером идентификатора пользователя и пароля, передаваемых непосредственно серверу. Этот режим задается при выборе пункта With SQL Server authentication using a login ID and password entered by the user (Проверка с помощью имени и пароля). Результаты проверки, выполненной Windows, не учитываются. Идентификатор пользователя и пароль должны быть заранее определены на SQL-сервере администратором и обеспечивают первый уровень защиты данных от несанкционированного доступа. Рис. 10.7. Выбор способа идентификации пользователя при подключении к серверу Далее мастер позволяет ввести имя базы данных сервера, которая будет доступна по умолчанию при использовании создаваемого источника данных: (рис. 10.8). Рис. 10.8. Определение базы данных, к которой по умолчанию открывается доступ при подключении к серверу Рис. 10.9. Информация о конфигурации вновь созданного источника данных После установки в следующем окне ряда параметров мастер завершает создание нового источника данных выводом окна ODBC Microsoft SQL Server Setup (Установка ODBC для SQL-сервера Microsoft) с информацией о его параметрах (рис. 10.9). Нажав кнопку Test Data Source (Проверить источник данных), можно произвести тестирование нового источника данных. Результаты тестирования отобразятся в соответствующем окне (рис. 10.10). Рис. 10.10. Результат тестирования нового источника данных Выполнив рассмотренные шаги по созданию источника данных и возвратившись в окно Администратор источников данных ODBC (ODBC Data Source Administrator), на вкладке Пользовательский DSN (User DSN) в списке увидим строку с именем нового источника данных (рис. 10.11). На этом создание источника данных завершается. Теперь, указав лишь имя источника данных, можно подключиться к конкретному SQL-серверу и базе данных. Использование этого имени при работе с данными SQL-сервера из базы данных Access будет рассмотрено далее. Для просмотра или изменения параметров существующего источника данных выберите его имя в списке Источники данных пользователя (User Data Sources) (рис. 10.11) и нажмите кнопку Настройка (Configure). Выполните необходимые действия по изменению параметров источника в диалоговых окнах, которые в точности повторяют рассмотренные ранее (см. рис. 10.6—10.10). Рис. 10.11. Список с новым источником данных "Поставка" для драйвера SQL Server Для удаления источника данных выберите его имя в списке Источники данных пользователя (User Data Sources) и нажмите кнопку Удалить (Remove). В каждом диалоговом окне Администратора ODBC имеется кнопка Справка, нажав которую, можно получить дополнительные сведения по каждому параметру создаваемого или модифицируемого источника данных. Запросы на языке SQL к базам данных SQL-сервера Рассмотрим первый способ использования данных из базы Microsoft SQL Server — запросы SQL. Запросы к базе данных SQL-сервера записываются непосредственно на языке SQL и передаются на сервер для выполнения. В этих запросах используются таблицы, сохраняемые в базе данных на сервере. Эти таблицы не представлены в окне базы данных Access, поэтому создание запроса в режиме конструктора невозможно, т.к. нельзя добавить их в схему данных запроса. Запрос должен быть записан на языке SQL-сервера (для Microsoft SQL Server — Transact SQL), которому будет посылаться запрос. Запрос передается на сервер через ODBC, но не обрабатывается им. Создание запросов SQL к серверу Процесс создания запроса на языке SQL в Access начинается с выбора в окне базы данных вкладки Запросы (Queries), нажатия кнопки Создать (New) и выбора в списке режимов диалогового окна Новый запрос (New Query) строки Конструктор (Design View). В диалоговом окне запроса Добавление таблицы (Show Table) не следует выбирать таблицы, его нужно просто закрыть. Далее в меню Запрос (Query) следует выбрать команду Запрос SQL (SQL Specific) и подкоманду К серверу (Pass Through). В открывшемся окне запроса к серверу вводится инструкция SQL. Синтаксис инструкций запроса должен соответствовать диалекту языка, поддерживаемого сервером SQL, к которому будет посылаться запрос. Для уточнения синтаксиса целесообразно обратиться к справочной информации. Инструкции SQL для типичных запросов к базе данных можно просмотреть, открыв созданные средствами QBE запросы в режиме SQL (см. главу 4). Для этого нужно открыть запрос и, нажав кнопку Вид (View), выбрать пункт Режим SQL Определение источника данных для запроса SQL Для выполнения запроса SQL нужно на панели конструктора запросов нажать кнопку Запуск (Run). Появится окно Администратора ODBC Выбор источника данных (Select Data Source) (рис. 10.12). В этом окне можно создать новый или выбрать ранее созданный источник данных, содержащий сведения о сервере и базе данных, к которой делается запрос. Используя сведения из выбранного источника данных, Администратор выполняет подключение к базе данных. Если в выбранном источнике данных не указано доверительное соединение, у пользователя будут запрашиваться имя и пароль. Чтобы не выбирать источник данных при каждом выполнении запроса, пользователь может сохранить сведения о нем в свойстве запроса Строка подключения ODBC (ODBCConnectStr). Для этого нужно в режиме конструктора открыть окно свойств запроса, нажав на панели инструментов кнопку Свойства (Properties). В окне свойств запроса в поле Строка подключения ODBC (ODBCConnectStr) ввести имя источника данных; например, для ссылки на пользовательский источник данных "Поставка", созданный ранее, нужно ввести ODBC;DSN=Поставка;. Для ввода данных об источнике может быть использован построитель строк подключения ODBC. Построитель для выбора источника данных выводит окно Администратора ODBC Выбор источника данных (Select Data Source) (см. рис. 10.12). Кроме того, построитель предлагает включить в строку подключения пароль пользователя (рис. 10.13). Рис. 10.12. Выбор источника данных для запроса SQL к базе данных сервера Рис. 10.13. Диалоговое окно построителя строк подключения ODBC Например, при выборе на вкладке Источник данных компьютера (Machine Data Source) пользовательского источника данных Поставка и утвердительного ответа на вопрос о сохранении пароля построитель введет в строку следующую запись: 0DBC;DSN=Поставка;Description=Работа с БД Поставка товаров;UID=NVP; DATABASE^ Поставка товаров SQL;LANGUAGE=русский;Network=DBMSSOCN; Trusted_Connection=Yes Если в свойстве Строка подключения ODBC (ODBCConnectStr) не указан пароль пользователя, который известен серверу, то при выполнении запроса отображается диалоговое окно регистрации на SQL-сервере (рис. 10.14). Рис. 10.14. Окно регистрации пользователя на сервере По умолчанию значением свойства Строка подключения ODBC (ODBCConnectStr) является ODBC. Это значение выводится в окне свойств и восстанавливается при удалении текущего значения. Даже если в свойстве не указана строка подключения, Access использует ODBC. В этом случае при каждом запуске запроса на выполнение будет выводиться диалоговое окно Выбор источника данных (Select Data Source) со списком имен всех источников данных ODBC (см. рис. 10.12), в котором надо выбрать необходимый для подключения к базе источник данных. Выбранный таким образом источник данных не сохраняется в строке подключения. Для сохранения строки подключения ее необходимо указать в свойстве запроса Строка подключения ODBC (ODBCConnectStr). При сохранении запроса строка подключения сохраняется вместе с ним. Свойство Строка подключения ODBC (ODBCConnectStr) используется только в запросах к серверу. Построитель устанавливает связь с нужным сервером базы данных SQL и прерывает связь после создания строки подключения ODBC. Источник данных компьютера (Machine Data Source) доступен только пользователям одного компьютера. Однако, если на одном компьютере определено несколько учетных записей пользователя, источник доступен только пользователю, создавшему его. Каждый другой пользователь должен создавать свои источники. При работе нескольких пользователей с базой данных сервера целесообразно создавать Файловый источник данных (File Data Source), доступный с разных компьютеров сети. Примеры запросов SQL к серверу Рассмотрим примеры создания запросов SQL. Запрос на выборку данных из таблиц накладная и отгрузка, размещенных на SQL-сервере в базе данных "Поставка товаров". Запрос возвращает таблицу с полями, указанными в первой строке запроса, и строками с данными об отгрузках, которые были выполнены в период, заданный условием отбора. SELECT НАКЛАДНАЯ.*, ОТГРУЗКА.КОД_ТОВ, ОТГРУЗКА.КОЛ_ОТГР FROM НАКЛАДНАЯ INNER JOIN ОТГРУЗКА ON {НАКЛАДНАЯ.КОД_СК=ОТГРУЗКА.КОД_СК) AN D (НАКЛАДНАЯ.НОМ_НАКЛ=ОТГРУЗКА.НОМ_НАКЛ) WHERE ДАТА_ОТГР>='01.06.2004'; Запрос на обновление данных в таблице покупатель. В записи с полем наим_пок='перспектива' наименование покупателя меняется на значение "Компьютерный мир". UPDATE ПОКУПАТЕЛЬ SET НАИМ_ПОК='Компьютерный мир' WHERE НАИМ_ПОК='Перспектива'; Запрос на обновление данных в таблице отгрузка. В записи с составным КЛЮЧОМ НОМ_НАК='Н001', КОД_СК='С01' И КОД_ТОВ='Т005' ПОЛЮ КОЛ_ОТГР присваивается значение 500. UPDATE ОТГРУЗКА SET КОЛ_ОТГР =500 WHERE НОМ_НАКЛ='Н001' AND КОД_СК='С01' AND КОД_ТОВ='Т005'; Запрос на добавление записи в таблицу товар. Добавляется запись со значениями полей, указанными в третьей строке запроса. INSERT INTO ТОВАР (КОД_ТОВ,НАИМ_ТОВ,ЦЕНА,ЕЙ,СТАВКА_НДС) VALUES ('ТО17','Микрокомпьютер',200,'штука',0.1); Запрос на создание новой таблицы таЫе2 в базе данных сервера, в котором определяются ПОЛЯ Name_Pl И Name_P2. CREATE TABLE Таblе2 ([Name_Pl] [char] (3), [Name_P2] [char] (2)); Если запрос не принадлежит к типу запросов, в которых возвращаются записи, для свойства Возврат записей (Returns Records) нужно выбрать значение Нет. В противном случае после выполнения запроса выводится сообщение (рис. 10.15). Для запуска запроса со значением Нет в свойстве Возврат записей (Returns Records) используется только кнопка Запуск (Run) на панели инструментов. Для запроса, возвращающего записи, можно также нажать на панели инструментов кнопку Вид (View). Рис. 10.15. Сообщение при выполнении запроса на добавление записи Связь с таблицами баз данных SQL-сервера Другим способом использования в приложениях Access данных SQL-сервера является связь с таблицами его баз данных. Связанные таблицы, оставаясь на сервере в формате источника, отображаются в окне базы данных Access. Эти таблицы могут быть использованы наряду с локальными таблицами базы дан­ных Access при создании запросов, форм, отчетов привычными диалоговыми средствами Access. Связанные таблицы можно просмотреть и в режиме конструктора, однако никакие изменения структуры таблицы невозможны. Создание связи с таблицами сервера Для создания связи с внешними таблицами предназначена команда Файл| Внешние данные|Связь с таблицами (File|Get External Data|Link Tables) При выполнении этой команды открывается окно Связь (Link) (рис. 10.16), в котором для создания связи с таблицами из внешних баз данных нужно выбрать в поле Тип файлов строку База данных ODBC (ODBC Databases). Рис. 10.16. Окно выбора типа источника данных для связи с его таблицами В следующем окне (см. рис. 10.12) выбирается источник данных ODBC, обеспечивающий подключение к нужному серверу и базе данных. В окне регистрации пользователя на сервере, если необходимо (см. рис. 10.14), вводится пароль. После получения доступа к базе данных сервера в окне Связь с таблицами (рис. 10.17) выбираются таблицы, которые будут использоваться в работе как связанные. Рис. 10.17. Окно выбора связанных таблиц Процесс установления связи с таблицами сервера отображается в окне, представленном на рис. 10.18. Рис. 10.18. Окно процесса Установка связи Если таблица базы данных сервера не имеет ключа, выводится окно выбора однозначного индекса (рис. 10.19). Выберите поля таблицы для создания ключа связанной таблицы. Этот ключ используется при работе со связанной таблицей на компьютере клиента и не создается в таблице, сохраняемой на сервере. Если таблица, с которой устанавливается связь, не имеет ключа и при связывании не выполнен выбор однозначного индекса, то через связанную таблицу невозможно осуществить обновление данных в таблице или добавление записей и не обеспечивается проверка целостности данных. Рис. 10.19. Выбор ключа для связываемой таблицы После выбора таблиц и выполнения процедуры связывания выбранные таблицы сервера будут отображены в списке таблиц окна базы данных Access (рис. 10.20). Если удалить связанную таблицу, удаляется связь с ней, но сама внешняя таблица сохраняется на SQL-сервере. Рис. 10.20. Окно базы данных со списком таблиц, включающим связанные При создании запросов, форм, отчетов связанные таблицы могут использоваться наряду с таблицами локальной базы данных. В окне добавления таблиц в запрос связанные таблицы отображаются наряду с локальными таблицами. Связанные таблицы могут, также как локальные, отображаться в режиме таблицы и в режиме конструктора. Однако структуру связанной таблицы модифицировать нельзя. Включение связанных таблиц в схему БД и установление между ними связи выполняется так же, как для локальных таблиц (рис. 10.21). Рис. 10.21. Установление связи между связанными таблицами сервера Как видно на рисунке, для связанных таблиц не могут быть установлены параметры обеспечения целостности, эти параметры устанавливаются на сервере. Для разрыва связи с таблицей на сервере достаточно удалить ее, как удаляют любую другую таблицу локальной базы данных, воспользовавшись командой Правка|Удалить (Edit|Delete) в окне базы данных. При этом таблица на сервере удалена не будет. Сообщение о разрыве связи приведено на рис. 10.22. Рис. 10.22. Сообщение о разрыве связи с таблицей сервера Вся информация о связях с таблицами сервера сохраняется в базе данных и после ее закрытия. При открытии базы данных в следующий раз в ней отображаются связанные таблицы. Повторно проводить процедуру включения связанных таблиц не требуется. Очевидно, что работу со связанными таблицами можно выполнять только в том случае, если сервер доступен. Сведения об источниках данных для связанной таблицы сохраняются в общих свойствах таблицы в строке Описание. Свойства можно просмотреть в режиме конструктора таблицы. Например, свойство связанной таблицы dbo.покупатель может иметь следующее значение: ODBC;DSN=Поставка;Description=Работа с БД Поставка товаров;APP=Microsoft Office 2003;WSID=UBB-2003;DATABASE=Поставка товаров SQL;LANGUAGE=русский;Network=DBMSSOCN;Trusted_Connection=Yes;TABLE=dbo.ПОКУПАТЕЛЬ Все связанные таблицы и параметры их источников данных можно увидеть в окне Диспетчер связанных таблиц (Linked Tables Manager), которое открывается по одноименной команде, представленной в пункте Служебные программы (Database Utilites) меню Сервис (Tools). В этом окне можно обновить связь, если изменилось местоположение или другие параметры источника данных. На рис. 10.23 представлено окно диспетчера связанных таблиц, в котором отображены связанные таблицы текущей базы данных. В данном примере первые семь являются связанными таблицами SQL-сервера и для каждой из них указаны параметры связи с источником данных через ODBC. Кроме того, на рисунке отображена связанная таблица Лист1, являющаяся документом Excel, и ее местоположение на локальном диске. Если изменилось местоположение или другие параметры источника данных, связь можно обновить. Для обновления связи нужно установить флажок в строке таблицы, связь с которой должна быть обновлена. При этом строка выделяется и становится доступной кнопка ОК, по которой инициируется процесс обновления. Рис. 10.23. Список связанных таблиц текущей базы данных Примеры запросов со связанными таблицами Рассмотрим примеры запросов со связанными таблицами. Запрос на выборку из локальных таблиц и связанных таблиц SQL-сервера. На рис. 10.24, 10.25 показан запрос, использующий данные о покупателях и договорах, которые хранятся в общей базе данных на сервере в таблицах dbo_ПОКУПАТЕЛЬ и dbo _ДОГОВОР, а также данные о накладных, сохраняемых на складе в локальной базе данных Access в таблице Накладные склада. Добавление записей из локальной таблицы в связанную таблицу базы данных SQL-сервера. Например, сведения об отгрузке товаров со склада хранятся В локальной базе данных склада В таблицах Накладные склада и Отгрузка склада. В общей базе данных на SQL-сервере имеются таблицы накладная и отгрузка, в которых собираются сведения со всех складов. Рис. 10.24. Запрос с таблицами сервера и локальной таблицей в режиме конструктора Рис. 10.25. Результат выполнения запроса для покупателя "Компьютер маркет" Для решения этой задачи установим из локальной базы данных связь с таблицами базы данных сервера. В результате установления связи таблицы сервера будут представлены в окне базы данных Access (см. рис. 10.20), например, под именами dbo_НАКЛАДНАЯ и dbo__ОТГРУЗКА. Для добавления записей из каждой локальной таблицы в соответствующую связанную таблицу базы данных SQL-сервера создадим отдельный запрос на добавление записей. Напомним, что запрос на добавление создается в режиме конструктора как запрос на выборку, который формирует необходимые для добавления записи. Далее запрос на выборку преобразуется в запрос Добавление (Append Query) (рис. 10.26). При этом в диалоговом окне (рис. 10.27) выбирается таблица, в которую нужно добавить записи. В запросе на добавление для создания записей использована локальная таблица накладные склада, а в качестве обновляемой выбрана связанная таблица dbo_НАКЛАДНАЯ. В результате выполнения запроса таблица на сервере будет дополнена записями из локальной таблицы. Рис. 10.26. Окно конструктора с запросом на добавление Рис. 10.27. Окно выбора таблицы, в которую добавляются записи С помощью аналогичного запроса выполняется добавление записей в подчиненную таблицу dbo_0ТГРУЗКА из таблицы Отгрузка склада. Импорт объектов из базы данных SQL-сервера в Access Запросы на языке SQL к серверу и работа со связанными таблицами сервера возможны только при установленной связи с сервером. При импорте объекты сервера копируются в базу данных Access и сохраняются в ней как собственные. Связь с сервером необходима только на время выполнения операции импорта. При использовании импортированных объектов связь с сервером не нужна. Для импорта данных из базы данных сервера используется команда Файл|Внешние данные|Импорт (File|Get External Data|Import). В окне Импорт (Import) в поле Тип файлов (Files of type) выбирается значение Базы данных ODBC (ODBC Databases). Далее выбирается источник данных, определяющий сервер и базу данных, из которой должны импортироваться объекты, производится регистрация на сервере и в окне Импорт объектов (Import Objects) выбирается любое количество нужных объектов. После выполнения этих действий полученные объекты отображаются в базе данных Access. Экспорт объектов Access в базу данных SQL-сервера Одним из способов создания таблиц в базе данных SQL-сервера является экспорт таблиц из локальной базы данных Access. Для экспорта объектов из базы данных Access в базу данных ODBC используется команда Файл|Экспорт (File|Export). Объект должен быть выбран, иначе команда недоступна. В окне Экспорт (Export) в поле Имя файла (File name) отобразится имя экспортируемого объекта. Для экспорта в базу данных через интерфейс ODBC нужно в поле Тип файла (Save as type) выбрать значение Базы данных ODBC (ODBC Databases). Например, если выбрать эти параметры для экспорта таблицы Накладные склада и нажать кнопку Сохранить (Save), отобразится окно, представленное на рис. 10.28. Рис. 10.28. Сообщение об экспорте таблицы во внешнюю базу данных Следующим отобразится окно для выбора источника данных ODBC, определяющего местоположение и имя внешней базы данных, в которую необходимо экспортировать объект. После принятия выбранного источника предлагается зарегистрироваться на сервере, проверяется идентификатор, пароль и права пользователя на проведение операции. Если пользователь имеет права на проведение операции, объект записывается в базу данных сервера. Связь с сервером после выполнения операции экспорта прерывается. Следует заметить, что полного соответствия сохраненной на сервере в результате экспорта таблицы и таблицы-источника нет. Так, например экспортированная таблица не сохраняет определение ключа.