Function CompactAndRepairDatabase(DatabasePath As String, _
Optional Password As String, _ Optional TempFile As String = "c:\temp.mdb") If DBEngine.Version < "3.6" Then DBEngine.RepairDatabase DatabasePath 'if no temp file specified, use "c:\temp.mdb" If TempFile = "" Then TempFile = "c:\temp.mdb" 'delete temp file if it exists If Dir(TempFile) <> "" Then Kill TempFile 'format password in form of ";pwd=PASSWORD" if password exists If Password <> "" Then Password = ";pwd=" & Password 'compact db: a new db will be created DBEngine.CompactDatabase DatabasePath, TempFile, , , Password 'kill first db Kill DatabasePath 'move compacted db to original db's path FileCopy TempFile, DatabasePath 'delete temp file Kill TempFile End Function Inputs:DatabasePath: full path to database Password [optional]: database password TempFile [optional]: full path To temp file to create Assumes:Reference Microsoft DAO (Data Access Objects) from Projects->References menu. REQUIRED: reference Microsoft Data Access Objects (DAO) from Project->References in the VB Menu this function will compact and repair a db and replace the original db with the compacted and repaired db if the DAO version is older than 3.6, then repair the db using the RepairDatabase method if the DAO version is 3.6 and later, you don't have to worry about repairing the db because the CompactDatabase also repairs the db |