Help me understand how MS Access works across a network
Posted on 2008-10-09
I have just tried testing an application that has a backend on a server that it needs to interrogate regularly and have run into a problem that I don't understand.
I have created a form that acts as a console providing the user with information that is loaded into the database by another application. The other application checks the table every 15 seconds too but in my testing only reads the table but does not add any records. It's 15 second event timer is not sychronised with my form's event timer. My form runs with a Timer event that triggers every 60 seconds to read some tables. The code I am using in that application is shown below. The form it runs on is bound to a table with a single record. The Debug.prints are put in there to provide me with an indication of where the bottlenect is arising. I have made notes <inside these marks> below to provide some detail on what is being interrogated and the result. I am puzzled as to why this process is taking so long to read and where the bottleneck is happening. Can anyone help me understand how Jet reads data and why I am having these bottlenecks? Eg does Jet completely re-establish its link with the BE and drag heaps of data across the network once it starts an interrogation or what?
Note I declare some variables that I need to use in a later part of this timer but since this first part is taking so long I have dropped the rest fo the code for now. The total code takes about 4 seconds and the part that I have excluded from my initial testing is far more intensive than the code I am using in my test.
Private Sub Form_Timer()
Dim dtimer As Double
dtimer = Timer()
Dim rs As Recordset
Dim rsZ As Recordset
Dim sSql As String
Dim lOutgoing As Long
Dim lErrors As Long
Dim lMessageId As Long
Dim sMessage As String
Dim sMobile As String
Debug.Print "Start " & Timer() - dtimer '<Answer is always O on every loop>
'On send messages if necessary
Me.CountOnforwards.Requery ' <Field contains the following code. The table being read contains 9 records.
=DCount("FolderId","Queue","FolderId =20 and OnSendHandled = false") >
Debug.Print "X " & Timer() - dtimer '<result is inevitably around 0.02
If Me.CountOnforwards > 0 Then ' <Me.countonforwards totals zero in my test the Then clause never exectes
Debug.Print "Y " & Timer() - dtimer
Debug.Print "Z " & Timer() - dtimer
Debug.Print Me.CountOnforwards '<The result in my tests is always 0
Debug.Print "A " & Timer() - dtimer '<Basically nothing has happened but the result is around 2.5 Why??