VBA/SQL Auto-Update Routine

[Rougy]

Is there a quick, down and dirty routine for automatically updating a VBA form with SQL data, either whenever there has been a change in the SQL file, or perhaps on a periodic basis (every 5 to 30 minutes).

Thanks

 

[kevinclark]

Here is one method. While the code below writes to a Worksheet you can just as easily change it to update data on a UserForm. The Function MyTimer will fire every 10 seconds and then update the SQL. One nice thing about the OnTime function is that you can continue to work on the Workbook while it waits to fire.


Dim dbTest As Database
Dim rsTest As Recordset


Sub Button1_Click()
Set dbTest = OpenDatabase("C:\Development\html\Test.mdb&quot
Set rsTest = dbTest.OpenRecordset("Select * From Books&quot
MyTimer
End Sub


Function MyTimer()
Dim R As Integer
Dim C As Integer
Dim rsData As String
rsTest.Requery
R = 0
Sheets("Sheet1&quot.Cells(1, 1).Value = "Last Update At: " & Now()
Do While rsTest.EOF <> True
For C = 0 To rsTest.Fields.Count - 1
rsData = rsTest.Fields(C).Value
Sheets(&quot;Sheet1&quot.Cells(R + 2, C + 1).Value = rsData
Next C
R = R + 1
rsTest.MoveNext
Loop
Application.OnTime Now + TimeValue(&quot;00:00:10&quot, &quot;MyTimer&quot;
End Function