Solved

Help me understand how MS Access works across a network

Posted on 2008-10-09
10
213 Views
Last Modified: 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
               ProcessOnforwards
               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
0
Comment
Question by:Rob4077
  • 5
  • 4
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22678770
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.

Regards,

Patrick
0
 

Author Comment

by:Rob4077
ID: 22678832
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.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22678983
I don't understand your results either.

However changing slightly to this eliminates all delays for me.

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

0
 

Author Comment

by:Rob4077
ID: 22679467
<<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.
 
0
 
LVL 77

Expert Comment

by:peter57r
ID: 22679575
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Rob4077
ID: 22679590
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
0
 

Author Comment

by:Rob4077
ID: 22679647
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?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 22679674
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(:-)
0
 
LVL 77

Expert Comment

by:peter57r
ID: 22679753
Obviously you can't use my approach if the field is not visible.
I would have thought that a variable would be equally effective.
0
 

Author Comment

by:Rob4077
ID: 22679762
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now