Question My database compacted itself? Can it DO that?

[bassman197]

Hi all,
I have a simple little database that I created to keep track of overdue contracts, payments etc and I periodically make a copy of the whole program as a backup. Up until now it's been slowly growing in size as more bits of data get entered in records and was up to just over 12MB, but on the next backup (two weeks later), it show as only 5.48MB?? All of the data, all the forms and reports seem to be there. I never told it to compact. Even if I had, that seems ambitious - less than half it's original size? Any thoughts on what happened here? I'm a little freaked out!...
-Steve

 

[Banana]

You can set it to compact on close. Check your options and see if this is the case.

 

[boblarson]

If you're using the Backup feature (available in 2003 {or maybe 2002} and on) then it would be compacted.

 

[bassman197]

Compact on close is not checked and I'm not using a backup utility - only copying and pasting the entire program to another folder and giving it a different name. The only thing I can think of is that I did compact a DIFFERENT database a few days ago - one we use to write contracts. Every year we make a copy of it, delete all records in the tables and compact it, so that the auto number of the contract will reset to start at one. That should not have been able to affect the other database, though, right?

 

[boblarson]

Compact on close is not checked and I'm not using a backup utility - only copying and pasting the entire program to another folder and giving it a different name. The only thing I can think of is that I did compact a DIFFERENT database a few days ago - one we use to write contracts. Every year we make a copy of it, delete all records in the tables and compact it, so that the auto number of the contract will reset to start at one. That should not have been able to affect the other database, though, right?

Nope, likely that someone else might have done it.

 

[bassman197]

I guess I could have Inadvertently compacted it, but what gave me pause was how MUCH in compacted. From 12MB to 5.48MB and all the data is still there? Does that sound feasible? This would be the first time in it's 2 year life that it's been compacted...

 

[Banana]

Sure.

I even had a database go from 2 GB (maxed out) to <1 MB.

But of course that was me doing crazy stuff to it....

The point is that during the session, it's not just single instance of data being worked with but potentially several duplicates and that adds up to a lot of bytes.

 

[bassman197]

Wow - had no idea it could compact that much. Thanks!

 

[HiTechCoach]

If your database is not split into a front end and back end, or you are int he front end, and enter design mode on a form or report, this usually will cause the database to bloat. I have seen a MDB that is 10 meg compacted go to over 150 meg after editing lots of different forms and reports.

 

[jwhite]

Not related to your reported issue, but rather than having a separate database for each year just for resetting the autonumber (?), keep all your data in one BackEnd database, and use a sequential numbering scheme as your identifier for the generation of new sequence numbers -- such as YYYY99999 where YYYY would be the current year. The number generator would evaluate the year part of the last number generated with the current Year -- if different, then the next sequence would be the current year + "00001". Then, you wouldn't have a slew of databases -- all data in one database. You could then write code to import the previous data in.

 

[bassman197]

Thanks, HiTechCoach! I didn't realize how much impact design mode could have on the size of the program. Lately, I had been getting "Microsoft Access has encountered a problem and needs to close..." messages when trying to do more design. I bet that's because I never compacted it! Haven't tried more design yet, but I'm guessing the error messages will be gone now...

 

[bassman197]

Thanks, jwhite! There are actually two databases at work here, one that I wrote to track overdue contracts (the one that shrunk so much when compacted that it alarmed me!) and one that I inherited to actually write contracts, invoices etc. Since 2009 is upon me, I'll have to keep the inherited one with the old new-every-year system for now, but with plenty of time to research, maybe I can implement your solution for 2010. Sure would be nice to not have to go through that every year!