Light-electric.com

IT Журнал
33 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Access docmd runsql

Выполнение SQL запросов к текущей базе данных в среде VBA (Access)

В Microsoft Office Access поддерживается выполнение SQL запросов к текущей базе данных из программного кода на VBA. Рассмотрим, как это можно использовать при разработке приложений баз данных.

Так как запрос выполняется к той же самой базе данный в которой выполняется модуль VBA, для выполнения SQL запроса нет необходимости работать со стандартными в подобных случаях интерфейсами ADO, ODBC и т.д. Всё можно сделать при помощи объектной модели Access.

Как известно SQL запросы можно условно разделить на две большие группы:

  • Запросы, не возвращающие данных (INSERT, UPDATE, DELETE и т.д.);
  • Запросы возвращающие данные (запросы на выборку (SELECT)).
Запросы, не возвращающие данных

Для выполнения запросов к текущей базе данных служит метод Execute объекта CurrentDb. Этот метод принимает в качестве параметра строку с SQL запросом, который необходимо выполнить.

Это простейший запрос. Но, как быть с запросами, которые манипулируют теми или иными данными?

Такие запросы тоже можно выполнить. Для этого нужно просто сформировать соответствующую строку.

Так, например, может быть реализовано добавление записей в таблицу:

А, так их обновление:

Выполнение другие типов запросов не возвращающих данных осуществляется по аналогичным принципам.

Запросы на выборку

Метод Execute объекта CurrentDb не поддерживает выполнение запросов на выборку. Поэтому для них нужен другой подход.

Чтобы работать с выборкой данных необходимо воспользоваться объектом RecordSet, который можно создать при помощи метода OpenRecordSet объекта CurrentDb. Этот метод также принимает в качестве параметра строку с SQL запросом.

После создания RecordSet мы можем перебрать все записи, как это показано ниже.

Помимо простого перебора доступны несколько видов поиска. Но в основном используются два.

Первый вид поиска реализован в виде методов FindFirst, FindLast, FindNext и FindPrevious объекта RecordSet.

Первые два устанавливают курсор на соответственно первую и последнюю записи, соответствующие условию поиска. Условие поиска передаётся в качестве параметра в виде строки. Само условие задаётся по аналогии с предложением WHERE в обычном SQL запросе.

Методы FindNext и FindPrevious устанавливают курсор на следующую и предыдущую записи в соответствии с условием поиска, если такие записи существуют. Условие поиска для этих методов задаётся также, как и для FindFirst и FindLast.

Обратите внимание, что эти четыре метода именно устанавливают указатель на нужную запись, а не производят фильтрацию выборки. Таким образом количество записей выбранных SQL запросом остаётся неизменным.

В следующем примере перебор записей начнётся с записи, у которой id равен 234, так как курсор был перемещён к ней до начала обхода набора записей в цикле.

Access docmd runsql

Question

Hi, i have a block of codes that works when use Docmd.RunSQL(sql).

however, when i changed it to db.Execute(sql) , it fails.

i keep recieving the error «Runtime error 3061», too few parameters.

My code is as follows

kindly advise. the reason why i am switching to db.Execute from Docmd.runSQL is because i want to suppress the warning pop up whenever a change is made to the database tables.

thanks in advance for any help provided.

Answers

RunSQL automatically attempts to evaluate and fill in values for parameters in the SQL string — and references to form controls count as parameters. Execute doesn’t do that, so you have to either build the values into the string, or else take extra steps to resolve the parameters.

An example of building the paramers directly into the SQL string would be this:

An example of taking extra steps to get Access to resolve the parameters would be this:

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

  • Marked as answer by aaronkoh Wednesday, March 27, 2013 1:42 PM

All replies

RunSQL automatically attempts to evaluate and fill in values for parameters in the SQL string — and references to form controls count as parameters. Execute doesn’t do that, so you have to either build the values into the string, or else take extra steps to resolve the parameters.

An example of building the paramers directly into the SQL string would be this:

An example of taking extra steps to get Access to resolve the parameters would be this:

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

  • Marked as answer by aaronkoh Wednesday, March 27, 2013 1:42 PM

Forget about .RunSQL method. Why does it exist at all? I think just to support the same macro action.

First of all, how did you try .Execute method? It should be

Secondly, maybe it is not the whole code you provided above, but I don’t see why you need a recordset.

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Oh, all these «VALUE» things are form controls? I thought they were .Value properties of multi-valued fields. That’s why it’s better to use Me.MyControl.Value, at least, for overall understanding of what the things are.

Dirk, thanks for the tip about parameters.

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

I don’t think it would work with that syntax. The command wouldn’t know where are the values are coming from.

Try a different approach. Something like.

INSERT INTO personnel SELECT [whateverColumns] FROM [whateverThatTableName].

Читать еще:  Функция dlookup в access

If you got the criteria just use the WHERE clause.

This is some of the currentdb.execute that doesn’t work.

You could use the RunSQL with «Docmd.SetWarnings = False» to disable the warning message and set it back with «Docmd.Setwarnings = True.

Oh, all these «VALUE» things are form controls? I thought they were .Value properties of multi-valued fields. That’s why it’s better to use Me.MyControl.Value, at least, for overall understanding of what the things are.

Dirk, thanks for the tip about parameters.

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

The way I see it is that the string syntax in this way. (although I guess/knew that OP might refer to a control)

Example. «NRICTB.Value». This refers to «MyTableName.MyColumnName» in the Action Query. Since all the input values are of different source, that is to say more than one Tables. But of course, it will never work.

Not sure why he claims that the insert query works in RunSQL.

Anyway, «Value» is a reserved word in Access. Should be avoided.

Access docmd runsql

Как запускать запросы из 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 ):

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

q.close: Set q= Nothing

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

Dim q As DAO.QueryDef

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

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

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

q.close: set q= Nothing

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

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

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

For Each p In q.Parameters

q.close: set q= Nothing

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

Rep_BegDate = Forms ! frmRep ! txtBegDate

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

WHERE . = Forms ! frmRep ! txtBegDate

Обратите внимание на 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 , . ;

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

Читать еще:  Access примеры кода

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

‘начиная с ADO версии 2.6 (. ) без этого свойства работать не будет —

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

For Each p In .Parameters

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

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

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

Set rs = .Execute(, , adExecuteNoRecords)

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

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

‘Set rs = New ADODB.Recordset

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

‘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)

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

For Each p In .Parameters

Set cmd = Nothing

cnn.Close: Set cnn = Nothing

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

On Error Goto mis

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

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

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

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

Public Function Test()

On Error Goto Rollback_Label

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

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

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

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

Run Microsoft Access Action Queries in VBA and Hide Warnings without Using DoCmd.SetWarnings

Provided by: Molly Pell, Senior Systems Analyst

Objective

Use the VBA DAO Database Execute command, rather than DoCmd.RunSQL and DoCmd.OpenQuery, to run action queries without warning messages and avoid changing the Access SetWarnings status.

Background

Action queries in Microsoft Access are queries that add, modify, or delete records from tables using INSERT, UPDATE, and DELETE commands (or create a new table via MAKE TABLE). When you run Action queries interactively, Access displays two messages. The first is a warning, which asks you to confirm:

The second displays the number of rows that will be affected:

When running Action queries in VBA code, you generally do not want to display these messages, since they require a user response before the code can continue.

VBA offers the following methods for executing Action queries in code:

  • RunSQL
    Executes a SQL statement, not a saved query. Shows messages that require user interaction.
  • OpenQuery
    Runs a saved query, not a SQL statement. Shows messages that require user interaction.
  • Execute
    Executes either a SQL statement or a saved query. Does not show messages that require user interaction. Allows you to return the # of rows affected.

One may assume all three are equivalent, but the first two should be avoided.

Problems with RunSQL and OpenQuery Commands

RunSQL and OpenQuery are the simplest to invoke, but they display the previously mentioned warnings, which require user interaction.

One option for suppressing the messages is to SetWarnings False before running the queries, then SetWarnings True after:

This is not advisable for the following reasons:

Читать еще:  Visual c access

Problem 1: Crash Could Leave Warnings Off

When you turn off system warning messages, you need to be very careful to turn them back on in case of an unexpected error or crash. Turning warnings back on in your error handler helps the situation, but does not handle cases if MS Access terminates unexpectedly. The result would leave Access in a condition where normal warning messages unrelated to queries are suppressed. This could let users accidentally delete objects without confirmation, close objects and modules without saving changes, etc.

Problem 2: Number of Affected Rows

Your application and your users have no way of knowing how many rows were affected by the query. That information may be useful if you’re expecting rows to be affected, but a data error causes no rows to be affected.

Problem 3: Impacts Warning Setting

We’re assuming the user has warnings turned on. If the user wants warnings off (which, of course, is not advisable), we’ll annoy them by turning the back on. Extra code is required to check the user’s preference before invoking SetWarnings.

For these reasons, it’s best to avoid using the SetWarnings command at all. Fortunately, we have an alternative.

Solution: Use the Execute Method

As you can see, while RunSQL and OpenQuery start off simple, they end up causing you a lot of extra work. The OpenQuery command is really designed for displaying a SELECT or CROSSTAB query for the user to browse. For Action queries, use the Execute method to run saved queries and SQL strings:

DoCmd.RunSQL will not run

DoCmd.RunSQL will not run

DoCmd.RunSQL will not run

I am trying to create a simple SQL command in a module.

The SQL string passes to the SQL following the DoCmd.RunSQL, but my code stops at the line DoCmd.RunSQL SQL

I get no error. It just doesn’t run. What could be wrong?

CODE —> code

RE: DoCmd.RunSQL will not run

There is a great need for a sarcasm font.

RE: DoCmd.RunSQL will not run

The code I was originally trying to run was an UPDATE query, but my code stops on the SQL= statement.

I don’t see what is wrong with either this statement or the SELECT statement above.

Any help would be appreciated.

CODE —> code

RE: DoCmd.RunSQL will not run

Thank you for replying Andrzejek.

I am expecting the code to run an update to the field «FullPath» in my table tblRunsheet, i.e.
to run an update on tblRunsheet.FullPath where FullPath field is null.

It works from an update query created in design view. That’s where I got the SQL statement from.

My code stops on the SQL= line. So, I tried running a simple SELECT * FROM tblRunsheet. That didn’t work either.

RE: DoCmd.RunSQL will not run

CODE —> vba

RE: DoCmd.RunSQL will not run

This source of this code come straight out of Microsoft’s Dev Center.

RE: DoCmd.RunSQL will not run

Thank you dhookom!

That did something. When it first runs, it opens as a parameter query. Even in I put something in as a parameter, I get the error message that the field is not updatable.

I can update from the query created in the query grid.

RE: DoCmd.RunSQL will not run

The source you mentioned states it’s for «action queries» and also uses the single quotes like I provided. The article could have been more explicit regarding the quotes since the mistake is very common.

What is the parameter prompt?

RE: DoCmd.RunSQL will not run

I had an typo in the name of the field. Adding the correct quotes got the code to run.
Another «DUH» for me! Kudos to you!

THANK YOU again for coming to my rescue. This path to coding is indeed a challenge for the challenged. 🙂

RE: DoCmd.RunSQL will not run

As a side note, I would recommend this:

There is a great need for a sarcasm font.

RE: DoCmd.RunSQL will not run

RE: DoCmd.RunSQL will not run

«got the Quotes right» — you may try this ‘trick’ :

There is a great need for a sarcasm font.

RE: DoCmd.RunSQL will not run

Thanks Andy for the additional tips. I have be creating database apps for decades and still include «debug.print» in my code to find my frequent errors.

I believe Path, Vol and Pg are fields in the table so this statement will probably result in an error:

CODE —> vba

RE: DoCmd.RunSQL will not run

RE: DoCmd.RunSQL will not run

Duane,
You are right — if «Path, Vol and Pg are fields in the table so this statement will probably result in an error:»

But, by the same token, I would not keep the calculated field (like FullPath ) in my table.
I would create a query where I would concatenate Path, Vol and Pg and show FullPath in this query.

As for Debug.Print — too much work (code)
I rather put a break point and do in the Immediate Window:
? SQL

There is a great need for a sarcasm font.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector