Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования Нижегородский государственный университет им. Н.И. Лобачевского Тарасов В.Л. Работа с базами данных в среде Access Учебное пособие Нижний Новгород 2005 Содержание Предисловие.......................................................................................................................................................6 1. Основные понятия....................................................................................................................................7 1.1. База данных.......................................................................................................................................7 1.2. Архитектура системы баз данных...................................................................................................8 1.2.1. Три уровня архитектуры...................................................................................................................8 1.2.2. Языки работы с базами данных........................................................................................................8 1.2.3. Функции администратора БД............................................................................................................9 1.2.4. Функции СУБД.................................................................................................................................9 1.2.5. Архитектура клиент/сервер..............................................................................................................10 1.2.6. Распределённая обработка...............................................................................................................10 1.3. Проектирование базы данных..........................................................................................................12 1.4. Рынок СУБД.....................................................................................................................................13 2. Среда Access..............................................................................................................................................15 2.1. Загрузка.............................................................................................................................................15 2.2. Интерфейс Access............................................................................................................................17 2.3. Окно базы данных.............................................................................................................................17 2.4. Объекты базы данных Access..........................................................................................................18 3. Таблицы....................................................................................................................................................20 3.1. Типы данных.....................................................................................................................................20 3.2. Структура базы данных..................................................................................................................21 3.3. Создание таблиц..............................................................................................................................22 3.3.1. Конструктор таблиц.........................................................................................................................22 3.3.2. Свойства полей.................................................................................................................................23 3.3.3. Мастер подстановок.........................................................................................................................24 3.3.4. Задание ключевого поля...................................................................................................................26 3.3.5. Создание таблиц с помощью мастера..............................................................................................26 3.3.6. Создание таблиц путём ввода данных.............................................................................................26 3.3.7. Импортирование данных..................................................................................................................27 3.4. Установление связей между таблицами.........................................................................................27 3.5. Ввод и просмотр данных в режиме таблицы..................................................................................31 3.5.1. Ввод и редактирование данных.......................................................................................................31 3.5.2. Ввод графической информации в поля таблицы.............................................................................31 3.5.3. Управление видом информации......................................................................................................32 3.5.4. Подтаблицы......................................................................................................................................33 3.5.5. Сортировка информации..................................................................................................................34 3.5.6. Поиск и замена информации............................................................................................................34 3.6. Фильтрация записей.........................................................................................................................35 3.6.1. Фильтр по выделенному..................................................................................................................35 3.6.2. Использование обычного фильтра...................................................................................................36 3.6.3. Расширенный фильтр.......................................................................................................................38 4. Запросы.....................................................................................................................................................41 4.1. Создание простого запроса..............................................................................................................41 4.1.1. Кнопка Вид.......................................................................................................................................43 4.2. Конструктор запросов.....................................................................................................................44 4.3. Запросы с выводом наборов значений..............................................................................................45 2 4.4. Подведение итогов по записям........................................................................................................46 4.5. Вычисляемые поля............................................................................................................................48 4.6. Перекрестные запросы....................................................................................................................50 4.6.1. Создание перекрестного запроса на базе таблицы..........................................................................50 4.6.2. Создание перекрестного запроса на базе простого запроса............................................................53 4.7. Запросы на изменение данных..........................................................................................................57 4.7.1. Создание таблицы из запроса..........................................................................................................57 4.7.2. Запрос на удаление данных..............................................................................................................59 4.7.3. Запрос на добавление.......................................................................................................................59 4.7.4. Запрос на обновление.......................................................................................................................59 5. Формы.......................................................................................................................................................61 5.1. Создание формы...............................................................................................................................61 5.1.1. Мастер форм.....................................................................................................................................62 5.2. Использование формы......................................................................................................................64 5.3. Модификация форм..........................................................................................................................65 5.3.1. Добавление связанных элементов управления................................................................................66 5.3.2. Свойства элементов управления......................................................................................................67 5.3.3. Форматирование элементов управления.........................................................................................68 5.3.4. Перемещение....................................................................................................................................68 5.3.5. Изменение размеров и выравнивание..............................................................................................69 5.3.6. Цвет и шрифт...................................................................................................................................69 5.4. Ввод данных с помощью формы.......................................................................................................70 5.5. Подчиненные формы........................................................................................................................73 5.5.1. Использование мастера автоформ...................................................................................................74 5.5.2. Использование мастера форм..........................................................................................................75 6. Отчеты......................................................................................................................................................77 6.1. Автоотчеты.....................................................................................................................................78 6.2. Мастер создания отчетов...............................................................................................................80 6.3. Использование отчета.....................................................................................................................85 6.4. Модификация структуры отчета...................................................................................................86 6.4.1. Работа в конструкторе отчетов........................................................................................................86 6.4.2. Управление группировкой...............................................................................................................91 6.4.3. Форматирование элементов управления.........................................................................................92 6.5. Создание наклеек..............................................................................................................................92 6.6. Связь с другими компонентами Office.............................................................................................95 6.6.1. Создание документа слияния в Office 2000.....................................................................................95 6.6.2. Создание документа слияния в Office XP и Office 2003..................................................................98 6.6.3. Публикация в Word........................................................................................................................103 6.6.4. Анализ в Excel................................................................................................................................103 7. Макросы.................................................................................................................................................104 7.1. Группа макросов.............................................................................................................................105 7.2. Условные макрокоманды................................................................................................................106 7.3. Запуск макроса...............................................................................................................................107 7.4. Назначение клавиш для выполнения макросов...............................................................................108 8. Управление приложением Access.........................................................................................................110 8.1. Главная кнопочная форма..............................................................................................................110 8.2. Анализ базы данных........................................................................................................................113 3 8.2.1. Анализ таблиц................................................................................................................................113 8.3. Защита базы данных......................................................................................................................118 8.3.1. Установка пароля...........................................................................................................................118 8.3.2. Удаление пароля............................................................................................................................119 9. Модули....................................................................................................................................................120 9.1. Первая программа..........................................................................................................................120 9.2. Краткий обзор языка VBA..............................................................................................................121 9.2.1. Алфавит и лексика.........................................................................................................................121 9.2.2. Ключевые слова.............................................................................................................................122 9.2.3. Операции........................................................................................................................................122 9.2.4. Типы данных..................................................................................................................................122 9.2.5. Переменные, константы, массивы.................................................................................................122 9.2.6. Операторы цикла и условия...........................................................................................................123 9.2.7. Процедуры и функции...................................................................................................................123 9.3. Среда разработки..........................................................................................................................124 9.3.1. Состав среды..................................................................................................................................124 9.4. Отладка..........................................................................................................................................125 9.5. Управление структурой проекта..................................................................................................126 9.6. Справка...........................................................................................................................................126 9.7. Классы и объекты..........................................................................................................................127 9.7.1. Объявление типов..........................................................................................................................127 9.7.2. Понятие класса...............................................................................................................................127 9.7.3. Пример класса................................................................................................................................128 9.7.4. Объекты и ссылки на объекты.......................................................................................................129 9.7.5. Иерархия классов...........................................................................................................................130 9.7.6. Скрытие данных.............................................................................................................................131 9.8. Полиморфизм..................................................................................................................................131 9.8.1. Оператор With................................................................................................................................133 9.9. Пример обработки данных на VBA................................................................................................133 9.9.1. Подключение библиотек объектов................................................................................................133 9.9.2. Постановка задачи..........................................................................................................................134 9.9.3. Алгоритм решения задачи..............................................................................................................135 9.9.4. Программа заполнения таблицы ПоставленныеДетали...............................................................136 10. Элементы теории баз данных..........................................................................................................139 10.1. Метод «сущность-связь»...............................................................................................................139 10.1.1. Сущности и их атрибуты..............................................................................................................139 10.1.2. Домены.........................................................................................................................................139 10.1.3. Связи между сущностями............................................................................................................139 10.1.4. Слабые сущности и обязательные связи......................................................................................140 10.1.5. Документирование сущностей и связей.......................................................................................140 10.2. Реляционная модель данных...........................................................................................................141 10.2.1. Отношения....................................................................................................................................141 10.2.2. Виды отношений..........................................................................................................................142 10.2.3. Сущности и отношения................................................................................................................142 10.3. Реляционная алгебра.......................................................................................................................142 10.4. Нормализация отношений..............................................................................................................144 10.4.1. Цели проектирования базы данных.............................................................................................144 10.4.2. Первая нормальная форма (1НФ)................................................................................................145 10.4.3. Функциональные зависимости.....................................................................................................145 10.4.4. Вторая нормальная форма............................................................................................................146 10.4.5. Третья нормальная форма............................................................................................................146 4 11. Язык SQL...........................................................................................................................................148 11.1. Краткая история SQL....................................................................................................................148 11.2. SQL в Access....................................................................................................................................148 11.3. Работа с таблицами......................................................................................................................150 11.3.1. Создание таблиц...........................................................................................................................150 11.3.2. Создание индекса.........................................................................................................................152 11.4. Инструкция SELECT......................................................................................................................153 11.4.1. Примеры использования инструкции SELECT...........................................................................154 11.5. Использование SQL в VBA..............................................................................................................157 11.5.1. Классы для работы с запросами...................................................................................................157 11.5.2. Вывод содержимого запроса........................................................................................................159 11.5.3. Сохранение запроса в таблице базы дданных..............................................................................160 Литература......................................................................................................................................................162 Основная.......................................................................................................................................................162 Дополнительная...........................................................................................................................................162 5 Предисловие В данном пособии рассматривается работа с базами данных в среде популярной программы Access. Выбор Access обусловлен тем, что с помощью этой программы можно разрабатывать базы данных различной сложности: от индивидуальной, предназначенной для использования на отдельном компьютере, до систем масштаба предприятия, рабо- тающих в сетях. Access, как одно из приложений пакета MS Office, доступна практически на любом компьютере, где установлен Office, но еще недостаточно широко используется из-за слабой подготовки пользователей. Часто задачи, эффективно и надежно решаемые с помощью Access, пытаются решать с помощью программы Excel, которая предназначена в основном для организации табличных вычислений и не ограждает от ошибок при работе с базами данных. Access обладает развитой системой меню для выполнения большинства манипуля- ций с базами данных. Для начинающих пользователей большую помощь оказывают мас- тера, которые предлагают последовательность шагов с подробными подсказками для соз- дания таблиц, форм, отчетов и выполнения других действий. Конструкторы предостав- ляют опытным пользователям полные возможности по созданию объектов базы данных. Все это позволяет быстро освоить работу с базами данных без изучения какого-либо языка программирования даже людьми, не являющимися профессионалами в области информа- ционных технологий. Рассмотрение начинается с общих вопросов, относящихся к базам данных, затем рассматривается конкретная работа с базами данных в среде Access. После того, как чита- тель получит конкретные навыки по работе с базами данных в среде конкретной СУБД Access, рассматривается теория реляционных баз данных, язык SQL, нестандартная обра- ботка данных с использованием языка программирования VBA в Access. 6 1. Основные понятия 1.1. База данных Существование каждого предприятия, организации, отдельного человека сопрово- ждается накоплением сведений о его деятельности, которые оформляются в виде записей на различных материальных носителях. Такими носителями могут быть различные пред- меты: глиняные таблички, папирус, пергамент, бумага, перфокарты, магнитные ленты, магнитные диски, оптические диски и т.п. Всю совокупность записей о деятельности ка- кого-либо предприятия можно назвать базой данных этого предприятия. Могут создавать- ся и локальные базы данных для хранения сведений об отдельной сфере деятельности предприятия или человека, например, в базе данных могут храниться сведения о чьих- либо знакомых, любитель музыки может вести базу данных об имеющейся у него коллек- ции альбомов. В настоящее время базы данных ведутся с помощью компьютеров. Применение компьютеров для хранения и обработки данных началось примерно с середины 1950-х годов. Компьютерные базы данных имеют ряд преимуществ по сравне- нию с традиционным бумажным способом ведения записей. Отметим некоторые из них. Компактность. Нет необходимости в многочисленных журналах и картотеках. Скорость. Обеспечивается быстрый поиск нужной информации. Низкие трудозатраты. Отпадает необходимость в утомительной, рутинной ручной работе по заполнению бумажных форм и карточек. Оперативность. Всегда доступна точная актуальная информация. Указанные преимущества привели к широкому распространению компьютерных баз данных, хотя их использование требует достаточно больших затрат, связанных с при- обретением компьютеров, программного обеспечения, обучением работников. Приведем несколько терминов, используемых, когда речь идет о базах данных. Система баз данных – это компьютеризованная система хранения записей. Система баз данных включает в себя данные, непосредственно сохраняемые в базе данных (БД), аппаратное обеспечение, программное обеспечение (ПО) и пользователей. СУБД (система управления базами данных) – это программная система, предназна- ченная для создания на компьютере базы данных для многих приложений, поддержания её в актуальном состоянии и обеспечения эффективного доступа пользователей к содер- жащимся в ней данным в рамках предоставленных им полномочий. Пользователи подразделяются на прикладных программистов, конечных пользова- телей и администраторов баз данных (АБД). Прикладные программисты отвечают за написание прикладных программ, исполь- зующих БД. Конечные пользователи работают с СУБД непосредственно через рабочую станцию или терминал. Администраторы баз данных – это технические специалисты, отвечающие за ад- министрирование БД и СУБД в соответствии с требованиями, устанавливаемыми админи- стратором данных. Если предприятие хранит свои данные в централизованной базе данных, должен существовать человек, который несёт основную ответственность за данные предприятия. Это администратор данных. Он принимает решения на уровне высшего руководства предприятия о том, какие данные необходимо вносить в базу данных в первую очередь, над какими данными и какие операции можно осуществить. Системы баз данных могут реализовываться как на малых персональных компью- терах (ПК), так и на больших машинах или системах машин, объединенных в сеть. В зави- 7 симости от мощности используемых машин и своего назначения, системы баз данных мо- гут быть однопользовательскими и многопользовательскими. Однопользовательская система (single-user system) – это система, в которой к базе данных в одно и то же время может получить доступ не более одного человека. Многопользовательская система (multi-user system) – это система, в которой к базе данных могут получить доступ сразу несколько пользователей. С точки зрения пользователя между этими системами нет разницы, так как в обоих случаях ему предоставляется одинаковая возможность работы с интересующими его дан- ными в рамках предоставленных полномочий. 1.2. Архитектура системы баз данных 1.2.1. Три уровня архитектуры Под архитектурой системы баз данных понимают общий подход к ее описанию. Различают три уровня архитектуры: внутренний, концептуальный и внешний, рис.1.1. Внешний уровень (индивидуальные представления пользователей) Концептуальный уровень (обобщённое представление пользователей) Внутренний уровень (представление в памяти) Рис. 1.1. Три точки зрения на базу данных Представление отдельного пользователя называется внешним представлением. На- пример, пользователь из отдела кадров может рассматривать БД как набор записей с ин- формацией об отделах плюс набор записей с информацией о служащих и ничего не знать о деталях, проектах, поставщиках и других вещах, сведения о которых есть в базе. Концептуальное представление – это представление всего содержимого БД. На концептуальном уровне данные определяются с помощью концептуальной схемы. Кон- цептуальная схема содержит перечень всех данных, хранящихся в базе данных, правила безопасности и целостности. Под целостностью понимается непротиворечивость сведе- ний, хранящихся в БД. Внутреннее представление описывается с помощью внутренней схемы, которая описывает типы хранимых полей, существующие индексы, способы представления хра- нимых полей, физическую последовательность хранимых записей. Внутренний уровень наиболее близок к физическому хранению. 1.2.2. Языки работы с базами данных Для работы с данными можно использовать универсальные языки программирова- ния: С, Pascal, Basic, C++ и специализированные языки, например, FoxPro. Универсальные языки называются базовыми языками. Они могут включать подъязык данных. В настоя- щее время в качестве подъязыка данных используется SQL (Structured Query Language, 8 структурированный язык запросов). Подъязык данных включает язык определения дан- ных и язык обработки данных. 1.2.3. Функции администратора БД Администратор базы данных (АБД) отвечает за нормальное функционирование БД. В его функции входит:  Определение концептуальной схемы.  Определение внутренней схемы.  Взаимодействие с пользователями.  Определение правил безопасности и целостности.  Определение процедур резервного копирования и восстановления. После того, как предприятие доверило свои данные СУБД, оно стало критически зависимым от успешного функционирования системы. В случае повреждения какой–либо части БД важно иметь возможность восстановить данные с минимальной за- держкой.  Управление производительностью и реагирование на изменяющиеся требова- ния. 1.2.4. Функции СУБД СУБД – это программное обеспечение, которое управляет работой с базой данных. СУБД должна обеспечивать выполнение ряда функций. Перечислим их.  Определения данных. СУБД должна обеспечивать определение структуры БД, то есть создание внешней, концептуальной и внутренней схемы.  Обработка данных. СУБД должна обрабатывать запросы пользователей на вы- борку, изменение или удаление существующих данных, а также на добавление новых данных в базу. Запросы к данным могут быть планируемыми, которые заранее предусмотрел АБД, и не планируемыми.  Безопасность и целостность данных. СУБД должна контролировать пользова- тельские запросы и пресекать попытки нарушения правил безопасности и цело- стности.  Восстановление и дублирование. СУБД должна иметь компонент, называемый администратором транзакций, который осуществляет контроль над изменени- ем данных, обеспечивает их восстановление и дублирование.  Словарь данных. Он содержит описание данных, входящих в данную БД. Сло- варь данных является системной базой данных, содержащей данные о данных.  Производительность. СУБД должна обеспечивать быстрое выполнение запро- сов пользователя к БД. Заметим, что в своё время система FoxPro стала лидером среди СУБД для персональных компьютеров именно благодаря своей высокой производительности. Как правило, СУБД реализует внутренний и концептуальный уровни архитектуры, то есть обеспечивает хранение данных на дисках и предоставляет возможность описания логической структуры данных. Для реализации внешних представлений пользователей пишется отдельная программа с удобным для конечного пользователя интерфейсом и возможностью выполнения типовых запросов как надстройка над СУБД. Опишем после- довательность обработки запроса пользователя к БД.  Пользователь выдаёт запрос на доступ;  СУБД перехватывает этот запрос и анализирует его; 9  Затем СУБД просматривает внешнюю схему для этого пользователя, соответст- вующее отображение внешний–концептуальный, концептуальную схему, ото- бражение концептуальный–внутренний и определение структуры хранения;  Выполняет необходимые операции над хранимой базой данных. 1.2.5. Архитектура клиент/сервер Систему баз данных можно рассматривать как систему, состоящую из двух частей – сервера (или машины базы данных) и набора клиентов (или внешнего интерфейса), рис.1.2. Сервер – это собственно СУБД. Он поддерживает все основные функции СУБД. Клиенты – это различные приложения, которые выполняются “над” СУБД – приложения, написанные пользователем и встроенные приложения, предоставляемые поставщиками СУБД или сторонними поставщиками. Конечные пользователи Приложения Клиенты СУБД Сервер База данных Рис. 1.2. Схема архитектуры клиент-сервер 1.2.6. Распределённая обработка Распределенная обработка предполагает использование нескольких компьютеров, соединенных между собой. Программу-сервер можно запускать на одном компьютере, а программу-клиент на другом, рис.1.3. Приложения Компьютер клиента СУБД Компьютер сервера База данных Рис. 1.3. Клиент и сервер запускаются на разных машинах Компьютер, на котором запускается программа-сервер, часто называют также сер- вером. Один сервер может предоставлять доступ нескольким клиентам, рис.1.4. 10