Compacting the database at this point should recover the project no longer being used and should reclaim some space (if not all 10 MBs).

The best choice to reduce the impact of this change is work on all the database files located on the local system and not on the server.

Access does automatically create indexes on primary keys, foreign keys and other fields as per the Autoindex on Import/Create in the Tools The problem with too many indexes is that this will slow down record insert and field updates as the indexes have to be updated. For example when doing a bulk loading of records, such as when converting a system, it can be very beneficial to delete all the indexes, load the records and create the indexes fresh again.

Note that in some performance tuning I just did for a client adding an index on a boolean field in a "master" table containing 800 records dropped the form load time from 30 seconds to 3 seconds.

Alternatively I'd suggest having the network people setup a share right on the directory of your BE for your use.

Instead of using \\Server\Dir1\Subdir2\Subdir3\subdir4\subdir5\you'd use \\server\Sharesubdir5. They can can append a $ to the end of the share name to make it a hidden share so as to not confuse people.

So run the code in the above mentioned Q261000 article in the Access 97 backend. Note that if you have Name Auto Correct on this will change the Subdatasheet property back to [Auto] for each table to which you make a change. There are many web pages on this topic both Access specific and general relational database systems so I'm not going to get into the details here..

However you should have indexes on all fields that are used for filtering and selection criteria and sequencing on forms and reports.

As a result, more is being written to disk then was done in the past.

Some changes to the database cause Access to make a copy of the project items instead of replacing the old project which can cause an increase in database size.

But in particular the Sub Datasheet Name property set to nothing can be a problem as it appears Access 2000 will default this value to [Auto].