Интернет-Университет Информационных Технологий    http://www.INTUIT.ru
Основы работы с базами данных
Лекция: СУБД Microsoft Office Access
Рассмотрены основные возможности и особенности СУБД Microsoft Office Access. Разобраны последовательность создания базы данных в этой системе, стандартные режимы работы с таблицами, методы разработки экранных форм, отчетов, запросов. Цель: освоение методов работы с базами данных в системе Microsoft Office Access.

Общая характеристика системы

Система Microsoft Access является одним из основных компонентов Microsoft Office и предназначена для работы с реляционными базами данных. Особенность данной СУБД: вся информация базы данных хранится в одном файле (*.mdb). Кроме информации таблиц, в этом же файле сохраняются компоненты приложения для работы с базой данных - экранные формы, отчеты, запросы, программные модули.

Для работы с базой данных система использует Microsoft Jet database engine - систему управления базами данных, извлекающую и сохраняющую данные в пользовательских и системных задачах. Ядро базы данных Microsoft Jet можно рассматривать как компонент диспетчера данных, с помощью которого строятся остальные системы доступа к данным, такие как Microsoft Access и Microsoft Visual Basic.

Язык написания программных модулей для работы с базой данных - Microsoft Visual Basic for Applications (VBA).

Основные возможности системы при работе с базами данных приведены в табл. 7.1.

Таблица 7.1. Спецификации системы Microsoft Office Access 2003
АтрибутМаксимальное значение
База данных
Размер файла базы данных (*.mdb)2 Гбайт за вычетом места, необходимого системным объектам
Число объектов в базе данных32 768
Модули (включая формы и отчеты), свойство Наличие модуля (HasModule) которых имеет значение True)1 000
Число знаков в имени объекта64
Число знаков в пароле14
Число знаков в имени пользователя или имени группы20
Число одновременно работающих пользователей255
Таблица
Число знаков в имени таблицы64
Число знаков в имени поля64
Число полей в таблице255
Число открытых таблиц2048 (фактическое число может быть меньше из-за внутренних таблиц, открываемых Microsoft Access)
Размер таблицы2 Гбайт за вычетом места, необходимого системным объектам
Число знаков в текстовом поле255
Число знаков в поле MEMO65 535 при вводе данных через интерфейс пользователя;1 Гбайт для хранения знаков при программном вводе данных
Размер поля объекта OLE1 Гбайт
Число индексов в таблице32
Число полей в индексе10
Число знаков в сообщении об ошибке255
Число знаков в условии на значение записи2048
Число знаков в описании таблицы или поля255
Число знаков в записи (кроме полей MEMO и полей объектов OLE)2000
Число знаков в значении свойства поля255
Запрос
Число установленных связей32 на одну таблицу за вычетом числа индексов, находящихся в таблице для полей или сочетаний полей, которые не участвуют в связях
Число таблиц в запросе32
Число полей в наборе записей255
Размер набора записей1 Гбайт
Предел сортировки255 знаков в одном или нескольких полях
Число уровней вложения запросов50
Число знаков в ячейке на бланке запроса1024
Число знаков для параметра в запросе с параметрами255
Число операторов AND в предложении WHERE или HAVING99
Число знаков в инструкции SQLприблизительно 64000
Форма и отчет
Число знаков в надписи2048
Число знаков в поле65535
Ширина формы или отчета22 дюйма (55,87 см)
Высота раздела22 дюйма (55,87 см)
Высота всех разделов плюс заголовки разделов (в режиме конструктора)200 дюймов (508 см)
Число уровней вложения форм или отчетов7
Число полей или выражений, которые можно отсортировать или сгруппировать в отчете10
Число заголовков и примечаний в отчете1 заголовок/примечание отчета; 1 заголовок/примечание страницы; 10 заголовков/примечаний групп
Число печатных страниц в отчете65536
Число элементов управления и разделов, которые можно добавить за время существования формы или отчета754
Число знаков в инструкции SQL, работающей в качестве свойства Источник записей (RecordSource) или Источник строк (RowSource) формы, отчета или элемента управления (оба .mdb и .adp)32750
Макрос
Число макрокоманд в макросе999
Число знаков в условии255
Число знаков в комментарии255
Число знаков в аргументе макрокоманды255

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

Таблица 7.2. Типы данных системы
Тип данных полейТип данных в VBAИспользованиеРазмер
ТекстовыйStringТекст, состоящий из любых символов в кодировке Unicode (2 байта на символ)До 255 символов
Поле МЕМОStringТекст в кодировке UnicodeДо 64000 символов
Числовой(Байт, Целое, Длинное целое, Одинарное с плавающей точкой, Двойное с плавающей точкой, Код репликации, Действительное)Byte, Integer,Long,Single,DoubleЧисловые данные1, 2, 4 или 8 байтов. 16 байтов только для кодов репликации (GUID)
Дата/времяПолный формат даты. Длинный формат даты. Средний формат даты. Краткий формат даты. Длинный формат времени. Средний формат времени. Краткий формат времениDateДаты и время. 31.12.04 23:55:5931 декабря 2004 г.31-дек-0431.12.0423:55:5911:5523:558 байтов(при активации поля всегда показывает полный формат даты)
ДенежныйCurrencyЗначения валют. Денежный тип используется для предотвращения округлений во время вычислений. Предполагает до 15 символов в целой части числа и 4 - в дробной8 байтов
СчетчикАвтоматическая вставка последовательных (увеличивающихся на 1) или случайных чисел при добавлении записи.4 байта. 16 байтов только для кодов репликации (GUID)
ЛогическийBooleanПоля, содержащие только одно из двух возможных значений, таких как Да/Нет, Истина/Ложь, Вкл/Выкл.1 бит
Поле объекта OLEStringОбъекты (например, документы Microsoft Word, электронные таблицы Microsoft Excel, рисунки, звуки и другие двоичные данные), созданные в программах, использующих протокол OLE. Объекты могут быть связанными или внедренными.До 1 гигабайта (ограничено объемом диска)
ГиперссылкаStringПоле, в котором хранятся гиперссылки. Гиперссылка может иметь вид пути UNC, либо URL-адресаДо 64000 символов
Мастер подстановокСоздает поле, позволяющее выбрать значение из другой таблицы или из списка значений, используя поле со списком. При выборе данного типа запускается Мастер для определения этого поляТот же размер, который имеет первичный ключ, являющийся полем подстановок

Система Microsoft Access имеет собственные средства для разграничения прав доступа пользователей к базе данных.

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

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

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

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

При запуске Microsoft Access от пользователя требуется идентифицировать себя и ввести пароль. Microsoft Access по умолчанию создает две группы: администраторы (группа Admins ) и простые пользователи (группа Users ). Допускается определение других групп и пользователей.

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

Создание базы данных

Процесс создания базы данных рассмотрим на примере описанной ранее (лекция 3) модели базы данных информационной системы "Контингент студентов университета".

В системе Microsoft Access процесс создания базы данных выполняется следующим образом. При запуске системы появляется диалоговое окно для выбора режима работы (рис. 7.1), в котором следует выбрать пункт Новая база данных...

Создание файла

Рис. 7.1.  Создание файла

После выбора первого пункта появляется окно для задания пути сохранения и имени новой базы.

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

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

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

Окно базы данных

Рис. 7.2.  Окно базы данных

Описание структуры таблицы в конструкторе

Рис. 7.3.  Описание структуры таблицы в конструкторе

Структура таблицы SPISOK приведена в табл. 7.3.

Таблица 7.3. Структура таблицы SPISOK
Имя поляТип данныхРазмер поляИндексированное полеПодпись
NZТекстовый8Да (Совпадения не допускаются)№ зачетки
FIOТекстовый45Фамилия, имя, отчество
DATA_PДата/времяКраткий формат датыДата поступления
N_FCLTЧисловойБайтДа (Совпадения допускаются)Факультет
N_SPECTТекстовый7Да (Совпадения допускаются)Специальность
KURSЧисловойБайтКурс
N_GRUPТекстовый10Группа
N_PASPТекстовый10Номер паспорта

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

Аналогичным образом создаем в базе данных справочник факультетов с именем файла FCLT, структура его приведена в табл. 7.4.

Таблица 7.4. Структура таблицы FCLT
Имя поляТип данныхРазмер поляИндексированное полеПодпись
N_FCLTЧисловойБайтДа (Совпадения не допускаются)Номер факультета
NAME_FТекстовый120Название факультета
Таблица 7.6. Структура таблицы OCENKI
Имя поляТип данныхРазмер поляИндексированное полеПодпись
NZТекстовый7Да (Совпадения допускаются)Номер зачетки
SEMESTRЧисловойБайтСеместр
N_PREDMЧисловойЦелоеДа (Совпадения допускаются)Предмет
BALLТекстовый1Оценка
DATA_BДата/времяКраткий форматДата
PREPODТекстовый45Преподаватель
Таблица 7.7. Структура таблицы PREDMETS
Имя поляТип данныхРазмер поляИндексированное полеПодпись
N_PREDMЧисловойЦелоеДа (Совпадения не допускаются)Номер предмета
NAME_PТекстовый120Название предмета

Далее задаем связи Один ко многим между таблицами в базе, открыв окно Схема данных (выбрав эту команду в контекстном меню для окна базы данных) и перетаскивая название поля первичного ключа к аналогичному полю другой таблицы (см. рис. 7.4.). При этом задаем в окне Изменение связей (см. рис. 7.5.) условия соблюдения ссылочной целостности данных: каскадное обновление связанных полей и каскадное удаление связанных записей.

Схема базы данных

Рис. 7.4.  Схема базы данных

Задание условий соблюдения ссылочной целостности данных

Рис. 7.5.  Задание условий соблюдения ссылочной целостности данных

Стандартный режим работы с таблицами

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

Для работы с информацией таблицы базы данных (добавление, редактирование и удаление записей) следует выбрать ее в разделе объектов базы данных "Таблицы" (см. рис. 7.6.) и двойным щелчком мыши открыть.

База данных

Рис. 7.6.  База данных

Таблица откроется в стандартном режиме работы с информацией, как показано на рис. 7.7.

Стандартный режим работы с таблицей базы данных

Рис. 7.7.  Стандартный режим работы с таблицей базы данных

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

В системе Microsoft Access существуют также дополнительные возможности использования в таблицах режима "Подстановка" для показа и выбора данных из раскрывающихся списков ("тип источника строк" - таблица или запрос, список значений или список полей). Этот режим можно создать с помощью Мастера, если выбрать тип поля "Мастер подстановок" или описать самому на странице "Подстановка". В примере этот режим задан для поля N_FCLT таблицы Spisok (см. рис. 7.8.). В дальнейшем для поля с описанными свойствами раздела "Подстановка" на экранной форме будет автоматически создаваться объект типа "Поле со списком".

Параметры страницы "Подстановка" в Конструкторе

Рис. 7.8.  Параметры страницы "Подстановка" в Конструкторе

Вид таблицы Spisok с использованием поля со списком для поля N_FCLT показан на рис. 7.9.

Таблица с использованием режима "Подстановка"

увеличить изображение
Рис. 7.9.  Таблица с использованием режима "Подстановка"

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

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

Разработка экранных форм для работы с базой данных

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

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

Для разработки экранной формы в окне базы данных выбираем объект Формы и на странице форм - режим Создание формы с помощью Мастера. Далее следует ответить на ряд вопросов Мастера:

  1. выберите поля для формы - выбираем все поля таблицы SPISOK и все поля таблицы OCENKI (для последней таблицы поле NZ расположим в конце списка, оно будет заполняться автоматически; добавляем его, чтобы убедиться, что на экране мы видим оценки только одного студента);
  2. выберите вид представления данных - выбираем подчиненные формы, т.е. расположение данных главной таблицы и связанной с ней на одной форме;
  3. выберите внешний вид подчиненной формы - выбираем ленточный ;
  4. выберите требуемый стиль - выбираем стандартный стиль ;
  5. задайте имена форм - задаем для главной формы название Студенты, для подчиненной формы Оценки студента и на том же экране ниже выбираем пункт Изменить макет формы, после чего нажимаем на кнопку Готово.

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

Экранная форма, созданная с помощью Мастера

Рис. 7.10.  Экранная форма, созданная с помощью Мастера

На полученной форме не все надписи полностью видны, расположение полей тоже можно улучшить.

Для формы и всех ее элементов можно открыть окно Свойства, щелкнув правой кнопкой мыши на любом объекте и выбрав в контекстном меню слово Свойства (рис. 7.11.). Кроме того, в контекстном меню присутствуют такие важные при оформлении объектов возможности, как "Выровнять" - полезно для выравнивания группы объектов, "Размер", "Цвет:", "Оформление", "Условное форматирование".


Окно свойств для объекта Форма, страницы Макет и Данные

Рис. 7.11.  Окно свойств для объекта Форма, страницы Макет и Данные

Все свойства в окне разбиты на группы:

Макет - расположение, шрифт, цвет и прочее, связанное с внешним видом объекта;

Данные - в этом разделе важнейшее свойство - Данные или Источник записей - для объектов, связанных с редактированием каких-либо данных;

События - методы, т.е. процедуры (программы), выполняющиеся для объекта при наступлении определенных событий ( Загрузка, Открытие, До обновления, После обновления и пр.);

Другие - прочие свойства.

На экранной форме присутствуют элементы (объекты) следующих типов: Надпись - текст на форме, обычно не изменяющийся. Главные свойства этого объекта присутствуют на странице Макет окна свойств (рис. 7.12.).

Поле - поле редактирования, связанное с полем базы данных или переменной. Главное свойство этого объекта - строка Данные на странице Данные окна свойств (рис. 7.13.).

Окно свойств объекта типа Надпись

Рис. 7.12.  Окно свойств объекта типа Надпись

Окно свойств объекта типа Поле

Рис. 7.13.  Окно свойств объекта типа Поле

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

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

Окно свойств объекта типа Поле со списком

Рис. 7.14.  Окно свойств объекта типа Поле со списком

Панель элементов экранной формы

Рис. 7.15.  Панель элементов экранной формы

Кроме того, на форме могут присутствовать и другие объекты, которые можно добавлять, используя кнопки Панели элементов (рис. 7.15.), список типов объектов приведен ниже.

  • Группа переключателей, Выключатель, Переключатель, Кнопка - кнопки и группы кнопок разного вида, связанные с выполнением определенных процедур (5-7 и 11 кнопки на Панели ). При выборе объекта Кнопка запускается Мастер, который предложит вам набор стандартных кнопок перехода по записям таблицы, обработки записей (восстановить, дублировать, печатать и пр.), работы с формой, с отчетами (печатать, просмотреть, отправить в файл или по почте), работы с приложениями и разное. Кнопки можно создать с помощью Мастера, при этом программный код, связанный с их действием, уже будет присутствовать (рис. 7.16.).

    Окно Мастера создания кнопок

    Рис. 7.16.  Окно Мастера создания кнопок

  • Флажок - поле, связанное обычно с полем таблицы логического типа, в котором стоит галочка или нет (6-й элемент Панели ).
  • Список - список данных для выбора одного из значений (10).
  • Рисунок - вставка рисунка в форму (12).
  • Свободная рамка объекта - любой объект Windows-приложений, редактирование которого будет возможно вызовом соответствующего приложения (13).
  • Присоединенная рамка объекта - для работы с полями таблиц типа "поле объекта OLE " (14).
  • Набор вкладок - многостраничная форма (16).
  • Линия, Прямоугольник - элементы оформления (18, 19).

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

Работа с базой данных с использованием экранной формы

Рис. 7.17.  Работа с базой данных с использованием экранной формы

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

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

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

Прежде всего, в Конструкторе изменим расположение полей и расширим надписи.

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

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

После всего этого форма будет иметь в Конструкторе вид, приведенный на рис. 7.18.

Усовершенствованная экранная форма в Конструкторе форм

увеличить изображение
Рис. 7.18.  Усовершенствованная экранная форма в Конструкторе форм

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

Работа с базой данных с использованием экранной формы, в которой присутствуют объекты Поле со списком

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

Разработка отчетов

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

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

Для этого выбираем раздел Отчеты и режим Создание отчета с помощью Мастера. На первом шаге Мастера выбираем поля главной таблицы базы ( SPISOK ), которые мы хотим показать в отчете, и все поля дочерней таблицы оценок ( OCENKI ) (рис. 7.20.).

Выбор полей для отчета в Мастере отчетов

Рис. 7.20.  Выбор полей для отчета в Мастере отчетов

На втором шаге Выберите вид представления данных - выбираем первый вариант, когда выделена таблица SPISOK.

На третьем шаге задаем группировку данных по факультетам, курсам и группам. Более трех уровней группировки Мастер задать не позволяет (рис. 7.21.).

Группировка данных в отчете

Рис. 7.21.  Группировка данных в отчете

Сортировку на следующем шаге не задаем.

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

Полученный отчет в режиме Конструктора представлен на рис. 7.22.

Отчет, созданный с помощью Мастера

увеличить изображение
Рис. 7.22.  Отчет, созданный с помощью Мастера

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

В Конструкторе отчет разбит на отдельные зоны, информация которых может присутствовать в отчете один раз ( Заголовок отчета и Примечание отчета ), в начале и в конце каждой страницы ( Верхний колонтитул и Нижний колонтитул ), в начале и в конце каждой группы ( Заголовок группы и Примечание группы, групп может быть много) и для каждой записи таблицы ( Область данных ). Зоны Примечания: более правильно было бы назвать Итоги:, т.к. здесь можно поместить поля общих итогов (сумма, среднее и пр.) для группы или всего отчета.

Полученный отчет можно просмотреть на экране, отправить на принтер (например, с использованием соответствующих кнопок на стандартной панели инструментов), в Microsoft Word или Excel (из режима предварительного просмотра).

Вид отчета в режиме предварительного просмотра приведен на рис. 7.23.

Отчет в режиме предварительного просмотра

Рис. 7.23.  Отчет в режиме предварительного просмотра

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

Для использования в отчете данных справочных таблиц их следует включить в Источник записей окна свойств отчета (см. рис. 7.24.). После нажатия на кнопочку с изображением трех точек откроется окно Построителя запросов, которое после добавления нужных таблиц будет иметь вид, показанный на рис. 7.25. Для добавления таблиц в запрос (показаны в верхней части экрана Построителя ) можно воспользоваться контекстным меню. После этого нужно добавить 3 поля - NAME_F, NAME_P и NAME_S в список используемых полей в нижней части экрана. Далее следует закрыть окно Построителя запросов с сохранением изменений.

Окно свойств отчета, страница Данные

Рис. 7.24.  Окно свойств отчета, страница Данные

Построитель запросов для свойства "Источник записей" отчета

Рис. 7.25.  Построитель запросов для свойства "Источник записей" отчета

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

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

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

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

Окно "Сортировка и группировка" отчета

Рис. 7.26.  Окно "Сортировка и группировка" отчета

В зоны примечаний поместим вычисляемые поля, которые будут показывать средние оценки для студента, группы, факультета и для всего отчета. При создании вычисляемых полей проще всего скопировать поле BALL из зоны "Область данных" в зону "Примечание группы:", далее перейти в окно свойств этого объекта и в пункте Данные нажать на кнопку с многоточием, после чего откроется окно Построителя выражений, где можно найти в списке функций AVG (среднее) и задать выражение AVG (BALL) (рис. 7.27.).

Создание вычисляемого поля для зоны примечаний отчета

Рис. 7.27.  Создание вычисляемого поля для зоны примечаний отчета

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

Отчет после его модификации в Конструкторе

увеличить изображение
Рис. 7.28.  Отчет после его модификации в Конструкторе

Отчет при печатании на бумаге будет выглядеть, как показано на рис. 7.29.

Окончательный вариант отчета, напечатанный на принтере

увеличить изображение
Рис. 7.29.  Окончательный вариант отчета, напечатанный на принтере

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

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

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

Запросы в системе Access бывают нескольких видов:

  1. Запрос для отбора данных по заданным сложным условиям из одной или нескольких таблиц баз данных, с группировкой данных для расчета итогов, с показом результатов выполнения запроса в виде таблицы, либо с использованием его для форм и отчетов; после редактирования данных в таблице запроса данные таблиц базы могут обновляться (с некоторыми ограничениями).
  2. Перекрестный запрос с формированием двухмерной итоговой таблицы, с группировкой по двум выражениям, одно из которых становится заголовком строки, другое - заголовком столбца.
  3. Запрос на создание новой таблицы.
  4. Запросы на изменение данных:
    • обновление данных - команда занесения общих изменений в группу записей одной или нескольких таблиц;
    • добавление данных - команда добавления группы записей из одной или нескольких таблиц в конец одной или нескольких таблиц;
    • удаление данных - команда удаления группы записей из одной или нескольких таблиц.

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

На первом шаге следует выбрать таблицы и поля, которые нужно включить в запрос. Выбор полей может быть выполнен из нескольких таблиц базы. Для нашего примера выбираем из таблицы SPISOK все поля, кроме DATA_P и N_PASP, из таблицы OCENKI - первые 4 поля и 2 поля таблицы PREDM (рис. 7.30.).

Выбор полей в Мастере запросов

Рис. 7.30.  Выбор полей в Мастере запросов

На шаге 2 ("подробный или итоговый отчет") выбираем подробный отчет. На последнем шаге 3 задаем название запроса Математика и выберем вариант Изменить макет запроса, после чего нажимаем кнопку Готово. Запрос открывается в конструкторе запросов, его вид показан на рис. 7.31.

Конструктор запросов

увеличить изображение
Рис. 7.31.  Конструктор запросов

В верхней части Конструктора запросов показаны таблицы, используемые для отбора данных и связи между ними, в нижней части - таблица для выбора полей, группировки данных (если строки "Групповые операции" нет, нужно выбрать эту команду в главном меню Microsoft Access в пункте "Вид"), задания сортировки и условий отбора.

Модифицируем запрос для задания условия отбора данных и упорядочения студентов по их фамилии. Для этого в колонке поля FIO зададим сортировку по возрастанию, для поля SEMESTR зададим условие отбора 1 (первый семестр), для поля BALL зададим условие 5 и для поля PREDMET зададим условие " математика ". Если в условии отбора написать текст в квадратных скобках, при выполнении запроса появится окно для ввода этого параметра. Например, если для поля PREDMET в условии написать Задайте предмет, можно будет использовать один и тот же запрос для отбора данных по разным предметам.

Можно также убрать галочки у тех полей, которые вы не хотите показывать на экране.

Сохраним запрос и посмотрим его текст в режиме SQL (Structured Query Language). Текст запроса будет выглядеть следующим образом:

SELECT Spisok.NZ AS Spisok_NZ, Spisok.FIO, Spisok.N_FCLT, Spisok.N_SPECT, 
   Spisok.KURS, Spisok.N_GRUP, OCENKI.SEMESTR, OCENKI.N_PREDM AS OCENKI_N_PREDM, 
   OCENKI.BALL, PREDMETS.N_PREDM AS PREDMETS_N_PREDM, PREDMETS.NAME_P
FROM Spisok INNER JOIN (PREDMETS INNER JOIN OCENKI 
   ON PREDMETS.N_PREDM = OCENKI.N_PREDM) ON Spisok.NZ = OCENKI.NZ
WHERE (((OCENKI.SEMESTR)=1) AND ((OCENKI.BALL)="5") AND ((PREDMETS.NAME_P)="математика"))
ORDER BY Spisok.FIO;

Закроем окно конструктора и выполним запрос командой Открыть или двойным щелчком мышью. Результат отбора данных будет показан на экране в виде таблицы (рис. 7.32.). Следует помнить, что редактирование данных этой таблицы приведет к изменению информации в таблицах базы данных!

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

увеличить изображение
Рис. 7.32.  Результаты выполнения запроса

Результаты выполнения запроса или данные таблиц можно представить в виде диаграмм и графиков. Создадим запрос, в котором покажем в графическом виде средний балл по студенческим группам по предмету "Математика" (№ предмета = 1). Для группировки данных, как отмечалось выше, в пункте "Вид" меню системы ставим галочку у строки "Групповые операции".

Получим следующий текст запроса:

SELECT Spisok.N_GRUP, OCENKI.N_PREDM, Avg(OCENKI.BALL) AS [Avg-BALL]
FROM Spisok INNER JOIN OCENKI ON Spisok.NZ = OCENKI.NZ
GROUP BY Spisok.N_GRUP, OCENKI.N_PREDM
HAVING (((OCENKI.N_PREDM)=1));

Для представления данных в виде графика в меню Вид выбираем пункт Сводная диаграмма, после чего открывается окно Построителя диаграмм. Методы оформления диаграмм аналогичны использованию объекта Диаграмма Microsoft Graph в программах Microsoft Word или Excel. На рис. 7.33. показана диаграмма для приведенного выше запроса. На рис. 7.34. приведена трехмерная диаграмма для запроса следующего вида:

SELECT DISTINCTROW FCLT.NAME_F, PREDMETS.NAME_P, Avg(OCENKI.BALL) AS [Avg-BALL]
FROM PREDMETS INNER JOIN ((Spisok INNER JOIN OCENKI ON Spisok.NZ = OCENKI.NZ) INNER 
  JOIN FCLT ON Spisok.N_FCLT = FCLT.N_FCLT) ON PREDMETS.N_PREDM = OCENKI.N_PREDM
GROUP BY FCLT.NAME_F, PREDMETS.NAME_P;

Результаты выполнения запроса с группировкой данных, представленные в виде диаграммы

Рис. 7.33.  Результаты выполнения запроса с группировкой данных, представленные в виде диаграммы

Результаты выполнения запроса с группировкой данных, представленные в виде трехмерной диаграммы

Рис. 7.34.  Результаты выполнения запроса с группировкой данных, представленные в виде трехмерной диаграммы

С использованием запросов других видов одной командой можно изменять (команда SQL UPDATE ) либо удалять (команда SQL DELETE ) данные множества записей таблицы, отобранных по какому-либо условию, а также добавлять записи из других таблиц (команда SQL INSERT ).

Компиляция базы данных (MDE-файл)

База данных Microsoft Access может быть сохранена в виде MDE-файла.

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

При сохранении базы в формате MDE будут скомпилированы все программные модули, удалены все исходные тексты программ, будет сжата. Программы Visual Basic будут по-прежнему выполняться, но их нельзя будет просматривать или изменять. Из-за удаления исходных кодов уменьшится размер базы данных, т.е. будет оптимизировано использование памяти, что повысит быстродействие базы данных.

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

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

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

  • Создание, просмотр, изменение форм, отчетов, модулей с использованием Конструкторов и Мастеров.
  • Создание и изменение программных модулей.
  • Создание, удаление и изменение ссылок на библиотеки объектов или базы данных.

Страницы доступа к данным

Страницы доступа к данным представляют специальный тип Web-страниц, предназначенный для просмотра и работы через Интернет или интрасеть с данными, хранящимися в базах данных Microsoft Access или в базах данных Microsoft SQL Server. Страница доступа к данным может также включать данные из других источников, например, Microsoft Excel.

Использование страниц доступа для работы с данными аналогично использованию экранных форм: пользователь имеет возможность просматривать, редактировать, добавлять и удалять записи в таблицах базы данных. Однако страницу можно использовать и за пределами системы Microsoft Access, предоставляя пользователям возможность обновлять или просматривать данные через Интернет или интрасеть с использованием браузера Microsoft Internet Explorer 5.01 с пакетом SP2 или более поздних версий. Страницы могут распространяться в электронном виде с помощью электронной почты; получатели будут видеть текущие данные при каждом открытии сообщения.

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

Страница доступа к данным в режиме работы с информацией базы

Рис. 7.35.  Страница доступа к данным в режиме работы с информацией базы

В Конструкторе страница доступа состоит из групп, которые подразделяются на разделы (Section): подпись, верхний колонтитул, нижний колонтитул, кнопки перехода (см. рис. 7.36.).

Страница доступа к данным в Конструкторе

Рис. 7.36.  Страница доступа к данным в Конструкторе

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

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

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

Проект Microsoft Access (ADP)

Проект Microsoft Access (*.adp) является файлом данных Microsoft Access, обеспечивающим эффективную работу с базами данных Microsoft SQL Server. Используя проект Microsoft Access, можно создавать приложения в архитектуре "клиент/сервер" так же легко, как приложения файлового сервера.

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

Проект Microsoft Access можно подключить к удаленной базе данных Microsoft SQL Server, локальной базе данных Microsoft SQL Server или к локальной установке Microsoft SQL Server 2000 Desktop Engine (MSDE).

Файл проекта не содержит данные или объекты определения данных, такие как таблицы и представления.

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

Краткие сведения о работе с проектом Microsoft Access будут даны в следующей лекции, которая посвящена системе Microsoft SQL Server.

© INTUIT.ru, 2003-2010. Все права защищены.