Search multiple DBs: Access 2000 - Mission Impossible

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

daizee97

Thread Starter
Joined
Jan 27, 2003
Messages
30
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

Anne
Joined
Feb 14, 1999
Messages
11,749
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

Thread Starter
Joined
Jan 27, 2003
Messages
30
..!!!

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

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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

Thread Starter
Joined
Jan 27, 2003
Messages
30
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

Anne
Joined
Feb 14, 1999
Messages
11,749
SQL costs a bit more, I think. Probably keep Access for:

Up to 30 simultaneous users.
Up to 50,000 records.
 
Joined
Dec 17, 2002
Messages
15
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top