I have a quick question. Does the "Compact on Close" feature in Advanced options compact just the front end database if the database is split, or does it compact both the front end AND the back end, which is the one that matters? Thanks!
I dont know if it is the same but do you mean the compact and repair? I think it depends which one you run it in. Whether you run it in the MDB or the MDE. Im sure someone will correct me if im wrong here?
The one that matters is the back end. As you add or delete data from you tables the indexes keep the data in order, but the data could be all over the hard drive, similar to fragmented files. When you compact a database all of the objects in a database are written to a new database and tables are written in index order. Keeping the records of one table all together. I have seen this significantly increase performance. But if you have a small database you probably won't notice anything.
Another issue you might have to consider is, how long will the compact take? With large databases it could be a half hour or so to compact.
My experience has been, only compact when you have space issues on your computer or you are experiencing performance problems.
You don't notice the file size of an access database steadily increase if it is not compacted?
The MDB will increase steadily in size, up to a point. Then it will stop.
Space in a database is allocated in "pages." In the MS-Access system, pages contain a varying number of records according to how large the field-values actually are. As records are inserted and removed, the content of the pages will change. When a new record is inserted, it may be added to a previously allocated page that isn't full, or a new page may be added.
When you compact a database, all of the pages are full. There is "no room in the inn." So as new pages are inserted, a lot of "page splitting" will occur and page-allocation will grow quite rapidly; giving apparent (but only short-term) credence to what you observe.
As time goes on, and once again depending very much on the actual distribution of data-values being inserted and removed, it will become more and more likely that a new page doesn't have to be allocated to make room for a new record. The file does not grow.
I support a database that was brought on-line in 1999 and has not been repaired or compacted once since then. Runs fine.
The way I compact the Backend click the compact on close then .
write a Autoexec Macro that Closes the database then opened
then get the sever to run it once a day about 2:00am in the moring
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
Sundial, you seem to have the proof in the proverbial pudding with your db from 1999. Your explanation makes sense from what (little) I know about how data is stored in Access/Jet. But, you seem to know more about the inner workings of Access db's, so I'll have to take your word for it.
DCKunkle, you seem to have some good points, however, about the fragmentation. Does anyone have any comments about this?
Just today I was placing a split database on a server for the first time. I tried running/editing the front-end from the server first off before I downloaded it to the workstations it's going on. I got an error twice out of just a few times running the database and its compact on close while it was on the server. Something to do with it being read-only so it saves it as db1.mdb. The only problem with this is that it didn't leave the originally named database in the folder. I only got this when it was on the server. And, yes, the user has access rights to the folder. Like I said, it worked most times, just not the two times before I took it off the server. Does anyone have any explanations for this and how to prevent it in the future in case it happens with the back-end?