Results 1 to 6 of 6
  1. #1
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150

    MS Access - Interface design with performance in mind

    Hi

    My application is not deployed over a network yet. I do my best to anticipate performance concerns in the mean time. But i have some questions, given that I have
    never deployed an app over the network.

    Let me make some assumptions for you:

    SQL server or regular Access backend
    Assume "average" or above average network speeds
    small business environment

    Now to my question specifically, a common interface i use in my forms (unbound) is a listbox selection to dynamically allow the user to browse data. See the image attached.


    Click image for larger version. 

Name:	Screen Shot 2019-09-18 at 2.31.05 AM.png 
Views:	23 
Size:	15.6 KB 
ID:	39755



    In your experience, with the interface below, would this be a somewhat laggy experience for the user? Would it take a discernible amount of time to load the contact info in the textboxes based on the
    user's selection? If they were browsing through the list quite aggressively, would this be uncomfortable?

    This would of course be loaded using DAO recordsets with VBA and querying the data directly from the backend as the user changes selection. Not many fields to return, maybe 10 fields of short text data type in most cases.

    My anticipation is that this will be somewhat laggy over a network. Maybe this is not true. But i have been remedying this solution by loading a TEMP table when the master record is loaded. The user reads/writes to the temp table


    until the user finally saves the master record and writes the temp table changes to the backend before closing. Is this a waste of time?

    Perhaps I can provide a better understanding with a sample. I will await feedback.

    Thanks for your help!

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If there is only one or two tables involved in loading the information I would make the contact info a sub form and simply link it to the listbox PK.
    I have forms with this approach with thousands of records from SQL server over a internal LAN and it works seamlessly. There is no obvious "lag"

    If you have dozens of sub forms put the ones that aren't viewed frequently on tabbed pages, and only load them when the tab is selected to save pulling the information across.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Minty,

    Thanks for your reply.

    But have you used this method via DAO recordsets in VBA or only strictly with bound methods?

  4. #4
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    UPDATE:

    So i just ran a test. I have a NAS server on my network and i built a quick test database with the backend on the NAS SMB share. It seems to be pretty impressive on my home network which is medicore (85Mbps/7Mbps).

    Running an initial query of searching for 10 of the same unique name (short text field) across 20,000 records completed in like a second and actually cycling through the data like the form above suggests, it did it instantaneously.

    Perhaps now i won't mess with creating temp tables.

    If anyone else has any confidence-instilling expertise on network performance and good design practices (that are not so obvious), im curious to listen.

    Thanks.

  5. #5
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If it helps with the "confidence levels" one application I maintained using the same techniques and was used by up to 30-40 people simultaneously within the building.
    My only recommendation is to try and avoid the use of wireless connections, more so if data updates are required.

    There are occasions when using a DAO recordset will have some use but generally if you have sql backend make use of views and stored procedures to do the heavy lifting server end, and you should be fine.

    Another hint - only load data you need.
    Load a bound form with an empty record then populate based on user input. (Something like Select * from YourTableorQuery where PK =0 )
    This prevents access pulling the entire recordset over the network.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming the database is built correctly (normalised, proper indexing) your biggest drag on performance by far is the network.

    As Minty says, push data processing to the back end and minimise the amount of data brought back across the network. So in addition to form recordsets, don't forget listboxes and combo boxes. And with regards subforms, don't use the linkmaster/child properties - set criteria and repopulate the subform as required.

    Note there may be occasions where you populate local tables or recordsets when the app is first opened to reduce network traffic. The rules I apply are a) the data is unlikely to change on a regular basis, b) it is not used in subsequent dao queries (OK to use in passthrough and stored procedures as a parameter) and c) is used frequently. Examples might include queries used for combo/listboxes.

    There are some caveats - sql server can be 'tuned' to favour certain processes over others - perhaps a regular update run of data from ETL will take priority, this can have an impact on the performance of your app whist this process is underway. A recent client had a similar process run 4 times a day. Sometimes it took 5 minutes, sometimes 15. Whilst underway the app effectively froze and frequently timed out. The start time for these updates was known so at the appropriate times I had the app flag up a 5 minute warning and then minimised what the user could do for 15 minutes. I'm sure the issue could have been resolved with investment in more kit, but IT did not have the budget. Also made use of begin/commit trans where appropriate.

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

Similar Threads

  1. help needed with user interface design
    By ntambomvu in forum Access
    Replies: 2
    Last Post: 10-20-2017, 04:07 AM
  2. Performance of access
    By tcox in forum Access
    Replies: 5
    Last Post: 01-07-2016, 08:43 AM
  3. User Interface Design
    By bchankent in forum Access
    Replies: 5
    Last Post: 12-15-2015, 09:20 PM
  4. Replies: 3
    Last Post: 07-23-2011, 06:29 AM
  5. Poor performance in design mode after split
    By sprovoyeur in forum Access
    Replies: 1
    Last Post: 04-13-2010, 03:25 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