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.





below 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.





' These codes can be placed in modules and used in other forms as well





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 temporary filename





strtempfile = gettemporarypath & "Temp.mdb"





If Len (Dir (strtempfile)) Then Kill strtempfile





' compresses database files through DBEngine





dbengine.compactdatabase Location, Strtempfile





' deletes the original database file





Kill Location





' Copy has just compressed the temporary database file to its original location





filecopy Strtempfile, Location








' Delete temporary files





Kill Strtempfile





Else





End If





Compacterr:





Exit Sub





End Sub





public Function Gettemporarypath ()





Dim Strfolder as String





Dim Lngresult as Long





strfolder = String (MAX_PATH, 0)





Lngresult = GetTempPath (MAX_PATH, Strfolder)





If lngresult <> 0 Then





Gettemporarypath = Left (Strfolder, InStr (strfolder, Chr (0))-1)





Else





Gettemporarypath = ""





End If





End Function





You can try this compression later when you use an Access database, you should find that I'm right.