Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compact database (Access 2000/2002)

    I would like to know if I can write the VBA code to compact the database when I am still in the same database itself.

    Thanks

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Compact database (Access 2000/2002)

    You can't directly compact a database in code - what Access does behind the scenes when you select Compact and Repair is
    (1) compact the current database to a new one (usually db1.mdb),
    (2) close and delete the current database,
    (3) rename the new compacted database to the name of the original,
    (4) open the compacted database as current database.

    The easiest way in Access 2000 and up to compact a database automatically is to check the Compact on Close option. Otherwise, you'll have to imitate the process described above. Have a look at the this page on the Access Web for more info and some downloadable utilities.

  4. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact database (Access 2000/2002)

    Thanks for the info. If I choose Compact on Close, what happens if multiple users are using the same database and then one quits but others are saving records at the same time?

    Thanks again.

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Compact database (Access 2000/2002)

    Compact on Close will only run when the *last* user closes the database. It won't (and can't) run while more than one user is active.

  6. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,869
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact database (Access 2000/2002)

    Hi Hans

    1. If Compact on Close is running and another user trys to open the app, does he get a message?

    2. What happens if there is an error on Compact on Close?

    John

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Compact database (Access 2000/2002)

    Hi John,

    1. When Access starts Compact on Close, it locks the database exclusively, so other users won't be able to open it. They'll get a warning that the database has been locked. When the compacted database has been created, Access will delete the original database and then rename the compacted one. So during a very short period, there is no database with the original name. If a user happens to try to open it at precisely that time, he/she will get a message that the database couldn't be found.

    2. If something goes wrong, most likely you'll be left with two databases: the uncompacted original database, and a compacted database with a name like db1.mdb (if the process repeats itself, you'll get db2.mdb, db3.mdb etc.). If the database contains corrupt records, Access will issue a message and create a MSysCompactError table that contains information about these corrupt records.

  8. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Timberville, VA
    Posts
    1,438
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Compact database (Access 2000/2002)

    It just so happens that this week I discovered the option to have Access "compact on close." It's a terrific choice--I don't see why it's not active by default, but then I'm certainly not an Access expert.

    As for your question about what do you do if you get an error during compact-and-repair: Pray. I've learned the hard way that you just gotta have a back-up copy of your database. I keep two copies: one is the untouchable back-up; the other is where I run my experiments. A few years ago, I built my very first Access db when I knew almost nothing about the application. One day my db just went belly-up. I called someone in my organization for help; he sent me a young guy who supposedly knew Access cold. But all the king's horses and all the king's men couldn't put Humpty-Dumpty together again. My hours--yeah, days--of work were gonzo...lost forever...junk. Had I kept a good back-up, I coulda gone to bed early.

    That's the best advice I can offer. As for what Hans has written: Pay attention. This guy knows what he's doing. My hero. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •