Аксесс связь многие ко многим

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

В этом курсе:

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

Что такое связь «многие-ко-многим»?

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

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

В таблице «Заказы» указаны заказы, сделанные разными клиентами из таблицы «Клиенты». Каждый клиент мог сделать несколько заказов.

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

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

Например, в заказ Арины Ивановой № 1012 могут входить продукты № 12 и 15, а также пять продуктов № 30.

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

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

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

Ниже рассмотрим пример, когда в заказ Арины Ивановой № 1012 входят продукты № 12, 15 и 30. Это значит, что записи в таблице «Сведения о заказах» выглядят следующим образом:

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

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

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

Нажмите кнопку Сохранить .

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

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

Столбец «Код» автоматически добавляется в Access в качестве первого. Измените имя этого поля на идентификатор вашей первой таблицы в связи «многие-ко-многим». Например, если первая таблица называется «Заказы», поле «Код» в ней переименовано в «Номер заказа», и его первичный ключ — число, измените имя поля «Код» в новой таблице на «Номер заказа», а в качестве типа данных выберите Числовой.

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

Выберите переименованное поле.

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

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

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

Объединение полей для создания первичного ключа

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

Откройте промежуточную таблицу в режиме конструктора.

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

Выберите Конструктор > Ключевое поле.
Рядом с обоими полями появится значок ключа.

Соединение трех таблиц для создания связи «многие-ко-многим»

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

После этого связи должны выглядеть следующим образом:

Вам нужны дополнительные возможности?

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

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

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

Чтобы создать отношение «многие ко многим», сначала нужно создать еще одну таблицу.

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

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

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

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

Так как связующая таблица создается в режиме конструктора, открываем вкладку Создание и нажимаем кнопку Конструктор таблиц. Вот наша новая, пустая таблица.

Теперь откроем таблицы «Товары» и «Заказы».

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

Первичный ключ для таблицы «Заказы» называется «ИД заказа». Он также имеет тип данных Счетчик.

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

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

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

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

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

Теперь мы можем создать отношения. Для начала закройте все открытые таблицы. Выберите Работа с базами данных > Схема данных.

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

Разместите таблицы так, чтобы связующая таблица «Сведения о заказе» была посередине. Перетащите поле «ИД заказа» из таблицы «Заказы» в связующую таблицу.

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

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

Нажмите клавиши CONTROL+S, чтобы сохранить изменения в схеме данных.

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

Аксесс связь многие ко многим

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, можно заменить на свои более осмысленные, например, код

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

Связь многие-ко-многим: пример в Access, в SQL. Как сделать связь многие-ко-многим?

Во всех СУБД (системах управления базами данных) имеется несколько типов отношений между таблицами. Среди них связь один-к-одному, один-к-многим, многие-к-одному (некоторые склонны отождествлять эти два типа в один) и связь многие-ко-многим. Пример последней, ее объяснение и применение в различных СУБД, таких как Access или SQL, будет рассмотрено в этой статье.

Определение

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

Представленная схема наглядно отображает суть данного отношения.

Когда может быть использована связь многие-ко-многим?

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

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

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

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

Microsoft Access

Офисный программный продукт от «Майкрософт» известен на рынке ПО уже довольно продолжительное время. Он поставляется вместе с текстовым редактором Worfd, табличным процессором Excel и прочими, входящими в линейку «офиса». Можно Access (читается как «аксес», дословный перевод — «доступ») приобрести и отдельно от его «коллег». Рекомендуется купить, разумеется, лицензионный софт, но ни для кого не секрет, сколько пиратских репаков можно найти на просторах Сети, в виде обычных файлов или раздач торрентов. «Майкрософт Аксес» доступен даже в портативной сборке. Она, не требующая инсталляции и особых навыков работы с ПК, лучше всего подходит для выбора, если ПО не будет использоваться продолжительно и часто.

Из контекста ясно, что «Майкрософт Аксес» — это система управления базами данных. Причем одна из популярнейших. Она является реляционной, что значит, она основана на логической модели данных, которая в ходе своей работы обращается к теории множеств и логике первого порядка. Связь многие-ко-многим в Access (примеры будут даны в ходе объяснения) реализуется очень и очень просто. Рассмотрим ее.

Есть две таблицы..

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

Если рассмотреть иной пример: допустим, футболисты и команды ( с учетом того, что хотя бы один из футболистов играл за разные сборные, и каждая сборная имеет в своем составе одиннадцать игроков), суть построения связи не поменяется. Также будут нужны три таблицы. Из них «Футболисты» и «Команды» в качестве основных, и одна промежуточная.

Схема данных

Связи между таблицами в СУБД «Майкрософт Аксес» реализуются при помощи вкладки «Схема данных». На появившуюся панель добавляются все необходимые сущности (в нашем случае все три таблицы). Создание связи многие-ко-многим будет происходить при помощи двух отношений один-к-многих между главными («Студенты» и «Преподаватели») и промежуточной таблицы. Для этого необходимо соединить между собой соответствующие первичные ключи.

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

Проектирование баз данных на SQL — задача сложнее, чем на «Аксес». Если майкрософтовский продукт полностью адаптирован под офисную среду, имеет огромный и, с каждым выпуском и обновлением все расширяемый, функционал, но в то же время и удобный для простого пользователя интерфейс, то SQL — это отдельный непроцедурный язык программирования, при помощи которого на разных платформах можно работать с базами данных. Известное ПО для данной задачи: Oracle MySQL и DB2 (популярное, но не единственное в своем роде). Несмотря на то, что у каждого из них есть свои тонкости и нюансы, язык SQL их «объединяет». Научившись работать хотя бы с одним из них, разобраться с другим будет гораздо проще.

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

Принцип создания связи многие-ко-многим

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

Реализация связи

Для реализации связи многие-ко-многим в скриптах SQL используются внешние ключи (FOREIGN KEY) аналогичные исходным ключам в основных таблицах. Они записываются вместе со всеми полями при их создании и/или редактировании.

Роль связи многие-ко-многим

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

А это значит, что при составлении схемы данных (в «Аксесе») или написании скриптов (в «Оракл» или «ДиБиТу») будет присутствовать как минимум одна связь многие-ко-многим. Пример SQl, часто использующийся при обучении курса «Организации баз данных» — БД Кинга.

База данных Кинга

Эта учебная база данных представляет собой сведения о корпорации Кинга. Среди таблиц:

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

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

Масштабные же таблицы, такие как «сотрудники фирмы», «фирмы-покупатели», «договоры о продаже» и «акты продаж» связаны сразу с несколькими сущностями, причем с некоторыми — при помощи «посредников» отношением многие-ко-многим. Таблица «фирмы-покупатели» сама является посреднической, как таковая, ведь в ней есть многие поля, заимствованные из других таблиц и являющиеся внешними ключами. Кроме того, масштабность и взаимосвязь базы данных «Корпорации Кинга» такова, что все отношения неразрывно коррелируют между собой и влияют одно на другое. Разрушение хотя бы одного из них повлечет за собой деструкцию целостности всей БД.

Важные нюансы

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

Аксесс связь многие ко многим

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, можно заменить на свои более осмысленные, например, код

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