4. Microsoft Access

Microsoft Access (англ. access – доступ) – одна из самых популярных СУБД, построенная на основе реляци­онных моделей и ориентированная на 32-разрядные микропроцес­соры.

В практикуме рассматривается 10 версия MS Access (ХР).

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

MS Access – полнофункциональная реляционная СУБД. В этой сис­теме можно пользоваться практически всеми средствами операцион­ной системы MS Windows. Применяется многодоку-ментальный интерфейс (MDI – Multiple Dokument Interface), т.е. одновременно можно работать с несколькими таблицами, фор-мами, отчетами, мак­росами, модулями.

Таблица является основой БД. В MS Access вся информация содержится в таблицах. Обеспечена связь между объектами.

MS Access имеет все необходимые средства для выполнения трех комплексных типов функций:

а) определение данных (задание структуры БД и их описание);

б) обработка данных;

в) управление данными (установление правил доступа).

В MS Access входят специальные программы: «Конструктор», «Мас­тер», «Построитель выражений», «Маска ввода», что позволяет со­здавать экранные формы, отчеты, запросы.

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

Программы «Мастер» помогают проектировать форму или отчет путем ответа пользователя на ряд вопросов для получения закончен­ного отчета (формы, запросы).

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

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

В любой момент можно вызвать контекстное меню с набором команд – открывается пра­вой кнопкой мыши.

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

MS Access включает в себя обширную справочную инфор-мацию, которая может быть оперативно востребована и получена пользова­телем. Команда Справка (Help) открывает диалоговое многостороннее окно, в котором приведено содержание справоч-ной системы по темам. Можно также получить нужную инфор-мацию по содержанию, Мастеру ответов и указателю. Нужная инфор­мация выводится на экран в диалоговом окне, она структу-рированна и разбита на разделы.

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

1. Строка меню содержит меню («Файл», «Правка», «Вид», «Вставка», «Сервис», «Окно» и др.).

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

Вид → Панели инструментов.

3. Окно базы данных имеет семь вкладок объектов: «Таблицы», «Запросы», «Формы», «Отчеты», «Страницы», «Макросы», «Модули», каждая из которых содержит объекты текущей базы данных. Для перехода на нужную вкладку надо щелкнуть на ее имя. В верхней части окна базы данных находится три кнопки: «Открыть», «Конструктор», «Создать».

Кнопка «Открыть» открывает выбранный объект (таблицу, запрос, форму, отчет). При открытии таблицы открывается окно таблицы; при переходе на вкладку «Отчеты» кнопка «Открыть» меняется на «Просмотр». Кнопка «Конструктор» предназначена для модификации выбранного объекта, кнопка «Создать» – для создания объекта.

4. Строка состояния расположена внизу главного окна. В ней выводится краткая справочная информация о текущем режиме работы или выбранном объекте. О готовности системы принять команду пользователя говорит сообщение «Готово».

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

Функции и назначение объектов базы данных

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

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

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

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

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

Модуль – представляет набор описаний и процедур на языке Visual Basic для приложений (VBA), сохраненных в одном программном блоке.

Страница доступа к данным – создана для просмотра данных из Internet или intrаnet и работы с ними.

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

Запустить Access, выбрать параметр «Новая база данных». Откроется окно «Файл новой базы данных». В поле «Папка» выбрать имя папки, в которой будет сохраняться создаваемый файл БД, а в поле «Имя файла» ввести имя файла БД. После нажатия кнопки «Создать» на экране откроется окно новой БД.

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

Этапы проектирования реляционной базы данных

1. Определение цели создания БД.

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

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

2. Определение таблиц, которые должна содержать БД.

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

При проектировке таблиц рекомендуется руководствоваться сле­дующими основными принципами:

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

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

3. Определение необходимых в таблице полей.

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

При разработке полей таблицы необходимо помнить:

• каждое поле должно быть связано с темой таблицы;

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

• в таблицах должна присутствовать вся необходимая информация;

• информацию следует разбивать на наименьшие логические единицы (например, поля «Фамилия» и «Имя», а не общее поле);

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

Тип данных – атрибут переменной или поля. Он определяет, какие данные могут содержаться в поле (например, в поле с типом «Текстовый» можно вводить как тексты, так и числа, а в поле с типом «Числовой» – только числа). В базе данных для каждого поля нужно выбрать тип данных, при этом учитываются характер сохраняемой в поле информации и операции, которые будут выполняться с ней (в табл. 4.1 приведены типы данных, обрабатываемых MS Access).

Таблица 4.1 — Типы данных, обрабатываемых MS Access

Тип данных	Применение и размер
1. Текстовый (Text)	Текстовая информация; до 225 символов
2. Числовой (Number)	Числовые данные, которые участвуют в математических вычислениях: 1, 2, 4 или 8 байт
3. Денежный (Currency)	Значение валют. Применяется для предотвращения округлений во время вычислений; 8 байт
4. Поле Memo (Memo)	Длинный текст или число, например описание; до 64 000 символов
5. Дата/время (Data | Time)	Дата и время; 8 байт
6. Счетчик (AutoNumber)	При добавлении записей автоматически вставляется последовательность чисел, которые отличаются на 1, или случайные числа; 4 байта
7. Логический (Yes|No)	Значения «Да» или «Нет»; 1 бит
8. Поле объекта OLE (OLE Object)	Объекты, которые созданы в других программах (MS Word, MS Excel, рисунки, звуки и др.), связанные или внедренные в таблицу MS Access; ограничены объемом диска
9. Гиперссылка (Hiperlink)	Адрес ссылки (путь) на документ или файл, находящийся в WWW, интрасети, LAN или на локальном компьютере
10. Мастер подстановок	Создается поле, которое позволяет выбрать значение из другой таблицы или из списка значений; 4 байта

4. Задание индивидуального значения каждой записи.

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

Первичный ключ – одно или несколько полей (столбцов), комбинация значений которых однозначно определяет каждую запись в таблице. Первичный ключ не допускает значений Null¹ и всегда должен иметь уникальный индекс. Первичный ключ используется для связывания таблицы с внешними ключами в других таблицах.

5. Определение связей между таблицами.

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

6. Усовершенствование структуры базы данных.

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

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

7. Ввод данных и создание других объектов базы данных.

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

Пример 1 (сделки торгового предприятия). Спроектировать и реализовать БД, которая учитывает сделки некоторого торгового предприятия, а информация по сделке описана в виде структуры:

1. Номер сделки

2. Дата (Д/вр)

3. Операция (может быть «продажа», «покупка» и «потеря»)

4. Клиент

   1. Название клиента

   2. Адрес

   3. Телефон

   4. Расчетный счет

5. Сотрудник

   1. Фамилия

   2. Должность

6. Стоимость доставки (Д)

7. Содержание сделки

   1. Товары

      1. Наименование товара

      2. Цена за единицу (Д)

   2. Количество товаров (Ч).

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

Рассмотрим создание на примере таблицы «Клиенты», используя Конструктор. Для этого в окне «База данных» выбрать вкладку «Таблицы». Нажать кнопку «Создать» и выбрать способ создания таблицы: «Конструктор». Ввести имена полей, типы данных и размеры полей.

Таблица «Клиенты» будет содержать следующие поля:

Имя поля Тип данных Размер

КодКлиента Счетчик Длинное целое (4 байта)

НазваниеКлиента Текстовый 20

Адрес Текстовый 20

Телефон Текстовый 10

РасчетныйСчет Текстовый 13

Примечания

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

2. Рекомендуется присвоение полям «говоря­щих» имен и коротких названий.

3. Для смены типа данных нужно нажать кнопку раскрытия списка типов полей и выбрать необходимый тип.

4. В графе «Описание» даются пояснения полям.

5. Для связи таблиц в базе данных нужно определить поле-ключ (значения ключа в таблице не повторяются, а значит, однозначно определяют каждую запись): нажать нужное поле, затем кнопку «Ключевое поле» на панели инструментов. Слева от имени поля в области выделенного ряда появится символ ключа. В примере выберем в качестве ключевого поле «КодКлиента».

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

Сохранить таблицу командой Файл→Сохранить→Клиенты.

В окне БД на вкладке «Таблицы» появится имя со­зданной таблицы. Для ввода в нее информации нужно открыть таблицу, дважды нажав имя таблицы или выбрав его, нажать кнопку «Открыть» в окне БД. На эк­ране откроется таблица в режиме таблицы, после чего можно присту­пать к вводу информации.

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

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

1. Использование панели инструментов Таблицы и Запроса в режиме таблицы:

• вырезание, копирование или вставка выделенного текста, поля, всех записей или всей таблицы полностью;

• сортировка записей;

• отбор записей, поиск или перемещение значений;

• добавление или удаление записей.

2. Работа со столбцами, строками и подтаблицами:

• использование области выделения столбца для перемещения, скрытия или переименования столбца;

• изменение размеров строк или столбцов;

• просмотр связанных данных с помощью подтаблиц;

• закрепление левого крайнего столбца так, чтобы он отображался на экране при прокрутке листа вправо.

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

• переход на первую, предыдущую, следующую и последнюю записи;

• введение номера нужной записи;

• переход на новую (пустую) запись.

Аналогично таблице «Клиенты» создаются остальные таблицы базы данных по учету сделок торгового предприятия и определяются необходимые в таблицах поля (рис. 4.1).

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

В MS Access различают первичные ключи трех типов.

1. Ключевые поля счетчика.

В поле с типом данных «Счетчик» при добавлении каждой записи в таблицу автоматически вносится порядковый номер. Указание такого поля в качестве ключевого является наиболее простым способом создания первичного ключа. Если до сохранения созданной таблицы ключевые поля не были определены, то MSAccess предлагает создать ключевое поле автоматически. При нажатии кнопки «Да» будет создано ключевое поле счетчика.

Рис. 4.1. Таблицы и поля базы данных по учету сделок торгового предприятия

2. Простой ключ.

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

3. Составной ключ.

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

Замечание. На рис. 4.1 первичные ключи (счетчики) в таблицах выделены полужирным шрифтом.

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

Например, форма, отображенная на рисунке 4.2, включает сведения из пяти таблиц, где введены обозначения: 1 – таблица «Сделки», 2 – таблица «Клиенты», 3 – таблица «Сотрудники», 4 – таблица «Товары», 5 – таблица «СодержаниеСделки».

Рис. 4.2. Форма, содержащая сведения из таблиц БД

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

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

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

Между таблицами существуют следующие типы отношений:

1) «один-к-одному» (1 : 1);

2) «один-ко-многим» (1 : М), «много-к-одному» (М : 1);

3) «много-ко-многим»(М : М).

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

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

В таблице «Команда» набор значений представляет подмножество для поля «КодСтудента» из таблицы «Студенты».

Рис. 4.3. Тип отношения «один-к-одному»

Отношение «один-к-одному» создается в том случае, когда оба связываемых поля являются ключевыми или имеют уникальные индексы.

Отношение «один-ко-многим» является наиболее часто используемым типом связи между таблицами. В отношении «один-ко-многим» каждой записи в таблице A могут соответствовать несколько записей в таблице B, но запись в таблице B не может иметь более одной соответствующей ей записи в таблице A.

Например, один сотрудник может совершить несколько сделок, однако за каждую сделку может быть ответствен только один сотрудник (рис. 4.4).

Рис. 4.4. Тип отношения «один-ко-многим»

Отношение «один-ко-многим» создается в том случае, когда только одно из полей является полем первичного ключа или уникального индекса.

Отношение «много-к-одному» схоже с отношением «один-ко-многим», но является уникальной связь слева направо.

Рис. 4.5. Тип отношения «многие-ко-многим»

При отношении «многие-ко-многим» одной записи в таблице A могут соответствовать несколько записей в таблице B, а одной записи в таблице B — несколько записей в таблице A. Этот тип связи возможен только с помощью третьей (связующей) таблицы, содержащей два поля, которые являются внешними ключами таблиц A и B. Отношение «многие-ко-многим» по сути дела представляет собой два отношения «один-ко-многим» с третьей таблицей.

Например, отношение «многие-ко-многим» между таблицами «Сделки» и «Товары» определяется путем создания двух отношений «один-ко-многим» с таблицей «СодержаниеСделки». В одной сделке может быть много товаров, а каждый товар может появляться в нескольких сделках (рис. 4.5).

Поле со списком подстановок

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

Например, если имеется таблица «Сделки», в которой уже определено поле «НазваниеКлиента», и требуется превратить его в поле подстановок, в котором будут выводиться названия клиентов из таблицы «Клиенты», то необходимо открыть таблицу «Сделки» в режиме конструктора и преобразовать поле «НазваниеКлиента» в поле подстановок.

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

1. создать таблицу «Сделки» в режиме конструктора;

2. выбрать строку поля «НазваниеКлиента»;

3. в столбце Тип данных нажать кнопку со стрелкой и выбрать Мастер подстановок;

4. в первом диалоговом окне мастера подстановок выбрать переключатель для создания столбца подстановок, использующего значения из таблицы или запроса. Нажать кнопку «Далее»;

5. выбрать таблицу «Клиенты». Нажать кнопку «Далее»;

6. выбрать поле «НазваниеКлиента» как столбец подстановки. Нажать кнопку «Далее»;

7. задать ширину столбцов, которые содержит столбец подстановки. Рекомендуется скрыть ключевой столбец. Нажать кнопку «Далее»;

8. задать подпись столбца подстановки. Нажать «Готово».

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

Аналогично связываются следующие пары таблиц: «Сделки» и «Сотрудники»; «СодержаниеСделки» и «Товары»; «СодержаниеСделки» и «Сделки».

Определение связей при помощи Схемы данных

1. Закрыть все открытые таблицы. Создавать или изменять связи между открытыми таблицами нельзя.

2. Для перехода в окно базы данных нажать клавишу F11.

3. Нажать кнопку Схема данных на панели инструментов.

4. Если в базе данных еще не определены связи, автоматически открывается диалоговое окно Добавление таблицы.

Если нужно добавить таблицы для создания связей, а диалогового окна Добавление таблицы на экране нет, нажать кнопку «Отобразить таблицу» на панели инструментов.

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

6. Чтобы установить связи между полями, выбрать поле в одной таблице и перетащить его на соответствующее поле во второй таблице. Чтобы перетащить сразу несколько полей, нажать клавишу «CTRL» и, удерживая ее, выбрать каждое поле.

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

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

• Целостность данных

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

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

Например, нельзя сохранить запись, регистрирующую сделку, сделанную несуществующим клиентом, но можно создать запись для сделки, которая пока не отнесена ни к одному из клиентов, если ввести значение Null в поле «НазваниеКлиента»;

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

Например, невозможно удалить запись из таблицы «Сотрудники», если в таблице «Сделки» имеются сделки, относящиеся к данному сотруднику.

• Каскадное обновление и удаление

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

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

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

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

Например, при удалении из таблицы «Клиенты» записи конкретного клиента будут автоматически удалены все связанные записи в таблице «Сделки» (а также записи в таблице «СодержаниеСделки», связанные с записями в таблице «Сделки»).

8. Для создания связи нажать кнопку «Создать».

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

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