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.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!