Как запускать запросы из VBA

Опубликовано: 28 янв 05
Рейтинг:

Добавить в закладки и поделиться

Оценить и прокомменитировать
Распечатать статью

Автор: Sfagnum, участники форума по Аксессу
Прислал: Владимир Саныч

1. Вопросы.
1.1. Как отключить подтверждения?
1.2. Execute либо OpenRecordset выдаёт ошибку "слишком мало параметров", что делать?
1.3. Как правильно использовать DoCmd.RunSQL?
1.4. Почему нельзя использовать конструкцию Application.SetOption?

2. Ответы.
Прежде чем перейти к ответам, надо отметить, что запрос будет выполнять непосредственно Jet и что DoCmd.RunSQL (Access), CurrentDb.Execute (DAO), Connection.Execute (ADO) являются не более чем интерфейсами к Jet.

2.1. Методы отключения сообщений на подтверждение при запуске Action Queries.
Подтверждения отключаются следующими способами (ответы перечислены в порядке применимости):

Перейти на использование конструкции CurrentDb.Execute

Использовать вместе с DoCmd.RunSQL, DoCmd.SetWarnings False

Изменить глобальные настройки с помощью конструкции

Application.SetOption "Confirm Record Changes", False

Application.SetOption "Confirm Document Deletions", False

Application.SetOption "Confirm Action Queries", False

и т.п.

NB!!! (Категорически НЕ рекомендуется).

2.2 Устранение ошибок в CurrentDb.Execute и в CurrentDb.OpenRecordset
Ошибка, как правило, возникает со следующим текстом "Too few parameters. Expected Число" ("Слишком мало параметров. Ожидалось Число").
Эта ошибка возникает, если команда или один из нижележащих запросов содержит обращения к формам или собственные параметры, - все эти обращения будут восприняты как параметры, которым не передано значение.
Почему так происходит? - Вот вольный перевод из MSDN (ms-help://MS.MSDNQTR.2003APR.1033/enu_kbacc2000kb/acc2000kb/209203.htm ):

MSDN

NOTE: В DAO Вы должны явно присвоить значение параметру. При использовании DoCmd.OpenQuery Вы этого делать не должны, т.к. DAO использует операции низкого уровня, что даёт Вам большую свободу в использовании параметров (т.е. Вы можете сами присвоить параметру значение переменной, а не использовать ссылку на форму), но Вы должны выполнить служебные действия, которые Аксесс делает "за кулисами" при исполнении DoCmd. С другой стороны, DoCmd работает на более высоком уровне, чем DAO. Выполняя DoCmd, Microsoft Access делает некоторые предположения о том, как поступить с параметрами, и не дает Вам никакой свободы в этом отношении.

Если все параметры являются ссылками на контролы форм (Forms![ИмяФормы]![ИмяКонтрола]), тогда самое простое (и красивое) решение:

Dim q As DAO.QueryDef, p As DAO.Parameter

Set q = CurrentDb.QueryDefs("ИмяЗапроса") 'как обычного запроса Select,

'так и INSERT/UPDATE; в запросах на удаление это не помогает

For Each p In q.Parameters

p.Value = Eval(p.Name)

Next

q.Execute

q.close: Set q=Nothing

Если вместо обращений к формам Вы используете собственные параметры (например, [Введите начальную дату:]), тогда Вам нужно задать параметры вручную:

Dim q As DAO.QueryDef

Set q = CurrentDb.QueryDefs("ИмяЗапроса")

q.Parameters("[Введите начальную дату:]").Value=Cdate(Ваше_значение_параметра)

'и т.д. пока не переберете все параметры

q.Execute

q.close: set q=Nothing

Узнать, какие параметры от Вас хочет Аксесс, можно с помощью следующего кода:

Dim q As DAO.QueryDef, p As DAO.Parameter

Set q = CurrentDb.QueryDefs("ИмяЗапроса")

For Each p In q.Parameters

debug.print p.Name

Next

q.close: set q=Nothing

Альтернативой может быть использование функций, которые будут брать значения либо напрямую из нужного контрола, либо из переменной. (Такая функция должна быть описана в стандартном модуле. В модуле формы можно пользоваться свойством без параметров, функцией без параметров либо публичной переменной, но на них надо ссылаться через форму и без скобок в конце.) Использование данного метода позволяет более широко контролировать подставляемое значение, а также позволяет избавиться от квадратных скобок, что иногда бывает критично. Пример: в модуле пишем функцию, которая получает значение из поля формы:

Function Rep_BegDate()

Rep_BegDate = Forms!frmRep!txtBegDate

End Function

и тогда в запросах и отчетах вместо

WHERE ... = Forms!frmRep!txtBegDate

пишем

WHERE ... = Rep_BegDate()

Обратите внимание на Rep_BegDate() со скобками.

Еще одной альтернативой является программное формирование команды SQL, в которой все значения параметров подставлены в явном виде. Подробнее см. здесь: http://www.sql.ru/faq/faq_topic.aspx?fid=157

Если аналогичное явление происходит не при CurrentDb.Execute, а при CurrentDb.OpenRecordset, то годятся все те же решения. При этом если параметры задаются вручную, то открывать рекордсет надо так:

Set rs = q.OpenRecordset(...)

'а не Set rs = CurrentDb.OpenRecordset("ИмяЗапроса"), ...)

В перекрестных запросах возникает то же явление. Правда, оно возникает не при попытке запуска (ибо перекрестный запрос не является Action Query), а при любом использовании, например при открывании формы, основанной на таком запросе. В этом случае надо действовать следующим образом:

PARAMETERS [Forms]![ИмяФормы]![ИмяКонтрола] Text, ... ;

...

WHERE ИмяТаблицы.ИмяПоля=[Forms]![ИмяФормы]![ИмяКонтрола] ...

...

Примечание. Везде в примерах, где сказано "ИмяЗапроса", имеется в виду не обязательно имя сохраненного запроса, но также и команда SQL, которая нигде не сохранена.

2.3 Устранение ошибок в Command.Execute

Если все параметры являются ссылками на контролы форм (Forms![ИмяФормы]![ИмяКонтрола]), тогда самое простое (и красивое) решение:

Dim cmd As ADODB.Command

Dim cnn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim p As ADODB.Parameter

Set cnn = CurrentProject.Connection

'или если не используете текущее соединение:

'Set cnn = New adodb.Connection

'cnn.Open "Cтрока подключения по аналогии с CurrentProject.BaseConnectionString"

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = cnn

.CommandText = "ИмяПроцедуры"

.CommandType = adCmdStoredProc

.NamedParameters = True

'начиная с ADO версии 2.6 (???) без этого свойства работать не будет -

'ADO будет заполнять коллекцию параметров не по имени, а по порядку

For Each p In .Parameters

'имена параметров равны именам контролов формы, только без @

'Имя контрола NameFIO, тогда в процедуре @NameFIO. @- убираем с помощью Replace

p.Value = Eval(Forms("ИмяФормы")(Replace(p.Name, "@", "", 1, 1, vbTextCompare)))

Next

Set rs = .Execute(, , adExecuteNoRecords)

'adExecuteNoRecords - можно и не указывать, если в процедуре стоит Set NoCount ON

'или чтобы иметь возможность задать свойства rs (обновляемость, например):

'Set rs = New ADODB.Recordset

'rs.CursorType = adOpenDynamic

'rs.Open cmd

End With

Set cmd = Nothing

rs.Close: Set rs = Nothing

cnn.Close: Set cnn = Nothing

Если вместо обращений к формам Вы используете собственные параметры, тогда Вам нужно задать параметры вручную:

Dim cmd As ADODB.Command

Dim cnn As ADODB.Connection

Dim rs As ADODB.Recordset

Set cnn = CurrentProject.Connection

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = cnn

.CommandText = "ИмяПроцедуры"

.CommandType = adCmdStoredProc

.NamedParameters = True

'Cоздадим параметр nVarChar (500)

.Parameters.Append .CreateParameter("@имяПарматра1", adVarWChar, adParamInput, 500, "Некая строка")

'создадим параметр типа int (SQL) - Long (VBA)

.Parameters.Append .CreateParameter("@имяПараметра2", adInteger, adParamInput, , CLng(111))

'и т.д.

Set rs = .Execute(, , adExecuteNoRecords)

End With

Set cmd = Nothing

rs.Close: Set rs = Nothing

cnn.Close: Set cnn = Nothing

Узнать, какие параметры от Вас хочет Аксесс, можно с помощью следующего кода:

Dim cmd As ADODB.Command

Dim cnn As ADODB.Connection

Dim p As ADODB.Parameter

Set cnn = CurrentProject.Connection

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = cnn

.CommandText = "ИмяПроцедуры"

.CommandType = adCmdStoredProc

For Each p In .Parameters

Debug.Print p.Name

Next

End With

Set cmd = Nothing

cnn.Close: Set cnn = Nothing

2.4. Использование DoCmd.RunSQL.
Иногда советуют поставить DoCmd.SetWarnings False перед DoCmd.RunSQL, но это крайне опасно. Это требует добавить DoCmd.SetWarnings True где только можно, особенно в обработчиках ошибок. В противном случае Аксесс в какой-нибудь момент вообще перестанет выдавать предупреждения на удаление и т.п. (в том числе и предупреждения об ошибках) до конца работы программы. Также есть возможность, что в отладочный период Вы остановите выполнение кода ДО включения сообщений, что тоже повлечёт за собой выше описанный результат. Но если Вы решили использовать данную конструкцию, то используйте её следующим образом.

On Error Goto mis

...

DoCmd.SetWarnings False

DoCmd.RunSQL ...

DoCmd.SetWarnings True

...

Exit Sub

mis:

DoCmd.SetWarnings True

2.5. Противопоказания к использованию конструкции Application.SetOption
Подтверждения исчезнут во всей аппликации насквозь, в том числе там, где это не планировалось. Это можно делать только в том случае, если соблюдены два условия:

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

Подтверждения не отключаются, если в приложении работает сам разработчик.

2.6. Возможность отката
Нередко возникает необходимость при возникновении ошибок отменить результат исполнения целого ряда запросов. DoCmd.RunSQL такой возможности не дает. CurrentDb.Execute, в отличие от него, позволяет откатить транзакцию, в которую включено то, что посчитает нужным программист. В CurrentDb.Execute есть опция dbFailOnError - она относится только к выполнению конкретного запроса, т.е. отвечает только за то, будет ли запрос при ошибке откачен целиком и будет возвращена ошибка, или же возможно частичное выполнение запроса.
Пример отката внешней транзакции:

Public Function Test()

On Error Goto Rollback_Label

DBEngine(0).BeginTrans

CurrentDb.Execute "Запрос1", dbFailOnError

CurrentDb.Execute "Запрос2", dbFailOnError

CurrentDb.Execute "Запрос3", dbFailOnError

DBEngine(0).CommitTrans

Exit_Label:

Exit Function

Rollback_Label:

DBEngine(0).Rollback

Resume Exit_Label

End Function

В данном случае если не выполнится хотя бы один запрос, то все запросы "откатятся".