Dynamic SQL and temporary QueryDefs

[deko]

I came across this MSDN article
http://msdn2.microsoft.com/en-us/library/bb177500.aspx and was wondering if
anyone has had any luck with it:

Dim db as DAO.Database
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
strSql = [dynamically created SQL]
qdf.SQL = strSql
qdf.Execute dbFailOnError
Set qdf = Nothing

Is this a better alternative to:

strSql = [dynamically created SQL]
db.Execute strSql, dbFailOnError

Either way, Access has to compile the query before running it, correct?

Any thoughts?

 

[Dirk Goldgar]

I came across this MSDN article
http://msdn2.microsoft.com/en-us/library/bb177500.aspx and was wondering if
anyone has had any luck with it:

Dim db as DAO.Database
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
strSql = [dynamically created SQL]
qdf.SQL = strSql
qdf.Execute dbFailOnError
Set qdf = Nothing

Is this a better alternative to:

strSql = [dynamically created SQL]
db.Execute strSql, dbFailOnError

Either way, Access has to compile the query before running it, correct?

Any thoughts?

I benchmarked this with a SELECT query, and found that

Set rs = db.OpenRecordset(strSQL)

was marginally faster than

Set qdf = db.CreateQueryDef("", strSQL)
Set rs = qdf.OpenRecordset

The difference was only detectable over hundreds of iterations, so it
doesn't make much difference, but I don't see any reason to prefer the more
complex version that requires creating and disposing of a QueryDef.

 

[deko]

I benchmarked this with a SELECT query, and found that

Set rs = db.OpenRecordset(strSQL)

was marginally faster than

Set qdf = db.CreateQueryDef("", strSQL)
Set rs = qdf.OpenRecordset

The difference was only detectable over hundreds of iterations, so it
doesn't make much difference, but I don't see any reason to prefer the
more complex version that requires creating and disposing of a QueryDef.

I think that proves that a temp qdf has to be compiled - so what advantage
is there? Apparently not speed.

In particular, I use a lot of ad-hoc (dynamic SQL) Update and Append
queries. I'm wondering if best practice is to assign the SQL to temp qdf, a
saved qdf, or just use 'db.Execute strSql'. When I have a similar need for
a Select query, I usually assign that to a saved qdf (qdf.SQL = strSql)
because it's normally used elsewhere as RecordSource or RowSource or
something. I know some who would frown on executing a SQL string
(db.Execute strSql), but desktop mdbs are not generally targets for cross
site scripting.