MS Access

Как запускать запросы из 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, в которой все значения параметров подставлены в явном виде. Подробнее см. здесь: https://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
    

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

  • Комментарии




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

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