Базы данных и ADO Объект ADO.Connection в VBA

          Назад



9.3 Объект Connection и коллекция Errors


Объект ADO.Connection в VBA, свойство ConnectionString, генерация строки подключения, открытие и закрытие соединения с базой данных, объект ADOError и коллекция Errors

Создание объекта Connection производится очень просто. Например, чтобы подключиться к базе данных Northwind на сервере SQL Server с именем LONDON, можно использовать код вида

Dim cn As New ADODB.Connection

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _

& "Initial Catalog=Northwind;Data Source=LONDON"

cn.Open

В принципе, этого вполне достаточно, чтобы подключиться к базе данных и создать объект соединения, который можно будет потом использовать. Однако у большинства пользователей возникает вопрос: а что такое написано в свойстве ConnectionString и как значение этого свойства можно написать самому?

Самый простой вариант — вообще ничего самому не писать. Значение для этого свойства можно сгенерировать в автоматическом режиме. Выглядит это очень просто:

Создаем любой пустой файл (например, текстовый). Для этого нужно просто щелкнуть правой кнопкой мыши по пустому месту в окне проводника Windows и в контекстном меню выбрать New (Новый) -> Text Document (Текстовый документ) — см. рис. 9.2

Рис. 9.2 Создание пустого текстового файла в Windows Explorer

Переименовываем этот файл так, чтобы у него было расширение UDL (от User Data Link). После переименования убедитесь, что иконка для него изменилась (см. рис. 9.3).

Рис. 9.3 Теперь у нас есть пустой файл 1.udl

Если она осталась такой же, как была для текстового документа, это значит, что реальное расширение для этого файла — .txt, а не .udl. В этом случае нужно в окне Windows Explorer в меню Tools (Сервис) выбрать FolderOptions (Параметры папок), перейти на вкладку View (Просмотр) и снять флажок Hide file extensionsfor known file types (Скрывать расширения для известных типов файлов). После того, как вы выполните эту операцию, нужно будет переименовать ваш файл, чтобы у него было расширение *.udl.

После того, как файл UDL будет создан, просто щелкните по нему два раза мышью. Откроется окно с четырьмя вкладками.

На первой вкладке Provider выберите нужный тип базы данных (например, Microsoft OLE DB Provider for SQL Server). Для подключения к базе данных Oracle выберите Microsoft OLE DB Provider for Oracle. Для подключения к базе данных Access нужно выбрать Microsoft JET 4.0 OLE DB Provider. Про подключение к листу Excel, как к базе данных, мы поговорим отдельно.

Далее нужно перейти на вкладку Connection. Для каждого типа базы данных эта вкладка выглядит по своему. Например, для SQL Server она выглядит так, как представлено на рис. 9.4, а для Access — так, как на рис. 9.5.

Рис. 9.4 Окно свойств соединения для подключения к SQL Server

Рис. 9.5 Окно свойств соединения для подключения к базе данных Access

Если вы не знаете, какие параметры вам нужно вводить в этом окне, их придется спросить у вашего администратора базы данных. После того, как все параметры введены, рекомендуется нажать на кнопку Test Connection, чтобы проверить возможность подключения к базе данных. После этого нужно нажать на кнопку OK, чтобы закрыть окно свойств соединения.

Последнее действие, которое нам потребуется сделать — щелкнуть правой кнопкой мыши по созданному файлу, в контекстном меню выбрать Open With (Открыть с помощью) -> Choose Program (Выбрать программу) и в появившемся списке выбрать Notepad (Блокнот) и нажать на кнопку OK. Созданный вами файл откроется в блокноте. В меню Format в блокноте снимите флажок Word wrap (Перенос по строкам) и скопируйте в буфер обмена последнюю строку этого файла (см. рис. 9. 6).

Рис. 9.6 Копируем сгенерированную строку подключения

Внимание!

Если этой строке у вас есть русские буквы (например, русское имя каталога в пути к файлу Excel), то рекомендуется производить копирование при включенной русской раскладке клавиатуры — иначе русские символы будут заменены на вопросительные знаки.

Осталось вставить скопированное значение в окно редактора кода как значение свойства ConnectionString и взять его в кавычки.

Конечно, вы вполне можете написать connection string (по русски — строка подключения) и вручную. Это просто набор параметров вида "свойство=значение", разделенных точкой с запятой (для значения в строке подключения кавычки не используются). Что означает каждый из параметров:

  • Provider — драйвер для подключения к источнику данных. Для каждого типа источника данных (SQL Server, Access, Oracle) он свой. Мы с вами использовали драйвер OLE DB. Альтернатива ему — применение драйверов ODBC. Они работают медленнее и в основном используются для обеспечения обратной совместимости, но иногда без них не обойтись (например, тогда, когда подходящего драйвера OLE DB просто нет). Мы будем использовать подключение по ODBC к таблице на листе Excel.

В принципе, в строке подключения значение для Provider можно не указывать вообще. Но тогда придется определить его как значение отдельного свойства объекта Connection.

  • IntegratedSecurity — в данном случае это свойство используется, поскольку мы используем для подключения к SQL Server аутентификацию Windows. Если бы мы использовали аутентификацию SQL Server, то этого свойство нам бы было не нужно, но вместо него нам потребовалось бы использовать два других свойства: User ID — идентификатор пользователя и Password — пароль. Если вы далеки от мира баз данных, просто узнайте необходимые значения у вашего администратора.
  • DataSource — имя источника данных. В нашем случае это — имя компьютера, на котором работает SQL Server. В других случаях оно могло бы быть именем экземпляра Oracle, или файла базы данных Microsoft Access — все зависит от того, к какой базе данных вы подключаетесь.
  • InitialСatalog — имя базы данных на этом сервере. В нашем случае — Northwind.

Когда вы генерируете строку подключения автоматически при помощи файла UDL, в первый раз это может показаться долгим. На самом же деле это занимает не более минуты. Кроме того, при этом вы гарантируете, что в строке подключения не будет ошибок, и у вас есть возможность проверить подключение к базе данных прямо из свойств файла UDL (кнопка Test Connection).

Фактически все, что нужно для открытия соединения с базой данных, мы сделали: создали объект Connection, настроили для него свойство ConnectionString и вызвали метод Open(). Однако для справки (поскольку не все знают английский) приведем информацию о свойствах и методах этого объекта.

  • Свойство Provider позволяет определить драйвер, который будет использован для подключения к базе данных. Мы с вами определили такой драйвер внутри значения ConnectionString, но можно для этой цели использовать и отдельное свойство. Значения свойств Provider для подключения к разным источникам данных могут выглядеть так:
    • "Microsoft.Jet.OLEDB.4.0" — для подключений к файлам Access и Excel и другим источникам данных на основе Jet;
    • " SQLOLEDB" — для подключений к SQL Server (как в примере)
    • "MSDAORA.1" — для подключений к серверу Oracle;
    • " ADsDSOObject" — для подключения к базе данных службы каталогов Windows.
  • Свойство ConnectionString — главное свойство объекта Connection. Оно определяет параметры подключения к источнику. Как именно работать с ним, мы рассмотрели выше.
  • Метод Open() позволяет открыть соединение с базой данных. Строку подключения можно не настраивать отдельно как свойство объекта Connection, а просто передавать его этому методу как параметр.
  • метод Close() позволяет закрыть соединение. Учтите, что объект соединения при этом из памяти не удаляется. Чтобы полностью избавиться от этого объекта, можно использовать код

cn.Close

Set cn = Nothing

или просто Set cn = Nothing — разрыв соединения произойдет автоматически.

Для этого объекта предусмотрено множество других свойств и методов, однако здесь они рассматриваться не будут (за дополнительной информацией можно обратиться к документации или учебным курсам Microsoft). Единственное свойство, которое обязательно необходимо рассмотреть — это свойство Errors, которое возвращает коллекцию объектов Error — ошибок. Ошибки при установке или работе соединения встречаются очень часто (неверно введен пароль или имя пользователя, у пользователя недостаточно прав на подключение, невозможно обратиться к компьютеру по сети и т.п.), поэтому настоятельно рекомендуется реализовывать в программе обработку ошибок. Самый простой вариант реализации обработчика ошибок может выглядеть так:

Dim cn As ADODB.Connection

Set cn = CreateObject("ADODB.Connection")

cn.Provider = "SQLOLEDB"

cn.ConnectionString = "User ID=SA;Password=password;Data Source = LONDON1;" _

& "Initial Catalog = Northwind"

On Error GoTo CnErrorHandler

cn.Open

Exit Sub

CnErrorHandler:

For Each ADOErr In cn.Errors

Debug.Print ADOErr.Number

Debug.Print ADOErr.Description

Next

End Sub

На практике перехватываются ошибки, характерные для данного подключения (нет файла Access, ошибка пароля или имени пользователя при подключении к SQL Server, нет прав, файл открыт в исключительном режиме и т.п.). и пользователю предлагается исправить ошибку.

Самые важные свойства объекта ADOError:

  • Description — описание ошибки. Обычно наиболее важная информация содержится именно в описании.
  • Number — номер ошибки. По номеру удобно производить поиск в базе знаний и в Интернет.
  • Source — источник ошибки. Эта информация полезна только в том случае, если в коллекции Errors могут оказаться ошибки из разных источников.
  • SQLState и NativeError — информация о возникшей ошибке, которая пришла с SQL-совместимого источника данных.

 




Далее