McOz
asked on
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]
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.
Thanks!
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]
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.
Thanks!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
Thanks!
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.
Thanks!
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.
Note:
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..
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.
Note:
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..
ASKER
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
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
<<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."
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."
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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...
Thanks
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...
Thanks
ASKER
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.
Valuable info.
Splitting your Access database into application and data