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

SomethngWicked

Board Regular
Joined
Feb 18, 2015
Messages
79
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Have you tried running Compact & Repair on the back-end?

PS You may as well do the front-end while you are at it.
 
Upvote 0
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
 
Upvote 0
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/
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top