Связь многие ко многим в access: MS Access — отношения «многие ко многим»

3. Связь типа «многие-ко-многим»

При установлении связи между таблицами возможна ситуация, когда между ними нельзя установить отношение «главная-подчиненная» из-за того, что любой записи в одной таблице может соответствовать несколько записей из другой таблицы. Примером могут служить таблицы Студенты и Преподаватели, так как каждый студент сдавал экзамены нескольким преподавателям, а каждый преподаватель принимал экзамен у нескольких студентов. Поэтому между этими таблицами нельзя установить ни одну из описанных выше связей. Это пример связи типа «многие-ко-многим». Access непосредственно не поддерживает такой тип связи. Но ее можно реализовать в виде двух связей типа «один-ко-многим» с помощью третьей (связующей) таблицы. В качестве такой связующей таблицы в БД Деканат используется таблица Сессия (см. рис. 3.2). Она связана как с таблицей Студенты по полю Код студента, так и с таблицей Преподаватели по полю Код преподавателя.

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

Для создания связей между таблицами нужно вернуться в окно БД и закрыть все открытые таблицы(иначе на экране вы увидите сообщение: ТАБЛИЦА ЗАБЛОКИРОВАНА ЯДРОМ БАЗЫ ДАННЫХ). Далее нужно щелкнуть по кнопкеСхема данныхпанели инструментов, либо вызвать щелчком правой кнопки мыши контекстное меню и выбрать в нем пунктСхема данных. Если связи в БД определяются впервые, то будет открыто пустое окноСхема данных. В это окно нужно добавить таблицы, между которыми устанавливается связь. Для добавления таблиц следует вызвать щелчком правой кнопки мыши контекстное меню окнаСхема данныхи выбрать в нем пунктДобавить таблицу. Откроется диалоговое окноДобавление таблицы, содержащее список таблиц БД (см. рис. 3.3). Для добавления таблицы нужно щелкнуть по ее имени, а затем — по кнопкеДобавить

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

Рис. 3. 3. Окно Добавление таблицы

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

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

Рис. 3.4.

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

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

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

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

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

• связанные поля имеют один и тот же тип данных;

• обе связанные таблицы принадлежат одной базе данных Access.

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

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

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

Любую связь можно изменить или удалить. Для изменения связи нужно сделать двойной щелчок по линии связи, и отредактировать ее в открывшемся окне Связи. Чтобы удалить связь, следует щелкнуть по ней и нажать клавишуDelete.

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

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

Этот сайт больше не обновляется.

Подключите Javascript, чтобы увидеть новый адрес страницы или перейдите к статье

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, поэтому все недостающие значения считает неопределенными.

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

Dim q As DAO.QueryDef, p As DAO.Parameter 
  Set q = CurrentDb.QueryDefs("ИмяЗапроса") 'как обычного запроса Select,
  'так и INSERT/UPDATE; в запросах на удаление это не помогает
  For Each p In q.Parameters 
    p.Value = Eval(p.Name) 
  Next 
  q.Execute 
  q.close: Set q=Nothing

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

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

Dim rst As Recordset
CurrentDb.OpenRecordset ("строка запроса")

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

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

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

 Dim rs

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

Private Sub КнопкаДобавить_Click()
If ([Forms]![Связи]![СписокКатегории] 
Нам остается отключить для формы встроенную навигацию (свойства "Область выделения" и "Кнопки перехода" со
вкладки "Макет" окна свойств формы), добавить с помощью мастера свою навигацию и получить работающее
приложение.
Кстати, стандартные сообщения для кнопок навигации, генерируемые Access, можно заменить на свои более
осмысленные, например, код 
MsgBox Err.Description

на
 MsgBox "Достигнуто начало базы данных", vbOKOnly, "Сообщение" 

Это пример можно скачать и доделать, ведь область применения отношений "многие-ко-многим" так же широка,
как сами эти отношения.
Скачать редактор связи "многие-ко-многим": base_mn.zip, 32 Кб





 

Видео: создание связей «многие ко многим» — служба поддержки Майкрософт

Использовать отношения

Доступ к обучению

Использовать отношения

Использовать отношения

Создание отношений «многие ко многим»

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

Следующий: Добавляйте и редактируйте данные

Попробуйте!

Что такое отношения «многие ко многим»?

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

Связь «многие ко многим» существует, когда один или несколько элементов в одной таблице могут иметь отношение к одному или нескольким элементам в другой таблице. Например:

• В таблице «Заказы» содержатся заказы, размещенные несколькими клиентами (которые перечислены в таблице «Клиенты»), и клиент может разместить более одного заказа.

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

• org/ListItem»>

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

Например, заказ клиента Элизабет Андерсен номер 1012 может содержать по одному продукту 12 и 15 и пять продуктов 30.

Создание связи «многие ко многим»

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

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

В нашем сценарии заказ Элизабет Андерсен под номером 1012 состоит из продуктов 12, 15 и 30. Это означает, что наши записи сведений о заказе выглядят следующим образом:

Идентификатор заказа	Код продукта
1012	12
1012	15
1012	30

Элизабет заказала по одному продукту 12 и 15 и по пять продуктов 30. Мы не можем добавить еще одну строку с номерами 1012 и 30, потому что идентификатор заказа и идентификатор продукта вместе составляют наш первичный ключ, а первичные ключи должен быть уникальным. Вместо этого мы добавляем поле «Количество» в таблицу «Сведения о заказе».

Идентификатор заказа	Код продукта	Количество
1012	12	1
1012	15	1
1012	30	5

Создать промежуточную таблицу

org/ItemList»>

1. Выбрать Создать > Таблица .

2. Выбрать Сохранить .

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

Создание полей в промежуточной таблице

В качестве первого столбца таблицы Access автоматически добавляет поле идентификатора. Измените это поле, чтобы оно соответствовало идентификатору первой таблицы в вашей связи «многие ко многим». Например, если первая таблица — это таблица Orders с именем Order ID, а ее первичный ключ — число, измените имя поля ID в новой таблице на Order ID и в качестве типа данных используйте Number .

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

2. Выберите поле, которое вы только что переименовали.

3. На вкладке Поля в разделе Тип данных выберите тип данных, соответствующий полю в исходной таблице, например Число или Краткий текст .

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

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

Объединение полей первичного идентификатора

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

1. В Design View откройте промежуточную таблицу.

2. org/ListItem»>

   Выберите обе строки, содержащие идентификаторы. (Если вы следовали предыдущей процедуре, это первые две строки.)

3. Выберите Дизайн > Первичный ключ .
   Значки клавиш отображаются рядом с обоими полями идентификатора.

Соедините три таблицы, чтобы создать отношение «многие ко многим»

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

Когда вы закончите, отношения должны выглядеть примерно так:

Хотите больше?

Начало работы со связями между таблицами

Создание, изменение или удаление отношения

Учебное пособие по Microsoft Access: создание отношения «многие ко многим»

---

Давайте вернемся к популярному аспекту проектирования вашей базы данных Microsoft Access Tutorial; Создание отношения «многие ко многим» .

Во-первых, посмотрите это короткое видео, в котором объясняются ключевые компоненты и начальные шаги для перехода в состояние «многие ко многим» для вашей базы данных Access.

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

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

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

Вот еще раз шаги по созданию третьей таблицы:

1. Перейдите на вкладку «Создать».

2. Нажмите на Дизайн таблицы. Будет открыта новая таблица.

3. Создайте два поля (плюс любые дополнительные вспомогательные поля).

ПРИМЕЧАНИЕ. Два ключевых поля будут первичными ключами из двух созданных вами таблиц, которые фактически называются вторичными или внешними ключами (дубликаты в порядке).

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

4. Назначить все поля первичным ключом таблицы.

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

5. Сохраните новую третью таблицу. Пометьте его соответствующим образом для облегчения идентификации.

Далее, создание отношения «многие ко многим»:

1. Перейдите на вкладку Инструменты базы данных.

2. Нажмите кнопку «Связи». Вы увидите таблицы.

3. Наведите указатель мыши на первичный ключ первой таблицы.

4. Щелкните левой кнопкой мыши, удерживайте, перетащите его поверх своего аналога в соединительной таблице.