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?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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.

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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 MVPCommented:
<<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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.