Что такое ODBC?

Для унификации способа работа с базами данных, созданными разными средствами (например, Access, FoxPro, SQL Server и т. д.), Microsoft разработал интерфейс доступа к данным, называемый протоколом ODBC (Open Database Connectivity - открытая связь с базой данных). Ввод, получение и работа с данными происходит при помощи структурированного языка запросов (SQL, Structured Query Language). Протокол ODBC обеспечивает стандарт передачи данных из одного приложения в другое.

Кроме того, он позволяет создавать, удалять, редактировать, сортировать и фильтровать поля, записи и таблицы.

DАО — объектный доступ к данным

Объектный доступ к данным (DAO, Data Access Objects) создавался как объектно-ориентированный интерфейс ядра базы данных Jet, т. е. для обеспечения взаимодействия между приложением, например VBA, и ядром базы данных Jet. В Office 97 включена новая версия DАО - DАО 3.5, которая позволяет манипулировать данными в обход ядра Jet. В DАО 3.5 можно напрямую связываться с источниками данных через ODBCDirect, что делает приложения более быстродействующими.

Объекты доступа к данным являются логическим представлением реляционной базы данных: входящих в нее таблиц, записей, полей, индексов и т. д.

Всего имеется 17 типов объектов доступа к данным. На рис. 15.10 представлены объекты доступа к данным первых четырех иерархических уровней. В верхней части иерархии объектов доступа находится объект DBEngine, непосредственно процессор ядра базы данных Jet. Объект Error, являющийся элементом семейства Errors, хранит информацию об ошибках, возникающих при объектном доступе к данным. Объект DBEngine также содержит в себе семейство workspaces объектов workspace, которые устанавливают именованные сеансы работы пользователя. Каждый объект workspace включает семейство Databases, которое состоит из одного или более объектов Database, являющихся открытыми базами данных. Объект TabieDef, элемент семейства TabieDefs, представляет сохраненное определение основной или присоединенной таблицы. Объект QueryDef, элемент семейства QueryDefs, представляет сохраненное определение запроса в базе данных Microsoft Jet или временное определение запроса в рабочей области ODBCDirect. Объект Recordset, элемент семейства Recordsets, представляет набор записей в основной таблице или набор записей, который получается в результате выполнения запроса. В объектах Container группируются однотипные объекты, а объект Relation представляет связь между полями таблиц или запросами.

Рис. 15.10. Иерархия объектов DАО

Для возможности использования объектов доступа необходимо задать ссылку на библиотеку DАО 3.5. Для этого в редакторе VBA необходимо выбрать команду Сервис, Ссылки (Tools, References) и в появившемся диалоговом окне Ссылки (References) установить флажок напротив элемента Microsoft DAO 3.5 Object Library

Microsoft Jet

Microsoft Jet — это ядро базы данных, которое обеспечивает возможность доступа к данным из любого приложения, поддерживающего OLE Automation, в частности VBA и Excel, независимо от интерфейса конкретной системы управления базами данных. Ядро Jet хранит свою информацию по умолчанию в файлах с базами данных, имеющих расширение mdb. Такие файлы содержат таблицы, модули, формы, отчеты, запросы и индексы. Конечно, формат mdb — это не единственный формат баз данных, с которыми работает Microsoft Jet. Он поддерживает форматы баз данных, созданных с помощью FoxPro, dBASE и т. д.

Объект Recordset

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

Имеются следующие типы объекта Recordset;

	Тип		Описание
	forward-only dynamic		Статический режим с последовательным доступом. Перемещаться от записи к записи можно только вперед. Этот режим самый скоростной Позволяет получать, анализировать, удалять и редактировать данные из нескольких динамически связанных таблиц. Этот тип соответствует динамическому указателю ODBC
	table dynaset snapshot		Работает с одной таблицей текущей базы данных. В этом случае данные можно индексировать, что ускоряет поиск записей и их сортировку. При поиске записей допустимо использовать вместо метода Find метод Seek Позволяет получать, анализировать, удалять и редактировать данные из нескольких динамически связанных таблиц. Этот тип соответствует указателю ключевого набора записей ODBC В этом режиме в общем случае нельзя редактировать базу данных, но скорость работы больше, чем в двух предыдущих

Создание объекта Recordset

Объектная переменная типа Recordset создается при помощи метода openRecordSet. Как и для любой объектной переменной, ее надо сначала объявить, а только потом установить ссылку на объект, возвращаемый методом OpenRecordSet.

Синтаксис для объектов Connection и Database:

Set НаборЗапйсей = объект.OpenRecordset (Источник, Тип, Параметры, Блокировки)

Синтаксис для объектов QueryDef, Recordset и TableDef: Set НаборЗапйсей = Объект.OpenRecordset (Тип, Параметры, Блокировки) Аргументы:

	Набор Записей		Объектная переменная, представляющая открываемый объект Recordset
	Объект		Объектная переменная, представляющая существующий объект, используемый при создании нового объекта Recordset
	Источник		Выражение (или переменная типа string) , определяющее источник записей для нового объекта Recordset. В качестве источника записей можно указать имя таблицы или запроса, а также инструкцию SQL, которая возвращает записи. Для табличного объекта Recordset в базе данных Microsoft Jet в качестве источника допускается указание только имени таблицы
	Тип		Константа, указывающая тип открываемого объекта Recordset. Допустимые Значения: dbOpenTable, dbOpenDynaset, dbOpenSnapshot, DbOpenForwardOnly и DbOpenDynamic
	Блокировки		Устанавливает тип блокировки данных. Допустимые значения: dbReadOnly, dbPessimistic, dbOptimistic и dbOptimisticValue
	Параметры		Специфицирует работу пользователя с объектом Recordset. Приведем некоторые из допустимых значений: dbAppendOnly (пользователь может добавлять записи, но не может редактировать уже существующие), dbReadOnly (запрещено вносить изменения в записи), dbDenyWrite (запрещено другим пользователям вносить изменения)

<
Методы и свойства объекта Recordset

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

Методы объекта Recordset.

	AddNew	Создает и добавляет новую запись. После внесения изменений в новую запись следует вызвать метод update для сохранения изменений и добавления записи в объект Recordset. До вызова метода update изменения в базу данных не заносятся
	Clone	Создает копию объекта Recordset. Синтаксис: Set Копия = Оригинал . Clone
	Close	Закрывает открытый объект доступа к данным
	Delete	Удаляет текущую запись в обновляемом объекте Recordset
	Edit	Копирует текущую запись из обновляемого объекта Recordset в буфер копирования для последующего изменения. После внесения изменений в новую запись следует вызвать метод update для сохранения изменений и добавления записи в объект Recordset
	MoveFirst, MoveLast, MoveNext и MovePrevious	Делает текущей первую, последнюю, следующую или предыдущую запись объекта Recordset соответственно
	FindFirst, FindLast, FindNext и FindPrevious	Находит первую, последнюю, следующую или предыдущую запись соответственно, удовлетворяющую заданным условиям, и делает эту запись текущей записью Синтаксис: (FindFirst | FindLast | FindNext | FindPrevious )Criteria criteria — это выражение (или переменная типа string), используемая для поиска записи Приведем несколько примеров критериев: "[Оценка] > 3" "[Фамилия] = 'Петров'" "[Оценка] =5 AND [Предмет] = 'Информатика'" " [ДатаЭкзамена] = #17/06/991

Seek	Находит в индексированном объекте Recordset типа table запись, удовлетворяющую заданным условиям для текущего индекса, и делает эту записи текущей. Синтаксис: Seek comparison, keyl, key2 . . . key13 Аргументы: comparison — допустимые значения: "<", "<=", "=", ">=" или ">". keyl, key2, ..., key13 — одно или несколько значений ключевых полей в текущем индексе объекта Recordset
GetRows	Загружает строки объекта Recordset в массив. Синтаксис: Set ИмяМассива = НаборЗаписей . GetRows (NumRows) NumRows — число строк
Update	Сохраняет вставки и изменения, произведенные в объекте Recordset При помощи методов AddNew и Edit
CancelUpdate	Отменяет все изменения объекта Recordset, выполненные При помощи методов AddNew и Edit

<


Свойства объекта Recordset.

	BOF EOF	Возвращает значение True, если указатель текущей записи расположен перед первой записью набора записей, и значение False, если указатель текущей записи расположен на первой записи набора или на любой записи после нее Возвращает значение True, если указатель текущей записи расположен после последней записи набора, и значение False, если указатель текущей записи расположен на последней записи набора или на любой записи перед ней
	Bookmark	Устанавливает или возвращает закладку, которая однозначно определяет текущую запись в объекте Recordset. Обычно используется для возврата в объект Recordset на определенное место, без указания конкретного адреса записи
	NoMatch	Возвращаемые значения: True, если нужная запись не найдена, и False в противном случае
	RecordCount	Возвращает число записей, к которым был осуществлен доступ в объекте Recordset. Свойство RecordCount не показывает, сколько записей содержится в объекте Recordset до обращения ко всем записям. После обращения к последней записи набора значение свойства RecordCount становится равным полному числу не удаленных записей в объекте Recordset. Для принудительного обращения к последней записи следует вызвать для объекта Recordset метод MoveLast
	Filter	Задает или возвращает значение, определяющее записи, которые будут включены в открываемый объект Recordset. При указания критерия фильтрации названия полей заключаются в квадратные скобки. Приведем несколько примеров критериев: [Оценка] = 5 [Оценка] >= 3 [Оценка] =5 AND [Предмет] = "Информатика" [Оценка] = 4 AND [Предмет] = "Информатика" [Оценка] >= 4 AND [Предмет] IN ("Информатика", "Право") [Длина] * [Ширина] > 100 В критериях вместо знака равенства допустимо использование ключевого слова LIKE, например: [Оценка] LIKE 5
	Sort	Задает или возвращает порядок сортировки записей в объекте Recordset (только в рабочей области Microsoft Jet). Для сортировки по возрастанию используется ключевое слово ASC, а по убыванию — DESC. Например, [Оценка] ASC [Фамилия] DESC Для сортировки первоначально по полю оценка, а потом по полю Фамилия: [Оценка] ASC, [Фамилия] DESC

Открытие базы (источника) данных

Открыть базу данных можно методом OpenDatabase объекта Workspace.

Синтаксис:

Set БазаДанных = РабочаяОбласть.OpenDatabase (name, options, readonly, connect)

Аргументы:

	БазаДанных		Объектная переменная, представляющая открываемый объект Database
	РабочаяОбласть		Объектная переменная, представляющая существующий объект workspace, который будет содержать базу данных. Если аргумент РабочаяОбласть не задан, метод OpenDatabase использует стандартную рабочую область
	Name		Выражение или переменная типа string, задающая имя существующего файла базы данных с ядром Microsoft Jet или имя источника данных ODBC

	Options		Для рабочей области ядра Microsoft Jet допустимыми являются следующие значения аргумента: True (открытие базы данных для монопольного доступа) и False (по умолчанию, открытие базы данных для общего доступа) Для рабочей области ODBCDirect допустимыми являются следующие значения аргумента: dbDriverNoPrompt, dbDriver Prompt, dbDrive r Complete, dbDriverCompleteRequired
	Readonly		Допустимые значения: True (база данных открывается только для чтения) и значение False (по умолчанию, база данных открывается как для чтения, так и для записи)
	Connect		Дополнительный аргумент, содержащий сведения о подключении, в том числе и пароли

Для закрытия базы данных следует применять метод close.

Например, для открытия базы данных с ядром Microsoft Jet можно использовать следующие две инструкции:

Dim БазаДанных As Database

Set БазаДанных = РабочаяОбласть

.OpenDatabase (Name:="C: \Пример.mdb",

Options:=True)

Порядок работы при объектном доступе к данным

При извлечении информации с помощью объектного доступа к данным надо:

Создать рабочую область (объект Workspace)

Открыть базу (источника) данных (объект Database)

Создать набор записей (объект Recordset)

Выполнить непосредственную работу с конкретными записями и полями

Пример приложения

Рассмотрим работу с базой данных на примере следующего простого приложения, в котором используются все основные операции с записями. База данных находится в файле студенты.mdb, созданным в Access, и состоит из одной таблицы ПервыйКурс. В таблице имеются четыре поля: Фамилия, Группа, предмет и Оценка. При запуске приложения на экране отображается диалоговое окно Студенты первого курса.

Рис. 15.11. Диалоговое окно Студенты первого курса

Приводимая ниже программа:

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

При нажатии кнопок > и < происходит перемещение на одну запись вперед и назад соответственно, а при нажатии кнопок >> и << — перемещение в конец и начало таблицы соответственно.

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

При нажатии кнопки Удалить удаляется запись.

При нажатии кнопки новая запись создается новая запись по данным, введенным в поля ввода.

При нажатии кнопки Редактировать вносятся изменения в запись из базы данных.

При нажатии кнопки Закрыть закрываются база данных и диалоговое окно.

При выборе переключателя хорошисты и отличники в диалоговом окне выводятся данные только о хорошистах и отличниках, а при выборе переключателя все — выводится информация обо всех студентах.

'

' Переменные уровня модуля

'

Dim РабочаяОбласть As Workspace

Dim БазаДанных As Database

Dim Запись As Recordset

Dim ЗаписьДубль As Recordset

Dim Фамилия As String

Dim Критерий As String

Dim Закладка As Variant

'

Private Sub CommandButton1_Click()

'

' Найти запись по фамилии

'

' Запоминается на закладке текущая запись

Закладка = Запись.Bookmark

'

' Считывается фамилия из поля Фамилия, на ее основе

' создается критерий поиска и ищется первая запись с

' подобной фамилией '

Фамилия = Trim(TextBoxl.Text)

Критерий = "[Фамилия]="' & Фамилия & "'"

Запись.FindFirst Критерий

' Если запись с указанной фамилией найдена, то она отображается

' в диалоговом окне.

' Если запись не найдена, то отображается сообщение и

' при помощи закладки происходит возврат к записи, с которой

' был начат поиск
'

If Запись.NoMatch = False Then

ПоказатьЗапись Else

MsgBox "Запись не найдена", vblnformation, "Студенты"

Запись.Bookmark = Закладка

ПоказатьЗапись End If

End Sub

Private Sub CommandButtonlO_Click()
'

' Переход к последней записи

'
'

Запись.MoveLast ПоказатьЗапись

End Sub
'

Private Sub CommandButton2_Click()

' Найти следующую запись по фамилии
'

Закладка = Запись.Bookmark

Фамилия = Trim(TextBoxl.Text)

Критерий = "[Фамилия]='" & Фамилия & "'"

Запись.FindNext Критерий

If Запись.NoMatch = False Then

Показать Запись Else

MsgBox "Больше таких записей нет", vblnformation, "Студенты"

Запись.Bookmark = Закладка

Показать Запись
End If
End Sub

Private Sub CommandButton3_Click()
'

' Удаление записи
'

With Запись

.Delete

.MoveNext End With ПоказатьЗапись

End Sub
'

Private Sub CommandButton4_Click()
'

' Добавление записи
'

With Запись .AddNew

.Fields("Фамилия").Value = TextBoxl.Text
.Fields("Группа").Value = TextBox2.Text
.Fields("Предмет").Value = TextBox3.Text
.Fields("Оценка").Value = TextBox4.Text .Update

End With

'

Exit Sub
'

End Sub

Private Sub CommandButton5_Click()
'

' Редактирование записи
'

With Запись .Edit

.Fields("Фамилия").Value = TextBoxl.Text
.Fields("Группа").Value = TextBox2.Text
.Fields("Предмет").Value = TextBox3.Text
.Fields("Оценка").Value = TextBox4.Text

.Update End With End Sub


Private Sub CommandButton6_Click()
'

' Закрытие записи, базы данных и окна
'

Запись.Close
БазаДанных.Close
РабочаяОбласть.Close
UserForml.Hide

End Sub

Private Sub CommandButton7_Click()

' Переход к первой записи
'Запись.MoveFirst Показать Запись
End Sub

Private Sub CommandButton8_Click()
'

' Переход к предыдущей записи

'Запись.MovePrevious
'

' Если достигнута первая запись, то отображается сообщение
'

If Запись.BOF = True Then

Запись.MoveFirst

MsgBox "Первая запись", vblnformation, "Студенты"
End If
'ПоказатьЗапись

End Sub
'

Private Sub CommandButton9_Click()

' Переход к последующей записи
'

Запись.MoveNext

' Если достигнута последняя запись, то отображается сообщение
'

If Запись.EOF = True Then

Запись.MoveLast

MsgBox "Последняя запись", vblnformation, "Студенты"
End If
ПоказатьЗапись

End Sub
'

Private Sub OptionButtonl_Click()
'

' Отображение данных только о хорошистах и отличниках
'

' Создание копии записи
'

Set ЗаписьДубль = Запись.Clone
'

' Фильтрация записей по критерию
'

Запись.Filter = "[Оценка] >= 4"
'

' Создание отфильтрованной записи
'

Set Запись= Запись.OpenRecordset()

If Запись.RecordCount > 0 Then
'

' Если отфильтрованная запись существует, то она отображается
' в диалоговом окне

'ПоказатьЗапись

Else
'

' Если отфильтрованной записи нет, то отображается соответствующее
' сообщение, восстанавливается первоначальный объект Recordset
' и выбирается переключатель Все

'

MsgBox "Таких студентов нет", vblnformation, "Студенты"

Set- Запись = ЗаписьДубль.Clone

Set Запись = Запись.OpenRecordset()

OptionButton2.Value = True
End If
End Sub

Private Sub OptionButton2_Click()
'

' Отображение всех студентов
'

Set ЗаписьДубль = Запись.Clone

Set Запись = ЗаписьДубль.OpenRecordset()

ЗаписьДубль.Close

ПоказатьЗапись

End Sub

'

Private Sub UserForm_Initialize()
'

' Создание рабочей области


'

Set РабочаяОбласть = CreateWorkspace(Name:="", UserName:="admin",

Password:="", UseType:=dbUseJet)
'

' Открытие базы данных студенты.mdb

Set БазаДанных = _

РабочаяОбласть.OpenDatabase
(Name:="C:\MY_DOC\студенты.mdb",

Options:=True)

'

'

' Создание записей
'

Set Запись = БазаДанных.OpenRecordset("ПервыйКурс",dbOpenDynaset)
'

' Принудительное перемещение на последнюю запись для того,
' чтобы определить число записей Запись.MoveLast
'

' Вывод числа записей в надписи Номер записи из
'

'

Labels.Caption = "Всего записей " & CStr(Запись.RecordCount)
'

' Принудительное перемещение на первую запись
'Запись.MoveFirst
'

' Вывод первой записи в поля диалогового окна

'

ПоказатьЗапись With UserForm1

.Caption = "Студенты первого курса"

.OptionButton2.Value = True End With

End Sub

'

Sub ПоказатьЗапись()

'

' Процедура вывода записи в поля диалогового окна
'

TextBoxl.Text = Запись.Fields("Фамилия").Value
TextBox2.Text = Запись.Fields("Группа").Value
TextBox3 .Text = Запись.Fields ("Предмет.").Value
TextBox4.Text = Запись.Fields("Оценка").Value

'

End Sub

РАБОТА С ВНЕШНИМИ БАЗАМИ ДАННЫХ

ГЛАВА 15. РАБОТА С ВНЕШНИМИ БАЗАМИ ДАННЫХ

СОЗДАНИЕ ЗАПРОСОВ С ПОМОЩЬЮ MICROSOFT QUERY

ЧТО ТАКОЕ ODBC?

MICROSOFT JET

DАО — ОБЪЕКТНЫЙ ДОСТУП К ДАННЫМ

ПОРЯДОК РАБОТЫ ПРИ ОБЪЕКТНОМ ДОСТУПЕ К ДАННЫМ

Создание рабочей области

Открытие базы (источника) данных

Объект Recordset

ПРИМЕР ПРИЛОЖЕНИЯ

Глава 15.

Работа с внешними базами данных

Создание рабочей области

Рабочую область можно создать методом CreateWorkspace объекта DBEngine.

Синтаксис:

Set РабочаяОбласть = CreateWorkspace(Name, UserName, Password, UseType)

Аргументы:

	РабочаяОбласть		Объектная переменная, представляющая создаваемый объект Workspace
	Name		Выражение или переменная типа string, содержащая уникальное имя нового объекта workspace
	UserName		Выражение или переменная типа string, определяющая владельца нового объекта workspace
	Password		Выражение или переменная типа string, содержащая пароль для доступа к новому объекту workspace
	UseType		Допустимые значения: dbUseJet (создание рабочей области ядра Microsoft Jet) и dbUseODBC (создание рабочей области ODBCDirect)

Например, для создания рабочей области Microsoft Jet можно использовать следующие две инструкции:

Dim РабочаяОбласть As CreateWorkspace

Set РабочаяОбласть = DBEngine.CreateWorkspace(Name:="МояОбласть", UserName:="admin", Password:="", UseType:=dbUseJet)

Рабочую область ODBCDirect можно создать следующим образом (ссылка на объект DBEngine применяется по умолчанию, поэтому во второй инструкции объект DBEngine опущен):

Dim РабочаяОбласть As CreateWorkspace

Set РабочаяОбласть = CreateWorkspace(Name:="МояОбласть", UserName:="UID'V Password:="", UseType:=dbUseODBC)

Создание запросов

с помощью Microsoft Query

В Microsoft Office входит отдельная программа, которая называется Microsoft Query, предоставляющая доступ к базам данных, созданных с помощью таких программных средств, как dBASE, Access, Paradox, Microsoft SQL Server и т. п. Эта программа позволяет отбирать информацию из базы данных, сортировать, редактировать и копировать ее на рабочий лист. Используя Microsoft Query, можно получить данные на основе заданных пользователем критериев. Excel связывается с Microsoft Query посредством механизма DDE (Dynamic Data Exchange — динамический обмен данными). Microsoft Query работает с внешними источниками данных через драйверы ODBC (Open Database Connectivity), которые являются разработанным Microsoft стандартом для работы с базами данных. Для получения данных по запросу посредством драйвера ODBC используется язык запросов SQL (Structured Query Language).

Рассмотрим последовательность создания запроса при помощи Microsoft Query на основе простой базы данных о студентах, созданной в Access (рис. 15.1).

Выполните команду Данные, Внешние данные, Создать запрос (Data, Get External Data, New Database Query). Появится диалоговое окно Выбор источника данных (Choose Data Source) (рис. 15.2). В этом окне выберите источник данных, например базу данных, составленную в Access 97. Нажмите кнопку Параметры (Options), при помощи появившегося окна Источник данных (Data Source) можно установить папки, в которых следует искать источник данных. Итак, выберите базу данных, составленную в Access 97, в качестве источника и нажмите кнопку ОК.

В появившемся диалоговом окне Select Database (рис. 15.3) в списке Database Name выберите имя файла базы данных (в данном случае студенты.mdb) и нажмите кнопку ОК, что приведет к запуску мастера запросов.

Рис. 15.1. База данных о студентах

Рис. 15.2. Диалоговое окно Выбор источника данных

Рис. 15.3. Диалоговое окно Select Database

Откроется диалоговое окно Создание запроса: выбор столбцов (Query Wizard — Choose Columns) (рис. 15.4), которое позволяет добавлять столбцы или поля в список Столбцы запроса (Query Columns).
При нажатии на кнопку >, расположенную в средней части диалогового окна Создание запроса: выбор столбцов (Query Wizard Choose Columns), выбранный столбец добавляется в запрос, кнопка < служит для удаления выбранного столбца из списка столбцов запроса, а кнопка « позволяет удалить все столбцы запроса. В рассматриваемом случае в список Столбцы запроса (Query Columns) добавим все поля базы данных, кроме поля номер. Нажмите кнопку Далее > (Next >), переходя к следующему шагу работы мастера запросов.

Рис. 15.4. Диалоговое окно Создание запроса: выбор столбцов

Откроется диалоговое окно Создание запроса: отбор данных (Query Wizard -Filter Data) (рис. 15.5), которое позволяет отфильтровать данные в выбранных полях. Для фильтрации данных в списке Столбцы для отбора, выделите столбец, по которому будет производиться фильтрация. В первом раскрывающемся списке выбирается операция отбора, а во втором, содержащем все имеющиеся в выбранном поле значения, выберите нужное или введите свое. При необходимости ввести дополнительную операцию отбора выберите переключатель и (AND) или или (OR), и воспользуйтесь следующими раскрывающимися списками для ввода критериев фильтрации и т. д. Завершив создание критериев фильтрации, нажмите кнопку Далее > (Next >), переходя к следующему шагу работы мастера запросов. В рассматриваемом случае проведем фильтрацию по полю группа, установив критерий равно значению Экономика, и полю предмет с критерием равно значению информатика, т. е. отобразим только информацию о результатах сдачи информатики студентов-экономистов. Отметим, что визуально выбранные поля отличаются от невыбранных тем, что они выделяются полужирным шрифтом.



Рис. 15.5. Диалоговое окно Создание запроса: отбор данных

Откроется диалоговое окно Создание запроса: порядок сортировки (Query Wizard - Sort Data) (рис. 15.6), которое позволяет отсортировать данные в выбранных полях в порядке возрастания или убывания. Отсортируем данные по фамилиям в порядке возрастания.


Нажмите кнопку Далее > (Next >), переходя к следующему шагу работы мастера запросов.



Рис. 15.6. Диалоговое окно Создание запроса: порядок сортировки

Откроется диалоговое окно Создание запроса: заключительный шаг (рис. 15.7). При желании еще раз воспользоваться тем же запросом его можно сохранить, используя кнопку Сохранить запрос (Save Query). Для завершения создания запроса выберите флажок Вернуть данные в Microsoft Excel (Return Data to Microsoft Excel) и нажмите кнопку Готово (Finish).



Рис. 15.7. Диалоговое окно Создание запроса: заключительный шаг

На экране отобразится диалоговое окно Возврат данных в Microsoft Excel (Returning External Data to Microsoft Excel) (рис. 15.8). выберите переключатель Имеющийся лист и укажите адрес верхней левой ячейки, куда надо поместить данные, нажмите кнопку ОК. Полученный результат запроса данного примера показан на рис. 15.9.



Рис. 15.8. Диалоговое окно Возврат данных в Microsoft Excel



Рис. 15.9. Результат запроса



Макрорекордер записывает перечисленные выше действия в виде следующего макроса:

Sub Макрос1()

'

' Макрос1 Макрос

' Макрос записан 03.06.99 (Андрей)

'

'

With ActiveSheet.QueryTables

.Add(Connection:=Array(Array(

"ODBC;DSN=MS Access 97

Database;DBQ=C:\МУ_DОС\студенты.mdb;

DefaultDir=C:\MY__DOC;Driverld=25;

FIL=MS Aceess;MaxBufferSize=512;PageT-imeo")

, Array("ut=5;")),

Destination:=Range("Al"))

.Sql = Array( "SELECT ПервыйКурс.Фамилия,

ПервыйКурс.Группа, ПервыйКурс.Предмет,

ПервыйКурс.Оценка" & Ghr(13) & "" & Chr(10) & "FROM

`C:\МY_ООС\СТУДЕНТЫ` .ПервыйКурс

ПервыйКурс" & Chr(13) & "" & Ghr,(10) & "WHERE (ПервыйКурс.Группа='Экономика')

AND (ПервыйКурс.Предмет='Инф" ,"орматика')"

& Chr(13) & "" & Chr(10) &

"ORDER BY ПервыйКурс.Фамилия")

.FieldNames = True

.RefreshStyle = xlInsertDeleteCells

.RowNumbers = False



.FillAdjacentFormulas = False

.RefreshOnFileOpen = False

.HasAutoFormat = True

.BackgroundQuery = True

.TablesOnlyFromHTML = True

.Refresh BackgroundQuery:=False

.SavePassword = True

.SaveData = True

End With

End Sub

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

Центральную роль в данном макросе играют: инструкция SELECT, указывающая выбираемые поля, инструкция WHERE, задающая критерий отбора записей и инструкция ORDER BY, устанавливающая порядок вывода записей.

В макросе используется семейство QueryTables. Семейство QueryTables является семейством всех объектов QueryTable, представляющих собой внедренные на рабочий лист результаты запроса по внешней базе данных посредством Microsoft Query. Основными методами семейства QueryTables являются Add и item. Несмотря на громоздкость макроса макрос1, метод Add имеет простую синтаксическую структуру:

Add(Connection, Destination, Sql)

Аргументы:

	Connection	ODBC-строковое выражение, указывающее базу данных, к которой производится запрос
	Destination	Устанавливает верхнюю левую ячейку диапазона рабочего листа, в который выводится результат запроса
	Sql	ODBC-строковое выражение, указывающее критерии, по которым создается запрос

---

---