DoCmd.RunSQL versus Executing a QueryDef

[JimP]

I have a choice between the 2 for an append query and I don't need to save
the QueryDef. Is one preferred over the other, or offer a performance
advantage?

 

[Douglas J. Steele]

Actually, you have a third option: you can use the Execute method of the
Database itself, rather than creating a QueryDef and using its Execute
method.

I much prefer using the Execute method because it allows you to specify
dbFailOnError as an argument so that trappable error messages are generated
if there's a problem running the query. As well, it doesn't present the
annoying "You are about to append n records..." message (yes, I know you can
turn that off using SetWarnings).

So in order of preference I'd say:

1. Execute method of Database object
2. Execute method of QueryDef object
3. DoCmd.RunSQL

 

[Roger Carlson]

In addition, I did a test a few years ago, and both the Execute method of
Database object and the Execute method of QueryDef object were MUCH faster
than DoCmd.RunSQL.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[JimP]

My experience with QueryDefs is similar to below.

Dim qdf As QueryDef
strSQL="....................."
qdf=CurrentDb.CreateQueryDef("", strSQL)
qdf.execute dbFailOnError

How is the execute method of a database object different?

 

[Douglas J. Steele]

strSQL="....................."
CurrentDb.Execute strSQL, dbFailOnError