Доступ к данным с помощью ADO

Цель работы: приобретение навыков извлечения данных из внешних источников с помощью ADO.

 

1 Теоретические сведения

 

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

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

Основные объекты ADO:

- объект Connection (Соединение) отвечает за соединение (связь) с источником данных;

- объект Command (Команда) представляет совокупность команд, таких как организация запросов или манипулирования данными;

- объект Parameter (Параметр) работает в паре с объектом Command, так как содержит параметры этого объекта;

- объект Recordset (Набор записей) – центральный объект объектной модели ADO. Представляет совокупность записей из внешнего источника данных;

- объект Field (Поле) описывает поля (столбцы) множества записей объекта Recordset;

- объект Error (Ошибка) представляет ошибки, возвращаемые из источника данных. Любой оператор, включающий в себя объекты ADO,

может генерировать ошибки. Каждая такая ошибка рассматривается как объект Error из коллекции Errors (Ошибки) объекта Connection.

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

1) установить соединение с источником данных;

2) получить доступ к данным;

3) извлечь определенные записи из всего множества данных;

4) закрыть соединение с источником данных.

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

- в окне редактора Visual Basic выполните команду Tools ® References (Сервис ® Ссылки);

- в списке Available (Возможные ссылки) выберите пункт Microsoft ActiveX Data Objects 2.0 Library (Библиотека Microsoft ActiveX Data Objects 2.0), установив флажок рядом с этим пунктом;

- щелкните на кнопке ОК. Ссылка на библиотеку будет установлена.

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

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

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

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\Samples\Борей.mdb”, _

CursorType: = adOpenStatic, _

LockType: = adLockOptimistic, _

Options: = adCmdTable

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

rsProducts.Close

End Sub

Примечание – Стрелочка Ä, поставленная в начале строки кода, обозначает, что строка кода, начатая на предыдущей строчке, не поместилась на печатной строке страницы и перенесена на следующую строку.

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

Ниже приведен пример программы Поворот.

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

Dim x As Integer

Dim y As Integer

Dim i As Integer

Dim j As Integer

Dim ArrayTranspose () As Variant

x = Ubound (ArrayOriginal, 1)

y = Ubound (ArrayOriginal, 2)

ReDim ArrayTranspose (y, x)

For i = 0 To x

For j = 0 To y

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

Next

Next

Поворот = ArrayTranspose

End Function

 

2 Практическая часть

 

З а д а н и е 1

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

Напишите процедуру, которая импортировала бы данные из таблицы данных Борей в соответствии с выбранным переключателем.

 

3 Контрольные вопросы

 

3.1 Назовите четыре этапа работы с ADO.

3.2 Как прервать соединение с внешним источником данных?

3.3 Какой объект представляет массив данных, извлеченный из внешнего источника с помощью ADO?

3.4 Какой аргумент метода Open используется для указания объекта, из которого извлекаются данные?

 

Лабораторная работа 10