How To Run Access VBA On A Timer Schedule
At one of the contracts I am on currently, I run several backup procedures through out the day on some of the main tables if something should happen while I’m not on site.
I set up a simple process. I am letting the Windows Task Scheduler, which runs as a service while the computer is on, open a database which performs the backup process, and then closes again.
I am launching a task on an hourly basis, so I don’t have to keep the database open all day.
Here is a list of procedures I am running:
I open the Task Scheduler and “Create Basic Task” (this particular one runs at 1pm daily).
(Program/Script):
This will open the MS Access 16 program on your computer:
“C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE”
(Add Arguments (optional)):
Now point to the path of your database:
“\\server\share\folder\backups\backup.accdb”
I didn’t do anything specific for this
The History tab just shows the outcome of your task.
So the way it works, is that when the Scheduled Task runs, a database is opened, which launches a hidden form in the database and runs my VBA code.
When the database opens, a form is launched:
The code in the “Load” event of the form is…
Private Sub Form_Load() MakeBackup Call btnProjBak_Click Application.Quit End Sub Private Sub btnProjBak_Click() '3/12/18 - ejl Dim strFile As String Dim strDestination As String Dim qdf1 As DAO.QueryDef Dim strSQL1 As String Dim qdf2 As DAO.QueryDef strFile = Format(Time, "hhmm") & "_" & Format(Date, "YYYYmmdd") & "_Projects.csv" Set qdf1 = CurrentDb.QueryDefs("qryPPProjects") strSQL1 = qdf1.SQL Set qdf2 = CurrentDb.QueryDefs("qryPPProjects_Export") qdf2.SQL = strSQL1 strDestination = "\\server\share\backups\interface\" & strFile DoCmd.TransferText acExportDelim, , "qryPPProjects_Export", strDestination, True '--------------------------------------------------------------------------- strFile = Format(Time, "hhmm") & "_" & Format(Date, "YYYYmmdd") & "_ProjectItems.csv" Set qdf1 = CurrentDb.QueryDefs("qryPPProjectItems") strSQL1 = qdf1.SQL Set qdf2 = CurrentDb.QueryDefs("qryPPProjectItems_Export") qdf2.SQL = strSQL1 strDestination = "\\server\share\backups\interface\" & strFile DoCmd.TransferText acExportDelim, , "qryPPProjectItems_Export", strDestination, True '--------------------------------------------------------------------------- qdf1.Close qdf2.Close Set qdf1 = Nothing Set qdf2 = Nothing End Sub
Here’s a part of what the folder looks like after this process is done.
That way if I am not around, they have all the vital data to keep them running smoothly until I return 🙂
Let me know if you have any questions.