CODEWITHSUNDEEP
ms-accessvba
Challenger
Challenger

Reputation: 127

Timer to run code every 30 minutes

Since I`m in a develop mode and my front ends requires a lot of updates. This is my function to check the version when the user try to open on of the front end:

Public Function FEPath(DBName As String)

Dim Conn As New ADODB.Connection
Dim DataConn As New ADODB.Recordset
Dim Comm As String
Dim strPath As String
Dim strDB As String
Dim strVer As String
Dim strExt As String

Comm = " SELECT tblFE.Database, tblFE.Version, tblFE.Extension " & _
        " FROM tblFE WHERE tblFE.[Database] = '" & DBName & "';"

Set Conn = CurrentProject.Connection
DataConn.Open Comm, Conn, adOpenKeyset, adLockOptimistic

With DataConn
    strDB = ![Database]
    strVer = ![Version]
    strExt = ![Extension]

    strPath = "C:\Databases\" & strDB & " " & strVer & strExt

    .Close
End With

Set Conn = Nothing

I need to somehow run a piece of code every 30 minutes, while the front end is open, to check to see if there is a new front end version available for upload. I would then notify the user, close the database, and update to the new version. Right now, version updating is always happening when the database opens. How can this be done?

Thank you.

Upvotes: 3

Views: 21912

Answers (1)

RubberDuck
RubberDuck

Reputation: 12728

There's an old trick MS Access programmers use. You use an autoexec macro to open a hidden form when your database launches. Then you can use the Form_Timer event to trigger any code that you want to run every so often.

The TimerInterval form property uses milliseconds, so you could either set it manually to 1800000 (1000 * 60 * 30) or use your hidden form's on_load event to set it. I usually opt for option 2 so it's spelled out in the code somewhere. 

Sub Form_Load()
    Me.TimerInterval = 1000 * 60 * 30 ' 30 minutes
End Sub

Sub Form_Timer()
    ' check the front end version here
End Sub

*Note: Setting the TimerInterval in the load event also allows you to set it to 0 (effectively turn it off) when you're developing. I don't expect you'll need to with a 30 minute timer, but when you're running something every 1/4 second, form timers can be a pain to work with while developing other places in your database application.

Upvotes: 6

Related Questions