Создание и использование запросов с использованием VBA.

Цели работы:

• закрепить навыки по редактированию таблиц;

• познакомиться с основными видами запросов;

• научиться создавать запросы на выборку различными спо­собами.

Ход работы:

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

Задание 1.Откройте учебную БД, изготовленную на прошлом занятии.

Порядок работы:

• Вызовите программу Access. Для этого дважды щелкните по пик­тограмме Microsoft Access. Откроется окно системы управле­ния базами данных, в котором появится меню.

• Включите мышкой переключатель Открыть базу данных, вы­делите из списка баз данных, расположенного ниже переклю­чателя, имя вашей базы я щелкните по кнопке ОК.

• Если вы включили в параметры запуска Заставку, то она появит­ся перед вами. Закройте ее. На экране возникнет диалоговое окно с основными элементами базы данных. В базе данных должны быть три таблицы: Список, Личные данные, Группы.

Задание 2. Создайте запрос на выборку с именем Номера телефонов.

Порядок работы:

• Выберите закладку Запросы, если находитесь в другом диало­говом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите Конструктор, щел­кните по кнопке ОК.

• Добавьте нужные таблицы (Личные данные и Список), выби­рая их и щелкая по кнопке Добавить. Так как таблицы связаны, то между ними появится линия, обозначенная цифрами 1 («один-к-одному»).

• Закончите выбор, щелкнув по кнопке Закрыть. Появляется воз­можность выбора полей из разных таблиц.

• Выберите поля Фамилия, Имя и Отчество из таблицы Спи­сок и НомерТелефона - из таблицы Личные данные. Для этого достаточно сделать двойной щелчок мышкой по име­ни поля. Второй вариант - перетащить мышкой название поля в клетки запроса.

• Сохраните запрос, щелкнув по кнопке Сохранить. Введите имя запроса Номера телефонов и щелкните по кнопке ОК.

• Щелкните кнопку для представления запроса. Это са­мый простой вид запроса на выборку. В результате вы получа­ете новую таблицу с другим набором полей.

• Перейдите в режим Конструктор.

Замечание. Условие отбора можно включать аналогично вклю­чению фильтра. Например, телефонная книга для всех учащих­ся, фамилии которых начинаются на букву «В», может быть получена с помощью включения условия Like «В*». Получите этот запрос.

• Щелкните кнопку представления запроса. Измените имя запроса, выбрав в меню пункт Файл - Сохра­нить как/Экспорт.

• В появившемся диалоговом окне наберите новое имя запроса: Выборка по В. Теперь в меню базы данных в окне Запросы будет показано два запроса.

Задание 3. Составьте запрос с использованием логических опе­раций в условии отбора.

Предположим, что вам нужно составить ведомость для выпла­ты стипендии всем учащимся, которые учатся без троек. Для этого нужно выбрать записи, в которых оценки по предметам 4 ИЛИ 5.

Порядок работы:

• Выберите закладку Запрос, если находитесь в другом диалого­вом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите Конструктор, щел­кните по кнопке ОК.

• Добавьте нужные таблицы (Личные данные и Список), выби­рая их и щелкая по кнопке Добавить.

Замечание. Так как таблицы ввязаны, между ними на экране появляется линия «один-к-одному». Если ее нет, таблицы нуж­но связать.

• Закончите выбор, щелкнув по кнопке Закрыть. Появляется воз­можность выбора полей из разных таблиц.

• Выберите поля Фамилия, Имя и Отчество из таблицы Спи­сок и поля Word, Excel, Access - из таблицы Личные дан­ные. Для этого достаточно сделать двойной щелчок мышкой по имени поля. Второй вариант - перетащить мышкой назва­ние поля в клетки запроса.

• В строке Условие отбора под полями Word, Excel и Access noставьте 4 Or 5.

• Сохраните запрос с именем Успеваемость, щелкнув по кноп­ке Сохранить. Теперь в меню базы данных в окне Запросы будет показано три запроса.

Самостоятельное задание, 1. Составьте запрос на учеников груп­пы 101, у которых оценка по курсу «Освоение базы Access» 4 или 5; сохраните запрос с именем Успеваемость2. 2. Составьте запрос на учеников групп 102 и 103, которые имеют оценку по курсу «Освое­ние программы Word» и «Освоение программы Excel» 4 или 5; со­храните запрос с именем УспеваемостьЗ.

Задание 4. Составьте запрос на выборку всех записей, кроме тех, в которых указана фамилия Баранова с использованием Пост­роителя выражений.

Порядок работы:

• Выберите закладку Запросы, если находитесь в другом окне.

• Выделите запрос Номера телефонов.

• Щелкните по кнопке Конструктор.

• Удалите поле НомерТелефона.

• Добавьте поле Адрес.

• Сохраните запрос с именем Адрес, выполнив команду Сохра­нять как/экспорт....

• Поставьте курсор в ячейку Условие отбора в столбце Фамилия.

• Удалите надпись в этой ячейке.

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

Порядок работы:

• Щелкните по кнопке Not, это слово появится в верхнем поле. Фамилию Баранова в кавычках наберите вручную.

• Щелкните по кнопке ОК. В поле Условие отбора появится данное выражение.

• Щелкните кнопку представления запроса.

•Закройте запрос, сохранив его с именем не_Баранова, выпол­нив команду Файл - Сохранить как/экспорт.

Задание 5. Создайте вычисляемые поля.

Порядок работы:

• Выберите закладку Запросы, если находитесь в другом окне.

• Щелкните по кнопке Создать.

• В появившемся диалоговом окне выберите Конструктор, щел­кнув по кнопке ОК.

• Добавьте нужные таблицы (Список и Личные данные), выби­рая их и щелкая по кнопке Добавить.

• Завершите выбор, щелкнув по кнопке Закрыть.

• Выберите поля Фамилия и Имя из таблицы Список и поля Word и Excel - из таблицы Личные данные.

• Поставьте курсор на клетку правее Excel (на линии Поле).

• Щелкните по кнопке Построить.

• В появившемся окне напечатайте вручную выражение Среднее: ([Word]+[Excel])/2, и щелкните по кнопке ОК.

• Это выражение подставится в новое поле. Нажмите клавишу [Enter].

• Щелкните кнопку представления запроса. Новое поле будет иметь имя Среднее. Закройте запрос

Задание 6. Создайте запрос с помощью команд VBA.

В данном примере показано использование запросов в программе, созданных с помощью языка SQL. Запросы созданные Вами до этого автоматически переводились конструктором на язык SQL. Посмотрите, как это выглядит, открыв любой запрос и выполнив команду «Вид – Режим SQL».

Порядок работы:

• Откройте форму Общая форма в режиме конструктора. Для этого выделите форму левой клавишей мышки и нажмите кнопку Конструктор (либо нажмите правой клавишей мыши на названии формы и контекстном меню выберите пункт Конструктор).

• Добавьте на форму текстовое поле и кнопку. Для этого выберите левой клавишей мыши на Панели элементов пиктограмму «Поле», затем переместите курсор на поле формы (он примет вид выбранной Вами пиктограммы). В выбранном Вами для размещения текстового поля месте нажмите левую клавишу мыши и, не отпуская ее, задайте размер создаваемого объекта. Отпустив клавишу мыши, Вы увидите созданное Вами текстовое поле. Вы можете изменить его местоположение и размер с помощью указателя мыши. Аналогичным образом разместите под созданным Вами текстовым полем объект кнопка.

• Задайте подпись для текстового поля: «Фильтр по первым буквам фамилии студента». Это можно сделать вызвав панель Свойства (Вид - Свойства) и выделив надпись, которая была автоматически создана для текстового поля и размещена по левую сторону от него. В свойствах надписи необходимо выделить закладку Макет и в поле Подпись ввести желаемую фразу. Аналогично задайте подпись для кнопки: «Применить».

• Аналогичным образом измените имена созданных Вами текстового поля и кнопки на txtFiltr и cmbFiltr соответственно (Закладка Другие, свойство Имя).

• В панели Свойства для объекта cmbFiltr перейдите на закладку События, найдите строку Нажатие кнопки, установите курсор в текстовое поле. Правее появятся значок раскрывающегося списка и кнопка обзор …. Выберите кнопку обзор и далее, в открывшемся окне, пункт программы, нажмите Ок. Перед Вами появится редактор VBA, где можно создавать собственные обработчики для различных событий с помощью языка команд языка VBA.

• Введите в этом окне следующий программный код:

Private Sub cmbFiltr_Click()

Dim strSQL As String

TxtFiltr.SetFocus

strSQL = txtFiltr.Text

Form.RecordSource = «SELECT Список.Код, Список.Фамилия, Список.Имя, Список.Отчество, Список.[Год рождения], Список.Школа, Список.Класс, Список.[Учебная группа], [личные данные].КодСтудента, [Личные данные].Адрес, [Личные данные].Телефон, [Личные данные].Word, [Личные данные].Exel, [Личные данные].Access FROM Список INNER JOIN [Личные данные] ON Список.Код=[Личные данные].КодСтудента Where Список.Фамилия like '» + strSQL + «*';»

Form.Requery

End Sub

• Закройте редактор программного кода и вернитесь в режим конструктора формы. Проверьте работу сделанных изменений, выполните Вид – Режим формы. Введите в текстовое поле букву «В» и нажмите Применить. Проверьте, что теперь в форме можно пролистать только студентов у которых фамилия начинается с буквы «В».

• Мы реализовали фильтр с помощью SQL запроса. Условие отбора задается после ключевого слова Where. Попробуйте изменить запрос таким образом, чтобы фильтр задавался для значений имен студентов.

• Сохраните сделанные изменения на форме и закройте ее.

Задание 7. Создайте поиск значения в форме с помощью команд VBA.

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

Порядок работы:

• Проделайте действия аналогичные описанным выше, до момента задания программного кода для обработки события нажатия кнопки. В качестве подписи для текстового поля задайте: «Условие для поиска студента по фамилии». Текстовому полю и кнопке задайте имена txtFind и cmbFind соответственно.

• Для обработки события нажатие кнопки введите программный код:

Private Sub cmbFind_Click()

Dim strFind As String

Dim byt As Variant

TxtFind.SetFocus

strFind = txtFind.Text

Form.Recordset.FindFirst «Список.Фамилия = '» + strFind + «'»