Need MS Access VBA to schedule automatic backup

Hi Experts, is this possible?

I would like a macro which runs automatically, maybe when the DB is opened, which will:

1) Check in a specified directory for a backup file matching a name like "myDB_backup_03_30_2011" where the date is less than a week old.
2) If such a filename is NOT found, ask the user if they want to run a backup.
3) If they do, run a backup and place the file in the directory using "[filename]_backup_[today]" in the same format as above.

I have used VBA quite a bit in other MS applications, but seldom in Access. Any suggestions on the best way to fire the macro also welcome. It should not require user intervention beyond them opening the file and agreeing to make a backup.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
The issue is that you should not attempt to backup any file that you have have opened.

This mean that you should not try to backup the database you have opened. You can backup the back

The first step to doing this will be to make sure that you have the database split. Is your database split?

Also see:I recommend something like this: Application Starter


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
McOzAuthor Commented:
Thanks for the response.

Yes, my database is split. I have a BE and two FEs on a network drive - one for reports and one for forms. This is just to allow concurrent editing of reports and entry of data by two users, I am not distributing multiple copies of the FE to any local machines.

From what you say about not backing up open DBs, I am wondering could you put the macro in each of the FEs, and have it backup the BE?

Just a simple macro to do the check and backup is all I am looking for, although thanks for the link. I will have a look at it in the meantime.

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
A Front end  should never be shared or opened twice at the same time.

The issue with a multi user environment is how to insure that  no other user has the back end opened.  This is why backups are normally schedule to run after hours when all the users are logged out. This helps insure you are getting a good backup.

In Access Marcro are NOT the same as VBA code (like in Word  and Excel).  Access Macros are very limited and probably will not be able to handle this. I would use VBA code..

McOzAuthor Commented:
Sorry, VBA is really what I am talking about.
So should the code also check for a lock file for the BE? No users ever work in the BE, they only use one of the FEs.

Any example code would be much appreciated.
Thanks for the tips
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
<<No users ever work in the BE, they only use one of the FEs.>>
Not totally accurate. It is true they don't open the BE directly. They open and use the  back end via the Front end .  The Front end opens the tables in the back end. So the end result is they are working in the back end. The Front end controls how the user works in the back end.

The Application Starter that I linked to in my first reply does everything that you need. You can look at the code for examples. I would recommend that you use it the Application Starter. No need to "reinvent the wheel."
Jeffrey CoachmanMIS LiasonCommented:

This also does what you are requesting:
McOzAuthor Commented:
HiTech, sorry about the delay in getting back to you.
So just to clarify, even though Access provides a Manage > Backup Database button which is available while you are in the database, this is not a course of action that is to be recommended? This is what I had naïvely been using manually up until now...

McOzAuthor Commented:
Did not answer my exact question, but I found out that what I wanted to do was not the best way to go about it in any case.

Valuable info.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.