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

Создание запросов с помощью 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)

Аргументы:

Что такое ODBC?

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

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

Microsoft Jet

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

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

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

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

• Создать рабочую область (объект Workspace)
  
• Открыть базу (источника) данных (объект Database)
  
• Создать набор записей (объект Recordset)
  
• Выполнить непосредственную работу с конкретными записями и полями
  

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

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

Синтаксис:

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

Аргументы:

Например, для создания рабочей области 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)

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

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

Синтаксис:

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

Аргументы:

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

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

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

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

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

Options:=True)

Объект Recordset

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. При нажатии кнопки Удалить удаляется запись.
   
2. При нажатии кнопки новая запись создается новая запись по данным, введенным в поля ввода.
   
3. При нажатии кнопки Редактировать вносятся изменения в запись из базы данных.
   
4. При нажатии кнопки Закрыть закрываются база данных и диалоговое окно.
   
5. При выборе переключателя хорошисты и отличники в диалоговом окне выводятся данные только о хорошистах и отличниках, а при выборе переключателя все — выводится информация обо всех студентах.
   

'

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

'

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

НОВОСТИ ФОРУМА Рыцари теории эфира

10.11.2021 - 12:37: ПЕРСОНАЛИИ - Personalias -> WHO IS WHO - КТО ЕСТЬ КТО - Карим_Хайдаров. 10.11.2021 - 12:36: СОВЕСТЬ - Conscience -> РАСЧЕЛОВЕЧИВАНИЕ ЧЕЛОВЕКА. КОМУ ЭТО НАДО? - Карим_Хайдаров. 10.11.2021 - 12:36: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от д.м.н. Александра Алексеевича Редько - Карим_Хайдаров. 10.11.2021 - 12:35: ЭКОЛОГИЯ - Ecology -> Биологическая безопасность населения - Карим_Хайдаров. 10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> Проблема государственного терроризма - Карим_Хайдаров. 10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> ПРАВОСУДИЯ.НЕТ - Карим_Хайдаров. 10.11.2021 - 12:34: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вадима Глогера, США - Карим_Хайдаров. 10.11.2021 - 09:18: НОВЫЕ ТЕХНОЛОГИИ - New Technologies -> Волновая генетика Петра Гаряева, 5G-контроль и управление - Карим_Хайдаров. 10.11.2021 - 09:18: ЭКОЛОГИЯ - Ecology -> ЭКОЛОГИЯ ДЛЯ ВСЕХ - Карим_Хайдаров. 10.11.2021 - 09:16: ЭКОЛОГИЯ - Ecology -> ПРОБЛЕМЫ МЕДИЦИНЫ - Карим_Хайдаров. 10.11.2021 - 09:15: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Екатерины Коваленко - Карим_Хайдаров. 10.11.2021 - 09:13: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вильгельма Варкентина - Карим_Хайдаров.