УДК 004.65
ББК 32.973.26-018.2
Б42
Бекаревич, Ю. Б.
Б42 Самоучитель Microsoft Access 2013 / Ю. Б. Бекаревич, Н. В. Пушкина. — СПб.: БХВ-Петербург, 2014. — 464 с.: ил. — (Самоучитель)
ISBN 978-5-9775-3299-0
Самоучитель позволяет освоить интерфейс и инструменты новой версии Access 2013, а также научиться разрабатывать базу данных и приложение для собственной предметной области пользователя. Описаны новые средства разработки веб-приложения пользователя, автоматически публикуемого на сайтах SharePoint организации или в облаках Microsoft Office 365 и доступного для коллективного использования с помощью веб-браузера. Приводятся решения актуальных задач оперативного анализа на основе данных базы средствами Excel 2013 и ее надстройки PowerPivot, непосредственно отображающей модель реляционной базы данных. Самоучитель построен на примерах, демонстрирующих основные приемы работы с инструментарием Access 2013 при разработке объектов как локальной базы данных, так и веб-приложения пользователя.
Для широкого круга пользователей и программистов
ISBN 978-5-9775-3299-0
© Бекаревич Ю. Б., Пушкина Н. В., 2014
© Оформление, издательство "БХВ-Петербург", 2014
Вводятся только те реквизиты, которые обведены.
Эта книга предназначена для изучения технологий, реализуемых новой версией приложения по управлению реляционными базами данных — СУБД Microsoft Office Access 2013.
Access 2013, сохраняя прежнюю функциональность, прежде всего позиционируется разработчиками как средство интеграции баз данных в ставшую привычной для пользователей веб-среду. Access 2013 включает возможность создания веб-приложений, публикуемых на серверах SharePoint 2013 (локальных или облачных в Office 365), при этом пользователи работают с ними через веб-браузер, не устанавливая приложение Access на компьютере. Access 2013 ориентирован на упрощение разработки веб-приложений и позволяет быстро создать приложение для отслеживания важных бизнес-данных, которое может совместно использоваться сотрудниками компании, работающими в разных городах и странах. Интерфейс для таких приложений формируется автоматически, на основе модели данных и с максимальным использованием стандартных структур и элементов управления, хотя при желании и умении многое можно модифицировать.
Access создает структуру базы данных с формами-представлениями, позволяющими добавлять и редактировать данные. Средства навигации и основные команды, встроенные в веб-приложение, позволяют сразу начать с ним работать. Завершив создание веб-приложения, автору не требуется его как-то специально публиковать — оно публикуется на сайте SharePoint автоматически и готово к использованию. Достаточно нажать кнопку Запустить приложение, чтобы перейти к работе с ним в браузере.
Для хранения данных в веб-приложениях необходим Microsoft SQL Server 2012 или SQL Azure. Использование среды SQL Server для хранения данных обеспечивает новый уровень управляемости и масштабируемости. Совместимость с Office 365 и SQL Azure значительно расширяет сферу использования приложений Access. Даже неопытные пользователи путем создания в интерактивном режиме таблиц и форм могут быстро разработать веб-приложение, которое обеспечит их потребности и общий доступ к данным из Интернета.
Из Access 2013 исключены сводные таблицы и диаграммы, которые в предшествующих версиях позволяли выполнять оперативный анализ данных реляционной базы. Однако появились новые эффективные средства, позволяющие отображать модель данных реляционной базы непосредственно в Excel и в отчетах сводных таблиц и диаграмм использовать ее для оперативного анализа данных.
Отчеты сводных таблиц и диаграмм Excel 2013 теперь могут вместо одной таблицы использовать набор взаимосвязанных таблиц, образующих модель данных. При импорте базы данных Access в отчет сводной таблицы Excel обеспечивается автоматическое создание такой модели. Включение в состав Excel 2013 надстроек PowerPivot и Power View делает его фактически стандартным персональным инструментом бизнес-анализа. Надстройка PowerPivot дополнительно позволяет отфильтровывать данные при импорте, корректировать модели и устанавливать связи в визуальном режиме (drag-n-drop), создавать новые KPI и вычисляемые поля. PowerPivot использует ядро СУБД для обработки огромных массивов данных (сводных таблиц), размещает и обрабатывает их в оперативной памяти, эффективно сжимает при сохранении, а также автоматически строит OLAP-куб. Пользователю не видны эти сложности, и он может с помощью предоставляемого набора высокоуровневых функций сосредоточиться на решении задач анализа.
В книге продемонстрированы возможности оперативного анализа данных базы Access в отчетах сводных таблиц и диаграмм расширенными средствами Excel 2013.
В Access 2013 по-прежнему можно с нуля создавать на компьютере как простые, так и очень сложные базы данных. С помощью новой версии Access конечные пользователи, не прибегая к помощи программистов и администраторов, могут в кратчайшие сроки начать выполнять повседневные задачи, работая с документами и реализуя деловые операции бизнес-процессов. В то же время, используя функциональные возможности Access 2013, специалисты отделов информационных технологий смогут обеспечить разработку и поддержку приложений Access, создаваемых на уровне компаний.
Широкий круг пользователей в значительной степени знаком с возможностями популярного приложения Microsoft Office Excel. Однако если объемы данных значительны, то от организации пользовательских списков в Excel целесообразно переходить к применению специализированных средств создания и использования баз данных Access. СУБД обладает мощным и удобным аппаратом распределения данных по нескольким таблицам, реализуя практически любую модель данных предметной области. Обработка и корректировка данных выполняется средствами форм и отчетов, обеспечивающими эффективный доступ к данным и их представление. В основе таких средств лежит реляционный язык структурированных запросов SQL. С помощью этого языка пользователь может формулировать практически любой запрос к базе данных и решать разнообразные задачи обработки данных.
Новая версия Access делает еще доступнее и удобнее использование средств СУБД для начинающих пользователей. Современный интуитивно понятный интерфейс, многочисленные готовые решения, простые средства создания таблиц базы данных, форм и отчетов в полной мере реализуют эту цель. Работа с усовершенствованным интерфейсом, обеспечивающим быстрый поиск нужных инструментов, позволяет сразу включиться в подготовку профессиональных документов, повышает эффективность, качество работы и сокращает сроки ее выполнения. Необходимые команды организованы в виде наборов вкладок, четко соответствующих функциям, реализуемым в Access, благодаря чему находить их легко и просто.
Готовые приложения, создаваемые по многочисленным шаблонам, помогут пользователю, обладающему ограниченными знаниями в области баз данных, познакомиться с самыми важными функциями и во многих случаях начать работу с первого дня использования Access. Можно использовать эти стандартные приложения без каких-либо модификаций или настройки, а можно взять их за отправную точку и адаптировать в соответствии с задачами и способами обработки информации. При разработке веб-приложения в Access 2013 также можно найти шаблон, который в значительной мере соответствует потребностям пользователя.
Access 2013 предоставляет гибкие средства для работы пользователя с разным уровнем подготовки. Новичок может начать создавать таблицы с любым числом полей непосредственным вводом данных без предварительного определения ее структуры. Тип введенных данных определится автоматически. Тут же, прямо в режиме таблицы, можно вмешаться в этот процесс: присвоить имя, уточнить тип данных, его формат, создать столбцы подстановки.
Кроме создания новых таблиц в базе данных, Access позволяет организовать связи с внешними данными с помощью интерфейса ODBC (Open Database Connectivity). Связанные таблицы отображаются и функционируют аналогично собственным таблицам. Создание связанных таблиц обеспечивает легкость, с которой пользователи Access могут перемещать, объединять и обновлять данные из нескольких разных источников. Через связанные таблицы возможно организовать взаимодействие со всеми распространенными серверами реляционных баз данных, включая Microsoft SQL Server, Oracle, IBM DB2, Informix и Sybase. Возможность создания клиентских приложений — проектов, обеспечивающих доступ к серверам баз данных через интерфейс OLE DB, из Access исключена.
Интерактивные средства обеспечивают простоту разработки форм и отчетов. Построить форму или отчет можно одним щелчком кнопки мыши. Режим представления форм и отчетов — режим макета — позволяет легко настроить их в соответствии с потребностями пользователя, непосредственно наблюдая изменения этих объектов. Разделенные формы с одной частью — для ввода данных, и другой — для быстрого просмотра данных в режиме таблицы, существенно повышают удобства в работе. Простота изменения дизайна форм, отчетов, электронных документов, сохраняемых в базе данных, обеспечивается объединением их элементов в макеты.
Простые в использовании и разнообразные средства фильтрации данных позволяют, не перестраивая макет, получить отчеты, представляющие данные в самых разных разрезах, и использовать их для анализа данных.
Защита баз данных, созданных в файлах форматов ACCDB или ACCDE, построена на доверии к происхождению ее выполняемых объектов. Некоторые объекты Access, такие как запросы на изменение, макросы, изменяющие базу данных, код VBA и элементы управления ActiveX, полученные из ненадежных источников, выполняясь, могут повредить базу данных. Поэтому база данных открывается для работы с полным набором функциональных возможностей только, если ее компоненты подписаны надежными издателями, или пользователь придал ей состояние доверенной и берет на себя ответственность за безопасность. Средства обеспечения безопасности баз данных не предусматривают защиты на уровне пользователей.
Вследствие возможности Access удовлетворять потребности самых разных групп пользователей система получила на рынке значительно большее признание, чем любое другое приложение для работы с базами данных. С помощью мастеров и графических инструментов Access пользователи, не владеющие специальными навыками, могут весьма успешно разрабатывать приложения баз данных.
В процессе подготовки самоучителя по Access 2013 перед авторами стояла задача ознакомить пользователей с особенностями новой версии, сформулировать и осмыслить тенденции развития технологий баз данных. Вместе с тем книга ориентирована на получение целостного представления о программном продукте, изучение во взаимосвязи всех его компонентов — от традиционных инструментальных средств до перспективных технологий. Важнейшей целью книги является демонстрация функционирования всех рассматриваемых средств на конкретных примерах.
В качестве сквозного примера рассматривается база данных, содержащая сведения о плановых и фактических поставках товаров гипотетического предприятия. Представлены примеры работы с данными справочных, плановых и оперативно-учетных документов и решение экономических задач. Такой выбор продиктован тем, что одним из характерных применений СУБД является хранение и обработка экономической информации.
Система взаимосвязанных примеров позволяет более наглядно показать возможности инструментальных средств конструирования объектов, средств программирования при разработке приложений, организации диалога при решении экономических задач. Показана возможность использовать среду Access 2013 при разработке профессиональных приложений, обеспечивающих подготовку системы взаимосвязанных документов, их сохранение в базе данных с возможностью оперативно получать аналитические оценки при дополнении базы новыми данными.
Выполнение приведенных в книге примеров на компьютере в процессе изучения Access 2013 позволит успешно освоить его инструментарий, приобрести навыки работы с базой данных и использовать их в любой профессиональной предметной области.
Книга рассчитана на широкий контингент пользователей: от студента, изучающего основы технологии баз данных, до разработчиков практических приложений в среде СУБД. Самоучитель сопровождает читателя-пользователя в процессе создания файла базы данных, ее таблиц, запросов, последующего конструирования объектов приложения: форм, отчетов и макросов, вплоть до последнего этапа, когда отдельные компоненты объединяются в единое приложение.
Для новичка, впервые открывающего эту книгу, она будет не только источником сведений по новым инструментальным средствам Access 2013, но и станет удобным практическим пособием при их освоении на компьютере, т. к. все примеры содержат подробное описание действий пользователя в процессе выполнения конкретных заданий. Проверить степень освоения изученного материала позволят вопросы с ответами, приведенные в конце каждого раздела.
Применительно к тематике настоящего издания важно не только изучить возможности программных средств Access 2013 и сформировать практические навыки, но и научиться использовать эти средства для разработки баз данных и приложений пользователя в соответствии с требованиями конкретной предметной области. В соответствии с требованиями времени приложение должно предоставлять возможность интерактивной совместной работы через веб-браузер, а для этого надо уметь создавать в Access веб-приложения, размещаемые на сайтах организации или в "облаках" Интернета и доступные пользователям с компьютеров различных платформ и из любого места.
Подавляющее большинство деловых операций сегодня записывается, отслеживается и анализируется в виде данных, хранящихся в реляционных системах управления базами данных.
Система управления реляционными базами данных Microsoft Office Access удовлетворяет потребности самых разных групп пользователей. С помощью мастеров и графических инструментов Access даже пользователи, не владеющие специальными навыками, могут весьма успешно разрабатывать полезные приложения баз данных. Исследования малых и средних предприятий, проведенные различными службами, показали, что Access является одной из самых популярных программ для работы с базами данных.
Приложения для автоматизации работы с электронными таблицами, такие как Microsoft Office Excel, используются на персональных компьютерах с момента их появления и, реализуя мощные вычислительные функции, средства анализа данных и построения диаграмм, позволяют выполнять многие стандартные задачи по обработке табличных данных. Современные приложения для совместной работы, такие как Windows SharePoint, также поддерживают создание и обслуживание списков, доступных через интерфейс веб-обозревателя. Но следует заметить, что ни один из продуктов, поддерживающих таблицы, не обладает всеми достоинствами настоящей реляционной базы данных.
По мере усложнения приложений на основе электронных таблиц или списков возникает вопрос о перемещении данных в стандартные реляционные таблицы, из которых можно выбирать и обрабатывать данные с помощью языка SQL (Structured Query Language). После превращения списков в реляционные данные Access позволяет быстро создавать приложения для решения самых разнообразных задач.
Для решения стандартных задач в Access доступны многочисленные шаблоны баз данных. Мастера и удобные средства конструирования обеспечивают простоту создания в Access реляционных структур данных, а также запросов, форм и отчетов, необходимых для работы с данными. Контекстно-зависимый интерфейс всегда предоставит пользователю элементы, необходимые в данный момент времени.
Простой интерфейс пользователя и интерактивные средства разработки в составе Access 2013 делают разработку приложений в среде этого программного продукта доступной для начинающих пользователей. Любой сотрудник, не имея опыта программирования и обладая ограниченными знаниями в области баз данных, может, используя Access, самостоятельно решать задачи по обработке данных.
В то же время Access удовлетворяет требованиям профессиональных разработчиков и позволяет за незначительное время разрабатывать сложные бизнес-системы.
Новые средства Access 2013 ориентированы на упрощение разработки веб-приложений, которые позволят сотрудниками компании совместно отслеживать важные бизнес-данные.
Благодаря тесной интеграции с Microsoft SharePoint пользовательские веб-приложения просто использовать в масштабах как корпоративной сети предприятия, так и Интернета в целом. Размещение веб-приложения на сайтах SharePoint обеспечивает общий доступ к данным базы через веб-обозреватель и сохраняет возможность доработки веб-приложения в Access.
Для создания веб-приложения и работы с ним помимо Access 2013 понадобится сайт Office 365 или локальный сервер SharePoint Server 2013 с поддержкой служб Access. Данные базы веб-приложения хранятся на сервере SQL Server 2012 или в базе данных SQL Windows Azure. При использовании Office 365 (как в версии для малого бизнеса, так и корпоративной) среда SharePoint 2013, включающая службы Access, обеспечивается на сайтах Office 365. Служба SharePoint в пакете Office 365 достаточно проста в управлении и предоставляет усовершенствованные технологии обеспечения безопасности, а также возможность доступа практически из любого расположения.
Интеграция с Office Outlook позволяет использовать электронную почту для сбора или обновления данных в таблицах Access, не прибегая к вводу данных вручную.
СУБД (система управления базами данных) является универсальным программным инструментом создания и обслуживания баз данных и приложений пользователя в самых разных предметных областях. СУБД обеспечивает создание, многоаспектный доступ к данным и использование одних и тех же данных различными задачами и приложениями пользователей.
СУБД поддерживаются различные модели данных. Модель данных — это метод (принцип) логической организации данных, используемый СУБД. Наиболее известными являются иерархическая, сетевая и реляционная модели.
В СУБД для персональных компьютеров (настольных СУБД) поддерживается преимущественно реляционная модель, которую отличает простота и единообразие представления данных простейшими двумерными таблицами. Реляционная модель обеспечивает возможность использования в разных СУБД операций обработки данных, имеющих единую основу, — алгебру отношений (реляционную алгебру), и универсального языка структурированных запросов — SQL (Structured Query Language).
Основной логической структурной единицей манипулирования данными является строка таблицы — запись. Структура записи определяется составом входящих в нее полей. Совокупность полей записи соответствует логически связанным реквизитам, характеризующим некоторую сущность предметной области.
Типовыми функциями СУБД по манипулированию данными являются выборка, добавление, удаление, изменение данных.
Данные из одной или нескольких взаимосвязанных таблиц могут подвергаться обработке. К операциям обработки относятся, например, расчеты в пределах каждой записи, группировка записей в соответствии с заданным критерием группировки и обработка записей выделенных групп с помощью статистических функций, таких как суммирование, определение максимального, подсчет числа записей в группе и т. п.
СУБД Microsoft Access является системой управления реляционной базой данных, включающей все необходимые инструментальные средства для создания локальной базы данных, общей базы данных в локальной сети или в Интернете на сайте SharePoint, сохраняемой на SQL-сервере, а также для создания приложения пользователя, работающего с этими базами данных.
База данных Access, создаваемая на локальном компьютере, хранит в файле не только все таблицы с данными, но и объекты приложения — формы, отчеты, а также программный код. Благодаря этому можно создать приложение, целиком хранящееся в одном ACCDB-файле, что существенно упрощает как создание, так и распространение приложений баз данных.
СУБД Access включает разнообразные и многочисленные относительно автономные инструментальные средства, ориентированные на создание объектов базы данных и приложений пользователя.
VBA является базовым компонентом Microsoft Office — он интегрирован в Access, Excel, Outlook, PowerPoint и Word. Все эти приложения, в том числе и локализованные на русском языке, используют англоязычный вариант VBA (включая справочную систему). VBA входит во все варианты поставок Microsoft Office.
VBA представляет собой базовую платформу программирования не только в среде Microsoft Office, но и многих других приложений. VBA содержит средства доступа не только к базам данных Access, но и к базам данных клиент-серверной архитектуры, таким как Microsoft SQL Server, Oracle и др.
Система доступа к данным построена на основе ядра базы данных Access Database Engine. Ядро базы данных используется для создания реляционных баз данных: таблиц для хранения данных и объектов баз данных, таких как индексы; выполняет загрузку, сохранение и извлечение данных в пользовательских и системных базах данных; обеспечивает высокую производительность и улучшенные сетевые характеристики, поддержку двухбайтового представления символов — Unicode, позволяющего использовать символы нескольких национальных алфавитов.
В Access активно развиваются технологические направления, составляющие основу совместного использования корпоративных баз данных.
В Access 2013 обеспечивается работа к многопользовательскими базами данных SQL серверов, реализующими архитектуру "клиент — сервер". Широко известными СУБД, используемыми в этой архитектуре, являются Microsoft SQL Server, Oracle, Sybase SQL Server и др. Эти СУБД являются реляционными SQL-серверами баз данных. Доступ и изменение данных в серверной базе производится по запросам пользователей, обработка которых осуществляется на сервере. Пользователю-клиенту, сделавшему запрос, возвращается только результат выполнения запроса. Access 2013 при работе в качестве клиента для взаимосвязи с SQL-сервером использует специальное программное обеспечение — широко распространенный интерфейс ODBC. Access предоставляет несколько способов взаимодействия приложения с данными сервера на основе интерфейса ODBC:
Замечание
Из Access 2013 исключена реализованная в предыдущих версиях возможность создания клиентских приложений Microsoft SQL Server — проектов, в которых можно было создавать новые базы данных на сервере или использовать существующие и взаимодействовать с ними на основе интерфейса OLE DB.
Интернет-технологии позволяют эффективно распространять и получать доступ к разнородной информации в глобальных и корпоративных сетях. Эти технологии обеспечивают унифицированный доступ к данным различных приложений в разнородных сетях. В роли универсального интерфейса для доступа и работы с данными из внешней среды вне зависимости от аппаратно-программной платформы компьютера пользователя и компьютера-источника данных здесь выступает веб-браузер. Access 2013 предоставляет платформу для создания веб-приложений, которые можно совместно использовать в пределах организации или в Интернете с помощью браузера. С веб-страницами приложения можно работать как с формами Access — для ввода и редактирования данных, как с отчетами Access — для отображения иерархически сгруппированных данных. Для работы с приложением на компьютере пользователя не требуется устанавливать Access. Веб-приложения разрабатываются и могут публиковаться как на сайтах собственного сервера SharePoint 2013 в интрасети, так и на сайтах SharePoint, предоставляемых Office 365 в "облаке". База данных веб-приложения сохраняется на SQL Server 2012, определенном в SharePoint. Безопасность доступа к данным и управление ими обеспечивается средствами SharePoint и SQL Server.
Access 2013 входит в состав Office 2013 (в наборы приложений выпусков Профессиональный, Профессиональный Плюс). Кроме того, можно оформить подписку на "облачные" версии Office 365 — Access входит в состав следующих выпусков Office 365: для дома расширенный, для малого бизнеса расширенный, для среднего бизнеса расширенный, профессиональный плюс, корпоративный.
Для работы Access 2013 рекомендуется использовать компьютер с процессором архитектуры x86 или x64 с набором инструкций SSE2 и частотой не ниже 1 ГГц. Памяти (RAM) необходимо не менее 1 Гбайт (для 32-разрядных систем) или 2 Гбайт (для 64-разрядных). Компьютер должен работать под управлением одной из операционных систем: Windows 7, Windows 8, Windows Server 2008 R2 или Windows Server 2012.
Для создания и использования веб-приложений помимо Access 2013 понадобится доступ к сайтам Office 365 или сайтам локального сервера SharePoint Server 2013 с поддержкой служб Access. Данные базы веб-приложения хранятся на сервере SQL Server 2012 или в базе данных SQL Windows Azure. При использовании Office 365 среда SharePoint обеспечивается в версиях для малого бизнеса или корпоративной.