Справочники в Access

Источник: AccessSoft

Admin

Создание таблиц

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

Идею статьи подсказало следующее сообщение:

"Подскажите, как правильно разработать структуру базы для реализации почтовой базы. Интересует правильная связь таблиц: Страна, Регион, Город, Улица."

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

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

Обычно ключевое поле таблицы, которое является уникальным идентификатором записи в этой таблице, называют внутренним ключом (в нашем случае это поля типа "Счетчик"), а ключевые поля, через которые внешние таблицы привязаны к данной - соответственно внешними ключами (в нашем случае это числовые поля Длинное целое)

Имя таблицы	Имя поля	Тип поля	Связанная таблица	Поле в связанной таблице
Адресат	idАдресат	счетчик
	idСтрана	Длинное целое	Справочник страны	idСтрана
	idРегион	Длинное целое	Справочник регионы	idРегион
	idГород	Длинное целое	Справочник города	idГород
	Улица	Текстовое
	Дом	Текстовое
Справочник страны	id	счетчик	Адресат	idСтрана
Справочник страны	Обозначение	Текстовое	Адресат	idСтрана
Справочник регионы	id	счетчик	Адресат	idРегион
Справочник регионы	Обозначение	Текстовое	Адресат	idРегион
Справочник города	id	счетчик	Адресат	idГород
Справочник города	Обозначение	Текстовое	Адресат	idГород

В руководствах для начинающих разработчиков часто присутствуют рассуждения, какое поле таблицы сделать ключевым. Дается определение ключевых полей и приводятся примеры связей между ними. На мой взгляд, такие рассуждения только сбивают с толку начинающих. Ведь в принципе, все довольно просто: в 99% случаев лучшее ключевое поле - счетчик. Оно 100% уникально (без повторений) и при установке связи с основной таблицей (в которую подставляются данные из этого справочника) с соответствующим полем Числовое (Длинное целое) связь автоматически определяется как один ко многим (одна запись в справочнике и много аналогичных записей в основной таблице).

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

Как видно, в справочниках только два поля: ключевое и обозначение. А основная таблица состоит в основном из числовых полей, кроме поля "Улица". Вот здесь то и проявляется основная особенность построения реляционных баз данных:

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

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

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

Имена полей таблиц не должны содержать пробелов, иначе могут быть проблемы с VBA, и однозначно возникнут проблемы, при переносе базы на SQL Server. Если имя поля состоит из двух слов, то можно выбрать например такай вариант: НазваниеУлицы или Название_улицы.

Хотя согласно Help, имя может включать любую комбинацию букв, цифр, и специальных знаков за исключением точки (.), восклицательного знака (!), надстрочного знака (`) и квадратных скобок ([ ]), но желательно не использовать в именах полей таблицы никаких символов, кроме букв и цифр. Дело в том, что например имя поля таблицы типа "Улица№" в проекте mdb скорей всего не вызовет никаких конфликтов, а вот при переносе базы на SQL Server, мастер переноса просто "выкинет" его из таблицы.

При создании однотипных справочных таблиц дело пойдет быстрее, если в окне проекта "Таблицы" выделить таблицу, затем Ctrl + C или в контекстном меню при правом щелчке мыши выбрать "копировать", затем выбираем вставить, в появившемся окне задаем имя новой таблицы и жмем "ОК". Вот потому то я во всех справочниках ввел однотипное поля "id", "Обозначение". В таблице "Адресат" так же создаем внешние ключевые поля (Длинное Целое) - idСтрана, idРегион, idГород. Не забудьте убрать в них значение по умолчанию = 0.

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

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

Флажок "Обеспечение целостности данных"

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

Флажок "Каскадное обновление связанных полей"

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

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

Остановлюсь еще на одном моменте: многим наверное приходилось сталкиваться с ошибкой "Слишком большое число". Если не хотите иметь с этим проблем, возьмите себе за правило не делать поля со списками в таблице (начинающие обычно для этого используют мастер подстановок). Дело в том, что из за несоответствия форматов единиц измерения ширины столбцов в разных версиях Access, эта самая ширина может вместо стандартной 2,54см стать … 57,2 см (слишком большим). Поэтому список лучше сделать на форме, а в таблице оставьте просто поле.

Справочная система без ... справочных таблиц

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

Ссылка таблицы на саму себя (Пример1. Вариант1)

Это самый простой способ организации справочника. Создаем форму "Адресат", цепляем к ней источник - таблицу "Адресат". Создаем поле со списком, источник которого - запрос к полю "Улица" из этой же таблицы. Запрос обязательно должен быть сгруппирован и в условии отбора необходимо исключить пустые стоки (Адресат.Улица = Is Not Null). Для обновления списка можно на свойство формы "После обновления" повесить Улица.Requery. Свойство "Ограничиться списком" должно быть "Нет". Иначе нельзя будет добавлять новые данные.

Достоинства: простота реализации, не нужны справочные таблицы, весь программный код - одной строчкой.

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

Впрочем, пробелы можно победить например так:

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

Но в нашем примере я завел для номеров домов тип поля "Текст" - ведь номер может быть например 1/2 или кор. 3. Поэтому, с домами разберемся по другому.

Добавление отсутствующего значение в список значений (Пример1. Вариант2)

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

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

Достоинства: не нужны справочные таблицы.

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

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

Добавление отсутствующего значение в список значений (Пример1. Вариант3)

В предыдущем примере рассматривался вариант добавления отсутствующего значения в список подстановок. Но данные не сохранялись, потому что для этого нужна справочная таблица. Теперь я покажу аналогичный пример, но уже с использованием справочной таблицы, тем самым решится проблема сохранения нового значения в справочнике. Добавлять данные, так же как и в предыдущем примере будем программно. Для этого воспользуемся DAO - Data Access Objects - объектная модель доступа к данным. Объекты доступа к данным создавались, как объектно - ориентированный интерфейс для ядра баз данных Jet фирмы Microsoft как раз для того, чтобы можно было программно вносить, изменять, удалять данные в таблицах.

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

Для работы с таблицей программно (через DAO), сначала необходимо объявить объектную переменную и присвоить ей источник данных (Recordset):

при работе с таблицей/запросом через DAO после выполнения действий со строками обязательно должно быть обновление источника rst.Update. Иначе компилятор тут же выдаст ошибку. И не забываем закрывать источник после работы - освобождаем память. В принципе это не обязательно, при выходе из процедуры Access его все равно обнулит, но стоит выработать такую хорошую привычку - освобождать переменные в конце процедур.

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

Формы - справочники.

Теперь займемся наконец справочными формами. Начнем с простого: создадим табличную форму - справочник. На событие "Открытие" формы вешаем процедуру:

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

1. Раз справочник - источник поля со списком, то почему бы дважды не щелкнуть по соответствующему списку для его открытия? А чтобы это как то узаконить, покрасим список в синий (можно, разумеется, и в любой другой) цвет и объявим в справке к программе, что при двойном щелчке по полю синего цвета открывается подобающая ему справочная таблица. Создаем процедуру открытия табличной формы:

acFormDS - указание на то, что форма открывается в режиме таблицы
acWindowNormal - указываем тип границы (вид формы) - обычная Windows ("Нормальная" в конструкторе форм)

Осталось сделать еще одну важную вещь: обновить список при закрытии справочника. Ведь если мы внесли туда новые данные (или удалили) изменения желательно тут же отобразить в списке.

IsLoaded - эта функция проверки, открыта ли форма "Пример1". Взята из базы "Боррей".

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

Для организации подобного интерфейса нам понадобится служебная таблица "ФормыПараметры" и функция fOpenForm. В этом примере для разнообразия применим другой вид формы.

Справочник у нас будет состоять из двух форм: простая форма будет служить контейнером для табличной. Поле серого цвета - поле фильтра. Начните вводить в него первые буквы искомого обозначения - и содержимое таблицы станет динамически фильтроваться (по первым буквам). Фильтрация происходит при помощи функции "fFilForm".

А теперь самое интересное: я сделал только одну подобную форму справочник (основная форма "Справочник", починенная табличная "SubFrm"), а в списке их у меня две: Страны, Регионы. И в базе вы не найдете форм "Справочник страны", "Справочник регионы". Откуда же они берутся? Все очень просто. Рассмотрим функцию "fOpenForm".

Здесь мы видим процедуру присвоения значений трем переменным: strFormName, strTextFormName, strTableName. А теперь посмотрим на самый верх модуля "Module1". Там мы увидим

Здесь мы подошли к такому важному понятию, как область видимости переменных. Раз эти три переменных объявлены в разделе General общего модуля с ключевым словом Public, то они становятся видимыми для всех модулей приложения. Например, их "увидят" процедуры модуля формы "Справочник" для того чтобы присвоить форме соответсвующие параметры. Рассмотрим их.

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

как видно, значения берутся из полей служебной таблицы "ФормыПараметры". А затем команда на открытие формы DoCmd.OpenForm strFormName

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

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

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

А теперь, попробуйте сами: "создайте" справочник "Города", заполнив соответствующими данными таблицу "ФормыПараметры".

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

Многоуровневые справочники.

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

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

Имя таблицы	Имя поля	Тип поля
СПРАВОЧНИК	id	счетчик
	Name	Текстовое
	Type	Длинное целое
СПРАВОЧНИК Sub	id	счетчик
	id1	Длинное целое
	Name	Текстовое

При установке связей между таблицами СПРАВОЧНИК и СПРАВОЧНИК Sub установим флажок "Каскадное удаление связанных записей" - это как раз тот случай, когда такая процедура будет полезной. Ведь при удалении записи из таблицы "СПРАВОЧНИК" связанные с ней данные становятся не нужными.

Если Вы откроете схему данных, то увидите только две связанные между собой справочные таблицы. В предыдущем примере к основной таблице "Адресат" были привязаны все справочники, и я еще подробно рассказывал о связях, флажках… Дело в том, что при данной схеме организации справочной системы мы не сможем прицепить таблицу "СПРАВОЧНИК" к какому либо полю, ведь в ней теперь хранятся все справочные данные. Да в этом и нет надобности. Если учесть, что пользователь будет работать с базой исключительно через формы, то вероятность ввода в основную таблицу записей, которых нет в справочной, сводится практически к нулю.

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

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

В модуль SprawForm добавилась новая функция - fFilListBox. Она во многом похожа на fFilForm, но служит для фильтрации списка, поэтому в параметрах вместо frm As Form поставлено lst As ListBox. Кроме этого, в обеих функциях изменена строка формирования фильтра:

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

то для списка нужно использовать свойство RowSource

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

В модуле формы "Справочник" добавилось условие отбора по полю Type таблицы СПРАВОЧНИК

и появилось новое - присвоение полю Type значения по умолчанию, равное текущему значению переменной strTableName

Ведь если этого не сделать, то запись в таблице СПРАВОЧНИК окажется не привязанной к текущему типу справочника.

В модуле формы двухуровневого справочника "СправочникМ" видим, что формируются две пары строк - источников данных: strSql, strSql1 - для списка и strSql2, strSql3 - для табличной формы. Строки разбиты на пары потому, что между ними нужно будет вставлять условие отбора (WHERE…) Чтобы новое введенное значение в поле справочной формы тут же отображалось в списке, служит процедура

А для навигации по справочной форме (поиску нужной записи) используем

Чтобы исключить возможность ввода в табличную форму (источник - СПРАВОЧНИК Sub) записей, не связанных с основной формой (источник - СПРАВОЧНИК) служит процедура, которая выводит соответствующее сообщение при подобной попытке и блокирует ее:

Раз на форме появилось второе поле фильтра, появилась соответствующая процедура фильтрации по этому полю

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

А теперь можете поэкспериментировать, создавая разного вида справочники - простые и двухуровневые.

Пример для данной статьи Вы можете скачать ниже

Ссылки по теме

Файлы для загрузки