To compress an Access database in VB

If you delete data or objects in an Access database, an Access project, you can create fragmentation and result in less efficient disk space usage. At the same time, the size of the database file does not decrease, but it grows until your hard disk has no space. Is there a good way to deal with it? In fact, you can compress the database in access to improve the performance of Access databases and access projects, essentially copying the file and then organizing how the files are stored on disk. However, making such a compression in an Access project does not affect database objects, such as tables or views, because they are stored in a Microsoft SQL Server database rather than in the Access project itself. Similarly, such compression does not affect automatic numbering in an Access project. In an Access database, if a record has been deleted from the end of the table, compressing the database will reset the AutoNumber value. The AutoNumber value of the next record you add will be greater than the AutoNumber value of the last record that is not deleted in the table.
The following describes how to use a compactjetdatabase process in VB to implement the compression processing of Access database files, in this process there is an optional parameter, that is, before compression you need to back up the original database files to the temporary directory (TRUE or FALSE). I use this method to compress the 21.6MB database to just 300KB.
' This code can be placed in a module and also used in other forms
Public Declare Function GetTempPath Lib "Kernel32" Alias _
"Gettemppatha" (ByVal nbufferlength as Long, ByVal lpbuffer as String) as Long
Public Const MAX_PATH = 260
Public Sub compactjetdatabase (Location as String, Optional backuporiginal as Boolean = True)
On Error GoTo Compacterr
Dim Strbackupfile as String
Dim Strtempfile as String
' Check if the database file exists
If Len (Location) Then
' Perform a backup if a backup is required
If backuporiginal = True Then
Strbackupfile = Gettemporarypath & "Backup.mdb"
If Len (Dir (strbackupfile)) Then Kill Strbackupfile
FileCopy Location, Strbackupfile
End If
' Create a temporary filename
Strtempfile = Gettemporarypath & "Temp.mdb"
If Len (Dir (strtempfile)) Then Kill Strtempfile
' Compress database files through DBEngine
Dbengine.compactdatabase Location, Strtempfile
' Delete the original database file
Kill Location
' Copy has just compressed the temporary database file to its original location
FileCopy Strtempfile, Location