1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Search multiple DBs: Access 2000 - Mission Impossible

Discussion in 'Business Applications' started by daizee97, Feb 3, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. daizee97

    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!
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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...
     
  3. daizee97

    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?
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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).
     
  5. daizee97

    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!
     
  6. Anne Troy

    Anne Troy

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

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

    Autoeng

    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
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Cool. I just assumed...
     
  9. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/116857

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice