Hi guys,
I'd appreciate your views on this odd thing...
I'm building a system for a transport firm, using MS Powerapps which integrates to an Access app in the office. The system uses a number of Sharepoint lists as the point of integration, using Office 365. The Access DB is not currently split during development but it will be.
In brief, the drivers can book on or off via the mobile app, they are given scheduled jobs for each day and can update with their progress as they arrive/depart each job. Back in the office, Access links the lists read/written by Powerapps and the info is written to local tables(s) so it can be displayed in near-real time to a number of agents, who can also pull up historical data.
Powerapps writes, for each of these updates, a record to a Sharepoint list called lstUpdates; marking the UpdateStatus field 'PENDING'.
The Access app in the office has a 'Dashboard' form that, every 30 seconds, polls the linked Sharepoint list with a SQL select query to see if there are any new updates i.e. any marked PENDING. If there are, it does whatever it needs to do such as writing them to a local table (LogEntries), and marks them as READ via an Update SQL query.
It all works absolutely fine except for one niggling issue!
Below I have reproduced the code from the Form_Timer event so you can see what is going on. You'll note that I am getting the system ticks from the Operating System (kernel32.dll) at the start and at the end, so that I can measure how long the whole poll and processing thing takes, and it writes that to debug.
Now here's the issue. When I first start Access and the app, the polling times come in at around 50-70mS. But after an hour, they have gradually increased to 300-400mS. After several hours, they are up in the seconds and the system is showing signs of stress e.g. unresponsive UI, endlessly twirling blue circle, forms disappearing from the screen, etc. Monitoring Access's memory usage, I can see that it starts at around 25MB but goes gradually up until after a couple of hours it's more than 100MB. And all this is under static conditions, i.e. no new updates coming in whatsoever- so only the select query on the SP list should be running every 30 seconds. The number of records in the SP List is static at about 12; there are only 40 records in the local table LogEntries.
What do you think is happening? I feel its some kind of resource leak; so in my code I am closing the recordset and database objects, is there anything else I should be doing? Are there better ways to access the data? Would it help do you think to isolate it to Access or Sharepoint by running a test using a local (Access) table for lstUpdates?
I'm not a huge Access programmer but I do a lot of work with other databases. I used to program VB6 many years ago so my VBA is fairly reasonable.
I'd appreciate your views on this odd thing...
I'm building a system for a transport firm, using MS Powerapps which integrates to an Access app in the office. The system uses a number of Sharepoint lists as the point of integration, using Office 365. The Access DB is not currently split during development but it will be.
In brief, the drivers can book on or off via the mobile app, they are given scheduled jobs for each day and can update with their progress as they arrive/depart each job. Back in the office, Access links the lists read/written by Powerapps and the info is written to local tables(s) so it can be displayed in near-real time to a number of agents, who can also pull up historical data.
Powerapps writes, for each of these updates, a record to a Sharepoint list called lstUpdates; marking the UpdateStatus field 'PENDING'.
The Access app in the office has a 'Dashboard' form that, every 30 seconds, polls the linked Sharepoint list with a SQL select query to see if there are any new updates i.e. any marked PENDING. If there are, it does whatever it needs to do such as writing them to a local table (LogEntries), and marks them as READ via an Update SQL query.
It all works absolutely fine except for one niggling issue!
Below I have reproduced the code from the Form_Timer event so you can see what is going on. You'll note that I am getting the system ticks from the Operating System (kernel32.dll) at the start and at the end, so that I can measure how long the whole poll and processing thing takes, and it writes that to debug.
Now here's the issue. When I first start Access and the app, the polling times come in at around 50-70mS. But after an hour, they have gradually increased to 300-400mS. After several hours, they are up in the seconds and the system is showing signs of stress e.g. unresponsive UI, endlessly twirling blue circle, forms disappearing from the screen, etc. Monitoring Access's memory usage, I can see that it starts at around 25MB but goes gradually up until after a couple of hours it's more than 100MB. And all this is under static conditions, i.e. no new updates coming in whatsoever- so only the select query on the SP list should be running every 30 seconds. The number of records in the SP List is static at about 12; there are only 40 records in the local table LogEntries.
What do you think is happening? I feel its some kind of resource leak; so in my code I am closing the recordset and database objects, is there anything else I should be doing? Are there better ways to access the data? Would it help do you think to isolate it to Access or Sharepoint by running a test using a local (Access) table for lstUpdates?
I'm not a huge Access programmer but I do a lot of work with other databases. I used to program VB6 many years ago so my VBA is fairly reasonable.
Code:
Private Sub Form_Timer()
Dim t1 As Long
Dim t2 As Long
Dim strReadSQL As String
Dim strWriteSQL As String
Dim db As Database
Dim rs As DAO.Recordset
Dim recordsAdded As Boolean
'get updates from Sharepoint Lists and transfer to tables as appropriate
t1 = GetTickCount()
lblUpdates.Caption = "Checking for updates."
recordsAdded = False
strReadSQL = "select * from lstUpdates"
Set db = CurrentDb
Set rs = db.OpenRecordset(strReadSQL)
If Not (rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
If rs!UpdateStatus = "PENDING" Then
'get update type from the UpdateType field
If rs!UpdateType = "BookOn" Then
'write to log entries
strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, LSTART, KILOMETERS, TRUCK, TRAILER) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!DateTime & "', '" & rs!Kilometers & "', '" & rs!TRUCK & "', '" & rs!Trailer & "')"
db.Execute (strWriteSQL)
'report to status label
lblUpdates.Caption = rs!Driver & " is booking on for " & rs!Journey & " with " & rs!TRUCK
End If
If rs!UpdateType = "BookOff" Then
'write to log entries
strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, FINISH, KILOMETERS, TRUCK, TRAILER) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!DateTime & "', '" & rs!Kilometers & "', '" & rs!TRUCK & "', '" & rs!Trailer & "')"
db.Execute (strWriteSQL)
'report to status label
lblUpdates.Caption = rs!Driver & " is booking off."
End If
If rs!UpdateType = "Arrival" Then
'write to log entries
strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, COLLECT_DELIVER, ARR_TIME) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!UpdateText & "', '" & rs!DateTime & "')"
db.Execute (strWriteSQL)
'report to status label
lblUpdates.Caption = rs!Driver & " has arrived at " & rs!UpdateText
End If
If rs!UpdateType = "Departure" Then
'write to log entries
strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, COLLECT_DELIVER, DEP_TIME) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!UpdateText & "', '" & rs!DateTime & "')"
db.Execute (strWriteSQL)
'report to status label
lblUpdates.Caption = rs!Driver & " has departed " & rs!UpdateText
End If
'mark it read
strWriteSQL = "update lstUpdates set UpdateStatus = 'READ' where id = " & rs!id
db.Execute (strWriteSQL)
recordsAdded = True
Else
'this one's already been done
'Debug.Print ("Update is marked as read.")
End If
rs.MoveNext
Loop
If Not recordsAdded Then
lblUpdates.Caption = "No new updates."
End If
Else
lblUpdates.Caption = "No records found."
End If
'close and tidy up
rs.Close
db.Close
t2 = GetTickCount()
Debug.Print ("Time: " & CStr(t2 - t1) & "mS")
End Sub