Управляющие запросы ядра Microsoft Jet

7. Управляющие запросы ядра Microsoft Jet

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

а. Запрос DROP

Этот запрос позволяет удалить из базы данных таблицу или индекс. Его структура –

DROP TABLE Имя_таблицы

DROP INDEX Имя_индекса

б. Запрос ALTER Table

Этот запрос позволяет редактировать структуру таблицы.

Его структура:

Alter table <имя таблицы> <описание изменения>

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

ADD <описание колонки>

Позволяет добавить новую колонку в таблицу. Описание колонки такое же, как и в Create table. После слова ADD можно указать слово COLUMN, которое позволяет сделать текст запроса более читабельным. После описания колонки можно указать слово FIRST, в этом случае колонка будет первой в таблице, или слово AFTER, после которого указывается название колонки, в этом случае навая колонка будет добавлена после указанной.

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

ADD INDEX (<имя колонки>,... )

Позволяет добавить новый индекс. В скобках указываются названия колонок, входящих в индекс. Между словом INDEX и скобкой можно указать название индекса.

ADD PRIMARY KEY (<имя колонки>,... )

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

ADD UNIQUE (<имя колонки>,... )

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

ADD FULLTEXT (<имя колонки>... )

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

ALTER COLUMN <имя колонки> SET DEFAULT <значение>

ALTER COLUMN <имя колонки> DROP DEFAULT

Запрос позволяет создать или удалить значение по умолчанию для колонки. При этом слово COLUMN можно опустить.

CHANGE COLUMN <имя колонки> <описание колонки>

Запрос изменяет указанную колонку на новую, описание такое же, как и в CREATE TABLE. Слово COLUMN можно опустить. В описании колонки указывается имя колонки, так что имя может быть изменено.

MODIFY COLUMN <описание колонки>

Изменяет колонку на новую, описание такое же, как и в CREATE TABLE. Слово COLUMN моно опустить.

DROP COLUMN <имя>- Удаляет колонку.

DROP PRIMARY KEY - Удаляет первичный ключ

DROP INDEX <имя индекса> - Удаляет индекс

RENAME TO <новое имя таблицы> - Переименовывает таблицу

в. Запрос CREATE TABLE

Этот запрос позволяет динамически создать новую таблицу в базе данных. Например:

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

CREATE TABLE Друзья
([Код] integer,
[Фамилия] text,
[Имя] text,
[ДатаРождения] date,
[Телефон] text,
[Заметки] memo,
CONSTRAINT [Индекс1] PRIMARY KEY ([Код]));

8.         Формирование и выполнение запросов в реальном времени (инструментарий Visual Basic For Applications)

Для того, чтобы запрограммировать автоматизацию действий с запросами, Microsoft Access предлагает два варианта:

а. Использование объектов доступа к данным (DAO)

б. Использование объекта-макрокоманды DoCmd.

Первый путь позволяет нам создавать хранимые запросы в базе данных (они будут появляться во вкладке «Запросы») или же работать с их результатами напрямую. Объект DoCmd, а точнее, его метод RunSQL используется исключительно для запуска запросов на изменение данных или управляющих запросов MS Jet.

Покажем общие приемы создания и исполнения запросов с помощью DAO. Для этого будем использовать объект Application.CurrentDB и, для создания/удаление хранимых запросов – коллекцию Queries, в этом объекте хранящуюся. Для работы с данными в реальном времени применим объект Recordset.

За основу возьмем запросы на выборку, показанные в разделе 2 данного реферата.

Option Compare Database

Option Explicit

Public Sub QRY_Example1()

 Dim s As String

 S = “SELECT Количество FROM Склад_гр INNER JOIN “ + _ “Ассортимент ON (Склад_гр.ID_товара = Ассортимент.ID_товара) ”+ _

“ORDER ASCEND BY Наименование;”

 Application.CurrentDb.CreateQueryDef("Query1", s)

End Sub

Такая процедура создаст в базе данных новый запрос с именем Query1 и текстом, хранящимся в переменной S. А следующая – удалит его:

Public Sub QRY_Example2()

 Application.CurrentDb.QueryDefs.Delete("Query1")

End Sub

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

Public Sub QRY_Example3()

 Dim s As String

 Dim rst As New Recordset

 S = “SELECT Количество FROM Склад_гр INNER JOIN “ + _ “Ассортимент ON (Склад_гр.ID_товара = Ассортимент.ID_товара) ”+ _

“ORDER ASCEND BY Наименование;”

 set rst = Application.CurrentDb.OpenRecordset(s, dbOpenDynaset, dbReadOnly)

 rst.MoveFirst

 for I = 1 to rst.RecordCount do

 MsqBox “На складе хранится “ + str(rst.Fields(“Количество”)) +” “ + rst.Fields(“наименование”)

 Rst.MoveNext

 Next

End Sub

По поводу свойств и методов объектов доступа к данным подробные сведения можно получить во встроенной справке VBA.

С объектом DoCmd все намного проще:

Public Sub QRY_Example4()

 Dim s As String

 Dim s2 As String

 Dim rst As New Recordset

 S = InputBox “Введите наименование товара”, “Наша_БД”

 S2 = InputBox “Введите количество этого товара”, “Наша_БД”

 S = “INSERT INTO Товары VALUES “ + _

 “(MAX(ID_Товара)+1, “ + “‘” + s + ”’,” + s2 + “);”

 DoCmd.RunSQL s

End Sub

Эта процедура в запросит в диалоговом окне ввести наименование товара и его количество, а затем добавит эти значения в виде новой записи в таблицу «Товары».

На этом демонстрацию возможностей ядра Microsoft Jet в области выполнения SQL-запросов можно считать оконченной.

9. Выводы

В данном реферате были рассмотрены основные типы запросов, которые поддерживаются Microsoft Access – запросы на выборку, добавление, удаление и обновление записей и управляющие запросы. Также освещена тема динамического формирования и выполнения запросов SQL средствами Visual Basic for Applications.

10. Список литературы:

1.              Microsoft Access 2002 Bible – Cary Prague and Michael Irwin – N.Y.: Hungry Minds, 2002.

2.              Mirosoft Access 2002 Help system © 1991-2002 Microsoft Corp.

3.              Alter table – лекция © RSC-Team 2001-2005 (http://rsc-team.ru/bk/index.pl?rzd=2&group=lection&ind=103)