When compacting an Access DB, why does the table size increase? 1

[Griffinator]

Hi,

I have a question regarding compacting and repairing an Access DB:

My original DB contains 1000 records, and currently its original size on disk is 152 KB. However, when I compact and repair the DB (using a snippet contained on this site), the size of the DB on disk increases to 232 KB.

Am I correct in assuming that when compacting the DB, the size on disk should decrease? If not, could someone explain why?

The code I am using to do this is as follows:
=============================

Public Function CompactAndRepairDB(sSource As String, _
sDestination As String, _
Optional sSecurity As String, _
Optional sUser As String = "Admin", _
Optional sPassword As String, _
Optional lDestinationVersion As Long) As Boolean

Dim sCompactPart1 As String
Dim sCompactPart2 As String
Dim oJet As JRO.JetEngine

On Error GoTo errhandler

' Put together the provider string for the source database
sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & sSource & _
";User Id=" & sUser & _
";Password=" & sPassword

' If the database has a user-level security file, add the
' details
If sSecurity <> "" Then
sCompactPart1 = sCompactPart1 & _
";Jet OLEDB:System database=" & sSecurity & ";"
End If

' Put together the provider string for the destination
' database
sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & sDestination

' The destination database will end up in the latest version
' of jet, unless a specific version has been requested;
' 1 = Jet 1.0, 2 = Jet 1.1, 3 = Jet 2.x, 4 = Jet 3.x,
' 5 = Jet 4.x etc
If lDestinationVersion <> 0 Then
sCompactPart2 = sCompactPart2 & _
";Jet OLEDB:Engine Type=" & lDestinationVersion
End If

' Compact and repair the database
Set oJet = New JRO.JetEngine
oJet.CompactDatabase sCompactPart1, sCompactPart2
Set oJet = Nothing

CompactAndRepairDB = True

Exit Function

errhandler:

MsgBox "The following error occurred while compacting / repairing the database: " _
& vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Error Description: " _
& Err.Description, vbOKOnly + vbExclamation, "Error While Processing Task"

CompactAndRepairDB = False

Err.Clear

Exit Function

End Function

=================================

Any guidance on this matter would be appreciated. Thanks!

 

[bjd4jc]

When compacting and repairing Access creates Temporary tables. So, these tables are not part of what gets compacted and repaired. Since your database is so small, data entered into the temporary tables causes it to actually increase.

I would venture to guess that when the size of your database increases, you won't see the size after a compact increasing, rather it would decrease. But since the temp tables aren't part of the compact and repair process, they are not compacted themselves.

 

[Griffinator]

Thank you, bjdrjc!

That's just the explanation I was looking for. Now it makes sense. A star for you!

 

[dilettante]

I wonder if JetComp.exe produces the same results? I know it handles the process differently.

 

[bjd4jc]

I've never used JetComp.exe before so I wouldn't know. Do you know how it handles it differently?

 

[dilettante]

For one thing, it doesn't open the database "as a database."

It opens the MDB directly and operates upon the file's internal structures directly as well, producing a new MDB as output.

You might want to try it out.

http://support.microsoft.com/?scid=kb;EN-US;273956

 

[Griffinator]

Thank you for your insight, dilettante. I will look into this utility as well, I was not aware it existed.