Search multiple DBs: Access 2000 - Mission Impossible

[daizee97]

Okay, I designed an order database with many fields and several linked tables. 3 years ago, we noticed that after about 1000 orders were entered, the database got really slow, the queries didn't want to run and records started becoming corrupt or disappearing altogether. So, we started separating them out: we essentially copied the database, deleted the old records and started with the next batch of orders.

Now, 9 databases later, I need the ability to search all the databases (or at the very least, the 4 most current ones) for a customer's reference number. So far, I've gotten as far as linking the tables from each database to the current database, but since the order number and customer reference number fields are named the same in each linked table, I am finding it hard to be able to view all the info on one table or query so that I can do a FIND for the number I'm looking for.

I'm really at a loss. Maybe this isn't possible.

HELP!

 

[Anne Troy]

Daizee.

Copy just one of the DBs. The biggest would probably be best. Call the copy MyTest or something.

Now open that DB and hit File Properties. Check the SIZE of the file. Write it down.

No, close the properties and go to Tools-Database utilities, and then Compact and Repair. After it has completed, please go check the size again.

Any help?

Databases are very difficult to overflow if you're compacting and repairing regularly. If it's a shared DB, get everyone out of it at least once a week and run compact and repair on it.

If it is NOT shared---or never used by more than one person at once, go to Tools-Options, General tab and choose "Compact on close" and it'll be done automatically for you every time you close the database.

Please confirm that compacting and repairing did NOT help, and then we can work on searching multiples...

 

[daizee97]

..!!!

The file was 6.66 MB, after compact, down to 804 KB!! What is all that info?

 

[Anne Troy]

This is very common in any database app, not just Access.

Databases run "on time". You know---the minute you enter a record, it's already saved in the database. Probably one of the reasons you can't File-Save as with an Access database.

When you delete records, the space the record used is left behind. So, compact is kind of like restocking the shelves in the supermarket, where you bring everything to the front and clear the space in the back.

You should probably be able to pull all your data into one DB now.

Also, consider making a new database. Import all your tables into it. Now, in your original/main DB, LINK all the tables from the other database. By doing this, you're lightening up the load of opening the database because you're not opening a 20MB file now, you're just opening the one with the forms in it. This is what is meant by a "front end" (the linked tables, forms, reports, etc.) and a back end (the DB with the tables).

 

[daizee97]

Thank you very much. I am merging copies of the most current databases into one right now. It looks like a 1 or 2 MB file after compact and repair, but things are still a little slow.

You mentioned "front end" and "back end". I've been wondering about using SQL as the back end and Access as the front end. Any knowledge or suggestions concerning this?

Thanks again!

 

[Anne Troy]

SQL costs a bit more, I think. Probably keep Access for:

Up to 30 simultaneous users.
Up to 50,000 records.

 

[Autoeng]

Dreamboat:

You can use Compact On Close in a multiuser environ because it will only run when the last user exits.

From Access Help:

Compact an Access database or Access project automatically when you close it
Microsoft Access can automatically compact a Microsoft Access database or Microsoft Access project every time you close it.

Open the Access database or Access project that you want Microsoft Access to compact automatically.


On the Tools menu, click Options.


Click the General tab.


Select the Compact On Close check box.
Note Compacting does not occur if you close a multiuser (shared) database while another user has it open.

Autoeng

 

[Anne Troy]

Cool. I just assumed...