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).

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:

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()

    Call btnProjBak_Click
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

    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.

Offsite Related Information:

“Go Fund Me” Page

($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Tags: , , , ,