with recordsets, what is the use of querydef? 2

[Eldaria]

Why use a querydef? is it faster or is it a waste of code to use it?

Example:
-----------------------------------------
Dim db As Database
Dim qdef As QueryDef
Dim rs As Recordset

Set db = CurrentDb()

Set qdef = .CreateQueryDef("", "SQL statement&quot

Set rs = qdef.OpenRecordset(dbOpenSnapshot)
-----------------------------------------

Would it no be easier to skip the Querydef?

-----------------------------------------
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("SQL statement", dbOpenSnashot)
-----------------------------------------
Eldaria

http://www.eldaria.net

That was my 25cent** of opinion.

** Inclusive Intrest, tax on interest, Genral tax, Enviromental tax, Tax, and tax on intrest, tax on fees, tax on tax, and other Various taxes and fees.

 

[jgoodman00]

This was a similar post to your question:
thread181-10170 you search for QueryDef you will find quite a lot of other posts regarding this... James Goodman

 

[JimHorton]

Eldaria,
The querydef will be faster. The sql statement, especially if you're changing the criteria values, must *always* be recompiled.
Every time a 'raw' sql string is passed to, say, and 'OpenRecordset' method, or a Form.recordsource property, JET must do the following:

Check to see that the tables exist.
Check to see that the fields referenced actually exist in those tables.
Check to see that any criteria and/or joins have similar data types.
Check the syntax.
Create an execution plan.
Compile the results. ( and in the case of an embedded sql, save it to disk with the hidden tilde prefix)

A querydef is *never* recompiled unless you open it, modify it and change it (not sure about 'dirty flagging' the query if a table changes).
No matter how many different parameters you give a parameterized querydef--the JET engine sees it as the exact same querydef with a predefined execution plan.
--Jim

 

[BoxHead]

Jim, that's good stuff to know.

Thanks. John

Use what you have,
Learn what you can,
Create what you need.

 

[Eldaria]

Thanks, this is the exact info I wanted..
Eldaria

http://www.eldaria.net

That was my 25cent** of opinion.

** Inclusive Intrest, tax on interest, Genral tax, Enviromental tax, Tax, and tax on intrest, tax on fees, tax on tax, and other Various taxes and fees.