Compact / Compress OnClose

[OldBirdman]

Access has an option for Compact on Close. Very good, as that should stop a major increase in size every time any .mdb file is used by Access.

My question is, Is this safe? It used to be that I would make a backup copy before I tried to compress. So now I can do it automatically, without a backup copy and without having to teach users about this procedure, and what it all means.

I use Outlook Express, and it reached 2GB, and failed. I find I was supposed to be doing a compress occasionally. I didn't realize this, and I'm sure most users are not aware that this is a database program, and needs to be compacted.

So if Microsoft does not do an automatic compact for Outlook Express, can I trust the Compact at Close for Access?

OldBirdman

[missinglinq]

Please stop using term compress and stick with compact! Compress is both confusing and inaccurate!

Using the Compact on Close option has often been cited as a cause of corruption in Access databases. I've seen these reports often enough that I never use the option and always backup before compacting. Your choice!

Now, about Outlook Express. The reason "most users are not aware that this is a database program" is very simple; it's not a database program, it's an email program!

Yes, it's recommended that you compact the files from time to time. Unlike web-based email programs, where your messages are stored on the email servers, with OE the messages are stored on your hard drive. Compacting is recommended in order to save storage space on your PC, not because there is a 2 GB limit as is true with Access. There are a number of reasons why it becomes corrupted. One of the biggest, user induced causes is interrupting the compact process once it has started! Another is, ironically, email scanning anti-virus programs.

Here are two excellent links that explain the ins and outs of Outlook Express compacting as well as a comprehensive overview all the causes of corruption/poor performence in it.

http://email.about.com/od/outlookexp...compact_oe.htm

http://www.microsoft.com/windows/ie/...ption.mspx#EEB

Linq ;0)>

[NeoPa]

As Linq says - I'd be wary of using the CompactOnClose option. I always use the Compact option without a database open. This way it asks for source and destination database names (inbuilt backup of course).
I also get mine to work from a command button in my database for the standard ones.

'CompactDb compacts a remote Access database.
Public Function CompactDb(ByVal strDB As String, _
                          Optional strPW As String = "") As Boolean
    Dim strNewDB As String, strLocale As String
 
    On Error GoTo ErrorCDB
    strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
    Call Echo(True, "Compacting """ & strDB & """.")
    If strPW <> "" Then strLocale = ";pwd=" & strPW
    If Exist(strNewDB) Then Kill strNewDB
    Call DBEngine.CompactDatabase(SrcName:=strDB, _
                                  DstName:=strNewDB, _
                                  DstLocale:=strLocale, _
                                  SrcLocale:=strLocale)
    Kill strDB
    Name strNewDB As strDB
    Call Echo(True, """" & strDB & """ compacted.")
    CompactDb = True
    Exit Function
 
ErrorCDB:
    CompactDb = False
End Function

I'm interested in your comments though Linq.
Certainly OE is an email client - as is Outlook. I can't speak for OE as I never use it but Outlook (when using Personal Storage .Pst files) certainly accesses a database as that is exactly what a PST file is. Not fully fledged Access of course, but certainly constrained to the 2GB limit that Access 2K files are.
Compacting these files (from within Outlook rather than Access) can release free space but how much tends to depend on the pattern of usage of the owner. It's always worth doing after a major cleardown of old cr*p though.

[ADezii]

As Linq says - I'd be wary of using the CompactOnClose option. I always use the Compact option without a database open. This way it asks for source and destination database names (inbuilt backup of course).
I also get mine to work from a command button in my database for the standard ones.

'CompactDb compacts a remote Access database.
Public Function CompactDb(ByVal strDB As String, _
                          Optional strPW As String = "") As Boolean
    Dim strNewDB As String, strLocale As String
 
    On Error GoTo ErrorCDB
    strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
    Call Echo(True, "Compacting """ & strDB & """.")
    If strPW <> "" Then strLocale = ";pwd=" & strPW
    If Exist(strNewDB) Then Kill strNewDB
    Call DBEngine.CompactDatabase(SrcName:=strDB, _
                                  DstName:=strNewDB, _
                                  DstLocale:=strLocale, _
                                  SrcLocale:=strLocale)
    Kill strDB
    Name strNewDB As strDB
    Call Echo(True, """" & strDB & """ compacted.")
    CompactDb = True
    Exit Function
 
ErrorCDB:
    CompactDb = False
End Function

I'm interested in your comments though Linq.
Certainly OE is an email client - as is Outlook. I can't speak for OE as I never use it but Outlook (when using Personal Storage .Pst files) certainly accesses a database as that is exactly what a PST file is. Not fully fledged Access of course, but certainly constrained to the 2GB limit that Access 2K files are.
Compacting these files (from within Outlook rather than Access) can release free space but how much tends to depend on the pattern of usage of the owner. It's always worth doing after a major cleardown of old cr*p though.

I think that one important point to mention in concert with Compaction is Defragging of the Hard Drive. After Compacting the DB, you would want the data to reside in contiguous Sectors on the Drive. Agreed?

[missinglinq]

From what I understand, I think you're assuming that Outlook Express is a limited subset of Outlook, and that isn't true! One of the links I posted takes pain to explain this. In short, the rules for one have nothing to do with the other, despite the common name Outlook! They're really two totally different programs! Another one of MS's cute little tricks! OE doesn't use .pst files, but rather .dbx files!

As to ADezii's suggestion of defragging after compacting the files, that certainly makes sense to me. I don't know about Outlook, but Outlook Express actually prompts the user to compact approximately every 100 times the program is opened, depending, as I understand it, on individual usage.


Linq ;0)>

[NeoPa]

I think that one important point to mention in concert with Compaction is Defragging of the Hard Drive. After Compacting the DB, you would want the data to reside in contiguous Sectors on the Drive. Agreed?

Probably one of the few places (large databses) where defragging can actually help noticeably.
Most tests (other than by those quoted by defrag software companies) indicate that while it never slows things down, the actual practical increase is almost always minimal.
Database files though, are probably the ones most affected by being stored contiguously.

[ADezii]

Probably one of the few places (large databses) where defragging can actually help noticeably.
Most tests (other than by those quoted by defrag software companies) indicate that while it never slows things down, the actual practical increase is almost always minimal.
Database files though, are probably the ones most affected by being stored contiguously.

Interesting, NeoPa. I would think that restricting the movements of the Read/Write Heads would have more than a 'minimal' effect on performance.

[NeoPa]

From what I understand, I think you're assuming that Outlook Express is a limited subset of Outlook, and that isn't true! One of the links I posted takes pain to explain this. In short, the rules for one have nothing to do with the other, despite the common name Outlook! They're really two totally different programs! Another one of MS's cute little tricks! OE doesn't use .pst files, but rather .dbx files!
...

This is something I was aware of (at a basic level) but I wasn't aware exactly where the similarities ended.
.PST & .DBX are probably not related except in as much as they are probably both database format files. Not necessarily a standard or specified format, but as they have to be able to store many and varied items within them and access them in a timely manner, they surely bare being referred to as databases.
That doesn't make Outlook or Outlook Express database programs, or even RDBMS systems. However, they do have database manipulation elements in them.
As your link pointed out (after your last post I had to go and read it properly :() these files need to be compacted from time to time to allow them to work efficiently, clearing away the dead wood.

[NeoPa]

Interesting, NeoPa. I would think that restricting the movements of the Read/Write Heads would have more than a 'minimal' effect on performance.

You and me both ADezii (not to mention various theoreticians). If my recollection of what I've heard about it is correct though, many of the expected improvements were cancelled out by other concepts already in place designed to improve performance. Particularly the caching.
If I had a better understanding of all the issues I'd explain more. Unfortunately all I can do is flag up the point and if anyone's interested they can dig. Good luck though, as most of what you'll find is by those with vested interests.

[OldBirdman]

Hey, this discussion is interesting, but not really going where I wanted it to go. The key question here is "How safe is an automatic 'Compact on Close'".

Every time I open an .mdb file, it increases by ~10%, whether I do anything or not. Maybe even more.

I mentioned Outlook Express only because it appeared to be a database program, and had a 'Compact' option, altho not mentioned in the simple help supplied.

Occasionally I find database programs inserted in my file structure (MS Exploring).

Apparently all database systems need compacting. My question was "Is an automatic compact safe"?

Outlook Express stores all email in files with the extension .DBX. Outlook Express needs to be compacted occasionally. Why was I in error assuming that OE is a database program? It most certainly is, and rightly so.

Whether OE is a subset of Outlook is academic, and we should not consider it on this thread.

Defrag is another subject completely. I have 80g on my harddrive, and I use about 10 g. What will defrag do for me. Answer: NOTHING. That isn't the question presented here.

Going back to the original question:
Is "Compact at Close" safe?

OldBirdman

[zaidlig]

Compact on Close is very safe if your database has no data or is not shared (multi-user). The best way to make an Access database uncrashable (in a multi-user environment) is use the Database Splitter under "Tools" this will split the data from the rest of the code. Then you can use Compact on Close without any issues, ever. This is also the most effective way to use Access even if its not muti-user, but stored on a network drive since all the user objects are local not being called over the network. All data transactions happen in realtime at the close of the record and it reports if a record write is not complete. In 10 years of creating a running 100's of databases I've lost only one record (it was later determined to be a drive failure) not a database error.

[NeoPa]

Sorry to let the discussion get off topic OB.
I didn't cover the question further as I felt that the question had been covered in posts #2 & #3 (I'd expressed my opinion at least).
Not all experts agree clearly (see previous post - #9), but you can choose from the info posted how you would like to proceed.

[OldBirdman]

Previous post #11 seems to get to the very heart of this question. He has a claim of being knowledgable with 10 years experience working with databases.

I have split some databases in the past, even for one user, stand-alone systems. This makes syncronizing 2 computers much faster, as only some tables are changed frequently, and some are almost never changed.

The links/references in post #2 are interesting. In the Microsoft article, although the word 'database' does not appear anywhere in the article, the word database could replace the word dbx every place that dbx is not used as a file extension. The article would then fully describe a database program, with each table (folder) being stored in a separate file. So this program has been "Split".

I guess the thing to do is what Microsoft does. It asks to compact the files after every 100 times the program is closed. Any warnings about shutting off the computer before this is done could be issued. Combining this with the code supplied by NeoPa would further reduce the risk.

Then at startup "If Exist(strNewDB) Then . . ." should detect if the compact was not completed, and appropriate action could be taken.

OldBirdman

[NeoPa]

Sounds like a plan :)