sovietsova

Создадим форму, которая всегда будет открываться при запуске нашей базы. Так называемое главное окно. На вкладке Создание нажмем Пустая форма.

Назовем ее main.

Далее Файл -> Параметры -> Текущая база данных, опция Форма просмотра, в выпадающем списке выберем форму main. Форма будет открываться при запуске приложения и с помощью нее можно организовать навигацию и доступ к другим объектам базы данных.

На вкладке Работа с базами данных нажмем на кнопку Visual Basic. Откроется окно редактора VBA. В редакторе мы будем писать код, который сможет обращаться ко всем нашим объектам (таблицы, формы, запросы, отчеты и т. п.) и принадлежащим им элементам, менять их свойства и поведение.

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

Sub MyFirstProcedure()

<Код на языке VBA>

End Sub

На панели инструментов нажмем Insert - > Module. Модуль это еще один объект нашей базы. В нем мы будем размещать наши процедуры, а затем вызывать их на исполнение. В окне кода модуля Module1 напишем следующее:

Sub MyFirstProcedure()

Debug.Print “Hello, world!”

End Sub

Убедимся что у нас открыто окно Immediate (View -> Immediate Window), оно необходимо для того, чтобы иметь возможность увидеть результаты выполнения программы в процессе ее отладки. На панели инструментов нажмем зленый треугольник (Run Sub), в окне Immediate увидим результат выполнения процедуры.

Теперь выведем наше приветствие на форму main. В режиме конструктора добавим текстовое поле и в окне свойств поля дадим ему имя txtHello.

С помощью VBA обратимся к этому полю из процедуры и поместим в него текст - Hello, world! Процедура будет иметь следующий вид:

Sub MyFirstProcedure()

Form_main.txtHello.Value = "Hello, world!"

End Sub

Запустим ее на исполнение и убедимся, что текстовое поле txtHello формы main получило значение - "Hello, world!"

Основу работы с реляционными базами данных составляет язык структурированных запросов SQL (Structured Query Language). Это набор инструкций по манипулированию объектами базы данных, с помощью них мы можем создавать таблицы, добавлять в них данные, производить выборку данных, удалять данные и удалять таблицы. Для всего этого мы пользовались соответствующими мастерами, но под капотом Access формулировал эти задачи на языке SQL и даже генерировал для нас готовый SQL код. Основные команды языка применимы к большинству реляционных баз данных (MS SQL Server, Oracle, MySQL, PostgreSQL, SQLite и т. п.), это устоявшийся стандарт в мире реляционных баз. Различные СУБД расширяют возможности SQL собственным функционалом, но основной синтаксисе с незначительными особенностями применим ко всем.

Откроем наш запрос contacts в режиме SQL. В окне построителя запросов мы наблюдаем код на языке SQL, код сгенерировал Access, но мы бы могли написать его сами и он бы заработал. Разберемся в структуре SQL запросов на более простом примере.

Самыми распространенными запросами являются:

запрос на выборку;

запрос на вставку;

запрос на удаление.

Самый простой запрос на выборку начинается с оператора SELECT, затем следует перечисление необходимых полей, затем оператор FROM после чего указывается имя таблицы (таблиц) которым принадлежат эти поля, например:

SELECT name, surname, email FROM contacts;

В результате мы получим все записи таблицы contacts, представленные тремя полями. Если же мы хотим вывести все поля мы можем написать SELECT * FROM contacts; В конце инструкции ставится точка с запятой.

Через вкладку Создание откроем уже не Мастер запросов, а Конструктор запросов. Не будем выбирать таблицу предложенную диалоговым окном и перейдем в SQL режим. Напишем здесь уже известную нам конструкцию SELECT name, surname, email FROM contacts; и отправим ее на исполнение нажав на кнопку с красным восклицательным знаком.

Убедимся в правильности вывода, действительно программа вывела все записи представленные тремя полями

Заменим перечисление полей в секции SELECT на символ * (звездочка):

SELECT * FROM contacts; и выполним запрос.

Результатом запроса будут все записи и все поля таблицы contacts.

Предположим нас интересуют только активные контакты. Реализуем это в SQL запросе, для этого существует оператор WHERE, это оператор условия. Запрос будет выглядеть следующим образом:

SELECT * FROM contacts WHERE active=true;

Иными словами показать все записи таблицы contacts где поле [active] имеет значение ИСТИНЫ, в SQL коде мы уже строго придерживаемся английского значения слова ИСТИНА – true и ЛОЖЬ – false.

Мы можем задавать несколько значений в секции WHERE. …WHERE условие1 AND условие2 AND условие3, перечисляя их через логические операторы AND. Например:

SELECT * FROM contacts WHERE active=true AND [regdate]= #27/10/2022#;

Запрос покажет все записи из таблицы contacts со значением поля [active] равным ИСТИНА и датой регистрации [regdate] равной 27.10.2022, заметим что дату мы обернули в символ решетки и разделили день, месяц, год через слеш, это необходимо при работе с датами в запросах.

В нашем запросе contacts, помимо таблицы contacts, есть еще таблица phones. В запросах мы можем указывать больше одной таблицы, но необходимо указывать по каким полям будет объединяться результат, для этого существует оператор INNER JOIN. Вообще тема объединения таблиц довольна обширна, мы же рассмотрим самый распространенный вид объединения - внутренне. Наш запрос усложняется наличием двух таблиц, имена полей теперь необходимо указывать в формате – имя_таблица.имя_поля, так как в разных таблицах могут находится поля с одинаковыми именами и как мы помним у нас в обоих таблицах есть поле [id]. Запрос на объединение таблицы contacts и phones c возможностью выбора полей из обоих таблиц будет выглядеть следующим образом:

SELECT contacts.name, contacts.surname, contacts.email, phones.phone

FROMcontacts INNERJOIN phones ON contacts.id = phones.userid;

Иными словами код говорит – выбрать поля [name], [surname], [email], [phone] из объединенных таблиц contacts и phones, где каждой записи таблицы contacts соответствует запись из таблицы phones, в которых поля [id] и [userid] совпадают.

Выполним запрос нажав на красный восклицательный знак.

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

Дополним наш запрос секцией WHERE, для выбора только активных контактов, при этом само поле [active] выводить не обязательно.

SELECT contacts.name, contacts.surname, contacts.email, phones.phone

FROM contacts INNER JOIN phones ON contacts.id = phones.userid

WHERE active=true;

На вкладке Создание выберем Мастер отчетов. В диалоговом окне в качестве источника данных выберем Запрос contacts. Заметим, что если бы мы выбрали в качестве источника таблицу, то на выбор у нас бы был только набор полей из этой таблицы, а в запросе contacts у нас есть набор полей из разных таблиц. Хотя мастер отчетов позволяет выбирать несколько таблиц и тем самым выбрать все нужные нам поля, в запросе contacts у нас есть условие – выводить только активные контакты.

Выберем для отчета поля [name], [surname], [email], [companyid], [phone], далее.

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

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

Завершим работу мастера. Теперь в области объектов базы данных появился новый объект – отчет contacts. Как и другие объекты базы, структуру отчета можно редактировать в режиме конструктора.

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

Изменим надписи полей на удобочитаемые.

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

Из вкладки Создание вызовем Мастер запросов.

Выберем простой запрос.

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

Результат простого запроса похож на обычную таблицу.

Перейдем в режим Конструктора запроса. В нижней части конструктора для каждого поля нашей таблицы мы можем задавать условия на отбор тем самым формируя сложные выборки. Предположим нас интересуют только активные контакты с номерами их телефонов. В условиях отбора поля [active] укажем Истина.

Отобразить таблицу и выберем таблицу phones, дважды кликнем на поле [phone], которое отобразится в наборе полей для запроса нижней части мастера запросов. Выполним запрос нажав на красный восклицательный знак панели инструментов.

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

В таблице contacts, поле [active] получило возможность взаимодействия с ним через элемент управления Checkbox,во многих случаях это удобно, хотя в программе за ним скрывается символ 1 или 0, в зависимости от наличия или отсутствия “галочки”.  Мы бы могли удалять записи неактивных контактов, но удалять какие-либо записи в базах данных нужно с особой осторожностью, как правило записи особым образом отмечаются или переносятся, и при необходимости к ним всегда можно вернуться.

Теперь обратим внимание на поля [phone1] и [phone2]. Исходя из опыта мы предположили, что у человека может быть более одного телефонного номера и создали два поля под эти цели, но а если у человека три телефонных номера и даже четыре? Логично предположить и создать под эти цели максимальное количество полей (помним, что вносить все номера в одно поле через запятую противоречит принципу минимальной неделимости), допустим создадим десять полей для хранения десяти телефонных номеров. И делать мы этого не будем по причине того, что система управления базами данных резервирует память под хранение данных и даже при наличии одного человека с десятью телефонными номерами большое количество пустых мест (рисунок ниже) будет замедлять работу нашей базы, когда она начнет разрастаться данными.

Создадим таблицу phones.

В ней же создадим ключевое поле [id] с типом данных Счетчик, поле [userid] – тип данных Числовой и [phone] – тип данных Текстовый. Поле [userid] является внешним ключевым полем, сюда мы будем записывать значение поля [id](которое соответствует конкретному человеку) из таблицы contacts и его номер телефона, столько раз сколько номеров телефонов у него есть.

Перенесем номера телефонов из таблицы contacts в таблицу phones и удалим поля [phone1] и [phone2].

На панели инструментов откроем вкладку Работа с базами данных и вызовем Схему данных. В конструкторе Схемы данных вызовем Отобразить таблицу и выберем из списка таблиц таблицу contacts и phone. Мы видим модели наших таблиц с соответствующими полями.

Наведем курсор мыши на поле [id] таблицы contacts, нажмем на него левой кнопкой мыши и не отпуская перенесем на поле [userid] таблицы phones. В появившемся диалоговом окне отметим пункт Обеспечение целостности данных (в таблице phones мы не сможем записать номер телефона для несуществующего контакта) и применим изменения.

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

Откроем форму contacts из панели объектов базы. Теперь наши поля формы [phone1] и [phone2] ссылаются на несуществующие поля таблицы contacts, все верно, мы их удалили.

Откроем форму contacts в режиме Конструктора уже знакомым для нас способом. Здесь мы имеем возможность в графическом режиме изменять размеры и расположение наших полей, а так же менять их свойства, и много чего другого. Выделим область расположения “битых” полей и удалим их.

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

В появившемся Мастере подчиненных форм выберем источником подчиненных данных Имеющиеся таблицы и запросы, далее во втором окне из выпадающего списка таблиц выберем таблицу phones, из левого списка полей таблицы phones выберем поле [phone] и переместим его в правый список соответствующей кнопкой.

Третий этап диалога предлагает нам связать подчиненные данные с таблицей contacts, если же мы выберем <отсутствие> такой связи, в подчиненной таблице, мы просто будем видеть полный список телефонов всех контактов, выберем первый пункт из списка, который соответствует нашей схеме связи, иными словами мы говорим – Показать все телефоны для такого-то контакта. Сохраним работу мастера, сохраним изменения в конструкторе формы.

На панели инструментов, в подразделе Сервис нажмем Добавить поля и перетащим поле [active] на макет формы. Сохраним изменения и выйдем из режима конструктора.

Теперь на форме contacts, в окне подчиненной таблицы,  перемещаясь по записям контактов сможем видеть и добавлять необходимое количество телефонов для конкретного контакта, все добавления будут записываться в таблицу phones. Добавим новый телефон для первого контакта и в таблице phones появится еще один телефон, заметьте поле [userid] таблицы phones заполняется автоматически соответствующим [id] (которое идентифицирует конкретного пользователя) из таблицы contacts. Также у нас есть возможность изменять “галочкой” активность контакта и эти изменения тоже будут отражаться в таблице contacts.