Объекты ADO (продолжение)

Добавление полей и ключа

В следующей процедуре в уже созданную таблицу добавляется новое поле и новый составной ключ. Приведу текст процедуры:

Public Sub InsertItems()
	'Эта процедура добавляет элементы в уже созданную таблицу
	Dim myT As Table
	Dim myC As New Column
	Dim myK As New Key
	Dim myInd As Index
	'Установить соединение с базой NewDB
	CreateConnection
	Cat1.ActiveConnection = Con1
	'Связывание объекта Table с таблицей "Книги"
	Set myT = Cat1.Tables("Книги")
	'Добавление поля
	myC.ParentCatalog = Cat1
	myC.Name = "Художник"
	myC.Attributes = adColNullable
	Call myT.Columns.Append(myC)
	
	'Добавление составного ключа
	myK.Columns.Append (myT.Columns("Автор"))
	myK.Columns.Append (myT.Columns("Название_книги"))
	myK.RelatedTable = "myT"
	myK.Type = adKeyUnique
	myK.Name = "AandB"
	Call myT.Keys.Append(myK)
End Sub

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

увеличить изображение
Рис. 6.10. Изменения полей и индексов таблицы "Книги"

Модификация характеристик

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

Public Sub Modify()
	'Изменение характеристик таблицы в Access невозможно!
	Dim myT As Table
	Dim myC As Column
	Dim myK As Key
	Dim myInd As Index
	'Установить соединение с базой NewDB
	CreateConnection
	Cat1.ActiveConnection = Con1
	'Связывание объекта Table с таблицей "Книги"
	Set myT = Cat1.Tables("Книги")
	Set myC = myT.Columns("Название_книги")
	'myC.DefinedSize = 150
	Set myC = myT.Columns("Число_страниц")
	'myC.Type = adVarWChar
	
	Set myK = myT.Keys("Код_книги")
	'myK.Type = adKeyUnique
	Set myK = myT.Keys("AandB")
	'myK.Type = adKeyPrimary
End Sub

Как видите, все операторы, производящие изменения закомментированы, поскольку выполнение любого из них приводит к появлению ошибки. Хотя модификация характеристик полей, ключей и индексов таблицы базы данных Access невозможна, удаление самих элементов происходит без затруднений. Это позволяет при необходимости внесения изменений в характеристики элемента, вначале удалить его, а затем ввести заново с новыми характеристиками. Приведу процедуру, выполняющую удаление поля, ключа и индекса:

Public Sub Removing()
	'Удаление элементов таблицы
	Dim myT As Table
	'Установить соединение с базой NewDB
	CreateConnection
	Cat1.ActiveConnection = Con1
	'Связывание объекта Table с таблицей "Книги"
	Set myT = Cat1.Tables("Книги")
	myT.Columns.Delete ("Художник")
	myT.Indexes.Delete ("AandB")
	'myT.Keys.Delete ("AandB")
End Sub

Заметьте, когда я вводил ключ "AandB", то был создан индекс с этим же именем. Удалив этот индекс, я автоматически удалили и ключ. По этой причине последний оператор процедуры закомментирован.

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

Объект Procedure и коллекция Procedures

Объект Procedure представляет собой хранимую процедуру. Хранимые процедуры, текст которых, чаще всего, пишется на SQL, хранятся на сервере в оттранслированном виде, что ускоряет их выполнение. Еще одно немаловажное достоинство состоит в том, что облегчается задача программиста, вызывающего хранимую процедуру, когда он пишет свой код на VBA или VBScript, поскольку ему не нужно вникать в тонкости операторов SQL.

Коллекция Procedures объекта Catalog включает в себя все хранимые процедуры базы данных. Как и у всех коллекций ADOX у нее три метода: Append, Delete, Refresh и два свойства - Item и Count. Тем не менее, есть особенность в создании объекта Procedure и добавлении его в коллекцию. Прежняя схема, используемая при создании объектов Table, Key, Index для него не применима. Этот объект не может быть создан с помощью конструктора New. Для того чтобы добавить в коллекцию новый объект Procedure, следует создать объект Command, задать текст процедуры, используя свойство CommandText, а затем применить метод Append коллекции Procedures. Заметьте, у этой коллекции метод Append имеет два параметра, - первый задает имя хранимой процедуры, второй объект Command, определяющий, по существу, саму процедуру. Используя объекты Procedure, Procedures и Command можно программно создать хранимую процедуру, модифицировать существующую процедуру или удалить процедуру из коллекции.

Объект Procedure устроен очень просто, - у него всего 4 свойства:

• Property Name As String. Задает имя объекта. Имеет статус "только для чтения", что объясняется особенностями создания этого объекта.
• Property DateCreated As Variant, Property DateModified As Variant. Эти два свойства, также имеющие статус "только для чтения", задают дату создания и последней модификации процедуры.
• Property Command As Variant. Задает объект Command из библиотеки ADODB. В предыдущей главе этот объект был достаточно подробно описан и приведено большое число примеров работы с ним. Свойство является центральным, поскольку именно объект Command определяет суть хранимой процедуры и позволяет создать процедуру.

Особенности работы с хранимыми процедурами в Access

В базе данных Access нет понятия хранимых процедур, их роль играют хранимые запросы. Было бы хорошо, если бы Провайдер Microsoft Jet при создании объектов коллекции Procedures создавал вместо этого хранимые запросы. Однако этого не происходит, - запросы не создаются. Поэтому, казалось бы, при работе с базой данных Access создавать программно процедуры невозможно и, главное, бесполезно. Тем не менее, при попытке создать коллекцию Procedures никаких ошибок не возникает и, по крайней мере, одна процедура создается и с ней можно впоследствии работать, вызывая ее на исполнение.

Я приведу сейчас пример, в котором демонстрируется сам способ создания коллекции Procedures. Этот способ не зависит от Провайдера и базы данных, он может с успехом использоваться при работе с Access MSDE или, например, с Microsoft SQL Server. Но, главная суть примера в том, чтобы показать недокументированную возможность программной работы с хранимой процедурой в Access. Как всегда, приведу вначале текст процедуры:

Public Sub CreateQuery()
	'Создание хранимых процедур
	Dim myCmd1 As New Command, myCmd2 As New Command
	'Установить соединение с базой NewDB
	CreateConnection
	Cat1.ActiveConnection = Con1
	'SQL-запросы, представляющие тексты хранимых процедур
	myCmd1.CommandText = "Select [Книги.Автор],[Книги.Название_книги]" & _
		"From [Книги]" & _
		"WHERE ((([Книги.Год_издания])= 1999))" & _
		"ORDER BY [Книги.Автор];"
	'Запрос с параметром
	myCmd2.CommandText = "Parameters [Avtor] Text(50);" & _
	"Select [Книги.Автор],[Книги.Название_книги]" & _
		"From [Книги]" & _
		"WHERE ((([Книги.Автор])= [Avtor]));"
	'Удаление процедур
	Dim i As Integer
	For i = 0 To Cat1.Procedures.Count - 1
		Cat1.Procedures.Delete (i)
	Next i
	'Добавление процедур
	Debug.Print Cat1.Procedures.Count
	Call Cat1.Procedures.Append("Books1999", myCmd1)
	Debug.Print Cat1.Procedures.Count
	Call Cat1.Procedures.Append("BooksOfAuthor", myCmd2)
	Debug.Print Cat1.Procedures.Count
	
End Sub

Сама процедура и то, как она выполняется, нуждается в подробных комментариях:

• Поскольку целью работы, которую я поставил перед собой, являлось создание двух хранимых процедур, то в процедуре создаются два объекта Command.
• Свойство CommandText этих объектов позволяет задать текст команд, представляющих в данном случае SQL-запросы. Второй из этих запросов представляет запрос с параметром.
• Успешно выполняется метод Append коллекции Procedures, которому в качестве одного из параметров передаются созданные объекты Command. По завершении работы процедуры создается впечатление, что коллекция Procedures с двумя элементами успешно создана. Однако это не совсем так.
• Прежде всего, заметьте, что никакие запросы в базе данных Access не появляются. Коллекция процедур действительно создается, но в ней присутствует только один элемент. При добавлении нового элемента он записывается на место ранее существовавшего. Так что свойство Count в конце работы этой процедуры будет возвращать число 1, а сама коллекция будет хранить второй параметрический запрос.
• Досадной ошибкой, усугубляющей ситуацию, является то, что имена записываемых процедур сохраняются полностью, не забивая друг друга. По этой причине при повторном запуске процедуры, несмотря на то, что все процедуры из коллекции удаляются перед их созданием, возникнет ошибка с выдачей сообщения о том, что есть хранимая процедура с именем " Books1999 ", хотя коллекция и пуста.

Не следует особенно расстраиваться, что хранимые процедуры не работают корректно в Access, - они и не должны работать. Можно, однако, использовать тот факт, что с одной процедурой все-таки работать разрешается. Вот пример работы, демонстрирующий работу с сохраненной в предыдущем примере процедурой с именем BooksOfAuthor, имеющей имя автора в качестве параметра запроса:

Public Sub WorkWithProcs()
	'работа с хранимыми процедурами
	 Dim myCmd As Command
	'Установить соединение с базой NewDB
	CreateConnection
	Cat1.ActiveConnection = Con1
	Set myCmd = Cat1.Procedures("BooksOfAuthor").Command
	Set Rst1 = myCmd.Execute(, "Пушкин")
	With Rst1
		.MoveFirst
		Do While Not .EOF
			Debug.Print .Fields(0), .Fields(1)
			.MoveNext
		Loop
	End With
End Sub

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