I have an application with lots and lots of queries. The application is 

distributed via an mde file. The code is secure but users can get to the 

queries. It strikes me that I could replace some of the queries with SQL 

in the data source of the forms (I didn't know that when I developed the 

app).



Are there performance issues between discrete queries and SQL as the 

source for forms please?

> I have an application with lots and lots of queries. The application is 

> distributed via an mde file. The code is secure but users can get to the 

> queries. It strikes me that I could replace some of the queries with SQL 

> in the data source of the forms (I didn't know that when I developed the 

> app).

> 

> Are there performance issues between discrete queries and SQL as the 

> source for forms please?





you can save the sql at the source of the form as a external query and assign it to 

the form ..... is this what you mean ... otherwise make it more clear to help you 

bye



abed

I believe that there is no difference performance-wise, so long as you are

typing SQL right into the RecordSource property, as opposed to setting it

programatically in code behind your form.  All queries have to be compiled

by Jet before they can be run.  Saved queries can be saved in a compiled

state, which saves your app the trouble of recompiling them the next time

they are run.  I don't think SQL stored in strings in code can be saved

compiled (by Jet that is--they are of course compiled into the VBA code) but

I believe that Access creates its own saved queries for SQL entered into

RecordSource properties 'behind the scenes'.



If you're hot to keep the queries under wraps & don't want to do security, I

say give it a try...



HTH,



-Roy



P.S. You can get some info about these system-generated queries by running

this query in your db:



SELECT Name, DateCreate, DateUpdate

FROM MSysObjects

WHERE Type=5 ;



Run that & look for the queries with wacky names that you didn't create.



-----Original Message-----

From: Clive Astley [mailto:clive.astley@p...]

Sent: Wednesday, August 29, 2001 12:29 AM

To: Access

Subject: [access] Speed - Queries v SQL in forms





I have an application with lots and lots of queries. The application is 

distributed via an mde file. The code is secure but users can get to the 

queries. It strikes me that I could replace some of the queries with SQL 

in the data source of the forms (I didn't know that when I developed the 

app).



Are there performance issues between discrete queries and SQL as the 

source for forms please?

Hi Abed,

The reason NOT to switch to using SQL strings is the query optimizer.  When

you build and save queries, and assuming your data has one or more indexes

available, the query optimizer will develop the best execution plan and

store

it with the query  Subsequent execution of the query will benefit from this

optimized execution plan.  

Unfortunately, I know of no way to accomplish optimization using SQL

strings,

even when taking the exact code from the query as View SQL and copying it.  

Queries are valid objects that are  performance optimized to the extent that

your data design permits.



Tom Hamilton

T_Systems, Inc

Database Programmer

(xxx) xxx-xxxx



>>> abed edammat 08/29/01 04:04AM >>>

> I have an application with lots and lots of queries. The application is 

> distributed via an mde file. The code is secure but users can get to the 

> queries. It strikes me that I could replace some of the queries with SQL 

> in the data source of the forms (I didn't know that when I developed the 

> app).

> 

> Are there performance issues between discrete queries and SQL as the 

> source for forms please?





you can save the sql at the source of the form as a external query and

assign

it to 

the form ..... is this what you mean ... otherwise make it more clear to

help

you 

bye



abed



-

Many thanks to all who replied. Thanks also Roy for the tip using 

MSysObjects - a facility I didn't know anything about.



Clive



> I have an application with lots and lots of queries. The application is 

> distributed via an mde file. The code is secure but users can get to the 

> queries. It strikes me that I could replace some of the queries with SQL 

> in the data source of the forms (I didn't know that when I developed the 

> app).

> 

> Are there performance issues between discrete queries and SQL as the 

> source for forms please?