Why use QryDef?

[Jim Shaw]

BlankIn another post I saw this code with the SQL SELECT statement in the OpenRecordset.

Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT * FROM MyTable")

If this works (which I assume it does), why would one use the dynamic QueryDef facility for unnamed queries?

-Jim

 

[Scott McDaniel]

BlankIt's a matter of programming style. Personally, I prefer the approach
you mention as it avoids the overhead associated with opening objects
unnecessarily. I rarely open a QueryDef object. Other opinions may vary.

Oh, and use Plain Text when posting to newsgroups. Lots of folks can't (or
won't) read HTML posts.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

In another post I saw this code with the SQL SELECT statement in the
OpenRecordset.

Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT * FROM MyTable")

If this works (which I assume it does), why would one use the dynamic
QueryDef facility for unnamed queries?

-Jim

 

[JohnFol]

They are pre-compiled and therefore execute minutely faster. Also, you can
set permissions to a querydef unlike dynamic SQL as per your example.

 

[Scott McDaniel]

Yes, the security issue could come into play. Thanks for pointing that out.

 

[Douglas J. Steele]

The security is true, but I don't believe that the query gets pre-compiled
until the first time you run it, therefore it's unlikely that there would be
any performance difference.

 

[JohnFol]

I can't find the source of that info, but happy to take your lead. Perhaps I
was thinking it is syntatically checked at the point of saving the QD,
whereas a dynamic one needs to be checked .. . .

 

[JohnFol]

Found the reference
ms-help://MS.MSDNQTR.2003JUL.1033/vccore/html/_core_DAO_Querydef.3a_.Using_Q
uerydefs.htm

Saving a Querydef
A saved querydef persists in its database (.MDB only), stored there along
with the database's tables and data. You can think of a saved query as a
compiled SQL statement - when you run the query, it executes faster than a
standard new query because the database engine doesn't have to compile the
SQL statement before executing it.

 

[Douglas J. Steele]

That's only true, though, if you're saving the query and reusing it.

The original poster specifically asked "why would one use the dynamic
QueryDef facility for unnamed queries?", which I assume to mean

Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim qdfCurr As QueryDef
Dim strSQL As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.CreateQueryDef("", "SELECT * FROM MyTable")
Set rsCurr = qdfCurr.OpenRecordset()

In this case, since the QueryDef hasn't been previously run, and since it
doesn't get permanently saved, I don't see any advantage.