Help me understand how MS Access works across a network

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
    End If
               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??
    Exit Sub
End Sub
Who is Participating?
I don't understand your results either.

However changing slightly to this eliminates all delays for me.

  Me.CountOnforwards.Requery  '  
  Debug.Print "X " & Timer() - dtimer     '
    If Me.CountOnforwards.text > 0 Then   '      
               Debug.Print "Y " & Timer() - dtimer
               Debug.Print "Z " & Timer() - dtimer
    End If
               Debug.Print Me.CountOnforwards.text          
                 Debug.Print "A " & Timer() - dtimer      
    Exit Sub

Patrick MatthewsCommented:
Hello Rob4077,

In Access, all the processing occurs on the box running Access.  So, if I am running the front end on my PC
and the Access back end is on a server, Access still has to fetch the data across the network to my PC for
processing.  If you are using large data sets, that can be S  L  O  W.

If performance is unacceptable, several of my clients have successfully tried putting both front end and back end
on a server, and then using Citrix / Remote Desktop / pcAnywhere etc to connect to the server.  That avoids
having to push and pull the data across the network.

Another option would be to go to a real server-based back end like SQL Server or MySQL, and using only
ubound forms, pass through queries, and sprocs to keep all the data handling on the server.  This is how
an ADP works, basically.


Rob4077Author Commented:
Thanks Patrick,
I understand that but I am still confused why it is taking practically no time (0.02) of a second to requery a Dcount and then another 2.3 seconds to test the value of the field containing the result of the DCount (If Me.CountOnforwards > 0 Then ) in a table with only 9 records and about 7 fields.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Rob4077Author Commented:
<<However changing slightly to this eliminates all delays for me.>> Did you actually emulate this on a network somehow and get the same result? All you seem to have done is inserted a SetFocus. I wonder why this is happening? I won't be able to test your result till I get back to the office after my holidays so I am curious if you have actually tested this on a remote database that enables you to confidently say this.

I have been thinking about this further (while lying in bed trying to sleep) and I had to get up to add a comment: Does MS Access drag all the data  for ALL linked tables accross the network when it refreshes automatically based on the Refresh Interval Setting EVEN IF there is no query requesting information being processed at the time? The reason I ask is that under the Refresh Method help it says "Microsoft Access refreshes records automatically, based on the Refresh Interval setting on the Advanced tab of the Access Options dialog box, available by clicking the Microsoft Office button and then clicking Access Options". Since that setting on my machine is 60 seconds, and my timer interval is also 60 seconds and both are managed by MS Access, could my requery be slowed down because MS Access is dragging all the data from all the linked tables in the database accross the network while my timer event is running? Or perhaps is it dragging across all the data for all currently opened forms (Keeping multiple forms open in MS Access 2007 is now easy and convenient with the tabbed option) Or is that not how it works? Peter, your comment would seem to make my thought wrong.
Yes. I tested it on a split database with the backend on another machine.

Access does not transfer table data unless it is needed by the application or by Jet to service the application.
If it can Jet just gets indexes and uses them to identify which data iit needs.

Just because a table is linked, it doesn't mean that refreshes are constantly taking place.  Data is refreshed only if you are looking at it in some way.
Rob4077Author Commented:
Oh and I forgot to mention too that the field CountOnforwards is an invisible field in my case. Originally the code read << If DCount("FolderId","Queue","FolderId =20 and OnSendHandled = false") > 0 then >> but that took even longer so I created an invisible field as an option to see if it was any faster.
And I should have noted that you did change from Me.CountOnforwards to Me.CountOnforwards.text - again I wonder why that would make a difference
Rob4077Author Commented:
Thank you very much for going to the trouble of testing it on a live backend.
So based on your comments, it must be the combination of setfocus and text that makes the difference. However since my count field is invisible (the user doesn't need to see it) I wonder whether saving the dLookup result to a memory variable then using that in the "IF..Then" clause will have a similar effect on speed or do I need to stick to the way it's set up and just make the field both visible and enabled so that your suggested code will work?
That was a pure guess on my part.
I was wondering whether some re-evaluation might be going on so i used the text property to limit the operation to the current contents of the textbox.

It was this action that reduced my elapsed time to zero.  BUt I don't understand why!!

In your position I might be thinking more about my holidays than all this rubbish(:-)
Obviously you can't use my approach if the field is not visible.
I would have thought that a variable would be equally effective.
Rob4077Author Commented:
Fair point but it will ruin my holidays not knowing how to solve this problem on my return. Anyway since your approach all but eliminated the delay I will change my code to your suggestion on my return and hope I have the same success you do.
Again thank you very much for taking the time to test it. I'm also relieved that you appear to have been able to replicate the original problem so it's not something else quirky that I am doing.
I am now off to bed to have a sleep - it's midnight here after all.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.