Автор: 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

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

Комментарии

• 04 августа 2010, 07:25 valdemar_ru

спасибо

• 12 апреля 2010, 14:12 Semann

в пункте 2.2 в первом примере из кодаи комментариев к нему следует, что любой запрос , в т.ч. на выборку можно запустить с помощью q.execute. Это неверно!!!!

• 11 июня 2009, 17:30 Dimus73

Спасибо!

• 29 мая 2008, 16:37 sql.sanja

Спасибо!

• 07 августа 2007, 11:45 FatumX

Огромное спасибо! Смог выкинуть тучу ненужного кода, и не натворить глупых ошибок в силу своей зелености в VBA.

• 30 июля 2007, 16:53 MaratDelman

пригодилось, спасибо

• 24 апреля 2007, 19:56 Ussama

RunSql Это актуально для меня

• 20 декабря 2006, 15:12 mikekar

спасибо

• 20 сентября 2006, 20:19 Чайник-777

Как сложить два поля. Это надо делать в таблицах или в запросах.....где писать формулу???

• 22 августа 2006, 02:07 Hotey

Спасибо, как раз то, что надо...

• 31 июля 2006, 05:34 slav1132

В самый раз. Спасибо

• 18 мая 2006, 17:59 Khromykhm

Нашел, что искал - Методы отключения сообщений на подтверждение. Спасибо!

• 22 марта 2006, 08:58 evil-cop

полезно и для общего развития и для конкретных задач

• 03 марта 2006, 15:30 Фыдун

Хорошо, когда знающие растолковывают незнающим...

• 17 февраля 2006, 13:10 fokval

Информация очень полезная!Попробую использовать.

• 25 июля 2005, 17:34 alicom

Огромное спасибо! Пригодилось по поводу отмены уведомления о невозможности вставки записей в таблицу из-за нарушения ключа

• 11 мая 2005, 13:27 as_is

пригодилось, спасибо

• 02 мая 2005, 17:21 Димон

Наконец понял что такое транзакции

• 23 марта 2005, 18:19 clerk

Оччень кстати

Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: MS Access / Как запускать запросы из VBA