Why use QryDef?

  • Thread starter Thread starter Jim Shaw
  • Start date Start date
J

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
 
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
 
They are pre-compiled and therefore execute minutely faster. Also, you can
set permissions to a querydef unlike dynamic SQL as per your example.
 
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.
 
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 .. . .
 
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.
 
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.
 
Back
Top