First of a series of questions. 1. Table vs. Questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 31 Jul 2007 06:45 GMT

Hello All,

I'm beginning to build a massive database with more than 10 tables.
Currently I have 1 database with 4 tables and 1 query linking certain info
from the tables. The query is rather slow when the number of records go
over 1000. Is there a way to speed this up when it opens? Which one would
you recommend for interactions between tables? Thank you.

Reply to this Message

Stefan Hoffmann - 31 Jul 2007 09:51 GMT

hi Paul,

> I'm beginning to build a massive database with more than 10 tables.

Massive is something completly different :)

> Currently I have 1 database with 4 tables and 1 query linking certain info
> from the tables. The query is rather slow when the number of records go
> over 1000. Is there a way to speed this up when it opens? Which one would
> you recommend for interactions between tables? Thank you.

First of all, have all your tables primary keys?
Then add for the fields used in your query indices in the tables.

btw, what exactly is "rather slow"? Are you running the .mdb on a local
drive (what kind of drive)?

mfG
--> stefan <--

Reply to this Message

John W. Vinson - 31 Jul 2007 19:41 GMT

>Hello All,
>
>I'm beginning to build a massive database with more than 10 tables.

<chuckle> Let's see... PawTrax now has 67 tables, and it's far from the
biggest Access app I've used.

>Currently I have 1 database with 4 tables and 1 query linking certain info
>from the tables. The query is rather slow when the number of records go
>over 1000. Is there a way to speed this up when it opens? Which one would
>you recommend for interactions between tables? Thank you.

What are you doing with the query? It's rarely necessary to create One Great
Master Query linking everything in your entire database together; typically
you'll have Forms bound to one- or two-table queries for data entry, and
Reports bound to queries joining several tables, but typically retrieving only
those few records which need to be reported. Having a query joining all ten
tables and displaying all records in those tables would be very unusual
indeed!

You may want to look at Tony Toews' Performance FAQ:

http://www.granite.ab.ca/access/performancefaq.htm

John W. Vinson [MVP]

Reply to this Message