I have a button on my Granite Fleet Manager app which allows the user to do a backup of the backend data MDB. I also have two other buttons which zip and email the backend data MDB to either themselves for backup purposes or to me for support purposes.
However any open forms or reports will lock the back end MDB file so a rename and compact won’t happen.
As I do not know which forms and reports the user might have left open when they click on any of the buttons I run through some code that closes all open forms and reports, then run the code doing the rename, compact back to the same file name and, if requested do the zip and email.
‘ close all forms and reports
For Each frm In Forms
DoCmd.Close acForm, frm.Name
For Each rpt In Reports
DoCmd.Close acReport, rpt.Name
DoEvents seems to be necessary to give things time to close. But maybe not..
Also note that I do keep a status form open during the above operation. I also do not have any list boxes or combo boxes with the row source set to a query or table referencing the data MDB on the form. And I don’t have any open recordset or database variables for performance purposes.
You can double check that this code works by stopping the code just after the above and seeing if the ldb file on the data backend mdb no longer exists
Of course if there are other users in the back end then the rename won’t work of course. Also see my Backup, do you trust the users or sysadmins? tips page for more info.