Особенности работы с хранимыми процедурами в 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

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

End Sub

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

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