Results 1 to 12 of 12
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    Query backend data

    I have an application that is reading backend data off a server and now with 3 users on the network the lag time between the user asking for data and the screen refreshing with the data is very slow.

    The backend holds only the tables with the data,
    The front end holds everything else.

    what we noticed is the data being received back to the user from the backend is in the 3mb range (while the data xfer to the backend server is in the 20kb range.
    This explains why the user's request seem to get to the server quickly but the reply back from the server seems to lag a long time. From what i understand this is because the user is requesting some data off the server through a query that is on the front end. The query is accessing a data off of a couple of tables that are stored on the backend. I believe the backend is sending a the whole dbase back to the front end - which filters it through the query showing the user the requested data. If this is what is happening it would make sense to run the query on the server (backend) and only send the user the filtered data (much smaller amount of traffic). The problem with this is Access doesn't seem to allow you to link to queries that are stored on a backend database (you can only link to tables).

    So how do you get the file size lower on the receive side of the transaction? Is there a way to query the data on the backend before sending back to the user over the network?

    I only have 3 users on the network and i have 4 backend data files each smaller than 33kb in size. Front end is 90kb in size. Backend data is .mdb format, front end is .accdb format

    thanks
    Steve

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I designed and managed a split database with up to 10 users and never experienced significant lag. Each user runs their own copy of frontend. Frontend 25MB and backend 156MB.

    Are you sure about your file sizes? Seem awfully small.

    Why not convert the backend to accdb? However, don't know if that will make a difference.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your lag may be an attribute of your network, perhaps it needs some "adjustment/tuning".
    It may also be related to your table design, indexes and relationships.
    Here is a link with a variety of performance improvement tips from FMS that may be a good reference to see how your set up compares.

    Good luck. Let us know if you resolve the lag issue.

  4. #4
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Is there a way to filter the data before sending back to the user?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why do you have mdb and accdb? You might want to migrate to accdb as June suggested.
    I don't know if that will resolve anything, but you will be working with a common design/structure.

    Have you spoken to network or technical people who manage your IT hardware/networks?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not sure. I have heard of 'pass through queries' and 'stored procedures' for SQLServer backend but never used. And since your backend is Access probably not applicable. Review https://support.microsoft.com/en-us/...uery-in-access and http://www.databasejournal.com/featu...oft-Access.htm

    If your files are truly that small you should not be seeing this lag issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I guess there's no chance the issue is due to the connection being made over wifi?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    I have all the terminals wired into the network (no wifi) we thought this may be the issue....so we hardwired....
    i'll check the other ideas given - thanks for all the feedback guys!

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    I've indexed all the tables the analyser suggested and that seemed to make little difference,

    The data xfer over the network is reasonable i believe - the largest receive rate I show is 3mb/s back from the server. From what i understand this is relatively low.

    Is there anything else that I can check?
    Doesn't seem to matter what computer(s) we're using, there are only 3 on the network
    There are 4 backend databases i'm linking the front end to and they are all smaller than 40mb in size.

    Two of the wkstns are new computers running WINDOWS 7 PRO, service pack 1, 64bit operating sys, MSOFFICE 2010
    1 laptop running WINDOWS 7PRO service pack 1, 64bit , MSOFFICE 2010
    server is running Windows 7 pro; service pack 1, 64bit, MSOFFICE 2010

    Can anyone suggest what else to try?

    much appreciated,
    Steve

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We went from one back connected to a FE to four? I've never done that many links, so perhaps those that have can comment on the effect, if any.
    Even though we're talking about hard wired network, I presume there's no VPN involved either.

    Here's something to note about pass through queries (esp. the 2nd reply):
    https://social.msdn.microsoft.com/Fo...orum=accessdev

    Before going too far, I'd try some simple Select sql statements that involve only one table, don't return too many records and contain no IIF, NZ or similar functions and see what happens. Apparently, anything specific to Access query language can result in all records being returned to the FE for processing. How many records in these tables anyway?

  12. #12
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Quote Originally Posted by Micron View Post
    We went from one back connected to a FE to four? I've never done that many links, so perhaps those that have can comment on the effect, if any.
    Even though we're talking about hard wired network, I presume there's no VPN involved either.

    Here's something to note about pass through queries (esp. the 2nd reply):
    https://social.msdn.microsoft.com/Fo...orum=accessdev

    Before going too far, I'd try some simple Select sql statements that involve only one table, don't return too many records and contain no IIF, NZ or similar functions and see what happens. Apparently, anything specific to Access query language can result in all records being returned to the FE for processing. How many records in these tables anyway?

    thanks, I"ve had 4 backend dbases for years without this problem so i didn't feel it was worth mentioning . I'll go through the links you are giving. - thanks again

Please reply to this thread with any new information or opinions.

Similar Threads

  1. updating new records w/ old backend data?
    By willmafingerdo in forum Queries
    Replies: 5
    Last Post: 01-26-2017, 09:02 AM
  2. Cannot Access Backend Data
    By JimmieC in forum Access
    Replies: 9
    Last Post: 02-12-2015, 01:17 AM
  3. Replies: 3
    Last Post: 01-02-2014, 08:11 AM
  4. Protecting backend table data
    By uncletreetrunk in forum Security
    Replies: 1
    Last Post: 07-30-2012, 09:51 AM
  5. Can't delete data from Backend
    By srcacuser in forum Access
    Replies: 2
    Last Post: 03-21-2012, 01:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums