Access Database takes a long time to pull data across the backend tables?

[SomethngWicked]

Hi Access Pros -

I have an Access database that is split up into two parts. A front end database that contains a form that is used to query information and a back end database that sits on a network share. It was designed this way as a means to prevent data loss in the event of a hard drive crash.

The problem is this Access database has grown significantly since it was first created as it's constantly having new user information added (it's now almost 150mb in size and it was only 20mb a couple years ago). So, this data is starting to crawl across the network and it can take anywhere between 5-10 minutes to open and then another 60 seconds to return a result. Is there any way to speed up this process? It's set to compact on close, but I'm not sure where else to really troubleshoot as I'm not too familiar with Access.

Can someone help point me in the right direction? Thanks!

 

[Norie]

Have you tried running Compact & Repair on the back-end?

PS You may as well do the front-end while you are at it.

 

[james_lankford]

there was some post on here years ago that said something like

if your front end database linked to a backend database at //something/something/something/something/database.mdb

then it would make a huge improvement if you went to the client and mapped a drive to the network location

so if you mapped the X drive to //something/something/something/something/

and then relinked the tables so that they'd be linked to
X:\database.mdb

you'd see a noticeable improvement

I called BS at the time but there were a couple of people more knowledgeable than me who said that it was actually true

so .. maybe give that a try

 

[welshgasman]

I'd be looking at how the BE performs if on the same computer to get some sort of feel of where the problem lies. if still slow, then have you indices where needed.?
When your DB was small it probably wasn't evident, but as it grows, that could cause a problem.
If it is blindingly fast, then your network is to blame possibly?

There is also something about keeping a connection open all the time on login, rather than connecting each time.? Persistent Connection

https://www.devhut.net/2012/09/29/ms-access-persistent-connection-in-a-split-database/

 

[Micron]

so many potential causes; see https://support.office.com/en-us/article/help-access-run-faster-f6827763-bb5c-4f48-8457-7a14addab6be

You could try seeing what happens when both parts are on your local drive (be sure to relink the be to the local fe or anything you do in the fe will affect the original be).
If nothing suggested helps much, see if there are any other complaints regarding speed. The network architecture maybe outdated. One time, I reported similar issues to the IT department. Turns out someone there had reconfigured the server to allocate more resources to their own pet project. Once that was fixed, it was fine. Just saying the problem could be way up stream.