Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help me understand how MS Access works across a network

Posted on 2008-10-09
10
Medium Priority
?
237 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 93

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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

877 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