query takes too long to load

[kpalazzi]

I have a form with many controls based on a query.
The query links 3 tables and has well over 100,00 records in it. There is no parameters, I need to use the query instead of the table to get info from three tables. However, when I open the form it takes 3 minutes to load.

How can I make this load faster?
Kimberly Palazzi
kpal29@hotmail.com

 

[Peps]

Hi Kimberly, have you tried compacting your database? You say you have 100,00 records, Is any of this info out dated? Could you delete any outdated records inorder to keep your databases to a minimum? (You could create a routine that does this automatically) Is all the info produced from your query really needed? - Peps

 

[WildHare]

Kimberly, you say your query has >100K records in it. I assume you mean the resultant dataset FROM the query? How many records are in each of the three tables? Could your joins be incorrect? It's amazing how quickly a cross-join can foul up your day when you really need an equi- or outer join.

Also, where are the tables? In an Access/Jet database? In an ODBC database? What are the keying structures? How about indices?

There are many variables that can affect query operation speed, to give you a good start on where to look to improve the time, we need to know a little bit more about the structure of the tables and how you are joining them.

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm