Успешно изучив это занятие, вы будете знать:

• основные отличия СУБД MS Access от электронных таблиц MS Excel;

• структуру базы данных в MS Access;

• назначение строк и столбцов таблицы базы данных;

• ограничения полей, этапы создания базы данных в СУБД MS Access;

• типы объектов в СУБД MS Access;

• этапы создания базы данных.

После изучения данной темы Вы будете уметь:

• различать поля и записи в таблице базы данных в MS Access;

• задавать значения полей таблиц;

• задавать ограничения целостности базы данных в СУБД MS Access;

• делать правильные запросы, использовать формы, создавать грамотные отчеты в СУБД MS Access;

• работать с некоторыми другими объектами СУБД MS Access.

После изучения материала Вы будете обладать навыками:

• различения сфер применения MS Access и MS Excel 2000;

• описания структуры таблицы в MS Access и других ее характеристик;

• применения ограничений полей таблиц MS Access;

• работы с основными объектами СУБД MS Access;

• создания целостной базы данных.

Таблица базы данных в MS Access, строка таблицы, столбец таблицы

Атрибуты объектов

Запись базы данных и ее структура

Поле записи, тип его записи

Язык программирования

Объект MS Access

Запрос

Отчет

Форма

Модуль

Макрос

Структура и связи таблиц

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

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

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

3. С таблицами Excel 2007 удобно работать, если они содержат ограниченное число строк; таблицы базы данных могут содержать огромное количество записей, и при этом СУБД обеспечивает удобные способы извлечения из этого множества нужной информации.

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

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

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

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

8. Access имеет развитую систему защиты от несанкционированного доступа, которая позволяет каждому пользователю или категории видеть и изменять только те объекты, на которые ему были выданы права администратором системы. Например, можно запретить использование некоторых команд меню, открытие определенных форм, изменение данных в таблицах или формах. Электронные таблицы тоже позволяют защитить данные паролем или разрешить только просмотр данных, однако эти средства менее надежны.

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

MS Excel 2007 является удобным средством хранения ограниченного количества табличных данных с широкими возможностями выполнения расчетов и анализа данных. Документы Excel 2007 предназначаются, как правило, для индивидуального использования.

Основные функции СУБД MS ACCESS — это определение данных (описание структуры базы данных), обработка данных и управление данными.

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

База данных в MS ACCESS представляет собой множество взаимосвязанных таблиц, каждая из которых содержит информацию об объектах определенного типа.

Каждая строка таблицы (рис. 17.1) содержит данные об одном объекте (например, клиенте, автомобиле, документе), а столбцы таблицы содержат различные характеристики этих объектов — атрибуты (например, наименования и адреса клиентов, марки и цены автомобилей).

Строки таблицы называются записями, все записи имеют одинаковую структуру — они состоят из полей, в которых хранятся атрибуты объекта (рис. 17.2).

Каждое поле в записи содержит одну характеристику объекта и имеет строго определенный тип данных (например, текстовая строка, число, дата) (рис. 17.3).

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

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

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

В MS Access действуют следующие ограничения на имена полей:

• имя должно содержать не более 64 символов;

• имя может включать любую комбинацию букв, цифр, пробелов и специальных символов за исключением точки (.), восклицательного знака (!), надстрочного символа ^О и прямых скобок ([ ]);

• имя не должно начинаться с символа пробела;

• имя не должно включать управляющие символы (с кодами ASCII от 0 до 31).

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

При задании имен не допускайте их совпадения с зарезервированными словами. Например, не следует давать полю имя Count, Name и т.п.

Имя поля должно быть уникальным в пределах таблицы. И хотя система не запрещает использование одинаковых имен полей в разных таблицах, избегайте использования одинаковых имен для обозначения разных по смыслу атрибутов. Имя должно быть понятно не только в контексте данной конкретной таблицы. Так, например, если в таблице «СОТРУДНИК» есть поле «Код», и такое же поле есть в таблице «КАФЕДРА», то в первом случае это будет код сотрудника, а во втором — код кафедры. Многие системы (и Access в том числе) автоматически связывают таблицы по полям, которые имеют одинаковые имя, тип и длину. Если имена даны непродуманно, то могут либо возникнуть неправильные связи, либо процесс задания связей будет несколько сложнее, чем при правильном задании имен.

После задания имени надо выбрать тип поля. Если щелкнуть мышкой по свободной ячейке графы «Тип поля», то высветится список допустимых типов полей, из которого и следует выбрать подходящий для описываемого поля тип. Имя и тип поля должны задаваться обязательно. Графа «Описание» может не заполняться. Эта графа используется в целях документирования проекта.

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

Набор допустимых свойств вызывает некоторое удивление. Наверное, не всеми возможностями надо пользоваться. Так, обычно не рекомендуется проводить индексирование по логическому полю. Назначение поля MEMO — хранение длинных текстов. Как и зачем задавать для них условия на значение — не совсем понятно. То же (но несколько в меньшей степени) относится и к формату поля данного типа, а также формату поля счетчик.

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

Свойство «Индексированное поле» определяет, надо ли создавать индекс по этому полю. Индекс ускоряет выполнение запросов, в которых используются индексированные поля, и операции сортировки и группировки.

Не допускается создание индексов для полей MEMO, гиперссылок и объектов OLE.

Свойство «Пустые строки» определяет, допускается ли ввод в данное поле пустых строк (строк, не содержащих символов).

При задании значения «Да» для свойств «Пустые строки» и «Обязательное поле» Microsoft Access различает несуществующие данные (сохраняются в виде пустых строк) и данные, которые существуют, но неизвестны (сохраняются в виде пустых (Null) значений).

Для различия пустых строк от значений Null можно использовать свойство «Формат поля» (Format). При этом вместо пустых строк можно выводить строку «Отсутствуют данные».

Кроме описания структуры таблиц, обычно задают связи между таблицами (рис. 17.5). Связи в базах данных под управлением MS Access определяются по совпадению значений полей в разных таблицах.

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

Внутреннее соединение — объединение только тех записей, в которых значения связанных полей обеих таблиц совпадают.

Левое соединение — объединение всех записей первой таблицы и только тех записей из второй таблицы, в которых значения связанных полей обеих таблиц совпадают.

Правое соединение — объединение всех записей второй таблицы и только тех записей из первой таблицы, в которых значения связанных полей обеих таблиц совпадают.

Обеспечение целостности БД является одной из важнейших задач при создании банка данных, так как обеспечение адекватности базы данных отображаемой предметной области является одним из основных требований, предъявляемых к банку данных.

В Access многие ограничения целостности могут задаваться при создании таблицы.

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

Многие из свойств полей также позволяют обеспечивать контроль целостности. Это такие свойства полей, как:

• размер поля;

• формат поля;

• маска ввода;

• значение по умолчанию;

• условия на значения;

• сообщение об ошибке;

• обязательное поле;

• пустые строки;

• индексированное поле.

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

Размер поля

В поле нельзя ввести больше символов, чем это зафиксировано в свойстве «размер поля» или предопределено типом поля.

Условия на значения

Одной из самых гибких возможностей определения ограничений целостности является задание «Условия на значения». Условия вводятся как выражения. Выражения могут быть простыми или сложными. Используя их, можно задавать и диапазоны. Например, условие: >#1.92#, заданное как «Условие на значения» для поля «ДАТА_ПРИЕМА_НА_РАБОТУ», будет означать, что допустим ввод дат только после 1992 года. (Значения-даты необходимо заключать в символы номера (#)). Такое ограничение целостности может быть использовано, например, в случае если организация, для которой ведется БД, была создана 1 января 1992 года, и все зачисления на работу были после этой даты. При задании такого ограничения целостности ввод значения в поле будет обязательным (даже если в свойстве поля «Условие на значение» зафиксировано «нет»).

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

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

Если пользователь задает значение свойства «Условие на значение», но не определяет свойство «Сообщение об ошибке», то при нарушении условия на значение Microsoft Access выводит стандартное сообщение об ошибке. Если значение свойства «Сообщение об ошибке» задано, то в сообщении об ошибке выводится текст, указанный в качестве значения этого свойства.

Маска ввода

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

В Access такая маска ввода будет выглядеть следующим образом: >L<L

Символ «L» в маске обозначает, что в данную позицию должна быть введена буква, символ «?» обозначает, что в данную позицию может быть введена буква. Символ «>» преобразует все символы, расположенные правее этого знака, к верхнему регистру, символ «<» преобразует все символы, расположенные правее этого знака, к нижнему регистру.

Все символы, которые могут быть использованы в масках, и их назначение можно посмотреть в «Справочной системе» Access.

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

Индексированное поле

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

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

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

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

При удалении основной записи, связанной с несколькими подчиненными, могут быть выбраны разные стратегии обновления:

• запретить удалять основную запись, если имеются подчиненные;

• удалить вместе с основной записью и все подчиненные (каскадное удаление).

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

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

Практически это можно использовать так.

В MS ACCESS можно не только создавать таблицы БД, но и использовать специальные способы работы с созданными таблицами БД. Такие способы работы с БД в MS ACCESS принято называть работой с объектами. Существуют следующие типы объектов, с которыми можно работать в MS ACCESS.

1. Таблицы — основной объект MS Access (рис. 17.6). Список таблиц, составляющих базу данных приложения, появляется в окне базы данных при первом открытии приложения. Кроме того, MS Access создает системные таблицы, в которых хранится информация обо всех объектах приложения, и эти таблицы при необходимости также могут отображаться в окне базы данных.

2. Запросы — используются для просмотра, анализа и изменения данных в одной или нескольких таблицах (рис. 17.7). Например, можно использовать запрос для отображения данных из одной или нескольких таблиц и отсортировать их в определенном порядке, выполнить вычисления над группой записей, осуществить выборку из таблицы по определенным условиям. Запросы могут служить источником данных для форм и отчетов Microsoft Access. Сам запрос не содержит данных, но позволяет выбирать данные из таблиц и выполнять над ними ряд операций.

   В Microsoft Access существует несколько видов запросов:

   • запросы к серверу, которые используются для выборки данных с сервера;

   • запросы на автоподстановку, автоматически заполняющие поля для новой записи;

   • запросы на выборку, выполняющие выборку данных из таблиц;

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

   • запросы на создание таблицы, создающие новую таблицу на основе данных одной или нескольких существующих таблиц, а также другие типы запросов.

3. Формы — используются, чтобы организовать удобный и интуитивно понятный интерфейс пользователя для работы с данными с помощью форм (рис. 17.8).

   Формами называются настраиваемые диалоговые окна, сохраняемые в базе данных в виде объектов специального типа.

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

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

   Элементами управления являются текстовые поля для ввода и правки данных, кнопки, флажки, переключатели, списки, надписи, а также рамки объектов для отображения графики и объектов OLE.

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

4. Отчеты — используются для предоставления пользователям необходимой информации на основе существующих данных (рис. 17.9). Отчеты позволяют выбрать из базы данных требуемую пользователем информацию и оформить ее в виде документов, которые можно просмотреть и напечатать. Источником данных для отчета может быть таблица или запрос. Кроме данных, полученных из таблиц, в отчете могут отображаться вычисленные по исходным данным значения, например итоговые суммы. Отчеты и формы Access имеют много общего. Однако, в отличие от форм, отчеты не предназначены для ввода и правки данных в таблицах. Они позволяют лишь просматривать и печатать данные. В отчете невозможно изменить исходные данные с помощью элементов управления, как это можно сделать с помощью форм. Хотя в отчетах можно использовать такие же элементы управления для указания состояния переключателей, флажков и списков.

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

5. Макросы — это небольшие программы на языке макрокоманд системы Access. Макрос в Access представляет собой структуру, состоящую из одной или нескольких макрокоманд, которые выполняются либо последовательно, либо в порядке, заданном определенными условиями (рис. 17.10). Набор макрокоманд в Access очень широк, с помощью макросов можно реализовать многое из того, что позволяют сделать процедуры на VBA. Каждая макрокоманда имеет определенное имя и, возможно, один или несколько аргументов, которые задаются пользователем. Например, при использовании макрокоманды ОткрытьФорму (OpenForm) в качестве аргументов необходимо задать по крайней мере имя открываемой формы и режим вывода ее на экран.

6. Модули — процедуры на языке программирования Visual Basic for Application, VBA (рис. 17.11). Язык программирования VBA является общим инструментом для всех приложений Microsoft Office, позволяющим решать любые задачи программирования, начиная от автоматизации действий конкретного пользователя и кончая разработкой полномасштабных приложений, использующих Microsoft Office в качестве среды разработки.

Создание структуры БД

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

Этапы создания базы данных

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

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

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

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

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

• каждое поле должно быть связано с темой таблицы;

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

• в таблице должна присутствовать вся необходимая информация;

• информацию следует разбивать на наименьшие логические единицы (например, поля «Имя» и «Фамилия», а не общее поле «Имя»).

Задайте ключевое поле. Для того чтобы MS Access мог связать данные из разных таблиц, (например, данные о клиенте и его заказы), каждая таблица должна содержать поле или набор полей, которые будут задавать индивидуальное значение каждой записи в таблице. Такое поле или набор полей называют основным ключом.

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

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

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

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

Практически это можно использовать так.

1. Основные функции СУБД MS ACCESS — это определение данных, обработка и управление данными.

2. В MS ACCESS можно не только создавать таблицы БД, но и использовать специальные способы работы с созданными таблицами БД. Такие способы работы с БД в MS ACCESS принято называть объектами: таблицы, запросы, формы, отчеты, макросы и модули.

3. Таблицы — основной объект MS Access. Он может содержать описание других объектов.

4. Запросы — используются для просмотра, анализа и изменения данных в одной или нескольких таблицах. Они могут служить источником данных для форм и отчетов Microsoft Access. Сам запрос не содержит данных, но позволяет выбирать данные из таблиц и выполнять над ними ряд операций.

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

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

Выполните задания к теме 17 в тетради-практикуме.