Same query, different format cause form to behave differently

[Kowalski]

Hi.
Funny one, and lots of kudos for the one who can answer me

I have a Access 2010 form based on a query (3 tables). When closing the form, the forms takes long to close (5 seconds) and says 'Running Query' in the status bar.

If I change the query a little bit, it works fine. The funny thing is that the query is exactly the same, apart from a different order of the tables in the FROM clause.

What is happening here? Basically, the problem is that I would like to add more tables to the query, but I don't understand why the form takes long to close. If I can figure out this simple case, it might put me on the right track to change the query.

This query as RecordSource, causes the form to close slowly:

SELECT RTUs.*, Repeaters.Network, Sites.SiteName, Sites.CustomerNetworkCentre
FROM Sites RIGHT JOIN (Repeaters RIGHT JOIN RTUs ON Repeaters.Repeater = RTUs.Repeater) ON Sites.Site = RTUs.Site;

And this query as RecordSource, works fine (i.e. form closes quickly):

SELECT RTUs.*, Repeaters.Network, Sites.SiteName, Sites.CustomerNetworkCentre
FROM (Sites RIGHT JOIN RTUs ON Sites.Site = RTUs.Site) LEFT JOIN Repeaters ON RTUs.Repeater = Repeaters.Repeater;

As you can see, the 2 queries are basically the same. In Query Design View it looks exactly the same.
So how can I ensure my form will close quickly if I add more tables to it?

 

[mrojas]

Have you tried creating the query in the design mode, and then making this query the form's record source?

 

[spikepl]

Check indexing