Разработка проекта базы данных MS Access

СОДЕРЖАНИЕ

 

Введение

1.Лабораторная работа №1. 3

Разработка проекта базы данных (MS Access)

2.Лабораторная работа №2. 12

Модификация проекта базы данных(MS Access)

3.Лабораторная работа №3. 23

Экспорт,импорт и присоединение данных (MSAccess)

4.Лабораторная работа №4. 34

Запросы выборки данных(MS Access)

5.Лабораторная работа №5. 44

Запросы на изменение данных (MS Access)

6.Лабораторная работа №6. 51

Создание запросов на языке SQL(MS Access)

7.Лабораторная работа №7. 74

Приобретение навыков работы в формах(MS Access)

8.Лабораторная работа №8. 90

Обучение построения отчетов (MS Access)

9.Лабораторная работа №9. 102

Автоматизация приложения использованием макросов(MS Access)

10.Лабораторная работа №10. 119

Использование модулей Access Basic для автоматизации управления

11.Лабораторная работа №11. 146

Создание пользовательской панели инструментов,

строки меню. Создание панели управления приложением (MS Access)

12.Лабораторная работа №12. 158

Основы работы с данными в локальной сети. Транзакции(MSAccess)

 

 

ЛАБОРАТОРНАЯ РАБОТА №1

РАЗРАБОТКА ПРОЕКТА БАЗЫ ДАННЫХ

Тема: разработка проекта базы данных.

Цель: получить навыки генерации проекта базы данных MS ACCESS.

 

Постановка задачи

1. Создать таблицы разрабатываемой базы.

2. Описать свойства полей, входящих в таблицы и самих таблиц.

3. Установить связи между таблицами проекта и описать их характер.

4. Создать первичные ключи полей таблиц и индексы.

 

Пример решения

 

2.1. Создание проекта базы данных STUD.

 

Войдите в Microsoft ACCESS, щелкнув в Windows на иконке Microsoft Access.

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

Примечание (для версии ACCESS 2.0):

Откроем меню File, содержащее основные команды управления базой данных, команды настроек и списком ранее открываемых баз. Для создания новой базы выберем пункт "New Database", и в появившемся диалоговом окне определим имя создаваемой базы как STUD и место на диске, где она будет храниться (диск, каталоги). После нажатия кнопки "ОК" на экране появится окно базы данных. содержащее информацию обо всех объектах созданной базы.

2.2. Создание таблиц в режиме конструктора.

 

Для создания таблицы необходимо при активном окне базы данных щелкнуть по корешку "Таблицы", а затем по кнопке "Создать". Access откроет диалоговое окно с запросом о методе создания таблицы, где выберем пункт "Конструктор" и нажмем “Ok”.

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

 

Заполним описания полей для таблицы "Преподаватели" данными таблицы1:

 

Таблица 1.

 

Поле "Тип данных" представляет собой скрытое меню, вызываемое щелчком мыши на кнопке "" на строке, для которой выбираем тип. О типах данных в Access см. прил.1.

 

2.3. Установка свойств полей таблицы "Преподаватели".

 

Каждое поле таблицы в Access имеет определенный набор свойств. Access автоматически устанавливает размер поля. Размер часто бывает больше, чем надо. Поэтому установим для полей созданной таблицы Преподаватели, следующие свойства:

 

Имя поля	Размер поля
Номер_П	Целое
Имя
Отчество
Фамилия
Кафедра
Должность
Дом_адрес
Зарплата	Денежный

 

2.4. Создание таблиц в режиме мастера таблиц.

 

Появившееся окно мастера содержит список шаблонов таблиц (слева), список полей, входящих в эти таблицы (середина окна) и итоговый список полей,

 

которые будет содержать наша таблица (справа).

Для включения поля в нашу таблицу необходимо выбрать это поле в списке и щелкнуть на кнопке ">". Для переноса всех полей используется кнопка ">>"

Выберем шаблон таблицы "Студенты", в нем выберем и перенесем поля: "Фамилия", "Имя", "Отчество", "Факультет", "Адрес", "Заметки". Нажмем кнопку "Далее".

В появившемся окне с запросом нового имени для таблицы введем "Студенты". Щелкнем по кнопке "Ок".

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

Если он находит такие поля - то предполагает, что между этими таблицами должна существовать связь типа "один-ко-многим". С этим вариантом можно согласиться, либо использовать в качестве ключа другое поле, список которых появится после выбора строки "Самостоятельное определение пользователем", и нажатия кнопки "Далее".

Связь между таблицами мы установим позже. Нажимаем кнопку "Далее" и на запрос о дальнейших действиях выбираем первый вариант: "Изменение структуры таблицы". Остальные варианты предназначены для ввода данных в созданную таблицу в табличном варианте, или через "мастер форм".

Нажмем кнопку "Готово" для завершения создания таблицы "Студенты". Откроется окно конструктора таблиц, в котором введем дополнительные поля приведенные в Таблице2 и введем новые размеры полей.

 

Таблица2

 

Свойства полей:

 

Имя поля	Размер поля
Номер_С	Длинное целое
Фамилия
Имя
Отчество
Факультет
Группа
Специальность
Год_рожд	Краткий формат даты
Адрес

 

Создайте самостоятельно таблицу “Результаты”

Таблица3.

 

2.5. Создание ограничений на значения для полей.

 

Свойство “Условие на значение” может содержать ограничения на значение, которое может принимать поле. Так, при необходимости ограничить значение оценки для результатов диапазоном 0..5 баллов в этом свойстве для полей "Оценка_Т", “Оценка_П”, “Оценка_Л” введем:

 

Between 0 And 5

Сложные выражения для условий можно также строить при помощи т.н. "Построителя выражений", который вызывается кнопкой расположенной справа от описания поля "Условие на значение", или на панели инструментов.

 

2.6. Создание первичных ключей для таблиц.

 

Каждая таблица в реляционной базе данных должна иметь первичный ключ. Для создания первичного ключа таблицы в Access щелкните по области маркировки, расположенной слева от имени поля в окне таблицы в режиме конструктора. Если необходимо определить несколько полей в качестве первичного ключа - нажмите клавишу CTRL и, удерживая ее нажатой, щелкнете по области маркировки необходимых полей.

Опишите первичный ключ для созданных таблиц по полю "Номер_С" в таблице “Студенты”.

 

2.7. Установление связей между таблицами.

 

Установим связи между таблицами "Преподаватели", "Студенты" и

"Результаты”.

Вернемся в окно базы данных, щелкнув на нем мышкой. Откроем окно схемы данных, выполнив в меню "Сервис" команду ”Схема данных” или щелкнув по кнопке "Схема данных" на панели инструментов.

В появившемся окне (если мы впервые определили для данной таблицы связи) выберем обе таблицы, щелкая на них и кнопке "Add". Нажмем кнопку "Close".

Access откроет окно "Схема данных", содержащее разделенные списки полей для каждой таблицы. Для определения связи между полями "Номер_П" таблиц"Преподаватели" и "Расписание" щелкнем мышкой на поле "Номер_П" таблицы "Преподаватели"; не отпуская кнопки мышки перетащим это поле на строку "Номер_П" в описании таблицы "Расписание", после чего отпустим кнопку.

Access откроет окно связи. Установим в нем флажок "Обеспечение целостности данных", что воспрепятствует созданию записи результаты с несуществующим преподавателем и не позволит удалять записи в таблице "Преподаватели", для которых остались подчиненные данные в "Результатах".

После выбора этого флажка активизируются опции "Каскадное обновление связанных полей" и "Каскадное удаление связанных записей".

Выбор первой опции приведет к изменению значений подчиненных ключей в дочерних таблицах при изменении первичного ключа таблицы основной.

При выборе второй опции Access удалит все дочерние строки при удалении родительской строки. Установим обе эти опции.

Также активизируется переключатель "Один к..", позволяющий задать тип связи: "один-к-одному" или "один-ко-многим". Установим тип "Много".

Кнопка "Объединение" позволяет задать тип объединения таблиц. Типы объединения будут рассмотрены ниже. После внесения требуемых установок нажмем кнопку "Создать" для завершения процесса создания связи.

Access проведет линию между этими двумя таблицами - линию связи. При установке опции "целостность данных" на линии со стороны родительской таблицы

Access ставит "1", а с другой - символ "бесконечность". Эти знаки указывают на связь типа "один-ко-многим".

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

Удаляется связь путем выделения ее щелчком мышки и последующим нажатием клавиши клавиатуры "Del".

После закрытия окна "Схема данных" Access запросит нас о необходимости сохранения внесенных изменений. Нажмем кнопку "Yes". Описание связей будет сохранено.

 

2.8. Создание индексов.

 

Access позволяет создавать индексы двух типов: простые и составные.

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

Создадим простой индекс по полю "Номер_П" для таблицы

"Преподаватели". Откроем таблицу "Преподаватели" в режиме конструктора, выберем поле "Номер_П", и в строке свойства "Индексированное поле" щелкнем мышкой на значке "" в правом углу строки. Появится список, содержащий типы создаваемых индексов.

Вариант "Да. Совпадения не допускаются" создает индекс, хранящий только уникальное значение данного поля.

Вариант "Да. Допускаются совпадения" разрешает наличие повторяющихся значений для индексируемого поля.

Вариант “Нет” автоматически устанавливается для первичного ключа таблицы.

Выберем вариант "Да. Совпадения не допускаются"

Для задания составного индекса по полям "Фамилия", "Имя" и "Отчество" по таблице "Студенты", не выходя из режима конструктора, сделаем активной таблицу "Студенты", щелкнув по ней мышкой. Затем выберем в меню "Вид" команду "Индекс..". Открывшееся окно описаний индексов содержит первичный ключ для данной таблицы по полю "Номер_С".

Для построения составного индекса помещаем курсор вниз на пустую строку, и вводим в столбце "Индекс" имя индекса "Составной для ФИО". В столбце "Имя поля" выбираем из всплывающего списка имя "Фамилия". Для добавления в этот индекс нового поля помещаем курсор на следующую строку и таким же образом указываем имя нужного поля. Введем, таким образом, поля "Имя" и "Отчество".

Для удаления существующего индекса выделяется строка описания индекса щелчком мыши и нажимается клавиша "Del".

На использование индексов налагаются некоторые ограничения. Так, Access использует индекс для поиска данных, если в условие поиска включается последовательность полей, начиная с первого поля индекса. Второе ограничение - только последнее условие поиска с использованием индекса может быть неравенством. При наличии нескольких неравенств - индексы не подключаются.

Примечание для версии ACCESS 2.0:

Сохраним созданный проект базы данных, вернувшись в окно базы данных, и выполнив команду "Save" меню "File". В появившемся диалоговом окне введем имя сохраняемого проекта: "stud.mdb".

Для версии ACCESS 97 созданный проект сохраняется автоматически.

Результаты работы

В результате выполнения лабораторной работы создан проект базы данных STUD.

Приложение 1: Типы данных в ACCESS.

Текстовый

Алфавитно-цифровые данные

До 255 байт

 

 

ЛАБОРАТОРНАЯ РАБОТА №2

МОДИФИКАЦИЯ ПРОЕКТА БАЗЫ ДАННЫХ

Тема: модификация проекта базы данных.

Цель: выработать навыки по модификации проекта базы данных СУБД ACCESS 97.

 

Постановка задачи

1. Научиться изменять описания полей и свойств данных созданных таблиц.

2. Модифицировать описания объектов разрабатываемой базы.

3. Изменить структуру связей между таблицами.

 

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

Access предоставляет широкий круг возможностей по изменению созданного проекта базы данных. Часть из них мы используем, модифицируя первоначальный проект базы STUD.

 

Пример решения

 

Некоторые действия над проектом могут привести к потере данных, логическим ошибкам, сбоям Access, поэтому желательно перед изменениями базы сделать ее резервную копию. Можно сохранить последовательно все объекты базы, но наименее трудоемким является путь копирования всего проекта средствами Dos или Windows.

Access все проекты хранит в отдельных файлах с расширением *.MDB". В эти файлы входят описания всех структур, относящихся к одной базе: описания

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

Сделаем копию проекта STUD, переключившись средствами Windows в окно диспетчера программ ("Program Manager"); запустим диспетчер файлов двойным щелчком на иконке "File Manager" в группе "Main". В диспетчере файлов найдем хранящийся файл STUD.mdb, выделим его, щелкнув на нем мышкой. В меню "File" выполним команду "Copy to..", и в появившемся окне введем имя будущего файла STUD_1.MDB. После нажатия кнопки "Ок" мы получим копию базы STUD, с которой и будем производить дальнейшие действия.

 

2.1. Модификация таблиц базы данных.

 

Рассмотрим возможность модификации проекта базы данных применительно к действиям над полями таблиц. В таблицы Access возможна вставка новых полей, их переименование, копирование, удаление, перемещение; изменение типов и размеров полей.

Запустим MS Access и откроем базу STUD_1.

 

2.1.1. Добавление нового поля в таблицу.

 

Откроем таблицу "Преподаватели" в режиме конструктора. Добавим в нее новое поле "Стаж", где будет храниться стаж работы преподавателя на данной кафедре.

Выделим строку таблицы "Должность". Для этого щелкнем по маркеру строки − серому прямоугольнику у левого края имени поля. Строка над которой будет вставлена новая выделится черным цветом Выполним команду Вставить строку("Insert row") в меню “Вставка” ("Edit"), или нажмем на клавиатуре клавишу "Ins". Над выделенной появится новая, пустая строка. Введем в ней:

Имя поля − "Стаж";

Тип данных − "Числовой";

Описание − "Стаж работы преподавателя на кафедре";

 

В области свойств поля введем для свойства “Размер поля” ("Field size") тип Байт ("Byte").

 

2.1.2. Копирование полей в таблице.

 

При наличии в таблице нескольких однотипных полей, или полей со сходными свойствами, рационально не вводить описания всех полей, а описать одно поле, а затем продублировать его, подставляя для поля только индивидуальные характеристики (такие как имя, свойства, маски ввода, наличие индексов и т.д.). Таким образом можно формировать поля "Имя" и "Отчество" как аналоги поля "Фамилия", адреса "Домашний" и "Рабочий" и т.д.

Создадим поля "Телефон_дом" и "Телефон_раб". Создаем поле "Телефон_дом" как было рассмотрено в ЛР1 с характеристиками:

Имя поля − "Телефон_дом";

Тип данных − Числовой;

Описание − "Домашний телефон".

В области свойств поля введем;

Размер поля − Целое;

 

Выделим эту строку целиком, щелкнув по области маркировки строки слева от ее названия. Скопируем эту строку в буфер обмена при помощи команды "Копировать" меню "Правка". Переместим курсор на строку, следующую за вставляемой (после "Телефон домашний"). Вставим пустую строку, выполнив команду "Вставить строку" меню "Вставка". Затем выделим созданную строку, щелкнув на области ее маркировки и выполним команду "Вставить" меню "Правка". Описание из буфера обмена будет вставлено в созданную строку.

Модифицируем название созданного поля на "Телефон_раб", щелкнув в данной строке на столбец "Поле" и введя в нем "Телефон _раб". Изменим также свойство "Описание" на "Телефон рабочий".

 

2.5. Создание масок ввода значений.

 

Для упрощения ввода форматированного текста может применяться маска ввода значений полей. Для задания номера рабочего телефона в формате:

[цифра][цифра]−[цифра][цифра]−[цифра][цифра]

 

для поля "Телефон_раб" свойство "Маска ввода" установим в 99-99-99

 

2.1.3. Удаление описания поля.

 

Для удаления из таблицы поля, выделим его рассмотренным выше способом, и в меню "Правка" выполним команду "Вырезать", или нажав кнопку "Del" на клавиатуре, или нажав кнопку “Вырезать” на панели инструментов. После предупреждения Access о том, что данные из этого поля могут быть потеряны, производит удаление.

После ошибочного удаления поля, строки или таблицы можно отменить удаление следующими способами:

−при появлении предупреждения о том, что данная строка, поле, таблица будет удалена, и хранимые данные могут быть потеряны − нажать кнопку "Cancel";

− в меню "Правка" выполняется команда "Отмена", или на кнопку панели инструментов "Отмена". Access может восстановить данные только в том случае, если после удаления не были произведены другие действия над базой;

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

2.1.4. Модификация свойств данных.

 

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

Точность некоторых числовых полей может оказаться недостаточной или избыточной, и было бы желательно изменить число десятичных разрядов. Некоторые поля удобнее хранить, используя другой тип данных, или в другом формате.

Access поддерживает возможность преобразования любых своих типов данных, за исключением OLE-объектов. Их нельзя преобразовать ни в какой тип из-за специфичности хранимой в этом поле информации.

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

Access позволяет изменять длину полей типа "Символьный" и "Числовой". Длину символьного поля можно менять в пределах от 1 до 255 символов (0 байт предназначен для индикации длинны строки, как в Паскале). При вводе данных попытки ввода символьных строк большего размера, чем зарезервировано под поле, приводит к предупреждению. Если мы урезаем существующее поле до меньшей длинны, часть данных может быть утеряна − текстовые значения будут усечены справа до нового размера поля.

Размер числового поля меняется от одного байта до 8. При уменьшении размера числового поля может возникнуть ошибки переполнения, поэтому желательно быть уверенным, что хранимые данные не содержат большее число цифр, чем в новом варианте.

Изменение размеров полей производится путем редактирования свойства "размер поля" для изменяемой строки.

Изменим тип данных и длину поля "Телефон_дом" и "Телефон_раб" на "Символьный" длинны 10 для более наглядного представления телефонных номеров, например "56-38-45" вместо "563845". Можно также еще увеличить число разрядов до 15 для хранения кода города (формат "(9999)99-99-99"). Введя маску ввода и представления строки можно уменьшить размер поля до 11 при той же информативности.

Изменение форматов представления данных и маски ввода можно произвести, редактируя соответствующие свойства выбранных полей.

Access анализирует преобразования типов данных и длинны полей, и в случае потери или неправильной трактовки преобразуемых данных выдает описание ошибки и запрашивает свои дальнейшие действия. Выбор варианта "Ок" продолжит начатые преобразования (возможно, с неверным исходом) или же выбрав кнопку "Отменаl" можно отказаться от производимых изменений.

 

2.2. Модификация структуры базы данных.

 

Модификация самой структуры БД включает в себя возможности расщепления, объединения таблиц; изменения первичных ключей и структуры связей между таблицами.

 

2.2.1. Изменение первичных ключей базы данных.

 

При работе с базой может возникнуть необходимость изменения первичных ключей некоторых таблиц. Так, если бы мы в таблице "Преподаватели" определили первичный ключ по полю "Фамилия", он бы не всегда однозначно идентифицировал записи в таблице, т.к. в таблице может быть несколько преподавателей с фамилиями "Петров", "Сидоров" и т.д.

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

Переопределение первичного ключа производится или сбросом определения ключа в описании индексов (команда "Индекс" меню "Вид" выводит список индексов БД, в ней активизируем строку "Первичный ключ" для поля, являющегося первичным ключом, и нажимаем кнопку "Del" клавиатуры); а затем установив новый первичный ключ (командой "определить ключ" меню "Правка"). Либо же активизацией поля будущего первичного ключа, и щелчком на кнопке "Первичный ключ" панели инструментов.

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

 

2.2.2. Модификация структуры связей базы данных.

 

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

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

Из видов модификации базы данных иногда может потребоваться расщепление одной таблицы на несколько, или же объединение группы таблиц в одну. рассмотрим их применение по отношению к базе STUD.

 

2.2.3. Расщепление таблиц.

 

Расщепление таблиц может потребоваться в том случае, если какая-то из таблиц проекта содержит редко используемую группу полей. Например, в

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

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

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

Можно использовать присоединение данных через сеть, ограничив доступ пользователей к данным средствами сетевой ОС.

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

Создадим копию проекта базы данных, т.к. расщепление таблицы будет нести демонстрационный характер, и в следующих лабораторных работах мы будем пользоваться сохраненным вариантом. Для создания копии базы средствами Windows или Dos скопируем закрытый проект базы данных (файл STUD.mdb) в другой каталог.

Простейший метод расщепления таблицы - создание ее копии, удаление из обеих таблиц лишних полей; настройка связей − и занесение модифицированных таблиц обратно в проект.

 

Рассмотрим общий алгоритм расщепления таблиц:

1. Создаем 2 копии таблицы.

2. Обеим копиям таблиц даются уникальные имена.

3. Из обеих таблиц удаляются лишние поля.

4.В окне "Свойства таблиц" удаляются все условия на значение, содержащие в себе ссылки на удаленные поля.

5. В окне "Индексы" удаляются все индексы и ключи, построенные на удаленных полях.

6. При необходимости генерируются первичные ключи для созданных таблиц (возможно введение нового поля типа "Счетчик").

7. В окне "Схема данных" (команда "Схема данных" меню "Сервис"), выбирается режим "Все связи" нажатием соответствующей кнопки на панели инструментов.

8. Удаляются все связи между таблицей − оригиналом и другими таблицами (щелкая по линии связи таблиц для активизации связи и нажимается клавиша "Del" на клавиатуре).

9. В схему данных вводятся таблицы − копии (щелкнув на кнопке "Добавить таблицу" панели инструментов).

10. Устанавливаются связи таблиц − копий с другими таблицами.

11. Возможно, удаляется таблица − оригинал.

12. Сохраняется структура проекта БД.

 

Применительно к таблице "Преподаватели":

−создадим таблицы "Преподаватели_личное" с полями "Зарплата", "Адрес_дом", "Телефон_дом",”Номер_П”; и "Преподаватели_служебное", со всеми оставшимися полями плюс "Номер_П" для связи;

−удаляем индексы на таблице "Преподаватели" и описание первичного ключа по полю "Номер_П";

−В окне схемы данных устанавливаем связи: "Преподаватели_служ- ебное.Номер_П" с "Занятия.Номер_П" типа “один-ко-многим” и "Преподаватели_служебное.Номер_П" с "Преподаватели_личное.Номер_П" типа “один-к-одному”;

−удаляем таблицу "Преподаватели";

−сохраняем проект под именем STUD_2.mdb.

 

 

2.2.4. Объединение таблиц.

 

При работе с базой данных может возникнуть необходимость объединения нескольких таблиц.

Объединение таблиц повышает производительность системы, когда:

−большинство обращений к данным производится в запросах одновременно к двум или более таблицам, но по раздельности они используются редко;

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

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

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

Запросы будут рассмотрены в следующих ЛР, а пока мы рассмотрим общий алгоритм объединения таблиц:

1. В окне базы данных активизируется одна из таблиц, на которых будет построен запрос;

2. Открывается окно конструктора запросов.

3. Выполняем команду создания по результатам запроса новой таблицы (команда "Создание таблицы" меню "Запрос" выводит окно ввода имени создаваемой таблицы);

4. В бланке QBE перечисляем поля, необходимые в генерируемой таблице;

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

6. Выполняем команду "Выполнить" меню "Запрос";

7. Устанавливаем связи полученной таблицы с остальной базой;

8. Разрываем связи исходных таблиц и при необходимости их удаляем.

Результат выполнения работы

 

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

ЛАБОРАТОРНАЯ РАБОТА №3

ЭКСПОРТ,ИМПОРТ И ПРИСОЕДИНЕНИЕ ДАННЫХ

Тема: Экспорт, импорт и присоединение данных в MS Access 97.

Цель: Получить навыки доступа из Access к данным других приложений − обработки данных.

 

Постановка задачи

2. Экспортировать информацию из БД для обработки в других приложениях. 3. Выполнить присоединение внешних таблиц к разрабатываемой базе данных.  

Пример решения

 

2.1. Импорт данных.

 

Access поддерживает две возможности использования данных других баз −импорт и присоединение.

Импорт подразумевает полную конвертацию существующей таблицы из другой среды в формат MS Access. Все модификации импортированной таблицы автоматически не переносятся в импортированную вами таблицу. Т.е. импортировать данные имеет смысл в тех случаях, если вы :

− полностью переходите на работу с Access и переносите все свои данные в эту среду, не обращаясь к старому приложению;

− используете информацию, редко модифицируемую пользователем: словари, справочники и т.д.;

− добиваетесь максимальной производительности по обработке данных в смешанных средах (совместного использования данных одновременно несколькими средами БД), т.к. Access быстрее работает со своими форматами в своей среде.

В предыдущих лабораторных работах мы уже создали проект базы данных STUD, поэтому нам остается только заполнить созданные таблицы данными.

 

2.1.1. Импорт таблиц баз данных.

 

Импортируем данные таблицы Access "Преподаватели" из базы STUD_1.mdb, содержащую информацию о преподавателях, в одноименную таблицу Access.

Откроем базу данных STUD и активизируем окно базы данных. Для активизации окна импорта в меню “Файл” выберем “Внешние данные ” > “Импорт” или щелкнем по кнопке “импорт ” на панели инструментов.

ACCESS выведет диалоговое окно “Импорт”. Найдем файл STUD_1.mdb базы и щелкнем по кнопке “Импорт”.

 

В следующем окне “Импорт объектов ” выберем для импорта объект, и щелкнуть по кнопке “OK”

Примечание: (для версии ACCESS 2.0)

Откроем базу данных STUD и активизируем окно базы данных. Для активизации окна импорта в меню "File" выполним команду "Import" или щелкнем по кнопке "Import" на панели инструментов.

В появившемся диалоговом окне в списке форматов выберем "Microsoft Access" и щелкнем по кнопке "Ок".

Access выведет диалоговое окно выбора файла. Найдем файл "STUD_1.mdb" базы и щелкнем по кнопке "Ок". Если искомый файл находится на сетевом сервере, то его путь указывается через кнопку "Network..", где нужно указать сетевое устройство и путь к базе.

В следующем окне определения объекта для импорта выберем в списке "Object Type" пункт "Tables" и в списке выбора "Objecs in STUD_1.MDB" щелкнем на таблице "Преподаватели", справа выберем переключатель "Structure and Data", после чего нажмем кнопку "Import".Появится окно с информацией о

завершении импорта. После нажатия "Ок" мы вернемся в окно "Import objects", где нажмем кнопку "Close".

Если импорт выполнен успешно, то вернувшись в окно базы данных мы увидим описание новой таблицы "Преподаватели1".

 

Для переноса данных в существующую таблицу "Преподаватели" активизируем щелчком мыши таблицу "Преподаватели1" (описание таблицы выведется на синем фоне), выполним команду “Копировать”("Copy") меню “Правка” ("Edit") для переноса содержимого таблицы в буфер, а затем команду “Вставить” ("Paste") в том-же меню.

Появится окно “Вставка таблицы” ("Paste Table As"), где в поле “Имя таблицы” ("Table Name") введем имя "Преподаватели", и в поле выбора “Параметры вставки” ("Paste Options") выберем “Добавление данных в таблицу” ("Append Data to Existing Table"), добавляющий данные в существующую таблицу. Предыдущие пункты позволяют копировать только структуру таблицы, или структуру с данными.

Теперь таблица "Преподаватели" содержит данные, импортированные из другой базы Access. Удалим таблицу "Преподаватели1" любым из рассмотренных ранее способов. Подобным образом производится импорт данных из баз данных FoxPro, dBase, Paradox и Btrieve. ".

 

2.1.2. Импорт данных из электронной таблицы Excel.

 

MS Access поддерживает импорт/экспорт данных с электронными таблицами Lotus 1-2-3 и MS Access. Можно импортировать как таблицы целиком, так и их отдельные фрагменты.

При импорте электронных таблиц можно либо дописать данные в уже существующую таблицу, либо сформировать новую. В первом случае необходимо либо сформировать имена столбцов эл. таблицы, идентичные именам полей таблицы Access, или расположить столбцы эл. таблицы в той же последовательности, что и поля таблицы БД, в которую импортируются данные.

Если мы будем создавать на импортируемых данных новую таблицу, то желательно ввести имена столбцов в первую строку импортируемой электронной таблицы, в противном случае Access назначит в качестве полей последовательные числа, начиная с 1 (их потом можно будет изменить, открыв таблицу в режиме конструктора).

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

− символьные данные переносятся в текстовые поля длинной 255 символов;

− числовые – в “Числовой”(” Numeric”)с размером 8 байт и "с плавающей точкой";

− числовые данные в денежном формате − в тип “Денежный” ("Currency");

− значения даты и времени − в тип “Дата/Время”("Date/Time").

 

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

Выполним импорт данных в таблицу БД "Студенты" из электронной таблицы Excel "Student".

Находясь в окне базы данных STUD, выполним команду "Внешние данные" ”Импорт” в меню "Файл".

Открывшееся окно "Импорт" содержит список приложений, поддерживаемых Access для импорта. Выберем строку "Excel" и щелкнем по кнопке "Ок".

Access откроет диалоговое окно "Выбор файла". Выберем файл "Student.xls" и нажмем кнопку "Импорт".

В открывшемся окне "Параметры импорта" сбросим флажок "Firs row contains field names", позволяющий использовать первую строку эл.таблиц как имена полей таблицы БД (т.к. таблица "Студенты" уже содержит описания полей).

Выберем переключатель "Append to existing table", для добавления данных в уже существующую таблицу, и выберем в списке справа от переключателя имя таблицы - "Студенты".

Если необходимо импортировать не всю эл. таблицу, а только ее часть, то в этом же окне можно установить диапазон импортируемых ячеек: в поле "Range" вводится диапазон (например "A1:D15" или "A1..D15").

Для запуска процедуры импорта нажмем кнопку "Ок".

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

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

Подобным образом производится и импорт данных из электронной таблицы Lotus 1-2-3.

2.1.3. Импорт текстовых файлов.

 

В таблицы Access можно переносить данные и из текстовых файлов. Поля в таких файлах могут быть разделены несколькими способами, но большинство из них можно преобразовать в формат таблиц Access не внося изменений в сам текстовый файл.

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

Access может импортировать текстовые файлы, в которых поля располагаются в фиксированных позициях внутри каждой записи файла (т.н. форматированный текст). Необходимо лишь указать положение каждого поля внутри строки, и его длину.

Импортируем данные о результатах из текстового файла с полями фиксированной ширины в таблицу "Результаты".

Находясь в окне базы данных STUD выполняем команду "Внешние данные".> “Импорт” в меню "Файл" и в диалоговом окне списка форматов выберем “Текст Фиксированная длинна". Щелкнем по кнопке "Ок".

Выберем текстовый файл в появившемся окне выбора файлов и щелкнем по кнопке "Импорт". Access выведет диалоговое окно "Импорт текста". В пункте (Выберете формат, описывающий поля наилучшим образом) выбрать один из пунктов, который вам подходит:

− с разделителями − поля разделяются запятыми или табуляцией;

− фиксированная ширина полей − интервалы заполнены пробелами;

Нажмем “Далее”. После проделанных шагов “Данные необходимо сохранить”:

− в существующую таблицу;

− новую таблицу;

Если мы импортируем данные в новую таблицу, выберем ‘”Дополнительно” и определим:

− имени поля - (Field Name);

− типе данных - (Datatype).

Нажмем “Готово”. В пункте “Импортировать в таблицу” указать имя таблицы.

После этого Access откроет диалоговое окно с сообщением о результатах выполнения импорта. При возникновении ошибки проверим установленные значения в настройках, а если импорт прошел успешно - выберем "Ок".

Также Access позволяет импортировать текстовые файлы с разделителями, в которых поля отделяются друг от друга символами запятой, табуляции или пробела.

Для импорта или экспорта необходимо лишь указать Access, какой из данных символов разделяет поля данных, и какой символ является ограничителем текста (если данный текст содержит символы запятой или пробела). Ограничителями текста обычно являются кавычки или апостроф, причем можно использовать для любого файла только один тип ограничителей.

Импортируем из текстового файла stud.txt данные для таблицы "Студенты".

Выйдя в окно БД и щелкнув по корешку "Таблицы", мы можем увидеть созданную таблицу "Студенты", содержащую информацию о студентах 1 курса.

После выполнения импорта из любых форматов может потребоваться некоторая модификация описания таблиц БД. Так, под текстовые поля Access выделяет поля максимально поддерживаемой длинны 255 байт, что не всегда является необходимым. В конструкторе таблиц эту длину можно уменьшить до требуемой. То же самое относится и к полям типа "Числовой" - не всегда есть необходимость в выделяемых 8 разрядах.

Возможно, придется произвести преобразования некоторых типов данных. Но нужно всегда учитывать ограничения, накладываемые на эти преобразования (см.ЛР2).

 

2.2. Экспорт данных.

 

Действия по экспорту объектов баз данных Access очень похож на действия по импорту. Рассмотрим общий алгоритм при выполнении процедуры экспорта:

1. Открывается требуемая база и вызывается окно базы данных.

2. Выполняется команда "Экспорт" меню "Файл".

3. В списке форматов выбирается , требуемый и нажимается "Ок".

4. В открывшемся диалоговом окне "Выбор объекта MS Access" выбираем тип объекта, а затем и сам объект для экспорта. Для таблиц можно экспортировать только структуру (определение таблиц) или структуру и данные. Объектами экспорта также могут являться запросы к БД. После выбора объекта нажимаем кнопку "Ок".

5. В открывшемся окне выбираем путь файла, в который будем экспортировать объект (при экспорте в другую базу MS Access после этого появится окно для ввода имени объекта в базе данных - получателе);

 

После нажатия кнопки "Ок" начнется процедура экспорта, результат которой можно проверить после ее завершения.

 

Некоторые приложения, в которые возможен экспорт данных MS Access, не поддерживают 64-разрядные имена полей. При переносе эти имена будут усекаться, что может привести к их дублированию. Рекомендуется или в конструкторе таблиц убедиться, что имена полей после урезания не будут повторяться; или же копировать таблицу, переименовать поля в копии, и экспортировать эту копию в другое приложение.

Самостоятельно экспортируйте таблицу "Студенты" в формат Excel, а "Преподаватели" в формат Paradox.

Экспорт в текстовый файл полностью аналогичен импорту: производится настройка спецификации экспорта (либо с разделителями, либо с фиксированной длинной поля), затем запускается процедура экспорта.

Выполните экспорт таблицы "Результаты”. Затем откройте в редакторе "Write" (в Windows в группе "Аксессуары") полученный файл и выведите его на принтер в качестве отчета по лабораторной работе.

Access также содержит Мастер по слиянию с MS Word. Он позволяет внедрить данные из базы Access в документы, созданные в редакторе Word.

Открывается требуемая база, выбираем таблицу или запрос для слияния и щелкаем по кнопке "Слияние с MS Word" панели инструментов. В диалоговом окне выбирается работа с существующим или создаваемым документом. Выбираем требуемый вариант, и нажимаем кнопку "Ок". Мастер запускает Word и устанавливает связь между документами. Далее внедренные объекты можно обрабатывать средствами текстового редактора.

 

2.3. Присоединение внешних данных.

 

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

При этом эффективность обработки данных резко падает, особенно если эти данные находятся в сети и используются несколькими пользователями. Но, с другой стороны, у присоединения тоже есть свои достоинства:

− можно поддерживать массивы данных размерами больше максимально допустимого Access предела в 1Гб;

− если данные этого файла часто изменяются пользователем;

 

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

Присоединение данных из других баз также является простой процедурой: кнопкой "Связь" на панели инструментов или при помощи команды "Внешние данные" >> “Связь” меню "Файл" вызовем окно присоединения, в котором выбираем тип источника данных. Источниками для присоединения могут являться файлы FoxPro, Paradox, dBase, или таблицы из другой базы Access.

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

таблиц FoxPro и dBase Access потребует ввести имена индексных файлов, после чего выдает то же информационное окно.

 

2.3.1. Присоединение таблицы Результаты из БД филиала.

 

База "Fil_1.MDB" содержит данные о результатах, прошедших семестров. Для присоединения этих данных для обработки в меню "Файл" выполним команду "Внешние данные" >> “Связь” (или щелкнув по одноименной кнопке на панели инструментов).

Появится окно для ввода типа источника данных, в котором выберем "Microsoft Access" и нажмем кнопку "Ok". В открывшемся окне выбора файла найдем базу "Fil_1.MDB" и вызовем ее описание, нажав кнопку "Ok". Следующее окно выводит описания всех таблиц базы филиала. Выберем таблицу "Результаты" и нажмем кнопку "Связь".

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

 

Результаты работы

В результате выполнения данной лабораторной работы мы получили таблицы базы "STUD", заполненные данными, экспортировали эти данные в среду Excel, в СУБД Paradox и для дальнейшего формирования отчета в формат Word. Также было выполнено присоединение данных из другой базы Access.

По окончании лабораторной работы удалите описания присоединенных таблиц.

ЛАБОРАТОРНАЯ РАБОТА №4

ЗАПРОСЫ ВЫБОРКИ ДАННЫХ

Тема: Запросы выборки данных в MS Access 97.

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

 

Постановка задачи

1. Создание запросов простых типов при помощи бланка QBE.

2. Создание групповых запросов.

3. Создание перекрестного запроса.

4. Создание запросов при помощи мастера.

 

Пример решения

2.1. Использование окна запроса.

2.1.1. Создание простого запроса.

Откроем базу данных STUD, как было рассмотрено в ЛР1. Создадим запрос, выводящий информацию о студентах, у которых имеются задолженности.

Для создания запроса активизируем окно базы данных (см. ЛР1). После этого щелкнем по корешку "Запрос" и кнопке "Создать". В появившемся диалоговом окне "Создание запроса" выберите кнопку "Конструктор". Access откроет диалоговое окно "Добавление таблицы", позволяющее выбрать базовые таблицы и запросы для создаваемого запроса. Выберите таблицы "Результаты" и "Cтуденты", нажимая кнопку "Добавить", после чего закройте это окно кнопкой "Закрыть".

Открывшееся окно конструктора запросов состоит из двух частей: верхняя содержит списки полей выбранных таблиц, а нижняя - бланк QBE для создания запроса. Каждый столбец бланка описывает одно поле, участвующее в запросе.

Включение поля в запрос производится перетаскиванием его из списка полей таблиц (расположенного в верхней части экрана) в нужный столбец бланка

QBE при помощи мышки. Включение всех полей таблицы происходит перетаскиванием символа "*", находящегося вверху списка полей данной таблицы в верхней части экрана.

Включите в запрос поля таблицы "Результаты":

Предмет

    перетащив их в бланк QBE. Рассмотренным выше методом перетащите из таблицы “Студенты” поле Фамилия.   Запрос “Задолженность” будет иметь вид, как показано на рисунке.

Результаты работы

  Приложение 1. Групповые операции в MS ACCESS.   Sum Вычисляет сумму всех значений заданного поля в каждой группе Только для числовых и…

ЛАБОРАТОРНАЯ РАБОТА № 5

ЗАПРОСЫ НА ИЗМЕНЕНИЕ ДАННЫХ

Тема: запросы на изменение данных в MS Acess 97.

Цель: научиться создавать запросы модификации данных в Access при помощи бланка QBE.

 

Постановка задачи

1. Создание запросов создания таблиц.

2. Создание запроса модификации данных таблицы.

3. Создание запроса на добавление данных в существующую таблицу.

4. Создание запросов на удаление данных из таблицы.

 

Пример решения

 

При необходимости произвести однотипные действия над несколькими строками в таблице обычно применяют запросы-действия. Запросы-выборки производят выборку данных из базы по некоторому критерию, а запросы-действия позволяют манипулировать данными из выборки: редактировать, удалять и добавлять поля и записи.

Вначале удобно создать запрос-выборку, фильтрующий данные, над которыми необходимо произвести действие. Запрос запускается, проверяется выходная таблица, и если запрос правильно отбирает данные, которые должны модифицироваться, преобразуется в запрос − действие. А затем этот запрос преобразуется в запрос − действие. Эта последовательность действий применяется и при работе с дизайнером запросов, и при использовании непосредственно команд SQL.

 

2.1. Вставка данных в таблицу.

Откроем базу данных STUD, и в ней таблицу "Новые результаты". Откроем окно нового запроса в режиме конструктора и выполним команду "Добавление" в меню "Запрос". Введем имя таблицы ”Новые результаты”. В диалоговом окне "Свойства запроса" введем имя таблицы, в которую будем добавлять данные – “Результаты". Если данные для копирования находятся в другой базе данных − установить переключатель в положение "В другой базе данных" и ввести внизу путь к базе − источнику. Нажмем кнопку "Ок".

Появится бланк запроса на добавление. Нам необходимо скопировать все поля, поэтому из списка названий полей перенесем символ "*". Если названия полей или их свойства различны − необходимо в бланке QBE описать все соответствия между полями исходной таблицы и таблицы − приемника. Если какое-то поле есть в таблице-приемнике, но не определено соответствие этому полю в источнике, то в нем ставится системой значение по умолчанию.

Access позволяет задавать условие фильтрации данных для вставки в таблицу. Для этого в столбцах, по которым будет идти отбор, в строке "Условие отбора" вводится корректное условие для выборки данных. Введем для поля "Дата сдачи" параметрическое ограничение по дате, начиная с которого будет производиться выборка на добавление

[Введите дату]

 

Сохраним полученный запрос − действие под именем "Дополнительные результаты". Запустим запрос на выполнение командой "Выполнить" меню "Запрос", или щелкнув кнопку "Выполнить" на панели инструментов. Введем дату в ответ на запрос диалогового окна и нажмем "Ок". Появится окно, информирующее о числе записей, которые будут скопированы. Нажмем кнопку "Ок". Открыв таблицу "Новые результаты" можно увидеть записи, добавленные в нее запросом − действием.

 

 

2.2. Создание новой таблицы при помощи запроса.

 

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

Создадим запрос − действие формирующий таблицу “Данные в деканат” о студентах сдавших задолженность в течении сессии. За основу возьмем запрос "Итог", созданный в предыдущей лабораторной работе. Вызовем его в режиме конструктора. Сбросим все условия отбора записей и все групповые функции. Группировку оставим по номерам студентов и фамилии, а в группы будут входить только записи, у которых дата сдачи задолженности попадает в задаваемый пользователем временной диапазон.

Для поля "дата сдачи" в строке "Групповая операция" установим значение "Условие", а в строке "Условие отбора" введем:

[Введите дату окончания сессии]

Мы создали параметрический запрос − выборку, выводящий информацию о должниках за определенный период. Для преобразования его в запрос на создание таблицы выполним команду "Создание таблицы" в меню "Запрос". В появившемся окне введем имя создаваемой таблицы. Сохраним полученный запрос под именем "Итоги в деканат".

Запустим запрос на выполнение командой "Запуск" меню "Запрос". Access запросит параметры для условия отбора, после чего (проанализировав все записи на соответствие этому условию), выдаст число записей, на которых будет сформирована генерируемая таблица. Нажмем кнопку "Ок". Для просмотра созданной таблицы активизируем окно базы данных и выбрав корешок "Таблицы" просмотрим таблицу "Данные в деканат".

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

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

"Создание таблицы" в меню "Запрос" выполняется команда "Добавление" этого же меню. В появившемся окне необходимо ввести только имя таблицы для дописывания итоговых данных.

 

2.3. Запросы на обновление групп записей.

 

Запросы − обновления применяются, когда необходимо произвести однотипные изменения нескольких записей в таблице. Так, при изменении цен на продукты питания можно одним запросом начислить группе преподавателей премии, с учетом стажа и т.д.

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

Пересчитаем зарплату преподавателям с учетом параметрически задаваемого коэффициента инфляции относительно предыдущего месяца. Создаем запрос - выборку по таблице "Преподаватели", куда включаем поля "Фамилия", "Номер_П", "Зарплата". Затем в меню "Запрос" выполним команду "Обновление". В блоке QBE появится строка "Обновление", куда вносятся формулы изменения требуемых полей. Введем в поле "Зарплата" формулу:

зарплата * [Коэф. инфляции]

Можно также задать условие отбора преподавателей, которым вводить коэффициент не нужно, или изменить на другой к-т. Выбор записей для изменения производится в строке "Условие выбора" для требуемого поля. Возможности условного отбора были рассмотрены в предыдущей ЛР.

Сохраним запрос под именем "Пересчет зарплаты" и запустим его на выполнение. После ввода параметра к-та появится информационное сообщение о числе изменяемых записей, и для подтверждения изменений нажмем кнопку "Ок".

 

Обновление нескольких полей производится таким − же образом. Особенностью такого запроса является возможность использования перекрестной инициализации полей. Так при наличии полей "Поле_1" и "Поле_2" в таблице можно "Полю_1" присвоить значение:

"Поле_2" * 2 и одновременно "Полю_2" присвоить:

"Поле_1" * 3.

 

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

 

2.4. Запрос на удаление группы записей.

 

Действия по удалению групп записей аналогичны действиям по обновлению, но здесь выполняется команда "Удалить" меню "Запрос". Вводится условие в бланк запроса, которому должны удовлетворять группы записей, предназначенные для удаления. После этого запрос переключается в режим "Удаление"и выполняется команда "Выполнить" меню "Запрос".

Создайте самостоятельно запрос на удаление из таблицы "Итоги" записей с номерами студентов, для которых средний бал меньше 2.

 

2.5. Ошибки, возникающие при выполнении запросов.

 

Некоторые запросы не могут быть выполнены Access, так как могут привести к нарушению целостности данных базы или к ошибкам в результатах.

Всего существует четыре типа ошибок, которые обнаруживает Access при выполнении запросов:

− появление дубликата первичного ключа. Если в результате изменений таблицы могут появиться несколько записей с одинаковыми значениями первичного ключа - нарушается его уникальность, что является ошибкой и Access отвергается;

− ошибки преобразования данных возникают при преобразовании полей одного типа в другой, и когда эти изменения могут привести к неправильной трактовке хранимых значений − Access предупреждает пользователя об этом;

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

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

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

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

этом случае игнорируются. При нажатии клавиши "Отмена" отвергаются все изменения, инициируемые данным запросом.

 

Результаты работы

 

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

ЛАБОРАТОРНАЯ РАБОТА №6

СОЗДАНИЕ ЗАПРОСОВ НА ЯЗЫКЕ SQL

Тема: создание запросов на языке SQL

Цель: научиться создавать запросы на языке SQL

 

Первая версия стандарта языка SQL была принята Американским национальным институтом стандартов (ANSI) и Международной ассоциацией стандартов (ISO) в октябре 1986 года и называлась SQL-86. В 1989 году появилась усовершенствованная версия - SQL-89. Существующий сейчас стандарт - SQL-92 (SQL-2) является версией с расширенными возможностями и поддерживается большинством разработчиков СУБД в своих приложениях.

Access также поддерживает язык SQL стандарта 1992 года (SQL-92) с небольшими дополнениями. Компания Microsoft добавила к стандартной версии языка некоторые предложения, позволяющие более полно использовать возможности СУБД Access. Так, были добавлены предложения TRANSFORM, IN, DISTINCTROW и опция WITH OWNER ACCESS OPTION, которые будут рассмотрены позднее.

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

Любой созданный в QBE запрос легко может быть конвертирован в инструкции языка SQL при помощи кнопки SQL на панели инструментов. Это позволяет при создании сложных запросов не выписывать все предложения на SQL, а сгенерировать основу запроса в бланке QBE, а затем перейти в окно команд SQL, в котором добавить все требуемые дополнения и опции.

В целях унификации процедуры сохранения запросов даже те из них, которые были сгенерированы при помощи QBE хранятся в SQL-формате, а при их вызове обратно преобразуются к форме QBE.

Язык SQL представляет пользователю широкие возможности по обработке данных БД, часть из которых будет рассмотрена в данной лабораторной работе.

Пример решения

2.1. Создание простых запросов.

 

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

 

Синтаксис команды SELECT:

 

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число [PERCENT] ]

список выбора

FROM [{имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] |

<таблица-объединение> },...

[WHERE условие отбора]

[GROUP BY имя-столбца,...]

[HAVING условие отбора]

[UNION [ALL] инструкция выбора]

[ORDER BY {имя столбца [ ASC | DESC ] },...]

IN <"имя-базы-данных-источника"> <[строка-подключения-источника-данных]>

[WITH OWNERACCESS OPTION];

 

где [1]список выбора есть:

 

{ * | {выражение [AS имя-столбца-выходной-таблицы] |

имя-таблицы.* |

имя-запроса.* |

 

псевдоним.* },...}

 

и где [1]таблица-объединение есть:

 

({ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] } |

<таблица-объединение>}

{ INNER | LEFT | RIGHT | JOIN

{ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] |

<таблица-объединения>}

ON <условие-объединения>)

 

Таблицы, которые должны быть объединены, указываются в предложении FROM, а строки, которые необходимо выбирать─ предложении WHERE. Предложение GROUP BY используется для определения, как должны быть сформированы группы для итогового запроса, предложение HAVING

Для отбора тех групп, которые должны быть включены в результат запроса.

Для каждой таблицы или запроса SQL позволяет определить альтернативное имя. Это имя может быть использовано как псевдоним вместо полного имени таблицы при задании имен столбцов в списке выбора, в предложении WHERE, или в подчиненных запросах.

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

Можно также определить строки, включаемые ACCESS в выходной набор записей, с помощью предикатов ALL, DISTINCT, DISTINCTROW, TOP и TOP число PERCENT.

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

и те, которые дублируются.

DISTINCT требует возврата только уникальных строк (дублирующие отбрасываются).

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

При выводе только части записей используется предикат TOP n и TOP n PERCENT. Первый выводит только n первых записей, а второй − только первые n %. n должно быть целым числом, а во втором случае меньше 100.

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

Создадим простой запрос на SQL по таблице "Преподаватели", выводящий информацию о преподавателях, отсортированных по номерным знакам. Для этого откроем базу данных STUD и щелкнем по корешку “Запросы”. Выберем кнопку "Создать" и в появившемся диалоговом окне выберем режим конструктора и кнопку "OK". Закроем окно “Добавление таблиц”.

После появления окна с пустым бланком QBE и диалоговым окном выбора таблиц для запроса щелкнем по кнопке "SQL" панели инструментов. Появится окно редактирования, в котором мы будем вводить создаваемые запросы. После создания запроса его можно просмотреть в форме бланка QBE, хотя некоторые элементы SQL в бланке отображены не будут.

В окне редактирования запроса введем инструкцию SQL:

 

SELECT Преподаватели.Номер_П, Преподаватели.Фамилия,

Преподаватели.Имя,

Преподаватели.Отчество,Преподаватели.Кафедра,

Преподаватели.Должность,Преподаватель.Дом_адрес

FROM Преподаватели

ORDER BY Преподаватели.Номер_П;

 

Запустим полученный запрос на выполнение кнопкой "Запуск" на панели инструментов.

Сохраним полученный запрос под именем "Сведения о преподавателях".

 

2.2. Создание многотабличного запроса.

 

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

Создадим запрос, выводящий информацию о преподавателях и о предметах, которые они преподают.

SELECT Преподаватели.Номер_П, Преопдаватели.Фамилия,

Преподаватели.Имя, Преподаватели.Отчество,

Преподаватели.Кафедра, Результаты.Предмет

FROM Преподаватели

INNER JOIN Результаты

ON Преподаватели.Номер_П = Результаты.Номер_П

ORDER BY Преподаватели.Номер_П;

 

Перейдем в режим таблиц и проверим результаты запроса. Сохраним полученный запрос под именем "Предметы и преподаватели".

 

2.3. Запросы с параметрами.

 

Типы запросов, в которых необходимо использовать параметрический ввод значений были рассмотрены в предыдущей лабораторной работе. В SQL эта возможность реализуется использованием предложения PARAMETERS в начале инструкции SQL. PARAMETERS предназначено для определения типов данных, используемых нами в запросе параметров.

 

Синтаксис предложения:

 

PARAMETERS {[ имя_параметра ] тип_данных }...;

 

Элемент <имя_параметра> выводится как его описание при запросе. Поэтому желательно создавать имена, однозначно определяющие данные, которые нам нужны. Так, вместо малоинформативных имен типа "Введите дату" или "Текущий номер" желательно дать расширенное описание требуемых значений.

Вторым элементом предложения является <тип_данных>, в котором будет хранится введенное значение. Соответствие типов данных SQL и отображение их в ACCESS приведено в Приложении 2. В SQL−инструкции указывается SQL−тип данных (для совместимости кода с другими приложениями, поддерживающими этот язык).

 

2.4. Создание многотабличного запроса с параметрически заданным условием отбора.

 

Условие отбора определяет простой или составной предикат, который может принимать значения "True", "False", или "Null" для данной строки или группы строк.

Условие отбора используется в предложении WHERE или в инструкции SELECT внутри предложения HAVING, в подчиненных запросах или в инструкциях DELETE, UPDATE для выбора группы строк, над которыми будут произведены действия или отбор. Если условие отбора для строки имеет значение "True", то такая строка включается в результирующий набор.

 

Синтаксис условия отбора:

[NOT] {предикат | (условие отбора)}

[ { AND | OR | XOR | EQV | IMP }

[NOT] {предикат | (условие отбора)}]...

 

Приоритет логических операторов следующий: NOT, AND,OR, XOR, EQV (эквивалентность) и IMP (импликация). Для изменения приоритетов выполнения условий применяются круглые скобки. Таблицы истинности логических операторов представлены в таблицах 1..5 приложения 1.

Создадим запрос, выводящий информацию о студентах сдавших сессию вовремя. Для этого в окне редактора инструкций SQL введем команду:

 

SELECT Студенты.Фамилия, Студенты.Имя, Студенты.Отчество,

Результаты.Номер_С AS Сдали задолженность

Результаты.Задолженность, Результаты.Дата_сдачи,

FROM Студенты

INNER JOIN Результаты

ON Студенты.Номер_С = Студенты.Номер_С

WHERE ((Результаты.Дата_сдачи<=[Введите дату сдачи задолженности])

OR (Результаты.Задолженность=0));

 

Сохраним полученный запрос под именем "Студенты сдавшие сессию".

 

2.5. Групповые запросы.

 

В инструкции SELECT могут быть заданы столбцы, используемые для формирования групп из выбранных строк. Строки в каждой группе будут содержать одно и тоже значение заданного столбца (столбцов). В SQL для формирования групповых запросов предназначено предложение GROUP BY.

Имя столбца в предложении GROUP BY может быть именем произвольного столбца или любой таблицы, описанной в предложении FROM.

Если GROUP BY стоит после предложения WHERE, создаются группы из

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

При включении предложения GROUP BY в инструкцию SELECT, список выбора должен состоять из итоговых функций SQL: AVG, SUM,MAX,MIN, COUNT, STDEV, VAR и VARP, или же из имен столбцов, указанных в предложении GROUP BY.

Создадим запрос, выводящий средний бал по предметам и количество задолженностей за определенный период времени, введя команду:

 

SELECT Студенты.Номер_С,

AVG(Результаты.Оценка_Т) AS Теория,

AVG(Результаты.Оценка_П) AS Практика,

AVG(Результаты.Оценка_Л) AS Лабы

FROM Студенты

INNER JOIN Результаты

ON Студенты.Номер_С = Результаты.Номер_С

WHERE ([Результаты,Дата_сдачи<=[Введите дату])

OR ([Результаты.Задолженность=0))

GROUP BY Студенты.Номер_С;

 

Сохраним запрос под именем "Средний бал сдавших сессию".

 

При необходимости произвести селекцию групп, включаемых в выходную таблицу, применяется предложение HAVING,. Это условие применяется к столбцам, указанным в предложении GROUP BY, столбцам итоговых функций и к столбцам, которые образованы выражениями, содержащими итоговые функции. Если некоторая группа не удовлетворяет условиям отбора, то она не включается в выходную логическую таблицу.

 

Разница между предложениями HAVING и WHERE в том, что условие выбора в WHERE применяется к отдельным записям перед, тем как они будут объединены в группы, а условие в HAVING используется в отношении уже сформированных групп строк.

 

2.6. Создание запроса на объединение таблиц.

 

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

Обновление набора записей возможно только при наличии (явно или по умолчанию) ключевого слова DISTINCTROW.

Существуют два основных типа объединения таблиц: внутреннее (INNER) и внешнее (OUTER).

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

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

− Декартовым (прямым) произведением таблиц называется объединение всех строк одной таблицы со всеми строками другой. Данное объединение используется по умолчанию (при отсутствии предложения JOIN), путем перечисления объединяемых таблиц в предложении FROM.

Например,

SELECT ......

FROM TAB_1, TAB_2

......

 

задает областью поиска все строки TAB_1, присоединенные к каждой строке TAB_2, независимо от того, соответствуют они друг другу или нет.

Можно создавать вложенные объединения, но только объединяя

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

Для указания типа объединения таблиц применяется предложение JOIN. Синтаксис предложения приведен ниже:

 

({ имя_таблицы [AS] псевдоним] |

имя_запроса_выборки [[AS] псевдоним] |

<таблица_объединения>

{ INNER | LEFT | RIGHT } JOIN

{имя_таблицы [[AS] псевдоним] |

имя_запроса_выборки [[AS] псевдоним] |

{таблица_объединение}

ON <условие_объединения>);

 

где <таблица_объединения> есть результат другой операции объединения, а <условие_объединения> − условие отбора, образованное из предикатов сравнений, сравнивающих поля первой таблицы с полями второй.

 

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

LEFT JOIN используется для вывода всех строк первой таблицы, и только тех строк второй, для которых выполняются условия объединения. Если таких значений не обнаруживается − в качестве значений соответствующих полей возвращается NULL.

RIGHT JOIN возвращает все строки второй таблицы, объединенные со строками из первой, для которых выполняются условия объединения. Если таких значений не обнаруживается − в качестве значений соответствующих полей возвращается NULL.

Если в условии объединения используется только предикат сравнения на равенство, то результат называется объединением по равенству. Бланк QBE позволяет представлять только такие объединения. Если же необходимо

определить объединения таблиц по условию неравенства ("<", ">", "< >", "<=", ">="), то запрос необходимо создавать через SQL.

Если по условию выполняется объединение таблицы с самой собой, то результат называется самообъединением.

При самообъединении (и в других случаях по желанию), используются псевдонимы. Это альтернативное имя можно использовать вместо полного имени таблицы при указании имен столбцов в списке выбора, в предложении WHERE или в подчиненных предложениях.

Если имя таблицы или запроса совпадает с зарезервированным словом SQL, то такое имя заключается в квадратные скобки.

Для определения списка всех студентов в группах с долгами и без, создадим запрос "Долги в группах", используя внешнее объединение таблиц "Студенты" и "Результаты":

 

SELECT Студенты.Номер_С, Студенты.Фамилия, Студенты.Группа,

Результаты.Предмет, Результаты.Задолженность

FROM Студенты

LEFT JOIN Результаты

ON Студенты.Номер_С = Результаты.Номер_С;

 

2.7. Запросы, использующие таблицы удаленной БД.

 

Для определения источника данных базовых таблиц запроса используется предложение IN. Источником может служить другая база данных MS ACCESS, файлы dBASE, FoxPro, Paradox, база Btrieve, или любая другая база, поддерживающая ODBS. Это предложение является расширением в ACCESS, и в стандартном SQL отсутствует.

Синтаксис:

IN<"имя_источника_базы_данных">

<[строка_подключения_источника]>

<строка_подключения_источника> вводится вместе с квадратными скобками. Для базы MS ACCESS вводится только параметр "имя_источника_базы_дан-ных". Форматы вводимых данных представлены в

 

Приложение 3

 

Предложение IN применяется ко всем таблицам, указанным в предложении FROM и в подчиненных запросах. В запросе можно ссылаться только на одну внешнюю БД. Если необходимо обращение к нескольким БД и файлам, то они присоединяются как таблицы (см ЛР N3), и используются уже как присоединенные.

Если для ODBS не указать DSN и DATABASE в строке подключения, то ACCESS выводит диалоговое окно с перечнем доступных источников данных и дает возможность выбрать нужный.

Если опустить UID и PWD, а сервер данных их требует, то ACCESS выводит окно регистрации пользователя.

Для баз dBASE, FoxPro, Paradox и Brieve можно вместо <Имени_ба-зы_данных_источника> ввести пустую строку "" и указать путь или имя

Файла с помощью параметра DATABASE в <строке_подклю-чения_источника_данных>.

Создадим запрос, выводящий таблицу "Результаты" из удаленной базы "Fil_1", содержащую список результатов пошедшей сессии, причем результатов, которые получены в течении сессии.

 

SELECT Результаты.Номер_С, Результаты.Предмет,

Результаты.Оценка_П, Результаты.Оценка_Т, Результаты.Оценка_Л,

Результаты.Дата_сдачи,Результаты.Задолженность

FROM Результаты

IN 'FIL_1.MDB'

WHERE (Результаты.Дата_Сдачи<=[Введите дату отбора] OR Результаты.Задолженность=0)

ORDER BY Результаты.Номер_С;

Сохраним запрос под именем "Результаты прошедшей сессии".

 

 

2.8. Запросы объединения инструкций SELECT.

 

При необходимости создания результирующей таблицы, содержащей строки нескольких инструкций SELECT, применяется операция UNION. Эта возможность не поддерживается QBE, и реализуется только в режиме SQL.

Синтаксис операции UNION:

 

инструкция_SELECT

UNION [ALL]

инструкция_SELECT

[ORDER BY { имя_столбца [ ASC | DESC ] },...]

 

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

Не рекомендуется использовать предикат ORDER BY внутри инструкции SELECT, но можно его использовать после последней такой инструкции. В качестве имен столбцов используются имена из первой инструкции SELECT.

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

SELECT Результаты.Номер_С, Результаты.Предмет,

Результаты.Оценка_Т,

Результаты.Оценка_П, Результаты.Оценка_Л,

Результаты.Задолженность, Результаты.Дата_сдачи

FROM Результаты

IN 'FIL_1.MDB'

UNION

SELECT Результаты.Номер_С, Результаты.Предмет,

Результаты.Оценка_Т, Результаты.Оценка_П, Результаты.Оценка_Л,

Результаты.Задолженность,

Результаты.Дата_сдачи

FROM Результаты

ORDER BY Номер_С;

 

Сохраним запрос под именем "Объединенные результаты".

 

2.9. Перекрестные запросы на SQL.

 

Инструкция TRANSFORM позволяет создавать перекрестные запросы, позволяющие осуществлять суммирование некоторого выражения; используя значение заданного столбца или выражения в качестве заголовков столбцов выходных таблиц, а другие столбцы либо выражения используются для задания условия группировки и формирования строк выходной таблицы.

Синтаксис инструкции TRANSFORM:

 

TRANSFORM выражение_с_итоговой_функцией

<инструкция_SELECT>

PIVOT выражение

 

где <выражение_с_итоговой_функцией> − выражение, использующее одну из итоговых функций. Оно определяет значения, которые должны появиться в ячейках выходной (перекрестной) таблицы.

Выражение <инструкция_SELECT> должна содержать предложение GROUP BY.

Конструкция <PIVOT выражение> определяет столбец или выражение, значения которого используются в качестве заголовков столбцов перекрестной таблицы.

При задании условий группировки для строк можно использовать несколько

столбцов или выражений.

Чтобы получить итоговые оценки по предметам, создадим запрос "Сетка успеваемости":

TRANSFORM MAX(INT((Результаты.Оценка_Т+Результаты.Оценка_П+ Результаты.Оценка_Л)/З))

SELECT Результаты.Номер_С

FROM Результаты

GROUP BY Результаты.Номер_С

PIVOT Предмет;

 

Запросы можно строить не только на основе данных таблиц, но и по данным, которые возвращает запрос. В этом случае имя вызываемого запроса указывается в квадратных скобках. Так, при необходимости сформировать сетку успеваемости за учебный год по прошедшему и текущему семестрах, SQL не дает возможности напрямую произвести объединение таблиц в перекрестном запросе. Приходится создавать запрос на объединение таблиц − "Объединенные результаты", а затем создаваемый перекрестный запрос "Сетка успеваемости за год", который использует результаты уже запроса-объединения:

 

TRANSFORM MAX(INT((Результаты.Оценка_Т+Результаты.Оценка_П+ Результаты.Оценка_Л)/))

SELECT [Объединенные результаты].Номер_С

FROM [Объединенные Результаты]

GROUP BY [Объединенные результаты].Номер_С

PIVOT Предмет;

 

2.10. Запросы − действия на SQL.

 

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

 

2.10.1. Запрос на создание таблицы.

 

Инструкция SELECT...INTO создает новую таблицу, используя значения, выбранные из одной или нескольких таблиц. Обычно этот тип запросов применяется при резервировании данных таблицы, или для вывода отчетов за определенный период.

 

Синтаксис команды:

 

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число [PERCENT]] список_выбора

INTO имя_новой_таблицы

FROM {{имя_таблицы [[AS] псевдоним] |

имя_запроса_выборки [[AS] псевдоним]} |

<таблица_объединение> }

[WHERE условие_отбора]

[GROUP BY имя_столбца ,...]

[HAVING условие_отбора]

[UNION [ALL] инструкция_выбора]

[ORDER BY { имя_столбца [ ASC | DESC ] },...

IN<"имя_базы_источника_данных"> <[строка_подключения_источника_дан-ных]>

 

Модификация вставляемых данных возможна только в режиме

SELECT DISTINCTROW.

 

Данный запрос создает новую таблицу с именем, задаваемым в <имя_новой_таблицы>. Если таблица с таким именем уже существует ACCESS запрашивает, удалять ли старую перед созданием новой.

Создадим запрос "Результат по практике".

SELECT DISTINCTROW Фамилия, Имя, Отчество, Студенты.Номер_С,

Count(Результат.Оценка_П) AS Практика

FROM Студенты

 

INNER JOIN Результаты

ON Студенты.Номер_С = Результаты.Номер_С

WHERE ((Результаты.Предмет=[Введите предмет])

GROUP BY Фамилия, Имя, Отчество, Студенты.Номер_С;

 

После этого создадим запрос, который будет по полученной выборке формировать таблицу "Результат":

 

SELECT DISTINCTROW [Результат по практике].*

INTO [Результат]

FROM [Результат по практике];

 

Запустим запрос на выполнение и после введения всех параметров убедимся, что Access создал таблицу "Результат".

 

2.10.2 Запросы на модификацию данных с использованием SQL.

 

Инструкция UPDATE позволяет изменять данные в заданных таблицах или запросах. При необходимости можно задавать условия для выбора строк, над которыми будет производиться модификация.

Синтаксис команды UPDATE:

 

UPDATE { { имя_таблицы [[AS] псевдоним ] |

имя_запроса_выборки [[AS] псевдоним ] } |

<таблица_объединение> },...

SET { имя_столбца = { выражение | NULL } },...

[WHERE условие_отбора]

Модификация вставляемых данных возможна только в режиме

SELECT DISTINCTROW.

Если необходимо внести изменения в связанные таблицы, то изменять содержимое столбцов можно только в таблице со стороны "многие" при связи "один_ко_многим". При связи "один_к_одному" можно обновлять столбцы любой таблицы.

В условии отбора можно ссылаться только на столбцы обновляемой таблицы или запроса. Если в условии отбора мы используем подчиненный запрос, то не должны ссылаться в нем на обновляемую таблицу или запрос.

Для перерасчета оценок по 40 бальной системе в созданной таблице "Результат" с учетом параметрически заданного коэффициента создадим запрос "Перерасчет результатов":

 

UPDATE DISTINCTROW [Результат]

SET [Результат].Практика =

[Результат]![Практика]*[Коэффициент новой системы];

 

Название таблицы заключено в квадратные скобки, так как содержит в себе пробелы.

 

2.10.3 Запросы на удаление данных на SQL.

 

Удаление записей производится при помощи инструкции DELETE. Удалять можно как отдельные записи, их группы, так и все записи в таблице.

 

Синтаксис команды DELETE:

 

DELETE [список_выбора]

FROM [{ имя_таблицы [[AS] псевдоним ] |

имя_запроса_выборки [[AS] псевдоним ] } |

<таблица_объединения> },...

[WHERE условие_отбора]

где <список_выбора> есть:

[ * | имя_таблицы.* | список_столбцов ]

 

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

 

DELETE DISTINCTROW [Результат].Практика

FROM [Результат]

WHERE (([Результат].Практика=[Введите оценку]));

 

Сохраним ее под именем "Положительные результаты”, и запустим на выполнение.

 

2.10.4 Запрос на добавление данных в таблицу.

 

Инструкция INSERT вставляет одну или несколько строк в указанную таблицу или запрос.

Синтаксис команды:

 

INSERT INTO имя_таблицы [({имя_столбца},...)]

{VALUES ({литерал},...) | инструкция_выбора}

 

Если не заданы имена столбцов, то ACCESS вставляет значения во все столбцы в том порядке, в каком они определены в описании таблицы. Если же имена столбцов заданы, то данные заносятся в поля в той − же последовательности, как и определены. Характеристики столбцов и вводимых данных должны совпадать.

Когда используется предложение VALUES, то вставляется единственная

строка. INSERT можно комбинировать с SELECT, и строки, возвращаемые SELECT будут вставлены в объект-приемник для SELECT.

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

"Обязательное поле" установлено в "Да".

Если набор вставляемых записей формируется с помощью SELECT, то имя таблицы 6 в которую производится вставка6 не может быть источником данных в предложении FROM инструкции SELECT, или подчиненных запросах.

Создадим запрос, дополняющий таблицу "Результаты" новыми записями. Новые данные будут формироваться по той-же выборке − "Результат по практике", но по другому предмету.

 

INSERT INTO [Результат]

SELECT DISTINCTROW [Результат по практике].*

FROM [Результат по практике];

 

Сохраним его под именем "Дополнение результатов по практике".

 

Результаты работы

В результате выполнения лабораторной работы были созданы запросы к базе данных STUD, производящие выборку данных из таблиц в требуемом формате, а…   Приложение 1. Таблицы истинности логических операторов.

ЛАБОРАТОРНАЯ РАБОТА №7

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

Тема:Приобретение навыков работы с данными в формах.

Создание форм различных видов.

Цель:Освоить основные принципы обработки данных с помощью форм, научиться создавать формы в среде Microsoft Access 97.

Краткие теоретические сведения

─ вывода и редактирования данных; ─ управления ходом выполнения приложения; ─ вывода сообщений;

Постановка задачи

2.1.Создайте простую форму на основе своей БД.

2.2.Ознакомтесь с возможностями перемещения по форме и работы с данными, предоставляемыми Access 97.

2.3.Создайте форму с помощью мастера по созданию форм.

2.4.Создайте сложную форму, содержащую подчиненную.

2.5.Создайте диаграмму с помощью возможностей Microsoft Graph.

2.6.Поэкспериментируйте со свойствами элементов управления, формы и ее частей.

 

Пример решения

3.1 Создание простой формы «Преподаватели».

 

3.1.1 .Выберите корешок «Форма» и щелкните на кнопке «Создать». Появится окно «Новая форма», которое имеет вид, как показано на рис.2.

Рисунок 3.

 

 

 

Данное окно предлагает выбрать таблицу или запрос в качестве источника данных. Выберем таблицу «Преподаватели». Метод создания формы с помощью «Конструктора». При входе в режим конструктора формы Access предоставляет панель инструментов конструктора форм, бланк свойств форм, незаполненную сетку области данных и основной инструмент работы ─ панель элементов. Если ее нет на экране, выполните команду Вид/Панель элементов или включите кнопку «Панель элементов» на главной панели инструментов.

После ее выбора откроется панель элементов:

Рис.4

 

 

Кнопки элементов (слева на право и сверху вниз):

─ выбор объектов ─ нажата по умолчанию и используется для выделения, изменения размера, перемещения и редактирования элемента управления;

─ мастер ─ активизация мастера по созданию элементов управления;

─ подпись ─ создает элемент управления подпись и может использоваться для создания заголовков и инструкций, не связанных с элементами управления;

─ поле ─ создаёт элемент управления поле;

─ группа ─ создание группы элементов управления, содержащей несколько выключателей, переключателей или флажков;

─ выключатель ─ создаёт элемент управления выключатель, который может принимать значение Вкл/Выкл;

 

─ переключатель ─ создает элемент управления переключатель, который может принимать значение Вкл/Выкл;

─ флажок ─ создает элемент управления флажок, который может принимать значение Вкл/Выкл;

─ поле со списком ─ объединение двух элементов управления: поле и список. Если список закрыт Access выводит значение первого столбца. Вы можете изменить его значение, выбрав в списке новое значение;

─ список ─ содержит список потенциальных значений;

─ кнопка ─ создание кнопки, с помощью которой можно активизировать макрос или функцию Access Basic;

─ рисунок ─ активизирует мастера диаграмм для включения в форму объекта Microsoft Graph;

─ свободная рамка объекта ─ включение объекта OLE из другого поддерживающего OLE─приложения;

─ присоединенная рамка объекта ─ включение объекта OLE, хранящегося в базовой таблице в форму;

─ разрыв страницы ─ вставляет символ конца страницы в многостраничных формах;

─ подчиненная форма/отчет ─ используется для вывода данных из нескольких таблиц в форме или отчете;

─ линия ─ включение в форму прямых линий;

─ прямоугольник ─ включение в форму прямоугольников;

─ дополнительные элементы ─ предназначен для дизайна создаваемой формы.

 

3.1.2.Переташите нижнюю границу области данных, чтобы увеличить поле работы, захватите ее край с помощью указателя мыши и потащите его. Откройте список полей базовой таблицы, нажав на кнопку панелей инструментов «Список полей». Перетащите по одному поля из списка или выделите все поля двойным щелчком по заголовку списка формы (можно выделить первое нужное поле и

последнее поле группы при нажатой клавише Shift). При размещении перетаскиваемого поля в форме учитывайте, чтобы Access мог разместить еще и подпись к элементу управления. Окно конструктора будет иметь вид, как показано на рис.3.

Рисунок 5.

 

3.1.3.Откорректируйте размер и расположение элементов по своему усмотрению. Например поместите поля «Фотография» и «Дом_телефон» с права от «Номер_П». Для этого необходимо пометить выбранное Вами поле и поместить маркер так, чтобы вместо указателя появилась рука. Для удобства ввода информации некоторые поля необходимо увеличить, выберите корректируемое поле и установите маркер в положение, чтобы он приобрел форму двойной стрелки. Увеличьте поля «Кафедра» и «Дом_адрес». Если необходимо перенести объект без надписи, установите маркер, чтобы он имел вид руки с поднятым указательным пальцем (поместите маркер в левый верхний угол помеченного поля).

3.1.4.Откорректируйте свойства элементов управления, открыв бланк свойств. Свойства Формат, Число десятичных знаков, Маска ввода, Значение по умолчанию, Сообщение в строке статуса наследуются из базовой таблицы, если они определены. Свойство подписи установите, каким Вы хотите видеть подпись к элементу. Вы можете написать полные имена полей для облегчения ввода в свойстве «Имя». Отклики на события сформируем позже. Для удаления сетки с

точками в области данных выполните команду Вид/Сетка. Для добавления заголовка и примечания выполните команду Вид/Заголовок/примечания формы.

3.1.5.Откорректируйте свойства формы. Для определения свойств элементов управления формы и всех ее разделов используется бланк свойств (команда меню Вид/Свойства или соответствующая кнопка на панели управления). Для получения бланка свойств формы выполните Правка/Выделить форму. При открытом бланке свойств Access выведет в нем необходимую информацию. Как показано на рис.6.

Рис.6

 

 

Установим Режим по умолчанию в Ленточная форма (для создания ленточной формы). Вы можете сделать элемент не видимым (свойство Видимость установить в нет), определить его расположение в поле вывода (свойство Выравнивание по центру). Для изменения свойства, размеров и расположения группы объектов выделите первый, а последующие при нажатом Shift.

Для выделения всех элементов ─ Правка/Выделить все. Вы можете настроить размер любого отдельного элемента, щелкнув дважды по левому нижнему углу этого элемента (для группы выполните Формат/Размер). Привязка элементов управления к сетке осуществляется командой Формат/Привязать к сетке. Если перед ней стоит галочка, то она активизирована.

Для выравнивания элементов управления выделите группу элементов, которую необходимо выровнять по какому-либо краю, и используйте набор команд в команде Формат/Выравнивание.

 

Для большей выразительности подачи информации используйте палитру: определяет толщину линии, ее тип, выпуклость элемента и цвет фона, текста и границы элемента.

Для определения порядка перехода по полям формы используйте команду меню Правка/Дублировать.

3.1.6. Проверьте результат работы, щелкнув по кнопке режима формы на панели инструментов.

3.1.7.Сохраните с именем «Преподаватели».

3.2. Перемещение в форме и работа с записями:

3.2.1.Просмотр: Если Вы вышли из базы, то откройте базу данных STUD, щелкните по корешку «Форма», выберите «Преподаватели» и щелкните по кнопке «Открыть». Чтобы перейти к подчиненной форме, можно использовать клавиши Ctrl ─ Tab или мышь. По подчиненной форме перемещаются также, как и по таблице. Для прокрутки записей используются кнопки в нижнем левом углу формы. Чтобы перейти к первой, последней, следующей и предыдущей.

3.2.2. Для начала ввода можно воспользоваться командой меню Записи/Ввод данных или щелкнуть на панели инструментов Новая запись. Если Вы хотите отменить ввод новой записи нажмите два раза ESC или щелкните по кнопке «Удалить запись» на панели инструментов. Для ввода объектов OLE выполните команду Вставка/Объект. После выполнения этой команды Access предоставит выбрать файл из которого будет вставлена картинка. Пример: картинка из Microsoft Clip Gallery. Рис. 7.

Рис.7

 

Рисунок 4.

3.2.3. Изменение и удаление данных:

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

Для удаления данных нужно выделить запись щелкнув по области маркировки или выполнив команду Правка/Удалить запись.

 

3.2.4-Поиск и сортировка данных:

Для поиска нужной записи сначала выделите поле, по которому будет осуществляться поиск, а затем выполните команду Правка/Найти или щелкнуть по кнопке «Поиск» панели инструментов (кнопка имеет вид бинокля). Для сортировки выберите поле и щелкните по кнопке нужного вида сортировки (для подчиненной формы не выполняется)

 

─ сортировка по возрастанию;

─ сортировка по убыванию.

 

Фильтр применяется также только к записям основной формы. Для определения нового фильтра выполните команду Записи/Фильтр или щелкнуть по нужной кнопке панели инструментов.

 

 

Значение кнопок (слева на право):

─ фильтр по выделенному;

─ изменить фильтр;

─ применить фильтр.

Чтобы отключить фильтр, выполните Зиписи/Удалить фильтр. Если Вы часто используете один и тот же фильтр, то перед закрытием фильтра выполните команду Файл/Сохранить как запрос, а для его использования в качестве фильтра

выполните Файл/ Загрузить как запрос.

3.2.5.Печать данных из формы:

Откройте нужную форму, выполните Файл/Печать. Увеличение изображения выполняется кнопкой масштаба. Для задания установок печати используется кнопка настройки панели инструментов.

 

3.3. Создание форм с помощью мастера.

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

 

3.3.1. Приступим к созданию формы "Задолженности":

 

3.3.1.1. Воспользуйтесь базовым запросом "Задолженности" (можно создать базовый запрос, хранящийся в самой форме, воспользовавшись построителем свойства Источник записей, но тогда мы не сможем воспользоваться мастером создания форм, а также редактирование его будет возможным только при открытой форме), который содержит Фамилию, Имя студента из таблицы "Студенты" и поле Задолженность из таблицы "Занятие ", используя сортировку по фамилии. Поле Предмет сделаем полем со списком.

 

3.3.1.2. Щелкните по кнопке «Создать», выделите из списка запрос "Задолженности" и щелкните по кнопке мастера. В диалоговом режиме Вы в любой момент можете использовать кнопку Готово для перехода к последнему окну мастера или Отмена для отмены создания формы. Для осуществления установок пользуйтесь Далее и Назад. Мастер попросит выбрать поля для формы, двойной стрелкой выбираем все поля.

 

3.3.1.3. Выберите мастера по разработке форм В один столбец. Далее выберите оформление формы ─ Облака (или любой другой стиль, который Вам понравится). В последнем диалоговом окне введите имя формы (оно же ─

заголовок формы), выберите режим модификации проекта и щелкните по кнопке Готово. Выберите поле Предмет и на панели элементов нажмите кнопку поле со списком. Появится окно «Создание полей со списком». Задайте в нем способ, которым поле получает свои значения. Выбираем первый способ (поле со списком использует значения из таблицы или запроса), как показано на рис.5.

Рисунок 8.

 

 

Когда мастер попросит ввести имя поля, введем «Список предметов». В бланке свойств Ограничится списком установим ДА, чтобы пользователь не имел возможности использовать это поле для ввода новых значений.

 

3.3.1.4. Модифицируйте проект, изменив размер и расположение элементов.

 

3.3.1.5. Сохраните форму.

 

3.3.1.6. Выйдите из режима конструктора и нажмите кнопку «Открыть» (или воспользуйтесь кнопкой «режим формы», не выходя из Конструктора). Форма имеет вид как показано на рис.6

Рисунок 9.

 

 

Создание подчиненной формы

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

Для наглядности работы с данными нашей БД есть смысл в форме "Сводные данные", которая предоставить информацию о среднем бале студента. Дня этого построим подчиненную форму, которая выглядит так же, как таблица и выполняет те же действия, но имеет заголовок и область примечаний для вывода вычисленных итогов.

3.3.2.1. Создайте форму, установите для нее источник записей таблицу "Результаты" и наличие заголовка и примечания.

 

3.3.2.2. Перенесите в форму все поля базовой таблицы, поместите все подписи полей в заголовок формы, выделите подпись элемента, нажмите кнопку «вырезать», активизируйте область заголовка формы и выполните команду Правка/Вставить. Выровняйте все элементы управления в области данных и измените размер области данных так, чтобы в ней остались только элементы управления. Создайте несвязанное поле в области примечаний формы, определите

подпись элемента как "Средний балл ", а сам элемент используйте для подсчета среднего балла, используя построитель выражений:

= Int(([Оценка_Т]+[Оценка_П]+[Оценка_Л])/3)

 

3.3.2.3. Перейдем к главной форме. В качестве источника данных для этой формы определим таблицу «Студенты». Создадим ее с помощью мастера, с полами: Фамилия, Имя, Отчество, Группа. Назовите се «Студенты_главная».

 

3.3.2.4. Связывание подчиненной и главной формы:

Выделите подчиненную форму в окне БД и перетащите ее в окно формы «Студенты_главная» в режиме конструктора, установив в нужном месте. Разместите подчиненную форму так, чтобы данные удобно было просмотреть. Т.к. источником данных для подчиненной и главной формы были таблицы, связи между ними устанавливаются на основе связей таблиц. Результат работы представлен на рис.10.

Рис. 10

 

 

3.3.3 Использование диаграмм:

 

Диаграммы ─ это еще один тип объектов, которые Вы можете внедрять в форму. Продемонстрируем на примере.

 

Построим форму "Показатели", которая выдает необходимую информацию об успеваемости и «График задолженностей», который характеризует общее количество задолженностей по каждому предмету.

 

3.3.3.1.Воспользуемся запросом «Задолженность» созданным в предыдущих лабораторных работах.

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

 

3.5.3.2. Создадим форму с помощью мастера форм на основе всех полей запроса. При активном окне форм щелкните на кнопке Создать, выберем мастера по разработке диаграмм и базовый запрос «Задолженности». Тип диаграммы выбираем круговой. Результат показан на рис.11.

Рисунок 11.

 

3.3.3.3. Приступим к созданию диаграммы успеваемости студентов. Для этого выберем мастер по созданию диаграмм и базовый запрос «Успеваемость». Предварительно переименовав в запросе «Выражение_1» на «Итоговая», и выберем поля "Оценка_Т", «Оценка_П», «Оценка_Л» и «Итоговая». Затем Вам предлагается выбрать вид диаграммы. Выбираем первую (она должна быть выбрана автоматически). Появиться образец диаграммы, дважды щелкнем на «Итоговая» появиться список «Вычисление итоговых значений» выбираем Среднее. Диаграмма имеет вид:

 

 

3.3.3.4.Изменение вида диаграммы. Установим нужный шрифт и корректную легенду. Для этого зайдите в «Конструктор» и дважды щелкните по диаграмме, чтобы открыть приложение Microsoft Graph с нужной диаграммой. Установите границы окна диаграммы так, чтобы выводились все значения по осям. Легенда устанавливается нажатием соответствующей кнопки на панели инструментов Graph. Выделите группу элементов, требующих замены шрифта и установите нужный шрифт, для этого дважды щелкните мышкой на выделенном объекте. Вы так же можете изменить цвет столбиков и т.д. Когда вид графика будет вас удовлетворять, сохраните изменения и вернитесь в окно БД.

Результаты работы

 

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

─ макеты форм различных видов с учетом полученных заданий, которые сохранены в Вашей базе данных;

─ отчет с кратким описанием проделанной работы.

 

ЛАБОРАТОРНАЯ РАБОТА №8

ОБУЧЕНИЕ ПОСТРОЕНИЮ ОТЧЕТОВ

Тема: Обучение построению отчетов в среде Microsoft Access 97.

Цель: Создание отчетов с использованием данных БД для наглядности данных и вывод отчетов на печать.

Краткие теоретические сведения

1.1. Отчеты − наилучшее средство для предоставления данных в виде печатного документа.

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

1.2. Конструктор отчетов предоставляет панель разработки отчетов, которая

 

во многом аналогична панели инструментов конструктора форм.

 

Сортировка и группировка

Но имеются следующие специфичные кнопки: − Всплывающее меню «Вид» имеет кнопку "Образец", которая…     - Кнопка « сортировка и группи­ровка данных». Отчеты игнорируют все условия сортировки,…

Постановка задачи

 

2.1. Создайте на основе данных БД простой отчет.

2.2. Создайте несколько отчетов с помощью мастера построения отчетов.

2.3. Создайте сложный отчет с внедренным отчетом или формой.

2.4. Внедрите в отчет какую-либо картинку или диаграмму.

2.5. Поэкспериментируйте со свойствами отчетов, их частей и элемен­тов управления. Поработайте с палитрой.

2.6. Выведите несколько отчетов на печать

Пример решения

 

3.1 Построение отчета "Сведения по зарплате":

 

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

1. Создание отчета. Откройте окно БД, щелкните по корешку «Отчеты» и нажмите кнопку "Создать". Создадим отчет сами с помощью конструктора, на основе таблицы «Преподаватели». Access создаст пустой бланк отчета с

разделами верхний и нижний колонтитулы (Page Header и Page Footer), в центре между которыми находится область данных.

2. На панели инструментов нажмем кнопку «Группировка и сортировка» в столбце Поле/Выражение выберите поле "Фамилия", сортировка по возрастанию установиться автоматически.

3. Выведите на экран список полей базовой таблицы (нажмите кнопку список полей, она идентична кнопке форм). Перетащите из списка в область данных отчета поля "Фамилия", "Имя", "Отчество", «Зарплата». Переместите подписи к этим полям в область Верхнего колонтитула (Page Header): для этого нужно выделить подпись, выполнить команду меню Правка/Вырезать, перейти в Верхний колонтитул и выполнить команду Правка/Вставить.

4. Добавьте в области данных 3 несвязанных поля для вычисления надбавки, налога и суммы к выдаче, а их подписи тоже переместите в область Заголовка отчета. С помощью кнопки «Свойства», войдите в бланк свойств и введите для каждого поля имя. Источником данных для них служит вычисляемое выражение. Для свойства Формат поля установите значение Денежный. Для элемента управления "надбавка" в свойство Данные введите выражение с помощью построителя выражений. Для этого выберите поле и нажмите кнопку «Построить»:

=([Зарплата]*0,4 )

 

Для поля "налог" :

Иногда есть потребность, чтобы определенные данные выводились в зависимости от их значения или значения других элементов управления. В Access имеется функция IIF, которая возвращает одно из двух значений в зависимости от логического значения аргумента – Ложь или Истина. В качестве первого аргумента функции вы задаете условие, выполнение которого вы хотите проверить. Вторым аргументом функции должно быть значение, которое нужно возвратить, если в результате проверки условия будет получено значение Истина, а третьим аргументом – значение функции в том случае, когда результат проверки

Ложь. Введите выражение =[Оклад]*0.01+IIf(([Оклад]>100) And ([Оклад]<250);[Оклад]*0,08; [Оклад]>300,[Оклад]*0,1)

 

А для поля "итог":= [Оклад] + [надбавка] - [налог]

 

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

 

5. В область Верхний колонтитул введите заголовок "Сведения по зарплате" и установите шрифт в Arial Cyr, размер − 14 и сделайте его жирным (кнопка Жна панели инструментов). В правом верхнем углу создайте поле для вывода номера страницы. Для этого выполните команду Вставка/Номер страницы. В открывшемся окне выберите нужный вам формат.

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

= Page

В области нижнего колонтитула создайте поле с выражением

= Now()

для вычисления текущей даты и времени, удалите подпись к нему.

 

7. Для подсчета общей суммы к выдаче воспользуемся свойством элементов управления отчетов − сумма с накоплением (Running Sum). Создайте в области данных отчета вычисляемое поле "общий" с источником данных:

= [Оклад] + [надбавка] − [налог]

и установим для него свойство Сумма с накопление (Running Sum) в бланке свойств в «Для всего» в области нижнего колонтитула создайте несвязанное поле с подписью "Итого:" и источником данных : =[общий].

 

8. Просмотрите результаты и сохраните с именем 'Сведения по зарплате'.

 

3.2 Создание отчетов с помощью мастера:

 

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

 

3.2.1. Рассмотрим создание отчетов с помощью мастера на примерах отчетов "Отчет о оценках" и "Почтовые наклейки".

 

Для создания отчета "Отчет о оценках":

1.При активном окне отчетов, щелкнем по кнопке Создать и выберем Мастера отчетов. Выберем в окне БД таблицу "Результаты".

2. Перенесите поля для участия в создании отчета (Номер_С, Оценка_Т, Оценка_Л, Оценка_П, Задолженность). Уровень группировки не добавляем, т.к. Access автоматически выберет поле Номер_С.. Access предлагает 6 макетов отчетов, выберете любой и поставьте галочку в строке, где Access спрашивает, хотите ли вы настроить ширину полей для размещения на одной странице отчета Далее выберете стиль отчета и введите его имя.

Сохраните отчет с именем "Отчет о оценках".

 

Рассмотрим еще один пример создания отчета с помощью мастера:

1. Выберите построение отчета с помощью мастера по построению почтовых наклеек на основе таблицы "Студенты".

2. Мастер попросит выбрать размер наклейки.

3. Установите нужный размер для наклейки и установки шрифта и цвета (поэкспериментируйте).

Прототип наклейки: Сдайте задолженность

Фамилия

Имя

Отчество

Группа

4. Задайте сортировку по фамилии студента.

5. Сохраните с именем "Почтовые марки".

3.3.Создание сложного отчета:

Данный отчет будет в виде информации о студенте и его успеваемости.

 

1.3.1 Создадим подчиненный отчет:

 

1.Создадим его на основе запроса «Успеваемость», имеющий только область данных. Выведите в нем все поля

2. Задайте группировку по полю Номер_С и сортировку по возрастанию.

3. Сохраните его в БД как "Отчет об успеваемости".

3.3.2 Создадим главный отчет и свяжем с ним подчиненный:

1. Главный отчет создадим на базе таблицы «Студенты», с полями Фамилия, Имя, Группа щелкните по кнопке создания нового отчета.

2. Добавьте области примечаний. Сделайте ширину верхнего примечания нулевой, а в области. Нижнего создайте поле с данными: =Page. В верхний колонтитул занесите название института, а в область нижнего – его координаты.

4. Щелкните по кнопке для вставки подчиненной формы/отчета на панели элементов конструктора отчетов и создайте элемент управления на свободном месте области данных. В бланке свойств подчиненного отчета свойство Источник данных Access занесет

Отчет. [Отчет об успеваемости], префикс Отчет указывает, что нужно вставить отчет, а не форму. По двойному щелчку можно открыть в режиме конструктора подчиненный отчет из главного.

 

6. Посмотрите результат работы и сохраните отчет с именем "Оценки".

 

3.4Создание отчета с внедрением объектов:

 

Создадим запрос «Ведомость» на основе таблиц «Результаты» и «Студенты» и запроса «Успеваемость» с полями:

Номер_С

Фамилия

Имя

Отчество

Группа

Итоговая

Приступим к созданию отчета:

1. Создайте пустой отчет на основе запроса "Ведомость".

2. Определите группу по полю "Номер_С" и установите свойство группы Заголовок группы в Да.

3. Откройте разделы Заголовок/Примечание, выполнив соответствующую команду меню. В разделе заголовка отчета поместите подпись для заголовка − "Ведомость деканата".

4.У левого края Верхний колонтитул вставьте подпись "Номер студента", а затем по порядку вставляйте группы подписей. Перетащите поле "Номер_С" из списка полей в область примечаний группы.

5.Создадим с помощью мастера диаграмм отчет «Ведомость−график» с полями Номер_С и Итоговая на основе запроса «Успеваемость»:

Внедрим полученный отчет в отчет «Ведомость»

6. При просмотре результатов работы Вы увидите, что отчет не может быть распечатан на стандартной странице. Выполните команду Файл/Печать Свойства, выберите ориентацию Альбомная и установите величину правого и левого поля страницы − 0.5 д.

7. Чтобы установить вторую логическую страницу отчета, расширьте отчет до 20 дюймов, выделите все элементы первой части отчета и выполните Правка/Копировать. Переместите дублированные элементы в область между 10 и 20 дюймами. Измените подписи и выражения для данных.

8. Посмотрите результаты работы и сохраните как "Ведомость".

Результаты работы

 

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

− макеты отчетов различных видов с учетом полученных заданий, которые сохранены в Вашей базе данных;

− отчет с кратким описанием проделанной работы и приложением распечатанных отчетов.

ЛАБОРАТОРНАЯ РАБОТА №9

АВТОМАТИЗАЦИЯ ПРИЛОЖЕНИЯ С ИСПОЛЬЗОВАНИЕМ МАКРОСОВ

Тема:Автоматизация приложения с использованием макросов в среде Microsoft Access 97 .

Цель: Обработка событий для форм и отчетов приложения и выполнение различных действий в зависимости от значений данных.

Краткие теоретические сведения

Макросы могут вызываться в ответ на многие виды событий: изменение данных, открытие или закрытие формы или отчета и даже передача фокуса от одного…   1.2.Access для работы с макросами предоставляет особую панель инструментов (слева направо):

Постановка задачи

2.1 Освойтесь с основными принципами построения макросов. 2.2 На основе своего приложения создайте макросы, обрабатывающие события Ваших…  

Результаты работы

─ тексты макросов, выполняющих заданные функции; ─ отчет с кратким описанием проделанной работы. Приложение 1. Краткий обзор макрокоманд:

ЛАБОРАТОРНАЯ РАБОТА №10

ИСПОЛЬЗОВАНИЕ МОДУЛЕЙ ACCESS BASIC ДЛЯ АВТОМАТИЗАЦИИ ПРИЛОЖЕНИЙ

Тема: Использование модулей Access Basic для автоматизации приложений Microsoft Access.

Цель: Приобретение навыков программирования на языке Access Basic с целью автоматизации приложений.

Краткие теоретические сведения

Хотя макросы являются мощным средством, все-таки есть задачи, которые невозможно выполнить с помощью макросов.   1.1 База данных может содержать 2 вида модулей:

Можно объявить тип данных переменной, добавив справа к имени символ обозначения типа (например, MyVar имеет тип integer). Если переменная в программе не объявлена явно, то Access присваивает ей тип variant.

Для определения общедоступной (глобальной) переменной используйте инструкцию Global в разделе описаний общего модуля.

 

1.7. Инструкции:

 

1. CONST ─ используется для определения константы.

Синтаксис:

{Global} Const {имя-константы=<константное выражение>}, …

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

A_, DATA_, DB_, LB_, OBJSTATE_, OLE_, SYSCMD_.

Пример:

Global Const PI=3.14159

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

Синтаксис:

Global {имя переменной [([<границы-массива>],...)]

[As тип данных]},...

где <границы-массива> есть

[нижняя граница To] верхняя-граница

Пример:

Global lngMyNbr As Long

3. ReDim ─ используется для объявления динамического массива внутри процедуры или для изменения размеров уже объявленного.

Синтаксис:

ReDim [Preserve] {имя-переменной (<границы-массива>,...)

[As тип-данных]},...

По умолчанию нижняя граница равна 0. Когда используется слово Preserve, можно изменять границы только в последнем измерении. Пример:

ReDim strProduct (20) As String * 25

динамическое размещение массива с именем strProduct из 20 строк по 25 символов.

4. STATIC ─ используется для объявления переменной, которая будет использоваться только в этой процедуре.

Синтаксис:

Statiс {имя-переменной [({<границы-массива>},...)]

[As тип-данных] },...

5. TYPE ─ используется внутри раздела описаний для создания собственного типа данных в виде структуры.

Синтаксис:

Type имя-типа

{имя-переменной As тип-данных} ...

End Type

Пример:

Type MyRecord

ID As Long

Name As String

Fam As String

End Type

Для объявления переменной этого типа и присвоения значения текстовой переменной введите внутри процедуры:

Dim Contacts As MyRecord

Contacts.Name="John"

 

1.8.Семейства, объекты, свойства и методы.

 

Microsift Access включает в себя 2 основных системы:

─ ядро приложения, которое обеспечивает конечный пользовательский интерфейс приложения;

─ ядро БД Microsoft Jet, которое управляет хранением данных и определений всех объектов Вашей БД.

Ядро приложения формирует и поддерживает семейство форм (все открытые формы) и семейство отчетов (открытых) и создает 2 специальных объекта: Приложение(Application) и (Экран)Screen.

Объект Application обладает тремя свойствами:

─ CurrentObjectName (имя текущего объекта);

─ CurrrentObjectType (тип текущего объекта);

─ MenuBar (строка меню).

Вы можете узнать значение свойства CurrentObjectName и тем самым определить имя текущего активного объекта. Чтобы определять тип текущего объекта, можно сравнить значение свойства

CurrentObjectType с внутренними константами A_TABLE, A_QUERY, A_FORM, A_REPORT, A_MACRO и A_MODULE. Вы можете присвоить свойству MenuBar имя макроса строки меню, чтобы установить для окна Базы

Данных пользовательскую строку меню.

ОбъектScreen (Экран) обладает четырьмя полезными свойствами:

ActiveControl (Активный Элемент управления);

ActiveForm (Активная Форма);

ActiveReport (Активный Отчет);

PreviousControl (Предыдущий Элемент управления).

Не зная имен, вы можете ссылаться на получивший фокус элемент управления (если баковой имеется), получившую фокус форму (если имеется), получивший фокус отчет (если имеется) или имя элемента управления, который прежде имел фокус (дополнительные сведения по этому вопросу приводятся ниже).

Ядро базы данных Microsoft Jet осуществляет управление всеми объектами в иерархической структуре семейств, объектов и свойств. При открытии базы данных в Microsoft Access, ядро базы данных Microsoft Jet сначала устанавливает семейство Workspaces (Сеансы работы и объект Workspace (Сеанс работы) по умолчанию. Если ваша база данных защищена, Access попросит вас ввести пароль и идентификатор пользователя, и тогда в объекте Workspace, установленном по умолчанию, ядро базы данных Microsoft Jet сможет создать объект User (Пользователь) и объект Group (Группа). Если открываемая база данных не защищена, ядро базы данных Microsoft Jet создает принятые по умолчанию объекты: объект типа User с именем Admin (Администратор) и объект типа Group с тем же именем Admin. Наконец, ядро базы данных Microsoft Jet создает для сеанса работы, установленного по умолчанию, объект Database (База данных). Ядро базы данных Microsoft Jet использует информацию о текущем объекте типа User и/или Group для того, чтобы определить, имеете ли вы право доступа к любому из объектов базы данных.

После того как база данных будет открыта, ядро приложения проверяет, существует ли в базе данных группа макросов с именем Autoexec. Если она находит Autoexec, то ядро приложения запускает эту группу макросов ─ для того, чтобы начать работу с приложением. Если такая группа макросов не существует, то ядро приложения выводит на экран окно базы данных и ждет, когда

пользователь выберет следующее действие с базой данных из доступных в интерфейсе.

Вы можете использовать процедуры Access Basic, которые вызываются макросом Autoexec или запускаются событием формы или отчета для того, чтобы создать дополнительные объекты типа Database в семействе Databases, открывая дополнительные файлы с расширением MDB.

Каждый открытый объект типа Database имеет семейство Containers (Контейнеры), и это семейство используется ядром базы данных Microsoft Jet для хранения (с использованием семейства Documents) определений всех пользовательских таблиц, запросов, форм, отчетов, макросов и модулей. Семейство TableDefs (Таблицы) можно использовать для того, чтобы посмотреть и модифицировать существующие таблицы. Вы можете также создать новые объекты типа TableDef (Таблица) внутри этой семейства. Аналогично, в семейство Relations (Отношения) входят объекты типа Relation (Отношение), которые определяют, как таблицы связаны между собой и какие режимы обеспечения целостности данных применяются к таблицам. Семейство QueryDefs (Запросы) включает в себя объекты типа QueryDef (Запрос), которые содержат определения запросов базы данных. Вы можете модифицировать существующие запросы либо создать новые. Наконец, семейство Recordsets (Наборы записей) включает в себя соответствующие объекты типа Recordset (Набор записей) для каждого открытого набора записей в базе данных.

 

1.10 Ссылки на семейства, объекты и свойства.

Существует три варианта синтаксиса ссылок на объект внутри семейства:

_ ИмяСемейства[ИмяОбъекта].

_ ИмяСемейства("ИмяОбъекта").

Например, Forms("'MyForm").

_ ИмяСемейства(ОттсительныйНомерОбьекта).

Access нумерует объекты внутри семейства от нуля (0) до ИмяСемейства.Соunt 1. Например, вы можете определить число открытых форм, выяснив значение свойства Count (Счетчик) семейства форм: Forms.Count.

Вы можете сделать ссылку на вторую открытую форму в семействе, написав

Forms(1).

Если вы хотите выяснить, как много объектов типа Workspace существует в семействе Workspaces, вам придется использовать ссылку на свойство Count семейства Workspaces. Например, вы можете ссылаться на свойство Count таким образом:

DBEngine. Workspaces. Count

Используя третий метод ссылок на объект, который описан выше, вы можете сослаться на объект Workspace, назначаемый по умолчанию, таким образом:

DBEngine. Workspaces ( 0 )

Аналогично, ссылайтесь на текущую открытую базу данных так:

DBEngine.Workspaces (0). Databases (0)

В связи с тем, что семейство Databases ─ это семейство, назначаемое по умолчанию для семейства Workspaces, вы можете задать ссылку на текущую открытую базу данных следующим образом:

DBEngine. Workspaces ( 0 ) ( 0 )

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

Чтобы упростить конструкции имен, используйте объектные переменные:

Set <имя-переменной> = <ссылка-на-объект>

Сначала Вы должны объявить <имя переменной> в инсрукциях

Dim,Global или Static.

Примеры:

Чтобы создать переменную, ссылающуюся на текущую базу данных,

введите:

Dim dbMyDB As Database Set dbMyDB = DBEngine.Workspaces(0). Databases (0)

Чтобы создать переменную, ссылающуюся на таблицу "Преподаватели" в текущей базе данных, используя только что созданную переменную dbMyDB, введите:

Dim tblMyTable As TableDef Set tblMyTable = dbMyDB! [Преподаватели]

У вас нет необходимости явно ссылаться на семейство TableDefs базы данных (как dbMyDB.TableDefs![Преподаватели]), так как TableDefs ─ это семейство базы данных, назначаемое по умолчанию.

Чтобы создать с помощью переменной ссылку на поле "Фамилия" в таблице "Преподаватели", используя только что созданную переменную tblMyTable, введите:

Dim fldMyField As Field Set fldMyField =

tblMyTable![Фамилия]

1.11 Методы объектов

Когда вы собираетесь по отношению к объекту вашей базы дан­ных выполнить некоторое действие (открыть запрос как набор записей или перейти к следующей строке в наборе записей), вы можете применить метод к объекту или к объектной переменной, которая указывает на этот объект. В некоторых случаях вы используете метод для создания нового объекта. Многие методы имеют аргументы, с помощью которых вы можете уточнить, как должен действовать этот метод на объект. Например, при выполнении метода OpenRecordset вы можете сообщить, хотите ли вы создать для некоторой таблицы динамический набор записей (dynaset-набор записей, которые можно изменять) либо копию (snapshot), которую можно использовать только для чтения.

Microsoft Access поддерживает множество различных методов. Наиболее полезная группа методов ─ это та, которая позволяет создать набор записей, а затем прочитать, обновить, вставить новую или удалить строку из набора данных.

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

OpenRecordset к текущей базе данных (указав имя таблицы, имя запроса или инструкцию SQL), к объекту QueryDef, TableDef или к другому объекту Recordset

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

Например, чтобы объявить набор записей для таблицы "Преподаватели" в базе данных STUD и открыть его как таблицу (что позволяет использовать индекс), введите:

Dim dbStud As Database

Dim rcdDriver As RecordSet

Set dbStud = DBEngine.Workspaces (0).Databases (0)

SetrcdDrive=dbPrompt.OpenRecordSet ("Преподаватели", DB_OPEN_TABLE) После того, как вы открыли некоторый набор записей, то для того, чтобы перейти к определенной записи, вы можете использовать один из методов Move. Чтобы осуществить переход к первой строке набора записей, используйте Recordset.MoveFirst. Другие методы для перемещения к определенным записям ─ это MoveLast, MoveNext и MovePrevious. Если вы хотите перейти к определенной строке в наборе записей, используйте один из методов Find. Вы должны будете указать строковую переменную, содержащую условие поиска необходимых вам записей. Синтаксис условия такой же, как для предложения WHERE в SQL, но само ключевое слово WHERE указывать не нужно.

Например, чтобы найти первый компонент в наборе записей "Преподаватели", у которого стаж меньше 10 лет, используя только что созданный набор записей, введите:

rcdDriver.FindFirst "Стаж < 10"

Чтобы удалить некоторую строку из набора записей, перемести­тесь к

нужной строке набора и затем обратитесь к методу Delete. Чтобы обновить содержимое некоторых строк из набора записей, пе­рейдите к нужной строке набора и затем обратитесь к методу Edit, чтобы захватить запись и разрешить ее обновление. Тогда вы сможете по имени обратиться любому полю записи и изменить его значение. Используйте метод Update для того, чтобы сохранить сделанные вами в наборе записей изменения прежде чем вы переместитесь к другой строке. Наконец, чтобы вставить новую строку в набор записей, используйте метод AddNew, чтобы начать создание новой строки. Установите значения всех обязательных полей записи и используйте метод Update для того, чтобы сохранить новую запись.

 

1.12 Функции и подпрограммы.

 

Вы можете создать в Microsoft Access Basic два типа процедур ─ функции (Function) и подпрограммы (Sub), называемые также процедурами-функциями и процедурами-подпрограммами. Процедуры обоих типов могут иметь аргументы ─ переменные, с помощью которых вы передаете процедурам значения, определяющие работу процедуры при конкретном ее вызове. Функция может возвратить единственное значение, а подпрограмма значений не возвращает. Кроме того, функции можно выполнять из любого места Microsoft Access, в том числе и из выражений в запросах, и из макросов. Подпрограмму можно выполнить только при вызове ее из функции, из другой подпрограммы или как процедуру обработки события в форме или отчете. Инструкция Function

Используйте инструкцию Function для того, чтобы объявить но­вую функцию, определить ее аргументы, тип возвращаемых ею данных и текст программы этой функции.

Синтаксис:

[Static] [Private] Function имя-функции [<аргументы>]

[Аs тип -данных ] [<инструкции-тела-функции>]

[имя-функции = <выражение>]

[Exit Function]

[<инструкции-тела -функции>]

[имя-функции == <выражение>]

End Function

где <аргументы> - это

{[ByVal] имя-аргумента [As тип-данных]],...

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

Используйте ключевое слово Private (Личный), чтобы сделать процедуру-функцию доступной только процедурам того же самого модуля.

Для определения типа возвращаемых функцией значений можно использовать символ обозначения типа данных в конце имени функции или предложение As (как для обычной переменной). Если вы не объявите тип данных, Access предположит, что функция возвращает значение типа Variant.

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

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

Если вы используете при объявлении аргумента ключевое слово ByVal (By Value ─ по значению), то Access передаст функции копию соответствующего аргумента. Любые изменения, которые вы сделаете с аргументом, объявленным с ключевым словом ByVal, не отразятся на состоянии исходной переменной в вызывающей процедуре.

Используйте инструкцию Exit Function в любом месте тела процедуры ─ для того, чтобы после перехвата ошибки нормально вернуться в вызывающую

процедуру.

Для объявления новой процедуры используйте следующий синтаксис:

[Static] [Private] Sub имя-подпрограммы ( [<аргументы>] )

[Аs тип -данных ] [<инструкции-тела-подпрограммы>]

[Exit Sub]

[<инструкции-тела -подпрограммы>]

End Sub

где <аргументы> - это

{[ByVal] имя-аргумента [As тип-данных]],...

 

1.1.3 Управление выполнением программы.

 

Microsoft Access Basic предоставляет следующие средства для осуществления управления ходом выполнения процедуры:

1. Инструкция Call

Используйте инструкцию Call для передачи управления подпрограмме.

Синтаксис:

Call <имя-подпрограммы> [ <аргументы> ]

или

<имя-подпрограммы>[< аргументы>]

где <аргументы> - это { [ByVal] <выражение>} , ...

2. Конструкция Do...Loop

Используйте конструкцию Do. ..Loop для выполнения блока инструкций несколько раз. Вы можете также определить условие, вызы­вающее выход из цикла в тот момент, когда условие станет ложным.

Синтаксис:

Do [{While | Until} <условие>]

[ <инструкции-тела -цикла>

[Exit Do]

[ <инструкции-тела -цикла>

Loop или Do

 

{<инструкции-тела-цикла>]

[Exit Do]

[ <инструкции -тела -цикла>

Loop [{While | Until} <условие>]

3. Конструкция For...Next

Используйте конструкцию For... Next для того, чтобы выпол­нить последовательность инструкций определенное число раз.

Конструкция For...Next особенно полезен в том случае, когда нужно выполнить последовательную обработку элементов массива или семейства.

Синтаксис:

For счетчик = начальное-значение То кoнeчнoe-знaчeние

[Step шаг]

{<инструкции-тела-цикла>]

[Exit For]

[ <инструкции-тела -цикла >]

Next [ счетчик], . . .

4. Инструкция GoTo

Используйте инструкцию GoTo для того, чтобы осуществить безусловный переход к другой инструкции вашей процедуры.

Синтаксис:

GoTo { метка 1 номер-строки}

Вы можете пометить строку процедуры, содержащую инструкцию, поместив в начале строки начинающуюся с буквы символьную метку, которая имеет длину не более 40 символов и заканчивается двоеточием (:).

5. Условная конструкция lf...Then...EIse

Используйте конструкцию If...Then...Else для выполнения той или другой группы инструкций в зависимости от значения условного выражения.

Синтаксис:

If <условие-1> Then

 

["<инструкции-процедуры-1>] [Elself условие-2 Then [<инструкции-процедуры-2>] ]... [Else [<инструкции-процедуры-п>]]

End If

Или If <условие> Then <инструкция-то> [Else <инcтpукция-инaчe>]

6. Условная конструкция Select Case

Используйте конструкцию Select Case для выполнения инструкций в зависимости от результатов сравнения некоторого выражения со списком значений или диапазоном значений.

Синтаксис:

Select Case <проверяемое-выражение>

[Case <список-сравнения-1>

[<инструкции-процедуры-1>] ]

[Case Else

[<инструкции-процедуры-n>] ]

End Select

где

<проверяемое-выражение> ─ это некоторое числовое или строковое выражение;

<список-сравнения> есть {<элемент-сравнения>, . . . }

где <элемент-сравнения> это { выражение | выражение1 То выражение2 |

Is <оператор-сравнения> выражение} и где <onepamop-cpaвнeния> есть:

{= | <>| <|>|<=| >=}.

Если <npoвepяeмoe-выpaжeнue> совпадает с одним из <элементов-сравнения> в предложении Case, то Access выполняет инструкции этого предложения. Если <элемент-сравнения> представляет собой одиночное выражение, то для того, чтобы выполнялся блок инструкций этого предложения, <проверяемое-выражение> должно быть равно <элементу-сравнения>. Если <элемент-сравнения> содержит ключевое слово То (До), то первое выражение должно быть меньше второго, и значение <проверяемого-выражения> должно находиться между значениями выражения 1 и выражения2.Если <элемент-

сравнения> содержит ключевое слово Is, то результат сравнения должен быть истинным.

7. Инструкция Stop

Используйте инструкцию Stop для того, чтобы приостановить выполнение вашей процедуры.

Синтаксис:

Stop

Инструкция Stop производит то же действие, что и установка контрольной точки на некоторой инструкции. Чтобы оценить состояние процедуры после того, как

8. Конструкция While. . .Wend

Используйте конструкцию While...Wend для того, чтобы повторно выполнять блок инструкций до тех пор, пока условие остается истинным.

Синтаксис:

While <условие>

[ <инструкции-процедуры> ]

Wend

9. Инструкция DoCmd

Используйте инструкцию DoCmd для того, чтобы выполнить макрокоманду из процедуры Access Basic.

Синтаксис:

Do Cmd макрокоманда [аргумент-макрокоманды], . . .

10. Инструкция On Error

Используйте инструкцию On Error для перехвата ошибок, передачи управления в блок обработки ошибок или для того, чтобы игнорировать некоторые ошибки или вовсе отключить режим перехвата ошибок.

Синтаксис:

On Error {GoTo идентификатор-строки I Resume [Next] I GoTo 0 }

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

On Error Resume Next

Чтобы включить режим перехвата ошибок и в случае возникновения ошибки продолжить выполнение программы с инструкции с меткой MyError, введите:

On Error GoTo MyError:

Чтобы отключить режим перехвата ошибок в текущей процедуре, введите:

On Error GoTo О

Постановка задачи

 

Автоматизировать приложение с помощью модулей Access Basic.

 

Решение задачи

3.1 Организация удаления данных из БД. Если вы правильно определили все связи в БД и включили режим поддержания… Тем не менее существует вероятность того, что может понадобиться запись о студенте, который уже не числится в…

ЛАБОРАТОРНАЯ РАБОТА №11

СОЗДАНИЕ ПОЛЬЗОВАТЕЛЬСКОЙ ПАНЕЛИ ИНСТРУМЕНТОВ

Тема:Создание пользовательской панели инструментов, строки меню. Создание панели управления приложением.

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

 

Постановка задачи

 

1. Приобрести навыки работы по определению новой панели инструментов.

2. Создать пользовательскую строку меню.

3. Определить главную панель управления для созданию конечного приложения.

4. Используйте макрос Autoexec для запуска приложения.

 

Пример решения

2.1 Определение новой панели инструментов.

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

Для этого ознакомьтесь с настройкой панелей инструментов, основными принципами работы с ними и создание пользовательских панелей:

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

2.1.2.При выборе Настройка в левой стороне диалогового окна Панели инструментов вы видите имена всех стандартных панелей инструментов, предоставляемых Microsoft Access. В конце списка находятся три специальные встроенные панели инструментов, которые Access выводит в конце списка.

Панель инструментов Microsoft содержит кнопки, которые позволяют запустить семь различных приложений Windows фирмы Microsoft: Excel, Word, Mail, PowerPoint, FoxPro, Project и Scheduler+.

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

Справа в диалоговом окне вы увидите кнопки: Создать, Переименовать, Удалить, Сброс, Свойства.

2.1.3.Если вы изменили одну из встроенных панелей инструментов, можно возвратить ей стандартный вид, выделив эту панель в диало­говом окне и щелкнув по кнопке Сброс. Чтобы вы случайно не удалили свои изменения, Access попросит у вас подтверждения. Любая определенная вами новая панель инструментов будет доступна только в той базе данных, которая была открыта в момент создания этой панели инструментов. Если вы хотите, чтобы нестандартная панель инструментов была доступна во всех базах данных, следует использовать одну из двух встроенных панелей инструментов. Access предоставляет две пустые панели инструментов ─ Служебная программа1 и Служебная программа2, которые можно использовать для создания пользовательского набора кнопок, доступных во всех базах данных. Единственным недостатком этих двух панелей инструментов является то, что вы не можете присвоить им другие имена.

2.1.4.Для того, чтобы начать определение новой панели инструментов, щелкните по кнопке Создать. Access попросит вас ввести имя для новой панели инструментов. Вы увидите это имя в конце списка имен, а в рабочем окне Access появится новое маленькое серое окно. (Если вы хотите использовать средства выравнивания положения элементов управления в форме, в каждой создаваемой вами базе данных, то для определения пользовательских кнопок позаимствуйте

одну из пустых панелей служебных программ. Прокрутите вниз список панелей инструментов до тех пор, пока не увидите строку Служебная программа1. Чтобы открыть панель инструментов, выделите ее и щелкните по кнопке (справа от названия в квадратике должна появиться галочка). Панель инструментов Служебная программа1 появится в виде небольшого серого окна в рабочем пространстве Access.)

2.1.3. Нажмите кнопку создать. В диалоговом окне Создание панели инструментов введите имя новой панели «Панель форм». Панель появилась в списке. Затем щелкните по кнопке Команды.

Это диалоговое окно позволяет вам изменить кнопки любой открытой панели инструментов. В правой части диалогового окна находится список всех категорий кнопок, предоставляемых Access ─ вам нужно выбрать кнопки для изменения размеров элементов управления и их выравнивания в форме. Чтобы увидеть эту группу кнопок, выделите категорию Конструктор форм/отчетов. При перемещении указателя по этим кнопкам Access выводит в нижней части диалогового окна описание действия каждой из этих кнопок. При разработке форм полезны кнопки Последовательность перехода (третья кнопка ), кнопки Линейка, Привязать к сетке, Дублировать, По левому краю, По правому краю, По нижнему краю, По верхнему краю, На передний план и На задний план. Перетащите эти кнопки по одной в новую панель инструментов.

2.1.5 Настроим внешний вид новой панели инструментов.

Самый простой способ расположить кнопки в нужном порядке ─ это добавлять их в панель инструментов в выбранном порядке в один ряд слева направо. Если вы создаете эту панель инструментов, когда форма открыта не в режиме конструктора, то все кнопки панели будут блеклыми (недоступны). Как только вы откроете форму в режиме конструктора, соответствующие кнопки станут доступными. Построив панель инструментов, вы можете изменить расположение кнопок и добавить пробелы между ними. Чтобы внести любые изменения, щелкните левой кнопкой мыши на панели инструментов, появится меню, выбираем команду Настройка. Когда откроется диалоговое окно настройки панелей инструментов, все кнопки окажутся доступными для редактирования: можно удалить любую кнопку из открытой панели инструментов (включая все стандартные панели инструментов) щелкнув по этой кнопке и перетащив ее в группу кнопок диалогового окна. Вы можете переставить кнопки, щелкнув по одной из них и перетащив ее в новое место на панели. Вы можете добавить кнопку, выбрав из группы кнопок нужную и перетащив ее в любую панель инструментов. Для удобства использования кнопки часто группируют, исходя из их функционального назначения, и между такими блоками кнопок оставляется пробел. На созданной панели инструментов выбираем кнопку для редактирования. Затем нажмите кнопку Изменить выделенный объект, появиться диалоговое окно. Вставьте пробелы между кнопками. Измените положение четвертой кнопки, сделав ее второй. Чтобы вставить пробел перед любой кнопкой, щелкните по этой кнопке при нажатой клавише Shift и переместите ее вправо (вам, возможно, придется изменить размер окна панели инструментов, чтобы вы могли увидеть эти пробелы между кнопками). Настройте панель по своему усмотрению.

 

2.1.6. Создайте для дальнейшей работы панель инструментов «Настраиваемая программа 1» и для нее создадим простую функцию, позволяющую открывать и закрывать пользовательскую панель инструментов. На пользовательские кнопки, которые Вы добавляете в любую панель инструментов, накладываются ограничения. Во-первых, они могут выполнять лишь простые действия, такие, например, открыть таблицу или форму, выполнить запрос, открыть отчет в режиме предварительного просмотра или запустить макрос. Во-вторых, вы не можете создавать пользовательскую кнопку на панели инструментов, действующую подобно выключателю.

Можно, однако, эмулировать кнопку выключатель, написав простую процедуру-функцию Access Basic и создав макрос для ее запуска. К сожалению, нельзя запустить функцию Access Basic непосредственно пользовательской кнопкой панели инструментов. Чтобы создать функцию-выключатель (открыть/закрыть) панели инструментов, выполните описанные ниже простые действия:

1. Перейдите в окно базы данных и щелкните по корешку Модуль. Щелкните по кнопке Создать, чтобы открыть новый модуль Access Basic.

2. Во вторую строку окна введите с клавиатуры следующий текст:

Global intFormTool As Integer 'Выключатель Вкл/Выкл 'пользовательской' панели инструментов

3. Выполните команду Вставка/Процедура. Access выведет диалоговое окно. Введите имя новой функции :FlipFormTool.

4. Щелкните по кнопке ОК. Access выведет шаблон для новой функции. Начиная с третьей строки, введите с клавиатуры следующий текст:

If intFontiTool Then

intFormTool = False

DoCmd. ShowToolbar “Настраиваемая программа” 1 , AsTOOLBARNO

Else

intFormTool = True

DoCmd. ShowToolbar “Настраиваемая программа 1”, A_TOOLBAR_YES

End If

5. Выполните команду Сохранить, назовите модуль "Панель инструментов" и затем закройте окно модуля.

 

2.1.7.Макрос для запуска функции-выключателя.

Вам понадобится макрос, который Вы сможете запустить с помощью кнопки пользовательской панели инструментов для выполнения только что созданной функции. Перейдите в окно базы данных и щелкните по корешку Макросы. Чтобы открыть окно нового макроса, щелкните по кнопке Создать. В первую ячейку столбца с именем Общая область введите с клавиатуры или выберите команду ЗапускПрограммы из раскрывающегося списка этой ячейки. Затем перейдите в нижнюю панель параметров макрокоманды и введите имя функции с последующими за ним открывающей и закрывающей круглыми скобками FlipFormTools (). Выполните команду Сохранить и назовите макрос "Инструменты форм".

 

2.1.8.Определение кнопки для запуска макроса.

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

Найдите в списке Категории ─ Все макросы. Выделите в списке макрос "Инструменты форм", перетащите его в панель инструментов формы в режиме конструктора и опустите между кнопками панели элементов и палитры. Access по умолчанию использует в качестве картинки на кнопке значок макроса. Всплывающая подсказка имеет текст "Запуск макроса 'Инструменты формы'". Access позволяет вам изменить как изображение на кнопке, так и всплывающую

подсказку к ней.

Чтобы открыть контекстное меню, содержащее опцию выбор оформления кнопки, щелкните правой кнопкой мыши по вашей новой кнопке на панели инструментов и выберите нужное оформление. Подпись к кнопке можно сократить, оставив лишь имя "Инструменты формы" ─ это описание будет появляться в качестве всплывающей подсказки, когда вы поместите на эту кнопку указатель мыши.

Щелкните по кнопке OK, чтобы изменить значок на вашей новой кнопке. Закройте диалоговое окно настройки панелей инструментов.

 

Теперь с помощью новой кнопки панели инструментов работа с формой можно открывать и закрывать панель инструментов Пользовательская программа 1, которая содержит пользовательский набор кнопок для работы с записями.

2.2 Создание пользовательской строки меню.

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

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

Нужно перечислить все команды меню в столбце Имя макроса. С помощью символа "&" Вы можете определить для каждой команды быструю клавишу. Вы можете попросить Access провести линию между разделами меню, вставив в столбец Имя макроса строку со знаком "─". Макрос команды меню может

запускать другую макрокоманду ДобавитьМеню, чтобы добавить подчиненное меню или макрокоманду (несколько макрокоманд), определяющую действия, которые должны выполняться Microsoft Access при выборе этой команды. Во многих случаях, чтобы сделать доступными команды встроенной строки меню из вашей пользовательской строки, вы будете пользоваться макрокомандой КомандаМеню. Также вы можете выполнять любые другие макрокоманды, включая запуск другого макроса или функции Access Basic.

Определить полный набор макросов для создания меню может быть довольно сложно. Это будет особенно утомительно, если вам требуется определить различные пользовательские строки меню для разных форм. Вы можете использовать специальный построитель меню, который поможет вам построить макросы определения меню. Щелкните по окну базы данных и выполните команду Сервис/Макрос/Создать меню из макросов. Access запустит построитель меню и выведет на экран первое диалоговое окно .

В диалоговом окне показаны все доступные в вашей базе данных макросы. Вы можете выделить определяющий строку меню макрос и щелкнуть по кнопке Изменить ─ чтобы пересмотреть и внести изменения в определение строки меню. Вы можете выделить макрос и удалить его, щелкнув по кнопке Удалить. Если вы удаляете макрос, который определяет меню, построитель меню также удалит все макросы, которые добавляют команды в это меню. Щелкните по кнопке Создать, чтобы начать определение нового набора макросов для строки меню. Построитель меню выведет на экран следующее диалоговое окно. Вы можете либо начать создание совершенно новой строки меню, либо выбрать шаблон, который полностью воспроизводит встроенные строки меню Microsoft Access. Поскольку вы хотите лишь немного изме­нить меню для всех форм в данном приложении, хорошей основой может послужить строка меню Форма. Выберите для создания меню шаблон Форма и щелкните по кнопке ОК. Построитель меню выведет на экран выбранный шаблон в диалоговом окне.

В диалоговом окне Построителя меню-[Новое меню] вы можете щелкнуть по любому элементу определения меню в нижней части окна и увидеть в верхней части окна подробное описание этого элемента.

Если этот элемент выполняет макрокоманду, вы увидите эту макрокоманду и ее аргументы. Построитель меню поддерживает такие макрокоманды, как ДобавитьМеню, КомандаМеню, ЗапускМакроса и ЗапускПрограммы. Если вам нужно построить команду меню, которая выполняет более сложное действие, вы можете с помощью построителя меню построить макрос, а затем его отредактировать.

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

Во многих случаях, вы, наверное, не захотите, чтобы пользователь мог открывать объекты в режиме конструктора или создавать новые объекты. Тогда вы можете удалить команды Создать из меню Файл вместе с его подменю. Вероятно также, что вам не потребуются команды Сохранить форму и Сохранить как для форм. Из меню Вид вы можете удалить команды Form Design, ToolBars и Options.

Добавим в меню Помощь свою команду, которая выводить на экран информационное диалоговое окно. В самом низу меню Help добавлена новая команда Hello! (о меню базы данных STUD), отделенная от стандартных команд "─". Эта команда меню запускает макрос, использующий макрокоманду Сообщение для вывода в диалоговом окне некоторой информации о меню базы данных STUD).

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

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

Если вы установите свойство Меню форм так, чтобы оно указывало на макрос этой строки меню (Stud Form Menubar), то при открытии формы вы сможете увидеть новое меню Help. В БД STUD созданное меню установлено в свойстве Меню формы "Сводные данные".

2.3 Создание панели управления.

Для связки всех форм и отчетов, созданных для БД STUD создадим панель управлении БД ─ "Главная панель". Это форма, построенная со установками свойств, такими же, как и при создании диалогового окна "Диалог печати", построенного при работе с макросами.

Кроме того, она содержит 4 командных кнопки, созданных при помощи панели элементов конструктора форм :

─ Работа с отчетами (выводит на экран "Диалог печати" для организации работы с отчетами БД);

─ Работа с формами (выводит форму для выбора формы, с которой необходимо работать);

─ Окно БД ( активизирует окно БД);

─ Выход из Access.

Для организации работы с формами БД построим форму по свойствам аналогичную "Диалог печати" и содержащей командные кнопки вызова всех форм БД:

─ "Задолженность",

─ "Преподаватели",

─ "Студенты_главная", и кнопку активизации "Главной панели" (переход к главной панели и закрытие окна работы с формами).

 

2.4 Использование макроса Autoexec для запуска приложения.

Можно создать макрос, который называется Autoexec ( автозапуск), и Access каждый раз при открытии БД будет запускать его. Autoexec БД STUD используется для открытия "Главной панели" и выполняет следующие действия: устанавливает вывод результатов макроса, выключает песочные часы, прячет окно БД STUD и открывает форму "Главная панель".

Так как БД STUD используется в учебных целях, то текст данного макроса сохранен с именем "Запуск", чтобы он не запускался автоматически.

Таблица 2.

Макрокоманда	Аргументы	Установки
ВыводНаЭкран   ПесочныеЧасы ВыполнитьКоманду ОткрытьФорму	Включить вывод Текст строки состояния Включить Команда Имя	Да   Да WindowHide Главная панель

Если в Autoexec Вы назначите загрузку своих панелей инструментов и меню, то в процедуре обработки события UnLoad формы "Главная панель" уберите Ваши панели инструментов и выведите стандартные.

 

Результаты работы

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

─ определению новой панели инструментов;

─ пользовательская строка меню;

─ главная панель управления для созданию конечного приложения;

─ макрос Autoexec для запуска приложения.

ЛАБОРАТОРНАЯ РАБОТА №12

ОСНОВЫ РАБОТЫ С ДАННЫМИ В ЛОКАЛЬНОЙ СЕТИ

Тема:Основы работы с данными в локальной сети. Транзакции.

 

Цель:Приобретение навыков работы с данными в локальной сети посредством среды Microsoft Access 97

 

Постановка задачи

1. Приобрести навыки работы по настройке оболочки Access для работы в режиме коллективного доступа к данным.

2. Ознакомиться с механизмом работы транзакции.

3. Ознакомиться с основными способами взаимодействия с сервером.

 

Пример решении

 

2.1 Транзакции.

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

Под транзакцией понимается серия изменений, которые проводятся над данными и структурой БД. Если по какой-либо причине операции, входящие в текущую транзакцию не могут быть завершены, то система возвращается в исходное состояние.

Метод BeginTrans объекта Workspace начинает транзакцию, а завершает ее метод CommitTrans. Если по каким-либо причинам приложение не сможет обратится к методу CommitTrans, то система вернется в первоначальное состояние. Выполнение же этого метода делает все изменения необратимыми. Метод Roolback отменяет все изменения в рамках транзакции.

Access поддерживает параллельные и вложенные транзакции. При параллельных требуется создать еще один объект Workspace. Каждая транзакция

завершается независимо друг от друга. Количество вложений не может превышать 5.

Транзакции являются глобальными в рамках объекта Workspace. Т.о. они могут охватывать несколько БД и множество объектов, которые в них содержатся.

Для демонстрации работы транзакций создадим еще одну форму "Замена", которая имеет свойства подобные свойствам формы "Панель управления", и содержит поля для ввода имени таблицы, имени поля в таблице, значение, которое требуется заменить, и значение, на которое требуется заменить искомое.А также кнопку "Заменить". Таким образом она выполняет замену значения поля на новое. Поле управления для ввода имени поля есть поле со списком, у которого Row Source Type установлено в Field list для упрощения ввода (оно будет содержать список полей таблицы, в которой требуется изменить поле, и не допускает возможности ввода нового, а следовательно потенциально неправильного значения).

Шаблон этой формы приведен в приложении 1, рис.1

Значение списка устанавливается свойством "After Update" поля "Tab" (имя поля ввода название таблицы):

Sub Tab_AfterUpdate()

Me!List.RowSource=Me!Tab

End Sub

Свойство кнопки "Заменить" (имя кнопки Finder) связано со следующей процедурой:

Sub Finder_Click ()

Dim dbMyBase As Database, Criteria As String

Dim rcdTab As Recordset, i As Integer, intMsgRtn As Integer

Dim Wks As WorkSpace, j As Integer, k As Integer

Set Wks = DBEngine.Workspaces(0)

Set dbMyBase = DBEngine.Workspaces(0).Databases(0)

k = 0

'проверка ─ есть ли такая таблица в текущей БД

For i = 0 To dbMyBase.TableDefs.Count – 1

If (dbMyBase.TableDefs(i).Name = Me!Tab) = -1 Then

'Если есть, устанавливаем признак и 'открываем набор данных

Set rcdTab = dbMyBase.OpenRecordset(Me!Tab, DB_OPEN_DYNASET)

k = 1

Exit For

End If

Next i

'если таблицы нет, выводим сообщение

If k = 0 Then

intMsgRtn = MsgBox("Такой таблицы нет",38,"Внимание")

GoTo Ex

End If

'определяем критерий

Select Case rcdTab(Me!List).Type

Case DB_INTEGER, DB_BYTE, DB_LONG, DB_DOUBLE

Criteria = Me!List & "=" & Str(Me!Val)

Case DB_TEXT

Criteria = Me!List & "=" & Chr(34) & (Me!Val) & Chr(34) Case Else

MsgBox ("Данный тип поля не обрабатывается")

If intMsgRtn=MsgBox("Продолжаете работу?"), 33, "Внимание! ") Then

rcdTab.Close

Goto Ex

Else

Me!Tab = " "

Me!List = " "

Me!Val = " " Me!ChangeTo = " "

Exit Sub

End If

End Select

'ищем запись

rcdTab.FindFirst Criteria

k = 0

Wks.BeginTrans 'начинаем транзакцию

'пока есть записи, удовлетворяющие условию

Do Until rcdTab.Nomatch

rcdTab.Edit

'меняем значение поля

rcdTab(i) = Me!ChangeTo

rcdTab.Update

'считаем количество измененных записей

k = k + 1

rcdTab.FindNext Criteria

Loop

intMsgRtn = MsgBox("Заменено " & Str(k) & " записей !",

64, "Информация")

If k Then

If intMsgRtn=MsgBox("Сохранить изменения ?",33,"Внимание") Then

Wks.CommitTrans 'завершить транзакцию

Else Wks.Rollback 'откат

End If

Else Wks.Rollback 'откат

End If

'закрываем набор записей

rcdTab.Close

'закрываем форму

DoCmd SetWarnings False

DoCmd Close A_FORM, "Замена"

DoCmd SetWarnings True

End Sub

 

2.2 Работа в режиме коллективного доступа посредством Access 2.0

Microsoft Access автоматически открывает БД с монопольным доступом.

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

Средства настройки оболочки Access позволяют устанавливать режимы по умолчанию для открытия БД и таблиц. Для этого используется команда <View/Options>. В диалоговом окне необходимо в списке "Multiuser/ODBC" выделить "Default Record Locking". После этого, используя список можно выбрать необходимый тип блокировки.

При выборе кнопки "No Locks" можно установить нежесткую блокировку записи, т.е. все редактируемые записи доступны для других пользователей, но если вы отредактируете запись и попытаетесь сохранить изменения, то столкнетесь с 2 вариантами:

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

─ после того, как вы начали редактировать данные, кто-то успел сделать изменения и записать их на диск.

Во втором случае появится окно сообщения, в котором предлагается 3 выхода:

─ игнорировать чужие изменения и записать на диск свои;

─ скопировать измененную запись в буфер обмена;

─ сохранить чужие изменения и отменить свои.

При выборе блокировки изменяемой записи для других пользователей будет заблокирована запись, которую вы редактируете, и соседние с ней, т.к. Access устанавливает блокировку для страницы (2 Кб). Эту блокировку называют "пессимистический".

При выборе блокировки всех записей остальные пользователи не могут открыть таблицу.

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

Можно использовать свойство объекта Application для изменения режимов

блокировки:

Function Set_Option()

Dim pot As String

'установка пессимистический блокировки

Application.SetOption "Default record locking" ,2

pot = Application.GetOption("Default Record Locking") MsgBox(pot)

Set_Option = True

End Function

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

 

2.3 Использование технологии "клиент-сервер".

 

Технология "клиент-сервер" разделяет приложение на 2 части:

─ клиентская часть, которая обеспечивает легкий в использовании, обычно графический интерфейс;

─ сервер обеспечивает управление данными, разделение информации, администрирование и безопасность.

При этой технологии клиентское приложение формирует запрос к серверу БД, на котором выполняются все команды, а результаты команд посылаются потом клиенту для использования и просмотра(когда БД располагается на файл-сервере, его функции ─ хранение данных и обеспечение доступа к ним, а вся обработка производится в компьютере пользователя, т.е. если пользователю требуются 2 строки из таблицы объемом в сотни тысяч записей, то сначала вся таблица с файл-сервера передается на компьютер пользователя, а затем СУБД отбирает нужные записи).

В качестве сервера может выступать любой сервер БД, имеющий драйвер ODBC (Open Database Connectivity), ─ MS SQL Server, Oracle и т.д. ODBC находится как бы посередине между приложением, использующим данные, и самими данными, хранящимися в формате, которые невозможно обработать напрямую в приложении, и используются как средства коммуникации между

клиентом и сервером.

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

Для использования Microsoft Access в качестве сервера в сети необходимо иметь:

─ сетевое ПО, поддерживающее именованные каналы (named pipes), такое как Microsoft LAN Manager, Novell, Netware или Banyan VINES;

─ компьютер с установленным Microsoft Windows и со свободным местом на диске не менее 11Мб для установки Microsoft Access 2.0 плюс дополнительное место для баз данных пользователей;

─ рабочие станции с конфигурацией, требуемой для установки Microsoft Access.

Microsoft Access спроектирован таким образом, что может быть использован как в качестве самостоятельной СУБД на отдельной рабочей станции, так и в сети в режиме "клиент-сервер". Поскольку в Access к данным могут иметь одновременно несколько пользователей, в нем предусмотрены надежные средства защиты и обеспечения целостности данных. Вы можете заранее указать, какие пользователи или группы пользователей могут иметь доступ к объектам БД. Access автоматически обеспечивает защиту данных от одновременной их корректировки разными пользователями. Access также опознает и учитывает защитные средства других присоединенных к базе структур (таких, как БД Paradox, dBase, SQL).

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

СУБД Access имеет наибольшее число способов взаимодействия с MS SQL Server. Рассмотрим два из них:

1. Присоединенные таблицы

---------¬ ------¬ ------------¬ ----------¬ ------------¬

¦ Access +--+ Jet +--+ Диспетчер +--+ Драйвер +--+ SQL Server¦ L--------- L------ ¦ ODBC ¦ ¦ ODBC ¦ L------------

L------------ L----------

Рис. 1. Схема организации работы с присоединенными данными

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

Диспетчер ODBC поддерживает связь между приложением и источником данных, обеспечивая информацией приложение и загружая драйверы динамически по мере необходимости.

Драйвер ODBC выполняет все вызовы ODBC функций, управляет всеми взаимодействиями между приложением и сервером, переводит SQL выражение на синтаксис источника данных.

2. Pass-through запросы:

-----------¬ -----------------¬

¦ Access +---------------------------+ SQL Server ¦ L----------- L-----------------

Рис. 2. Схeма взаимодействия Access и SQL Server через Pass-through запросы.

При этом обращение идет напрямую к серверу на его языке, минуя процессор БД Access. Запрос, хотя и проходит через все компоненты ODBC, ни один из них реально не обрабатывает его. После того, как Вы присоединитесь к Вашему источнику данных, можно использовать набор SQL pass-through запросов.

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

 

2.3.1. Использование присоединенных таблиц

Для использования присоединенных таблиц используйте команду меню <File/Attach Table>. Вы попадаете в диалоговое окно "Attach". После выбора в <Data SourсeL> типа файла "SQL DataBase" возникнет следующее диалоговое окно, в котором находится список всех источников данных "Data Sourсe", доступных на компьютере. Если нужного источника в списке нет, то с помощью кнопки <New> Вы попадаете в окно администратора ODBC, где и создаете требуемый источник.

После выбора источника данных Вам необходимо выбрать из списка доступных таблиц те, которые Вы хотите присоединить. Если в таблице нет уникального индекса, Вам будет предложено выбрать их самостоятельно, чтобы Jet Access мог однозначно передавать информацию на сервер.

Среди дополнений (File/Add-Ins) есть "Data Manager", который позволяет отслеживать работу с присоединенными таблицами.

Пример: В заданном источнике данных STUDY создается присоединенная таблица "Example".

Function CreateAttachTable()

Dim myDB AS Database, myTabDef AS TableDef

Set myDB = DBEngine, Workspaces.Database(0) Set myTabDef = myDB.CreateTableDef("Example") myTabDef.Connect = "ODBC;dsn=STUDY" myTabDef.SourceTableName = "country" myDB.TableDefs.Append myTabDef CreateAttachTable = True

End Function

 

2.3.2. SQL pass-through инструкции

 

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

Для того, чтобы создать запрос с помощью конструктора запросов, щелкните по корешку "Query" в окне БД, затем в меню <Query> выберите команду <SQL Query> и опцию <Pass-Through>. После выбора этого пункта меню больше не будет доступен режим конструктора QBE и SQL инструкции придется набирать вручную.

Чтобы получить доступ к диалогу свойств выберите команду <Properties> в меню <View>. В нем выберите имя источника данных. Свойства "ReturnRecords" уcтановите в "Yes" при выборке данных и в "No" при определении данных.

Для того, чтобы создавать запросы pass-through запросы используйте объект

QueryDef.

Основное отличие pass-through запроса от остальных наличие непустого свойства "Connect" и установка свойства "ReturnRecords".

Например, для получения выборки всех данных некоторой таблицы "example" при выше использованном источники данных введите следующей инструкцией в функцию Access Basic:

Dim DB As Database, Q1 As QueryDef, Rs As Recordset

Set DB = DBEngine.Workspaces.Database(0)

....

Set Q1 = DB.CreateQueryDef("Q1")

Q1.Connect = "odbc; dsn = STUDY" Q1.SQL = "SELECT*FORM Example" Q1.ReturnRecords = True Q1.Execute

Set Rs = DB.OpenRecordset("Q1",DB_OPEN_SNUPSHOT)

А для передачи данных

.....

Set Q1 = DB.CreateQueryDef("Q1")

Q1.Connect = "odbc; dsn = STUDY"

Q1.SQL = "APDATE Example SET account = 10 WHERE account = 2" Q1.ReturnRecords = False

Q1.Execute

Set Rs = DB.OpenRecordset("Q1",DB_OPEN_SNUPSHOT)

Но основное предназначение запросов SQL ─ Pass-trough не выборка данных, а запросы определения данных и выполнения хранимых на сервере процедур:

......

Set Q1 = DB.CreateQueryDef("Q1")

Q1.Connect = "odbc; dsn = STUDY"

Q1.SQL = "CREATE TABLE Example (Fild1 c(10)))" Q1.ReturnRecords = False

Q1.Execute

Результаты работы

 

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

─ текст модуля на языке Access Basic, демонстрирующий работу транзакции и выполняющий задачи Вашего приложения.

─ текст модуля на языке Access Basic, демонстрирующий использование pass-through запросов при работе в сети.

─ отчет с кратким описанием проделанной работы.

 

Варианты индивидуальных заданий

 

1. Отдел кадров (фирма по созданию крупного ПО): фамилия сотрудника, имя, отчество, должность, начальство/подчиненные, круг обязанностей, оклад.

2. Модемы: тип, скорость передачи, тип чипсета, протокол модуляции, цена, изготовитель.

3.Операционные системы: тип, быстродействие, сфера использования, создатель, цена, тип архитерктуры.

4. Персональные ЭВМ: фирма-изготовитель, тип процессора, тактовая частота, емкость ОЗУ, емкость жесткого диска.

5. Отдел компьютерной периферии: тип, по 2 характеристики на каждый тип, цена, производитель.

6. Мониторы: тип, частота, глубина цвета, инерционность, размер, производитель, цена.

7. Радиодетали: обозначение, тип, номинал, количество на схеме, обозначение возможного заменителя.

8. Текстовые редакторы: наименование, фирма-изготовитель, количество окон, количество шрифтов, стоимость.

9.Сетевая карты: тип, скорость передачи, тип сетевого интерфейса, количество разъемов, тип кабеля, совместимость с операционной системой, цена, производитель.

10. Видеокарты: активная/пассивная, тактовая частота видеочипа, количество пиксельных процессоров, слоты расширения, объем видеопамяти, цена, производитель.

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

12. Микросхемы памяти: обозначение, разрядность, емкость, время доступа, количество на схеме, стоимость.

13. Языки программирования: создатель, уровень/ориентация, возможности, заменители, цена, сложность, применимость.

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