Faster Queries by setting relationships between tables

[barbarossaii]

Hi,

I've already searched the forum for this "newbie question", but I found no answer to this.

I'm wondering if queries run faster if I set relationships between the used tables via the menu ("extras", "relationships") or in the queries (in the design view by pulling one field on the other and establishing the desired join).

Thanks,
Barbarossa II

 

[barbarossaii]

Thanks for your explanation Pat,

the first part is clear, but as a "newbie" to Access I didn't get the 2nd part.

quote:
---------------------------
If you are worried about execution time, make all of your queries stored querydefs rather than dynamic SQL that you paste into recordsources or write in your VBA code modules.
-------------------------------

As I use a non-English version of Access I had problems with "querydefs" and "dynamic SQL".

Please explain.

Thanks in Advance,
Barbarossa II

 

[The_Doc_Man]

Pat is correct, but if her explanation confused you, I will try to help you understand it.

When you open the Queries window, fill in the query fields in the grid, and save it, that query goes into a collection of queries. The collection is called 'querydefs.' Similarly, when you define a set of tables and store the definition, each table definition goes into the collection of 'tabledefs.'

When you store queries in querydefs, they have already been validated.

When you write a program that creates an SQL query "on the fly" and tries to execute THAT, you have created a 'dynamic SQL' query that has NOT been pre-validated and is NOT in querydefs.

The bit about 'VBA' or 'pasting into recordsources' is because those are two places where the dynamic SQL could occur.

Hope that helps your understanding.

 

[barbarossaii]

Got it !

Got it!

Thanx @ Pat & The_Doc_Man,

Barbarossa II