Sql Queries vs. Access Queries

[Dherrera]

Will my program run faster if i use one or the other?

 

[scriverb]

What are you referring to? ACCESS queries are SQL queries. They are ACCESS SQL queries but they do use a SQL language. Are you referring to MS SQL from a connected SQL Server? If so, yes a pass-through query will run faster using the power of a SQL server. But, like I said they must be constructed as a pass-thru query in MS SQL language rather than the ACCESS SQL. This means that the connect string, ID and passwords are included in the properties and the code is passed to the SQL server where its power is used. Then the recordset is returned to ACCESS for processing.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dherrera]

sorry, i was a little vague.

i have a report that runs off a query created by access. i have that query as the record source. would it run faster if i put the sql code in the record source? i have several queries in my database and it looks like access runs each one of them at start up.

 

[scriverb]

Saved queries that have been run one time are optimized are will run faster than SQL code pasted into a RecordSource. This is a fact. So, if you have a complex query with multiple tables and linked connections it is best to save the query and run it one time. This save your database for distribution and use. This process optimizes and compiles the SQL code.

You also said that you have queries that run on startup? Why do you think that? Check your AutoExec module or any code that is set to open with your initial form opens. See if there are queries that are runninng. These are more than likely saved queries.

Post back if you have more questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]