Performance Tuning with Multiple MDB Files 1

[harryrich]

The Access 97 Performance Analyzer misses some points when dealing with multple MDB files.&nbsp;&nbsp;In particular, when a query creates a join between tables in different files the Analyzer may not recommend or create a relationship betweem the two tables based on the joined fields.<br><br>Further, while Access allows you to create the relationship, it seems to be primarily documentation.&nbsp;&nbsp;The indexes supporting the relationship don't seem to appear.<br><br>It takes some effort do the job manually but it can be worth the trouble, even for a single use.&nbsp;&nbsp;I've seen queries go from &quot;Is the system dead?&quot; to running in two or three seconds.<br><br>In each table create an index based on its field(s) used in the join, making sure that, if possible, it is designated a unique index. Next create the relationship for documentation purposes.<br><br>Unfortunately checking the type match between the related fields and maintaining referential integrity are up to you.<br><br>