Slow Access file

Question

We use Access for our CRM with 4 users each using a 'Client' file of their own which is located on the server and all the tabels are on another file on the same server. It works fast with just one user but as soon as anyone else joins it slows down and we get 'not responding' for 10 seconds when opening records. We are all using current MS365 accounts. Our IT support has ruled out firewall and trust centre problems and say the error is in the Access file, our server is new and works well. The Access programmer man we use says all table are up to date and clames the fault must be with the server. I regually run compact and repair but that doesn't help. Has anyone got any more suggestions?

Answer 1

To conclued this post. It seems that we have fixed it now. Working with my Access developer man, we have upgraded from .mdb to .accdb. Then we deleted 4 rouge records on the main table that were in error, then we set up the Job number field to Indexed which is the number the form feeds from. I can't say which of the above has fixed it because the problem comes and goes, but it's been good for 3 days now, so hopefully we have a fix! I compact and repair regually of course. Thanks all, for the helpful tips!

Answer 2

No words.

A Persistent Connection is a best practice with any Access database. I'd be worried if my developer hasn't automatically implemented this to begin with.

Here are some of the things that your db should have, or not,

https://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/

https://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/

Also, a simple test would be to open the database, create a persistent connection (open a small bogus table), then open another table. Is that action fast or slow? If the tables open fast, but forms are slow, then you know the issue is with the forms/code. If the tables are slow to open, then there's a network/connection issue.

Be sure to use UNC or IP paths when creating linked tables, not mapped drives.

You could delete all the linked tables and recreate them all. Sometimes a little spring cleaning can help.

Answer 3

Do look into and implement the Persistent Connection, that alone can have a drastic impact on performance in certain scenarios.

Answer 4

Thank you Daniel. We will try putting the front end local because they are currenly all on the same server in different locations. Start up every morning is fast, no problems, then we leave the file open all day as it's aleays needed. Other MS applications work fine. We are all hard wired to a server in the building.

Answer 5

The database must be split, which you indicate it is. The backend (tables) is to be placed on a server accessible to all. The front-end (user interface) is then to be distributed to each user and placed on their local PC (not the server).

As for performance, there can be numerous causes, but I always start with asking if the front-end establishes a persistent connection at startup. Then you get into form complexity, database structure, indexing, ...

As a general question, how do you find your network generally? Is it fast at opening large Word or Excel documents?

You are running on a wired LAN network?