none
MS Access FRONT END is getting slow, especially when switching views (Design View --> Form View, etc.) RRS feed

  • Question

  • Hey guys, my front end has the compact and repair option on close, but it's very slow when I'm editing it. It's also getting slow for users. It's a pretty massive database, it's a one-in-all shell for multiple departments in a facility with over 5000 employees, all entering data simultaneously. Is this considered "normal" for Access? Is this what people talk about when they say "Access has it's limitation." Any solutions to make the database more seamless? Would placing the BE on SQL make it better? Should I get into learning a different/better front end?
    • Edited by InnVis Thursday, September 5, 2019 11:46 PM
    Thursday, September 5, 2019 11:46 PM

All replies

  • You could review and implement http://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/

    Compact on close is typically to be avoided.

    Is your database split?
       Does each user have their own copy of the front-end?
    Do you create/establish a persistent connection between the front-end and back-end at the very startup of the front-end?

    Also, all design work needs to be done on your local PC and never over the network.  So you need to copy the front-end and back-end locally, relink the tables and then perform your development.  When completed, relink the front-end to the production back-end on the server and deploy the new version of the front-end to each of your users.

    SQL Server can address many issues, but speed isn't always one of them.  Furthermore, an Access front-end needs to be designed/optimized for SQL Server differently than for an Access back-end, so the migration may require work.  I'm not saying it isn't something to consider, but not something you just jump into.

    "It's a pretty massive database, it's a one-in-all shell for multiple departments in a facility with over 5000 employees, all entering data simultaneously."

    How massive is massive?  What is the current file size of the back-end and front-end?

    If you truly have this quantity of users, you do need to migrate to a more robust back-end such as SQL Server (Express edition is free), MySQL, PostgreSQL, ...


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Thursday, September 5, 2019 11:54 PM
  • The database is split and the backend is sitting on a network. The users have their own copy of the front end. What do you mean by a persistent connection at the start of the front end? I use a navigation form for the shell and each tab has their own set of data tables. By MASSIVE I mean we enter a quarter of a million records per month, each record has approximately 20+ data points, I divide the backends by department to get around the 255 simultaneous user cap until we can migrate to SQL. Every month and a half I have the backend saved on a server and my users get a fresh backend. I'm currently working on putting my database on SQL, but I'm getting through the learning curve first. I work for one of the largest hospital systems in the U.S., my background is medical and I am self taught at making Access databases. It began as a small project to make my life easier, it then turned into a small departments, now I work with logistics to develop agile tools for all business operations and clinical data analysis.
    Friday, September 6, 2019 1:59 AM
  • To test if your data is slowing you down just remove any tables from your forms...simply delete their Recordsource and test it..it will surely show "#Name?" everywhere...but does it feels fast again?...start adding back the RecordSource....it will show what is slowing you down.


    Friday, September 6, 2019 5:18 AM
  • See Creating a Peristent Connection in MS Access

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, September 6, 2019 12:31 PM