Results 1 to 5 of 5
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Too Much Data, Slow Response times

    I actually have a tiny amount of data, I think my who db is 14mb. but my backend is in SQL Server and to long to explain what the setup is or why.

    Anyway I have in my db a "Transactions" tbl/qry/frm/rpt. which has all of the financial transactions for the last 3 years. (just like a checkbook register)

    it takes a little while to load and when doing anything in the form (sorting by category, or even scrolling) it is really slow.

    I really only need the last 12 or 13 months. or actually 90% of the time only the last 6 months. (there is also another area of the db with a similar issue)

    PLEASE!! I am not looking for ways to speed up my current qrys or improve my backend speed etc.

    for the purpose of this post I am just trying to come up with design ideas that will allow me and the other users to normally deal with the recent data and allow the full set of data when needed.

    current ideas are a separate menu item "Recent Transactions" "All Transactions" that would take you to different forms.


    or just a button on the main form that would toggle between recent and all transactions.
    or the button on the recent transactions form that would open a separate "All Transactions" window.

    the biggest problem is that I always try to design for the "lowest common denominator" and for this I definitely need to. if its not obvious people will be using bad data and I will get calls about how the data is not right etc.

    anyway, Just looking for some ideas,
    thanks for reading, and thanks for any ideas/help

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Initially, load the form with 6 months of data max as a default.
    Try this by querying the table with a date restriction on it.

    If it is still slow, then it could be the form design.

    If it improves add command buttons to reload the form with 12 or 18 months of data if the users need it.
    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
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Create on your database a view with only the columns and records you need and link it a s a table to your access application. If needed you can create a history form linked to the whole table and put a warning on it: "this can take some time".
    SQL server is the most performant actor in your environment, so try to do the filtering and most work there and only load the data you need into your access front-end

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I assume relevant fields are indexed in the back end? (fields for primary keys, foreign keyS plus those fields that are regularly used for searches/filtering, sorting and non relationship defined query joins)

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 10
    Last Post: 11-04-2017, 03:33 AM
  2. Replies: 4
    Last Post: 11-07-2014, 04:25 PM
  3. Replies: 8
    Last Post: 03-20-2013, 11:39 AM
  4. Slow response over VPN or To Filter or not To Filter
    By rcrobman in forum Database Design
    Replies: 0
    Last Post: 04-30-2011, 02:37 PM
  5. Slow Database Response
    By Nixx1401 in forum Access
    Replies: 3
    Last Post: 02-25-2010, 11:09 AM

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