Как сделать связь один ко многим в access 2010?

Как сделать связь один ко многим в access 2010?

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

После того, как созданы таблицы БД, можно установить связи между ними, обратившись к схеме данных. Запуск схемы данных выполняется через значок «Схема данных» на панели инструментов Access.

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

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

Типы связей

Тип отношения в создаваемой Microsoft Access связи зависит от способа определения связываемых полей.

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

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

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

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

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

Предположим, что требуется установить связь между таблицами «Кафедра» и «Преподаватель» через поле ККАФ (код кафедры). В таблице «Кафедра» это поле является уникальным ключом, а в таблице «Преподаватель» — внешним ключом. Если схема данных создается заново, то при нажатии на кнопку «Схема данных» поверх окна схемы данных появится окно «Добавление таблицы» . В этом окне следует выделить требуемые таблицы и нажать «Добавить» .

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

Необходимо перетащить мышью поле ККАФ таблица «Кафедра» на поле ККАФ таблицы «Преподаватель» . В открывшемся окне «Изменение связей» следует установить флажок «Обеспечение целостности данных» . В этом случае Access будет выдавать предупреждающие сообщения о неправильном вводе данных, если, например, в поле ККАФ подчиненной таблицы «Преподаватель» будет введено значение, отсутствующее в поле ККАФ базовой таблицы «Кафедра» .

Обратите внимание, что Access автоматически определил тип связи как «один-ко-многим».

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

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

Для установления связей по составному ключу необходимо в окне «Изменение связей» в полях «Таблица/Запрос» и «Связанная таблица/запрос» вручную выбрать из списков пары связываемых полей. На рисунке показан пример связи по составному ключу.

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

Как сделать связь один ко многим в access 2010?

Pers.narod.ru. Обучение. Access VBA — редактируем связь многие-ко-многим и программно выполняем запрос с параметрами формы

Статья написана в учебных целях, хотя в ней есть и пара интересных неучебных нюансов.

Постановка проблемы: мы хотим динамически редактировать в Access связи между 2 таблицами, объединенными отношением «многие ко многим» (например, теги и слова, помеченные тегами, студенты и дисциплины, которые они посещают, или просто абстрактные «объекты» и «категории»). Думаю, понятно, что «многие ко многим» означает, что один объект может относиться к нескольким категориям и, наоборот, одной категории соответствует несколько объектов.

Классический способ реализации такой связи — промежуточная таблица, хранящая внешние ключи категорий и объектов и реализующая, таким образом, две связи «один ко многим», на которые мы разложили наше отношение:

Сама по себе реализация такого редактора в Access несложна, вот весь процесс.

1. Создаем новую базу данных и сохраняем ее.

2. В окне базы данных на вкладке «Таблицы» создаем в режиме конструктора 3 таблицы:

• таблица «Категории» будет включать поле «Код категории», имеющее тип «Счетчик» и текстовое поле «Категория», служащее для описания; щелкнув правой кнопкой на поле «код», сделаем его ключевым:
  
• таблица «Объекты» будет устроена аналогично: она включает ключевое поле-счетчик с именем «Код объекта» и текстовое поле «Объект», предназначенное для данных;
• наконец, таблица «Связи» состоит из двух числовых полей, показанных ниже:
  

Обратите внимание, что оба поля я сделал ключевыми — это поможет избежать дублирования связей — например, объект 1 не должен иметь две одинаковых связи с категорией 1. Чтобы сделать оба поля ключевыми, нужно при нажатой клавише Ctrl выделить их, щелкая по области ключа, а затем вызвать правой кнопкой пункт меню.

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

4. Закрыв и сохранив схему, вносим по несколько записей в таблицы «Категории» и «Объекты».

5. Формы для работы с категориями и объектами по отдельности или в связке «главная и подчиненная таблица» сделать легко. Для последнего, например, достаточно перейти на вкладку Формы, вызвать Мастер форм, добавить для формы все поля таблиц «Категории» и «Объекты», а на следующем шаге определить главную и починенную формы. Но нас интересует сейчас не это. Главное, что мы хотим сделать — спроектировать форму «Связи» для редактирования наших данных.

6. Вызываем Конструктор форм, получаем новую пустую форму. Если окна «Раздел: область данных» (на самом деле это окно свойств) нет на экране, вызываем его, выбрав в окне формы правой кнопкой мыши пункт меню Свойства.

7. В выпадающем списке окна свойств выбираем «Форма» и назначаем на вкладке Данные нашей форме источником данных таблицу «Связи».

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

Если мастера для этого или других элементов не вызываются, причин может быть 2: не нажата кнопка «Мастера» на Панели элементов или не установлены соответствующие компоненты Access.

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

9. Аналогичным образом создаем список для отображения объектов из таблицы «Объекты», а называться он будет «СписокОбъекты».

10. В принципе, все готово. Наша форма работает, в чем можно убедиться, открыв ее кнопкой Вид

Проблема состоит в том, что при повторном добавлении связи Access начинает ругаться стандартными сообщениями («Изменения не были внесены из-за повторяющихся значений в индексе. «) и, более того, не дает сохранить последние внесенные изменения. Напишем небольшую процедуру на VBA для решения проблемы и лучшей обработки записей.

11. Вернувшись в режим конструктора, добавим на свободное место кнопку и с помощью мастера «Создание кнопок» назначим ей действие «добавить запись» из категории действий «Обработка записей». Все остальное можно настроить по вкусу.

12. При выбранной кнопке в окне свойств перейдем на вкладку События и обратимся к коду сгенерированного нами обработчика:

13. Мы перепишем код сгенерированной Access процедуры так, чтобы он отслеживал ситуацию, когда в списках категорий и объектов ничего не выбрано, а также не давал повторно добавить уже существующую связь. Для последнего действия нам понадобиться выполнить из кода на VBA дополнительный запрос по извлечению выбранных на форме кода категории и кода объекта из таблицы «Связи». Если этот запрос вернет пустой результат, значит, такой связи еще нет и следует добавить запись. Сгенерировать такой запрос на вкладке «Запросы» и потом просто вызвать его, к сожалению, не получится. Дело в том, что Access не видит взятых из формы параметров запроса, если запрос выполняется программно. Ошибка, как правило, возникает со следующим текстом «Too few parameters. Expected Число» («Слишком мало параметров. Ожидалось Число»). Эта ошибка возникает, если команда или один из нижележащих запросов содержит обращения к формам или собственные параметры, — все эти обращения будут восприняты как параметры, которым не передано значение.

Почему так происходит? По этому поводу в справке Microsoft MSDN написано примерно следующее:
NOTE: В DAO Вы должны явно присвоить значение параметру. При использовании DoCmd.OpenQuery Вы этого делать не должны, т.к. DAO использует операции низкого уровня, что даёт Вам большую свободу в использовании параметров (т.е. Вы можете сами присвоить параметру значение переменной, а не использовать ссылку на форму), но Вы должны выполнить служебные действия, которые Access делает «за кулисами» при исполнении DoCmd. С другой стороны, DoCmd работает на более высоком уровне, чем DAO. Выполняя DoCmd, Microsoft Access делает некоторые предположения о том, как поступить с параметрами, и не дает Вам никакой свободы в этом отношении.

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

В Интернете можно встретить советы предварительно обработать все параметры процедурой вида

Однако, для работы этого кода нужно, во-первых, иметь подключенную DAO (в окне редактора Visual Basic при остановленной программе вызвать Tools, References (или Сервис, Ссылки), найти и включить в списке библиотеку Microsoft DAO 3.6 Object Library), во-вторых, работа кода все-таки не гарантируется и в этом случае.

Мы хотим обойтись стандартным кодом

однако, едва избавившись от ошибок с недостающимим параметрами, получим сообщение о нессответствии типов (type mismatch, ошибка с кодом 13)!

Вся проблема состоит в том, что объект RecordSet есть и в библиотеке DAO, и в используемой Access по умолчанию библиотеке ADODb! Таким образом, наличие прямой ссылки на DAO, как в показанной выше процедуре, не гарантирует работоспособность кода — может возникать куча заморочек, связанных с тем, какая библиотека подключена в данный момент и у какой выше приоритет.

Поискав (и не найдя) ответ по всему Интернету я догадался, наконец, описать RecordSet как Variant, то есть, без указания типа:

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

Нам остается отключить для формы встроенную навигацию (свойства «Область выделения» и «Кнопки перехода» со вкладки «Макет» окна свойств формы), добавить с помощью мастера свою навигацию и получить работающее приложение.

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

Это пример можно скачать и доделать, ведь область применения отношений «многие-ко-многим» так же широка, как сами эти отношения.