Save queries rather than SQL sources 1

[perrymans]

I once asked a question on whether it was better (for speed) to save a query as a query, or use the SQL builder and store the procedure. Well now I have my answer, and I believe it is opposite of what I was told before.

I was cruising MSDN Access Developer site and reading the example articles from some Access books. I was real excited to see some Ken Getz articles from The Access Cookbook (I have been wanting this book). In his section on optimizing forms and queries he writes:

"Saved queries are always faster than SQL statements because the query optimizer optimizes the query when it is saved instead of when it is run."

This does go along with what I have read on the Jet Query Optimizer. I will possible submit a FAQ on this, depending on response to this posting.

Sean.

 

[DonQuichote]

You can go a step further. You can make parameter queries for queries that are similar except for one or two parameters. If you run the query in Access, the parameter can be control value if you want.

Let me give an example. Say you have a tblDepartments table and a tblEmployees table. There are two comboxes on a form: cboDepartments has a rowsource property of (first column is hidden):

SELECT lngDepartmentId,strName FROM tblDepartment ORDER BY strName;

and cboEmployee has a rowsource of (also with hidden first column):

SELECT lngEmployeeId,strName FROM tblEmployee WHERE lngDepartmentId=[cboDepartment] ORDER BY strName;

By the way, if you put SQL in properties of controls, they ARE saved as a normal query. You just can't see them in the queries window.

Best regards

 

[perrymans]

"By the way, if you put SQL in properties of controls, they ARE saved as a normal query. You just can't see them in the queries window."

That I had not heard (or read). Interesting.

I assume strName in your examples is a variable from VBA.

Thanks. Sean.

 

[Parax]

I had not heard of that either but I just checked and it is correct!!

if you want to see for yourself:
create a new DB
set view system objects to true (in tool/options)
look in msysobjects table
create and save a new form [Form1]
notice the object appears in msysobjects table - Name:[Form1]
go back to the form and create a combo with sql source
notice that a new object apears in the msysobjects - Name:[~sq_cForm1~sq_cCombo0]

note the object ID and look in msysqueries table - hey presto it is there as a query, already optimised!!

=)

 

[DonQuichote]

Dear perrymans,

The strName is a field in the tblEmployees table.

Best regards