What is the REAL difference between OpenQuery & the Execute method?

[AnExpertNovice]

The books say "DoCmd.OpenQuery queryname" and "cnn.Execute" statements run a
store action query.

However, the OpenQuery runs the same query we can run manually and the
Execute statement runs some sort of stored query that is not always updated
when the query is changed.

As a result of this change running the query manually or with the OpenQuery
appends to the the table the correct number of records but the Execute
statement appends the number of records before several changes were made.
The database has been decompiled, queries modified then saved and executed.
All to no avail.

 

[Alex Dybenko]

DoCmd.OpenQuery - runs query "through" Access, so you can use references to
form's controls, user defined functions, same like you run query by opening
from database window
Execute method passes execution directly to jet, only limited number of
functions you can use, and you have to replace foms reference by values.
That is why normally second method is faster

 

[AnExpertNovice]

Thanks. The executed query references 2 additional queries, etc. In the
end, 7 queries are involved and 2 tables. 3 of the queries refer to a
specific user function twice. (Records are date ranged and the queries
select based on a selected date, which is returned by the function).

That is probably not important other than your comment about the "limited"
number of functions.

Is there any reason (function limit?) why running the same query using these
two methods:
DoCmd.OpenQuery "aqryTblMkt_SelectedMonth"
CurrentDb.Execute "aqryTblMkt_SelectedMonth"
works. But fails when executed using one of these two methods.
cnn.Execute CommandText:="aqryTblMkt_SelectedMonth", _
RecordsAffected:=vbNullString, _
Options:=adCmdStoredProc Or adExecuteNoRecords

cmd.CommandText = "aqryTblMkt_SelectedMonth"
cmd.Execute

I'm probably going to end up using CurrentDb since it works even though
dbFailOnError parameter can't be used since DAO is not referenced. (nor
will it be referenced for this one line of code.)

Thanks..

 

[Alex Dybenko]

Hi,
yes, I forgot about dbFailOnError. This is advantage of Execute method - you
can catch an error using it.
as for your question - difficult to say without knowing all your queries why
ADO method fails. Perhaps somebody else has better idea on what is the
difference between 2 execute methods. Anyway - DAO method is preferable for
Access

 

[Guest]

'128 = dao.dbFailOnError
codedb.execute "...", 128

AFAIK, ado queries like that can't refer to user defined
functions at all. ?????

Also, depending on how the ADO connection is opened
and the query is created, ADO may be in 'ansi92' mode,
and unable to run some Jet queries.

(david)