В начало

Приложение. Ответы

21-й час. Доступ к данным с помощью ADO

В предыдущем часе мы изучали способы импортирования данных в Excel с помощью MS Query, отметив и недостатки, присущие данной технологии. В этом часе мы рассмотрим Извлечение данных из внешних источников с помощью ADO. Эта технология более гибкая и эффективная, чем MS Query.

В этом часе будут рассмотрены следующие вопросы.

• Что такое ADO.
• Обзор объектов ADO.
• Как создать ссылку на библиотеку ADO.
• Использование ADO для импортирования данных в Excel.

Что такое ADO

Технология ADO (ActiveX Data Objects - Объекты данных ActiveX) позволяет разрабатывать приложения, которые получают доступ и взаимодействуют с данными внешних баз данных с использованием стандарта OLE DB. В отличие от сводных таблиц и MS Query, в данном случае можно не только извлекать данные, но и манипулировать ими непосредственно в базе данных. Благодаря этому пользователь имеет больший контроль и может управлять данными с меньшими затратами ресурсов своей компьютерной системы.

OLE DB - это совокупность интерфейсов Component Object Model (Модель составных объектов), сокращенно СОМ, которые предлагают единообразный подход для организации доступа к широкому спектру информационных ресурсов.

С помощью ADO можно импортировать данные из внешнего источника в рабочую книгу Excel, изменить их, а затем вернуть обратно.

Объекты доступа к базам данных

Как следует из названия ActiveX Data Objects (Объекты данных ActiveX), ADO предлагает объектную модель, т.е. совокупность объектов, посредством которых можно получить доступ к внешним данным и манипулировать ими. Вот несколько основных объектов этой модели.

• Объект Connection (Соединение) - отвечает за соединение.(связь) с источником данных.
• Объект Command (Команда) - представляет совокупность команд, таких как организация запросов или манипулирования данными.
• Объект Parameter (Параметр) - работает в паре с объектом Command, так как содержит параметры этого объекта.
• Объект Recordset (Набор записей) - центральный объект объектной модели ADO. Представляет совокупность записей из внешнего источника данных.
• Объект Field (Поле) - описывает поля (столбцы) множества записей объекта Recordset.
• Объект Error (Ошибка) - представляет ошибки, возвращаемые из источника данных. Любой оператор, включающий в себя объекты ADO, может генерировать ошибки. Каждая такая ошибка рассматривается как объект Error из коллекции Errors (Ошибки) объекта Connection.

Использование ADO

Основная цель разработчиков приложений при использовании ADO - импортировать данные из внешней базы данных в рабочую книгу Excel. Для достижения этой цели необходимо выполнить следующее.

1. Установить соединение с источником данных.
2. Получить доступ к данным.
3. Извлечь определенные записи из всего множества данных.
4. Закрыть соединение с источником данных.

Создание ссылки на библиотеку ADO

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

1. В окне редактора Visual Basic выполните команду Tools > References (Сервис > Ссылки). Откроется диалоговое окно References (Ссылки).
2. В списке Available References (Возможные ссылки) выберите пункт Microsoft ActiveX Data Objects 2.0 Library (Библиотека Microsoft ActiveX Data Objects 2.0), установив флажок рядом с этим пунктом.
3. Щелкните на кнопке ОК. Ссылка на библиотеку будет установлена.

(Ссылка на библиотеку ADO устанавливается только для открытой рабочей книги. Поэтому создавайте ссылку после того, как будет отрыта та книга, в которой будет находиться процедура, использующая ADO. - Прим. ред.)

Создадим процедуру, которая с помощью ADO будет извлекать данные из базы данных Access Борей и помешать их в рабочий лист. Откройте новую рабочую книгу и создайте процедуру ИмпортДанных, код которой приведен в листинге 21.1.

Листинг 21.1. Процедура ИмпортДанных

l:Sub ИмпортДанных()

2: Dim rsProducts As ADODB.Recordset

3: Set rsProducts = New ADODB.Recordset

4: rsProducts.Open Source:="Товары", _

5:  activeconnection:="Provider=Microsoft.Jet.OLEDB.4.0; _

6:  Data Source=C:\Program Files\Microsoft _

7:  Office\Office\Samples\Борей.mdb", _

8:  CursorType:=adOpenStatic, _

9:  LockType:=adLockOptimistic, _

10:  Options:=adCmdTable

11: With Worksheets("Лист1")

12:  .Range("A1").CurrentRegion.Clear

13:  Application.Intersect(.Range(.Rows(l), _

      .Rows(rsProducts.RecordCount)),

      .Range(.Columns(1), .Columns(rsProducts.Fields.Count))). _

      Value = Поворот(rsProducts.GetRows _

      (rsProducts.RecordCount))

14: End With

15: rsProducts.Close

16:End Sub

В начале своего выполнения процедура работает с объектом Recordset. Обратите внимание на метол Open (Открыть) этого объекта. Аргумент Source (Источник) метода Open задает имя таблицы в базе данных (в данном случае процедура будет работать с таблицей Товары). Аргумент activeconnection (активное соединение) содержит всю информацию, необходимую для соединения с базой данных:

Dim rsProducts As ADODB.Recordset

Set rsProducts = New ADODB.Recordset

rsProducts.Open Source:="Товары", _

 activeconnection:="Provider=Microsoft.Jet.OLEDB.4.0; _

 Data Source=C:\Program Files\Microsoft _

 Office\Office\Samlples\Борей.mdb", _

 CursorType:=adOpenStatic, _

 LockType:=adLockOptimistic, _

 Options:=adCmdTable

После установления соединения с базой данных, все готово для импортирования ее, данных в рабочую книгу. Но здесь вас подстерегает небольшая проблема. Массив данных, извлекаемый из базы данных с помощью ADO, организован в виде двумерной таблицы, где каждый столбец соответствует одной записи данных, а строка соответствует полю, т.е. данные представлены совсем не в том виде, к какому мы привыкли.

Поэтому, прежде чем помешать данные в рабочий лист, весь массив данных надо транспонировать (повернуть) так, чтобы столбцы стали строками, а строки - столбцами. Если вы опытный пользователь Excel, то можете предположить, что для этого можно воспользоваться функцией Excel ТРАНСП (транспонирование). Но дело осложняется тем, что массивы импортируемых данных, как правило, очень велики и функция ТРАНСП с ними не всегда справляется. Поэтому надо создать собственную функцию транспонирования (код такой функции с названием Поворот приведен ниже в листинге 21.2). В процедуре ИмпортДанных в строках кода

With Worksheets("Лист1")

 .Range("A1").CurrentRegion.Clear

 Application.Intersect(.Range(.Rows(1), _

  .Rows(rsProducts.RecordCount)), _

  .Range(.Columns(1), .Columns(rsProducts.Fields.Count))). _

  Value = Поворот(rsProducts.GetRows _

  (rsProducts.RecordCount))

End With

сначала очищается текущая активная область рабочего листа, начиная с ячейки А1, а затем используется метод Intersect (Пересечь) В сочетании с функцией транспонирования Поворот. Метод Intersect возвращает объект Range (Диапазон), состоящий из прямоугольной области пересечения двух и более диапазонов ячеек. В данном случае создается пересечение диапазона импортируемых данных и ячеек всего рабочего листа.

Последний оператор rsProducts.Close закрывает соединение с базой данных.

Теперь приведем код функции транспонирования Поворот.

Листинг 21.2. Функция Поворот

1: Function Поворот(ByRef ArrayOriginal As Variant) As Variant

2:  Dim x As Integer

3:  Dim у As Integer

4:  Dim i As Integer

5:  Dim j As Integer

6:  Dim ArrayTranspose() As Variant

7:

8:  x = Ubound(ArrayOriginal, 1)

9:  y = Ubound(ArrayOriginal, 2)

10:

11:  ReDim ArrayTranspose(y, x)

12:

13:  For i = 0 To x

14:   For j = 0 To у

15:    ArrayTranspose(j, i) = ArrayOriginal(i, j)

16:   Next

17:  Next

18:

19:  Поворот = ArrayTranspose

20:

21: End Function

Теперь все готово для выполнения процедуры ИмпортДанных. Перейдите в рабочую книгу и выполните процедуру. Через несколько мгновений в рабочем листе появятся импортированные данные (рис. 21.1).

Рис. 21.1. Данные, импортированные с помощью ADO

Резюме

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

В следующем часе мы продолжим изучение технологии ADO и создадим экранную форму, которая позволит пользователю выбирать и редактировать данные.

Вопросы и ответы

Вопрос. Только ли с базами данных Access можно работать посредством ADO?

Ответ. Нет, с помощью ADO можно работать со многими типами баз данных, включая SQL Server и FoxPro.

Вопрос. Можно ли процесс импортирования данных посредством ADO записать с помощью макроса?

Ответ. Нет, так как запрос на выборку данных здесь основан на использовании специальных команд, которые невозможно выполнить с помощью средств Excel.

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Какой аргумент метода Open используется для указания объекта, из которого извлекаются данные?
2. Истинно или ложно следующее утверждение: для организации доступа к внешним данным с помощью ADO достаточно создать соответствующую процедуру?
3. Какой объект сохраняет информацию о проблемах, возникших при использовании ADO?
4. Истинно или ложно следующее утверждение: с помощью ADO можно получить доступ только к базам данных Microsoft Access?
5. Какой объект представляет массив данных, извлеченный из внешнего источника с помощью ADO?
6. Как прервать соединение с внешним источником данных?
7. Назовите четыре этапа работы с ADO.

Упражнение

Создайте экранную форму с тремя переключателями Заказы, Поставщики, Товары (соответствуют таблицам в базе данных Борей). Напишите процедуру, которая импортировала бы данные из таблицы базы данных Борей в соответствии с выбранным переключателем.