- •Часть I. Хранение данных в таблицах 34
- •Глава 1. Создание вашей первой базы данных 35
- •Глава 2. Создание более сложных таблиц 66
- •Глава 3. Обработка листа данных: сортировка, поиск,
- •Глава 4. Блокировка неправильных данных 136
- •Глава 5. Связывание таблиц с помощью отношений 168
- •Часть II. Обработка данных с помощью запросов 206
- •Глава 6. Запросы, выбирающие записи 207
- •Глава 7. Основные хитрости, применяемые в запросах 241
- •Глава 8. Запросы, обновляющие записи ..272
- •Глава 9. Анализ данных с помощью перекрестных запросов и
- •Часть III. Отчеты 323
- •Глава 10. Создание отчетов 324
- •Глава 11. Проектирование сложных отчетов 356
- •Часть IV. Разработка пользовательского интерфейса
- •Глава 12. Создание простых форм 392
- •Глава 13. Проектирование сложных форм 426
- •Глава 14. Создание системы переходов 465
- •Часть V. Программирование в access 496
- •Глава 15. Автоматизация задач с помощью макросов 497
- •Глава 16. Автоматизация выполнения задач средствами языка
- •Глава 17. Написание кода с более развитой логикой 571
- •Часть VI. Совместное использование access 615
- •Глава 18. Совместное использование бд несколькими пользователями 616
- •Глава 19. Импорт и экспорт данных 650
- •Глава 20. Подключение Access к sql Server 692
- •Глава 21. Подключение Access к SharePoint 724
- •На профессиональном уровне. Преимущества хорошо спроектированной базы данных
- •Для тех, кто понимает. Когда программы Access недостаточно
- •Уголок ностальгии. Зачем опять изобретать колесо?
- •Уголок ностальгии. Сочетания клавиш в Access 2003
- •Часть I
- •Часто задаваемый вопрос. Использование чужой бд
- •На профессиональном уровне. Шаблоны, подходящие для разных целей
- •На профессиональном уровне. Работа Access в интерактивном режиме
- •Для тех, кто понимает. Использование Access бд, созданных в более ранних версиях программы
- •На профессиональном уровне. Проектирование бд для начинающих
- •На профессиональном уровне. Вставка больших значений в узкие столбцы
- •Для тех. Кто понимает. Если сомневаетесь, не удаляйте
- •Малоизвестная или недооцененная возможность. Копирование записи целиком за один шаг
- •Малоизвестная или недооцененная возможность. Сжатие бд
- •Часто задаваемый вопрос. У какого файла расширение laccdb?
- •Практические занятия для опытных пользователей. Изменение папки, которую Access использует для хранения бд
- •Малоизвестная или недооцененная возможность. Сворачивание ленты
- •Экономящая время подсказка. Создание ярлыка для таблицы
- •Глава 2
- •Для тех, кто понимает. Изменение типа данных может привести к потере информации
- •На профессиональном уровне. Нормативы максимальной длины
- •На профессиональном уровне. Как Access предотвращает дублирование записей
- •На профессиональном уровне. Почему так важна уникальность
- •Глава 3
- •Малоизвестная или недооцененная возможность. Настройка всех листов данных
- •На профессиональном уровне. Числа и специальные символы в текстовых полях
- •Практические занятия для опытных пользователей. Фильтры в противоположность запросам
- •Малоизвестная или недооцененная возможность. Поиск и замена
- •Глава 4
- •Для тех, кто понимает. Не требуйте слишком многого
- •На профессиональном уровне. Как работают индексы
- •Часто задаваемый вопрос. Индексы и производительность
- •Практические занятия для опытных пользователей. Вставка вашей маски в список масок программы
- •На профессиональном уровне. Создание списка подстановки, использующего другую таблицу
- •Глава 5
- •Часто задаваемый вопрос. Отключение обеспечения целостности данных
- •Для тех, кто понимает. Пользуйтесь каскадным удалением с осторожностью
- •Практические занятия для опытных пользователей. Изменение параметров подтаблицы
- •Часто задаваемый вопрос. Обновление списка
- •Для тех, кто понимает. Применяйте связи "один-к-одному" с осторожностью
- •Часто задаваемый вопрос. Работа со связями "многие-ко-многим"
- •Часто задаваемый вопрос. Печать ваших отношений
- •Часть II
- •Для тех, кто понимает. Не бойтесь подстановок
- •На профессиональном уровне. Синтаксис фильтра
- •Практические занятия для опытных пользователей. Как индексы ускоряют поиск
- •Малоизвестная или недооцененная возможность. Запросы на базе запросов
- •Для тех, кто понимает. Подумайте дважды, прежде чем изменять структуру таблиц
- •На профессиональном уровне. Сравнение: отношения и объединения
- •На профессиональном уровне. Изменение данных при использовании запроса с объединением
- •Глава 7
- •На профессиональном уровне. Синхронизация запросов
- •Малоизвестная или недооцененная возможность. Переименование поля в запросе
- •Часто задаваемый вопрос Банковское округление
- •Практические занятия для опытных пользователей. Улучшенные числовые форматы
- •Малоизвестная или недооцененная возможность. Использование случайных чисел для сортировки в случайном порядке
- •Практические занятия для опытных пользователей. Как извлечь первое слово из текстовой строки
- •Для тех, кто понимает. Вычисления для дат и времени
- •Глава 8
- •Аварийная ситуация. Когда Access блокирует ваше обновление
- •Малоизвестная или недооцененная возможность. Скрытие запроса
- •Глава 9
- •Часто задаваемый вопрос. Итоговый проигрыш; итоговый запрос против перекрестного
- •Для тех, кто понимает. Создание запроса с объединением для лучшей группировки
- •На профессиональном уровне. Правильный выбор групп
- •Малоизвестная или недооцененная возможность. Помещение сводных таблиц в их собственные формы
- •Часть III
- •Глава 10. Создание отчетов
- •Глава 11. Проектирование сложных отчетов
- •Глава 10
- •На профессиональном уровне. Выполнение тяжелой работы с помощью запроса
- •Часто задаваемый вопрос. Добавление изображений в отчеты.
- •На профессиональном уровне. Учитесь любить pdf-файлы
- •Часто задаваемый вопрос. Разные способы экспорта данных
- •Малоизвестная или недооцененная возможность. Формат по образцу.
- •Практические занятия для опытных пользователей. Разные линии сетки
- •Глава 11
- •Часто задаваемый вопрос. Ошибки выражений
- •Часть IV
- •На профессиональном уровне. Поля типа Счетчик в формах
- •На профессиональном уровне. Разные люди — разные формы
- •Малоизвестная или, недооцененная возможность. Вывод на экран изображений из бд
- •На профессиональном уровне. Семейство форм Access
- •Глава 13
- •На профессиональном уровне. Присоединенные элементы управления
- •Малоизвестная или недооцененная возможность. Повторное применение ваших любимых настроек стиля границ
- •Часто задаваемые вопросы. Осовременивание элементов управления Windows
- •Практические занятия для опытных пользователей. Как освободиться от привязки к сетке
- •На профессиональном уровне. Насколько велик ваш экран?
- •Глава 14
- •Малоизвестная или недооцененная возможность. Варианты сортировки и просмотра в области переходов
- •На профессиональном уровне. Кнопочные формы с несколькими страницами
- •За кадром. Меню кнопочных форм сохраняются в бд
- •Часть V
- •На профессиональном уровне. Макросы по сравнению с программным кодом
- •Практические занятия для опытных пользователей. Обработка ошибок макроса
- •На профессиональном уровне. Макрокоманды, которым Access не доверяет
- •На профессиональном уровне. ОтправитьОбъект работает с вашей программой элекронной почты
- •Часто задаваемый вопрос. Внедренный макрос
- •Глава 16
- •Малоизвестная или недооцененная возможность. Справка по Visual Basic
- •Для тех кто понимает. Как код связывается с событиями
- •Малоизвестная или недооцененная возможность Разбиение длинных строк кода
- •На профессиональном уровне. Взаимодействие с другими формами
- •Практические занятия для опытных пользователей. Получение нужного цвета
- •Часто задаваемый вопрос. Ленточные формы и неприсоединенные элементы управления
- •Практические занятия для опытных пользователей. Связывание записей с рисунками
- •Глава 17
- •Практические занятия для опятных пользователей. Применение более сложных переменных
- •Практические занятия для опытных пользователей. Применение пользовательских функций в запросах
- •На профессиональном уровне. Алгоритм Луна (Luhn Algorithm)
- •Часто задаваемый вопрос. Запуск других Windows-программ
- •На профессиональном уровне. Станьте знатоком статистических функции по подмножеству
- •Уголок ностальгии. Dао против аdо
- •Часть VI
- •Для тех, кто понимает. Поиск места в сети для вашей бд
- •На профессиональном уровне Указание местонахождения в сети
- •Часто задаваемый вопрос. Как поведут себя старые версии Aсcess?
- •Аварийная ситуация. Мистическая ошибка “Файл уже используется”
- •Часто задаваемый вопрос. Когда не следует пользоваться форматом accde
- •Уголок ностальгии. Отмирание страниц доступа к данным
- •Практические занятия для опытных пользователей. Разделение таблиц для более безопасных корректировок
- •Уголок ностальгии. Защита с помощью рабочих групп упразднена
- •Глава 19
- •На профессиональном уровне. Sql Server и SharePoint: два частных случая
- •Сберегающая время подсказка. Копирование из одной бд в другую
- •На профессиональном уровне. Опасность дубликатов
- •Малоизвестная или недооцененная возможность. Экспорт отчетов
- •На профессиональном уровне. Более внимательный взгляд на теги
- •Глава 20
- •На профессиональном уровне. Важнейшие причины перехода на sql Server
- •Часто задаваемые вопрос. Можно ли доверять корпорации Microsoft?
- •На профессиональном уровне. Проекты Access по сравнению со связанными таблицами
- •0 Запросах
- •Для тех, кто понимает. Синтаксические различия
- •Глава 21
- •Часто задаваемый вопрос. Путаница, связанная с SharePoint
- •На профессиональном уровне. Установка SharePoint
- •На профессиональном уровне. Пять интересных инструментов программы SharePoint, которые стоит опробовать
- •Малоизвестная или недооцененная возможность.
- •Малоизвестная или недооцененная возможность. Представление таблицы данных Access
- •Малоизвестная или недооцененная возможность. Параметры списков SharePoint
Глава 8
Запросы, обновляющие записи
Запросы больше всего известны своей способностью отображать небольшие подмножества больших объемов информации. Этот тип запроса известен как запрос на выборку, и именно его вы изучали в предыдущих двух главах.
Многие приверженцы Access не знают, что у запросов есть другое назначение. Их можно использовать не только для поиска информации, но и для изменения данных. Запросы, оказывающие более сильное действие, будь то удаление, обновление или добавление записей, называют запросами на изменение (action query).
О запросах на изменение
Запросы на изменение не так полезны как запросы на выборку, поскольку в них гораздо меньше гибкости. Идеальный запрос создается единожды и повторно используется снова и снова. Запросы на выборку соответствуют этому определению, поскольку часто требуется обзор данных одного и того же сорта (заказы прошлой недели, самые ходовые товары, размеры классов и т. д.). Запросы на изменение коварней, поскольку они вносят необратимые изменения.
В большинстве случаев, изменение — одноразовое действие, поэтому нет оснований связываться с запросом, который повторно вносит одно и то же изменение повсеместно. И даже если какие-то данные нужно изменять регулярно (например, цены товаров или уровни складских запасов), задаваемые каждый раз реальные значения не одни и те же. В результате невозможно создать запрос на изменение, который бы вносил это изменение в автоматическом режиме.
Прежде чем вы отложите эту главу и займетесь чем-то более интересным, важно рассмотреть некоторые ситуации, в которых применение запросов на изменение удивительно удобно. Запросы на изменение хороши, если у вас есть следующие типы задач.
Пакетные задачи, которые вы хотите выполнять многократно. Некоторые задачи могут повторяться регулярно. Если нужно копировать большое число записей из одной таблицы в другую, удалять порцию старых данных или обновлять поле статуса в группе записей, и такие задачи вы вынуждены выполнять снова и снова, запросы на изменение — замечательное, экономящее время средство.
Сложные или трудоемкие задачи, влияющие на большое количество записей. Любая таблица время от времени нуждается в незначительной реорганизации. Вы можете решить, что пора поднимать цены на 15% или вы установили, что все записи, связанные с клиентом 403, на самом деле должны указывать на клиента 404. Это одноразовые задачи, но
они воздействуют на большое количество записей. Для того чтобы расправиться с ними, вам придется потратить много времени на исправление листа данных — или же можно создать новый запрос на изменение, который внесет исправление гораздо эффективнее. Когда запрос сделан, решите, удалять его или сохранить на случай, если вы захотите откорректировать его и повторно применить позже.
Задачи, зависящие от единственной порции информации, которую вы предоставляете при каждом выполнении запроса. Можно создать запрос на изменение, также содержащий параметры и позволяющий задавать важные значения каждый раз, когда выполняется запрос. (О параметрах запроса см. разд. "Параметры запроса " главы 7,) Используя параметры запроса, можно превратить относительно жесткий запрос (который удаляет конкретную запись) в более гибкий (удаляющий любую выбранную запись).
Тестирование запросов на изменение (с осторожностью)
В плохих руках запросы на изменение — не что иное, как высокотехнологичный способ навредить себе. Они фиксируют изменения (обычно во множестве записей), и после применения изменений вы не можете их отменить. Некоторые поклонники БД вообще избегают запросов на изменение.
Если вы все же решили применять запросы на изменение (и есть множество полезных трюков, которые молено в них использовать), следует принять должные меры предосторожности. Важнее всего перед применением запроса на изменение сделать резервное копирование БД! Этот шаг особенно важен при создании нового запроса на изменение, потому что он не всегда формирует результат, который вы ждете. Для создания резервной копии можно скопировать ваш файл с расширением accdb (как любой другой файл; один из способов— щелчок по нему правой кнопкой мыши и выбор команды Копировать). Если же вы не хотите связываться с Проводником Windows, можно создать резервную копию, не покидая программы Access, с помощью последовательности Office → Управление → Резервная копия базы данных (Office → Manage → Back Up Database) (см. разд. "Создание резервных копий" главы 1).
Подсказка
Всегда легче сделать резервную копию, чем устранять последствия изменений, оставленных разбушевавшимся запросом на изменение.
Резервные копии незаменимы при устранении неисправностей, но неплохо не допускать ошибок с самого начала. Один из безопасных способов — начать с запроса на выборку. В этом случае вы можете убедиться в том, что запрос отбирает нужные записи, прежде чем сделать следующий шаг и преобразовать его в запрос на изменение (выбрав один из типов запросов на изменение в группе на ленте Работа с запросами │ Конструктор → Тип запроса (Query Tools │ Design → Query Type)).
Семейство запросов на изменение
В программе Access есть четыре типа запросов на изменение:
запрос на обновление изменяет значения в одной или нескольких записях;
запрос на добавление выбирает одну или несколько записей и вставляет их в существующую таблицу;
запрос на создание таблицы выбирает одну или несколько записей и создаст для них новую таблицу;
запрос на удаление удаляет одну или несколько записей.
В следующих разделах мы попробуем создать запросы всех этих типов.
Запросы на обновление
Запрос на обновление находит некоторые записи и затем изменяет их. Обычно изменения ограничиваются одним полем, но программа Access разрешает корректировать столько полей, сколько нужно. У вас также есть некоторая свобода в способе реализации обновления. Простейший вариант — ввести совершенно новое значение в поле. Можно создать запрос, который перемещает все товары из одной категории в другую с помощью ввода нового значения в поле CategoryID. Другой вариант — изменение текущих значений в поле с помощью выражения (специальная формула БД, способная выполнять разнообразные вычисления). Можно повысить цены на 10% или добавить неделю к сроку завершения для всех невыполненных проектов.
Подсказка
Если вам нужно выполнить очевидное одноразовое обновление, может быть, предпочтительней воспользоваться поиском и заменой на листе данных (см. разд. "Поиск" главы 2). Этот подход предоставляет возможность просмотреть найденные совпадения и решить, заменять каждое из них или нет.
В приведенном далее примере используются таблицы Products и Products Categories из БД Boutique Fudge (которая описана в разд. "Магазин шоколадных изделий" главы 5). Запрос обновляет все товары в категории Beverages (напитки), повышая цены товаров на 10%. Вы можете самостоятельно выполнить этот пример, загрузив примеры к этой главе со страницы "Missing CD" на Web-сайте www.missingmanuals.com.
Для создания запроса на обновление выполните следующие действия.
1. Создайте новый запрос, выбрав Создание → Другие → Конструктор запросов (Create → Other → Query Design).
На экране появится диалоговое окно Добавление таблицы (Show Table).
2. Добавьте все таблицы, которые вы хотите включить в ваш запрос, выбрав каждую и щелкнув мышью кнопку Добавить (Add) (точно так же, как вы делали, создавая запрос на выборку). По завершении щелкните мышью кнопку Закрыть (Close).
Обычно в запросе на обновление используется одна таблица, но если нужна информация из нескольких связанных таблиц, добавьте их все. Включение в запрос нескольких таблиц создает объединение (см, разд. "Запросы и связанные таблицы" главы 6). Операция объединения в запросе на изменение действует так же, как в запросе на выборку — она извлекает информацию из таблицы-родителя и отображает ее рядом с записями из дочерней таблицы.
В данном примере вам потребуются таблицы Products и ProductCategories.
3. Измените тип запроса на запрос на обновление, выбрав Работа с запросами | Конструктор → Тип запроса → Тип запроса: обновление (Query Tools | Design → Query Type →Update).
Столбец со списком свойств полей в нижней части окна изменится, отражая новый тип запроса. Строки Сортировка (Sort) и Вывод на экран (Show) исчезнут (поскольку они не имеют смысла в запросах на обновление) и для каждого поля, включенного в запрос, появится строка Обновление (Update To).
4. Добавьте поле (или поля), которое вы хотите использовать для отбора и задайте для каждого свойство Условие отбора (Criteria).
Условия отбора определяют, какие записи отберет программа Access. Поскольку данный запрос — это запрос на обновление, отобранные записи — это записи, в которые будут вноситься изменения.
В данном примере следует использовать поле CategoryID или поле CategoryName. Если используется поле CategoryID, нужно задать значение кода (ID) для вашей категории. Если применяется поле CategoryName, можно искать соответствия с помощью названия категории.
Для добавления поля дважды щелкните его кнопкой мыши на схеме в прямоугольнике таблицы так же, как вы делали это в запросе на выборку. Затем задайте условие отбора для значения, с которым вы хотите найти совпадения, как показано на рис. 8.1. Если вы хотите обновить все записи в таблице, то никакого условия отбора не нужно.
Рис. 8.1. Этот запрос ищет продукты в категории Beverages
5. Добавьте поле (или поля), которое хотите изменить.
В данном примере следует добавить поле Price, таким образом, вы сможете изменить цены продуктов.
6. В строке Обновление задайте новое значение, которое ваш запрос поместит в каждое поле.
Существуют два способа обновления поля. Можно задать фиксированное значение, введя его в строке Обновление. Если выбрать этот подход, программа Access вставит в каждую отобранную вами запись именно это значение.
Можно также применить выражение, которое берет одно или несколько значений из существующих полей и использует их для вычисления нового значения. Вы можете применять все операции и функции, описанные в главе 7 и предназначенные для обработки текста, чисел и дат. Например, можно использовать следующее выражение в поле Price для повышения цен товаров на 10%:
[Price]*1.10
Подсказка
В выражении обновления может использоваться один или несколько параметров (см. разд. "Параметры запроса" главы 7). В этом случае Access запрашивает у пользователя, выполняющего запрос, важную информацию (например, каково процентное изменение цены).
7. Добавьте любые другие поля, которые хотите использовать для подтверждения правильности отбора записей.
Прежде чем выполнять ваш запрос и вносить изменения, выполните предварительный просмотр, который выводит все строки, отбираемые вашим запросом на обновление (и, таким образом, все записи, которые изменятся, когда вы выполните запрос). Для того чтобы убедиться, что ваш запрос отобрал нужные записи, возможно, на листе данных понадобится некоторая дополнительная идентифицирующая информация, например, ProductName.
Для того чтобы заставить работать этот предварительный просмотр, нужно применить один формальный прием. Программа Access игнорирует поля, которые вы не собираетесь обновлять. Поэтому если вы хотите добиться вывода на листе данных поля ProductName, следует задать что-то в строке Обновление. В данном случае используйте значение [ ProductName ]. Этот шаг заставит программу Access заменить значение в поле ProductName текущим значением поля ProductName. Другими словами, Access на самом деле ничего менять не будет, но отобразит поле ProductName па листе данных в окне предварительного просмотра.
На рис. 8.2 показан законченный запрос на обновление.
8. Щелкните правой кнопкой мыши заголовок вкладки и выберите команду Режим таблицы (Datasheet View) для просмотра записей, на которые повлияет ваш запрос (рис. 8.3).
Этот шаг позволит просмотреть строки, которые вы собираетесь изменить, прежде чем запустите выполнение запроса. На листе данных вы увидите все записи, удовлетворяющие вашим условиям отбора, — иначе говоря, все записи, которые вы измените при выполнении запроса, но вы не увидите изменений, которые хотите внести.
Примечание
В обычном запросе на выборку просмотр листа данных и выполнение запроса — равнозначные действия. В запросе на изменение отображение листа данных показывает строки, которые будут изменены, но на самом деле не изменяет их. Выполнение запроса изменяет данные, но не показывает измененные записи.
9. Теперь вернитесь в Конструктор (щелкните правой кнопкой мыши заголовок вкладки и выберите Конструктор (Design View)). Если вы уверены в том, что запрос действует
правильно, выберитеРабота с запросами | Конструктор → Результаты → Выполнить (Query Tools | Design → Results → Run) для запуска запроса на обновление и внесения заданных изменений.
Рис. 8.2. Этот запрос отбирает все товары в заданной категории и повышает их цену на 10%
Рис. 8.3. Здесь показан предварительный просмотр. В нем отображаются все товары в категории Beverages с текущими ценами. Когда вы выполните запрос, именно эти записи изменятся
Помните: перед выполнением этого шага рекомендуется сделать резервное копирование вашей БД.
Когда вы выполняете запрос на изменение, программа Access предупреждает о том, что собирается выполнить изменение БД (рис. 8.4). Щелкните мышью кнопку Да (Yes) для внесения изменений.
Рис.8.4. Вверху: при каждом выполнении запроса на изменение Access предупреждает о том, что запрос изменит БД. Если вам не нужно это напоминание, выполните действия, перечисленные в этом окне для открытия диалогового окна Параметры Access и отключения вывода на экран этого предупреждения. (Сначала нужно щелкнуть мышью кнопку Нет для закрытия диалогового окна). Внизу: далее Access сообщает о количестве изменяемых записей и дает вам последний шанс отказаться от изменений. Программа всегда предоставляет эту информацию, даже если отключен вывод стандартных предупреждений. Если сейчас щелкнуть мышью кнопку Да, Access обновит таблицу
К сожалению, программа Access не показывает измененные записи — она вообще ничего не показывает. Если вас интересует, что же произошло, и вы хотите просмотреть только что измененные записи, у вас один вариант — снова вывести на экран окно предварительного просмотра записей, которые вы только что изменили (щелкнув правой кнопкой мыши заголовок вкладки и выбрав Режим таблицы). Этот способ действует до тех пор, пока вы не изменили записи таким образом, что они больше не соответствуют условиям отбора. (Если это произошло, следует создать новый запрос или просмотреть таблицу для двойной проверки ваших данных.)
10. Для сохранения запроса нажмите комбинацию клавиш <Ctrl>+<S> (или закроите вкладку запроса). При этом придется задать имя запроса.
Используйте имя запроса, четко указывающее на то, что это запрос на изменение. Можно, например, задать имя UpdateProductPrices (изменение цен товаров). Запросы на изменение отображаются в области переходов с пиктограммой восклицательного знака. У каждого типа запроса на изменение слегка отличающаяся пиктограмма — для запросов на обновление применяется пиктограмма с карандашом и восклицательным знаком за ним (рис. 8.5).
Если вы не собираетесь повторно использовать свой запрос, может быть, стоит его удалить. Удаление запроса защитит от случайного выполнения вами (или кем-то еще) запроса и внесения нежелательных изменений.
Рис. 8.5. Помните о том, что двойной щелчок по запросу в области переходов запускает его на выполнение. Если вы щелкнули мышью запрос на изменение, например, такой, как выделенный на этом рисунке, то можете изменить или удалить важные данные. (Для открытия запроса на изменение без его запуска щелкните по его имени правой кнопкой мыши и выберите команду Конструктор)