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

Введение

Основными задачами курсового проекта являются получение навыков при взаимодействии программных продуктов MS Excel и MS Access, с ориентированием внимания на работе в системе управления базами данных MS Access, использование и практическое применение языка программирования VBA.

Тема проекта «Разработка базы данных в MS Access на основе таблиц MS Excel» вариант № 9. В процессе его выполнения необходимо разработать базу данных учета успеваемости школьников. Информация должна содержаться в электронных таблицах MS Excel. Хранение данных в MS Access не допускается. База данных должна содержать запросы, формы и отчеты. Управление базой данных должно осуществляться через формы с использованием кнопок (заполнение, редактирование, вывод и печать отчетов).

Для реализации контрольного примера используются списки учащихся 11-х классов в период обучения с 2007 по 2011 годы.

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

1. Обзор средств реализации курсового проекта

1.1   Microsoft Excel

Excel является мощным программным средством для работы с таблицами данных, позволяющим вычислять значения, упорядочивать, анализировать и графически представлять различные виды данных. Excel совместима с другими приложениями Microsoft Office, а так же подобными себе таблицами и базами данных других производителей, что позволяет Excel импортировать в свои таблицы, объекты из других прикладных программ и передавать (экспортировать) свои таблицы для встраивания в другие объекты. Для обеспечения безопасности Microsoft Excel может выводить предупреждение при открытии книг, содержащих макросы, в которых могут находиться вирусы. Для защиты от вирусов такие файлы могут быть открыты с отключением макросов.Excel имеет встроенный язык программирования - Visual Basic for Аpplications (VBA). Этот язык позволяет создавать приложения, выполняемые в среде Microsoft Office. Это могут быть разнообразные аналитические программы, финансовые системы, программы учета кадров, системы автоматического создания официальных писем / документов с помощью библиотеки готовых шаблонов и т. п. При этом интерфейс создаваемой программы может быть совершенно непохожим на интерфейс того приложения, в котором она написана.совместима с другими приложениями Microsoft Office 97, а так же подобными себе таблицами и базами данных других производителей, что позволяет Excel импортировать в свои таблицы, объекты из других прикладных программ и передавать (экспортировать) свои таблицы для встраивания в другие объекты. Excel поддерживает новейший стандарт в обмене данными, который называется OLE 2.0 (Object Linking and Embedding).

Таблица предполагает совместную работу с прикладными программами Microsoft Office 97 - Microsoft Outlook и Подшивка Office.

Для обмена данными таблицы с другими приложениями и внешними пользователями может использоваться Панель Web. Эта панель команд облегчает просмотр файлов Microsoft Excel и Office, связь с которыми устанавливается гиперссылками. Эта панель может работать вместе с программой просмотра Web. Таблица может создавать гиперссылки для перехода к другим файлам Office на компьютере пользователя, в сети Интранет или Интернет либо локальных сетях.

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

1.2   Взаимодействие Excel и Visual

excel база данные visual

Чаще всего для передачи данных в книгу Excel используется программирование объектов (автоматизация). Этот способ обладает наибольшим спектром возможностей для указания местоположения данных в книге Excel, а также обеспечивает возможность форматирования книги и настройки различных параметров во время выполнения. Программирование объектов позволяет использовать для передачи данных несколько подходов:

Передача данных по одной ячейке

Передача массива данных в диапазон ячеек

Передача набора записей ADO в диапазон ячеек с помощью способа CopyFromRecordset

Создание в листе Excel объекта QueryTable, содержащего результаты запроса по источнику данных ODBC или OLEDB.

Передача данных в буфер обмена с последующей вставкой содержимого буфера обмена в лист Excel.

Также существуют способы передачи данных в Excel, не требующие программирования объектов. При работе с серверным приложением рекомендуется освободить клиентов от большого объема обрабатываемых данных. Ниже приведены способы передачи данных, не использующие программирование объектов:

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

Передача данных на лист Excel с помощью ADO

Передача данных в Excel с помощью динамического обмена данными (DDE).

1.3   Краткое описание языка программирования VBA

Basic for Applications представляет собой новаторское достижение в языках программирования, возможно наиболее значительное с тех пор, как состоялся выпуск первой интегрированной среды разработки. Это означает, не то что VBA - лучшее средство разработки, а лишь то, что VBA существует. Visual Basic, вероятно, самое популярное средство разработки, и теперь совершенно неожиданно этот язык включен в не менее популярный комплект прикладных программ.

Поскольку основой приложений Microsoft послужил один и тот же язык программирования, вероятно, многим придет в голову мысль воспользоваться им. Точно так же, как пользователям Windows для подготовки множества программ необходимо освоить один интерфейс, в VBA достаточно изучить одну среду программирования, чтобы составлять разнообразные приложения. Около 40 компаний - производителей программного обеспечения заключили с корпорацией Microsoft соглашение о включении VBA в их продукты. Первой из этих компаний на рынок вышла фирма Visio Corp., которая интегрировала VBA в пакеты Visio Professional и Visio Technical, версия 4.5. Можно ожидать от таких фирм агрессивной рыночной политики в отношении VBA, поскольку с этим языком их продукты оказались в заметном выигрыше. Настоящее имя VBA - Visual Basic Application Edition, и он на самом деле относится к семейству VB, следовательно, вызывает повышенный интерес как самостоятельное инструментальное средство.

Встраивание VBA в какое-то приложение означает для него создание объектной модели, в результате VBA-программа может обращаться к объектам этого приложения и манипулировать ими. Для изделий фирмы Visio к числу таких объектов могут относиться как прямоугольник на рисунке, так и сам рисунок, а объектная модель распространяется на все эти элементы.

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

Точно так же, как можно составить макрокоманду Office, которая обращается к программам Word и Excel, можно разработать VBA-программу, сочетающую в себе средства Access и Visio. Составное приложение для планировки кухни можно считать неким гибридом Access с механизмом черчения или Visio с базой данных.- собственное подмножество VB5, но на самом деле в VBA есть почти все, что можно найти в VB5. Надо признать, что незначительные отличия все же имеются (например, запуск из Word, а не в автономном режиме), но в любом случае программирование осуществляется одинаково. Объектные модели Office 97 имеют силу до тех пор, пока выполняются приложения, которым они принадлежат, поэтому и VBA, и VB5 могут манипулировать ими. Vs Мы видим, что от аналогичной в VB5 интегрированная среда разработки в VBA почти ничем не отличается.

2. Описание программного комплекса

.1 Структура программного комплекса

excel база данные visual

Программный комплекс состоит из двух файлов, служащих основанием базы данных, это database.mdb - система управления базой данных и книга.xls - табличный редактор MS Excel. Главным файлом управления базой данных является успеваемость школьников.mdb. Посредством его проходят все основные действия - это просмотр, добавление и удаление данных. На рисунке1 изображены таблицы, которые были созданы путем связывания таблиц.

Рисунок 1 - Система управления базой данных

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

Чтобы создать таблицу в MS Access выбираем в объектах «таблицы» и нажимаем на панели инструментов Создать. В Появившемся окне выбираем связь с таблицами, так как данные хранятся в MS Excel (рисунок 2).

Рисунок 2 - Создание таблицы

После нажатия Ок появляется окно в котором мы выбираем расположение файла с которым необходимо установить связь (рисунок 3).

Рисунок 3 - Связывание таблиц

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

Рисунок 4 - Выбор листа для таблицы

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

Рисунок 5 - Связь с таблицей

В появившемся окне необходимо ввести имя связанной таблицы. В нашей базе данных таблицы названы годом обучения. Оставляем имя 2011 и нажимаем Готово (рисунок 6).

Рисунок 6 - Создание имя для таблицы

После нажатия кнопки Готово появляется уведомление о том что связывание таблиц завершено (рисунок 7).

Рисунок 7 - Связь с электронной таблицей

После нажатия ОК новая таблица появляется новая таблица в списке таблиц. На рисунке 8 показана созданная таблица. В таблице имеются Ф.И.О. учеников, год рождения ученика и оценки. В других листах хранятся данные о других учениках по другим годам и их оценки.

Рисунок 8 - Таблица в Excel, лист 2011

1.4   Описание файла книга.xls

Работа выполнялась в MS Office Excel 2000.

Файл, содержит таблицы данных книга.xls, в котором происходит хранение таблиц.

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

Лист 2007 содержит список учащихся одиннадцатого класса 2007-го учебного года, лист 2008 список учащихся одиннадцатого класса 2008-го учебного года и т. д.

1.5   Описание файла успеваемость школьников.mdb

Данные хранятся в файле книга.xls. Интеграция успеваемость школьников.mdb и книга.xls осуществлялась с помощью Связывания таблиц. Таблица состоит из следующих полей:

номер школьника по списку «номер»;

фамилия школьника «фамилия»;

имя школьника «имя»;

отчество школьника «отчество»;

год рождения «год рождения»;

предметы, по которым ведется учет успеваемости школьника «Русский язык», «Белорусский язык» «Математика», «Биология», «Физика», «Химия».

Рисунок 9 - Данные в MS Access

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

Рисунок 10 - Запросы

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

Рисунок 11 - Выбор поля для запроса

В появившемся окне оставляем подробный отчет и нажимаем Далее (рисунок 12).

Рисунок 12 - Выбор отчета

В появившемся окне задаем имя запросу и нажимаем Готово (рисунок 13).

Рисунок 13 - Создание запроса

На рисунке 14 показан созданный запрос.

Рисунок 14 - Запрос на выборку данных 2011 года

После того как были созданы запросы переходим к созданию форм. В объектах выбираем формы и нажимаем на панели инструментов Создать. В появившемся окне выбираем запрос 2011 года и переносим все поля для формы. Затем нажимаем Далее (рисунок 15).

Рисунок 15 - Создание форм

В появившемся окне выбираем внешний вид форсы в один столбец нажимаем Далее (рисунок 16).

Рисунок 16 - Внешний вид формы

Далее появляется окно выбора стиля отображения данных выбираем стандартный и нажимаем Далее (рисунок 17).

Рисунок 17 - Выбор стиля

В данном окне необходимо ввести имя формы и нажимаем Далее (рисунок 18).

Рисунок 18 - Создание формы

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

Рисунок 19 - Формы

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

Рисунок 20 - Запрос форма

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

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

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

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

В следующем шаге нам предлагается добавить уровни группировки, оставляем как есть и нажимаем Далее.

Следующее окно в нем можно создать сортировку данных, если это необходимо. Мы выбираем сортировку от А до Я на 3 поля отображаемые в отчете. Это Фамилия, Имя и Отчество. И нажимаем Далее.

В следующем окне выбираем вид макета для отчета. Макет табличный, ориентация книжная и нажимаем Далее.

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

Следующие отчеты отличаются лишь данными содержащимися в таблицах.

В кнопочном меню есть кнопка возврата в главное меню. В мастере создания кнопок нет подходящей строки для этой кнопки, поэтому было принято решение в Microsoft Visual Basic в коде для данной кнопки внести изменение чтобы она не только возвращалась в главное меню, но и закрывала предыдущее окно, иначе будет много открытых окон, которые будут закрываться вручную. Во всех пунктах меню где имеется кнопка возврата в главное меню, открываем конструктор. Затем обработку событий и в появившемся коде вносим изменения, добавляем оператор DoCmd. Close после второй строки. Далее в строке stDocName = ChrW(1052) & ChrW(1077) & ChrW(1085) & ChrW(1102) было добавлено & ChrW(1077) & ChrW(1085) & ChrW(1102).

3. Описание работы приложения по контрольному примеру

.1 Заполнение списков учащихся

В таблице представлены данные об учениках 11-х классов в период обучения с 2007 по 2011 годы, хранящиеся в табличном редакторе книга.xls. В редакторе никаких вычислений не происходит, данные только хранятся в нем. Книга представляет собой несколько листов, которые подписаны в соответствии с годами обучения учащихся 11 классов.

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

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

3.2 Редактирование данных

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

При нажатии на требуемый год, например 2008 открывается запрос на выборку, в котором и вводятся изменения. Выбирается строка, в которую необходимо внести изменения.

3.3 Просмотр списков учеников

Чтобы просмотреть списки учеников выбираем в меню списки учеников. Открывается подменю, в котором чтобы просмотреть список требуемого года необходимо выбрать его в списке.

3.4 Печать списков учащихся

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

Заключение

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

Список использованных источников

1 Дубнов, П.Ю. Access 2000. Проектирование баз данных [Текст]: учебное пособие / П.Ю. Дубнов - М.: ДМК, 2000. - 272 с.: ил.

2 Блюттман, К. Access. Трюки [Текст]: учебное пособие / К. Блюттман - М.: Питер, 2006. - 332 с.

3 Золотова С.И. Практикум по Access [Текст]: учебное пособие / С.И. Золотова - М.: Великие луки, 2006. - 256 с.

4 Гарнаев А. Самоучитель VBA [Текст]: учебное пособие / А. Гарнаев - М.: БХВ-Петербург, 2004. - 327 с.