Is there some way to automate teh compact and repair facility to run in the middle of the night? We have a number of users that are staying after hours to get work done, which limits the times I can manually get in to open exclusive and do this.
would this be in VB or could one right a macro to do it when the database was last closed after 5pm or? ?
You will need to referrence Microsoft Jet and Riplication Objects 2.5 Library.
the code would be something like the following:
Dim DatabasePath as string
Dim DatabasePath_New as string
DatabasePath = "C:\Program Files\TestDB.mdb"
DatabasePath_New = "C:\Program Files\TestDB1.mdb"
Dim Je as new JRO.JetEngine
"Provider=Microsoft.Jet.OLEDB.4.0.;DataSource=" & DataBasePath, "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" & DatabasePath_New
"Compact and repair" should not be required on an ordinary nor frequent basis. The MDB file format is designed to be self-maintaining and generally it is. I'd make regular backups of the file but there's really no need to constantly rebuild it. The file will never be "as small as it theoretically could be," but that's not the point.