Griffinator
Programmer
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!
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!