Help with slow linked tables

clut · Jan 13, 2009

Hi,

I have developed a small database which tracks projects, and items within a project.

Because the database may be accessed by 3 or 4 different people, I have placed a copy of the database (with all forms and reports removed) on a network drive, and the users have local copies of the database with forms and reports intact, and linked tables pointing to the server.

The problem is, their network is terribly slow. it takes about 15 seconds to transfer 500kb. That is ok, as the whole server portion of the database is only 560k, but when the users try to use the system, pulling data across from the linked tables can take up to 2 minutes (even just small tables with minimal data in).

I don't expect to get better performance than the poor network can cope with, but it is currently running far far slower than the network can cope with.

Is there anyway I can speed up the linked tables performance in access?

Many Thanks
Alan

alansidman · Jan 13, 2009

Sorry
Mis-read the problem. Ignore this.

Alan

SydneyGeek · Jan 13, 2009

One of the first things you need to do when running Access over a network is forget about pulling all the data at once. Here are some ways to speed things up:
1. Make sure that each user has their own copy of the front end, on their local drive.
2. Build a search form as the sarting point. Let users pick from drop-downs or list boxes so that you get to a single record, then open the data entry form filtered to that record only.
3. If you have combo boxes that rely on large lookup tables, consider putting those tables in the front end (on the user's local drive) if they don't change much. That way, you aren't fetching huge amounts of data across the network every time you populate a combo box.

There are plenty of other ways to speed things up but these will be the quickest wins for you. For good book resources, check out
Building Access Applications
Access Cookbook

Denis

clut · Jan 14, 2009

SydneyGeek,

Thanks for your advice.

All users do have their own local copy of the interface, which has been built in a way that it pulls back only the data it needs at any one time.

The database is only 560k at the moment, and the first screen, which only pulls back a few columns from a single table (probably about 10% of the data in the db, 20% max) already takes 3 or 4 times longer than it would to transfer a whole 560k file over the network.

Given the above, even if the interface wasn't just trying to pull the data it needed, and instead was trying to pull all of the data across it should still happen much faster than it currently does.

I am exploring the possibility of moving the location of the database to a faster network location, but it would still be good to resolve this issue.

Thanks.

SydneyGeek · Jan 14, 2009

Something that could help, is to go to Tools > Options and turn off Name Autocorrect. It's on by default and is both a resource and performance hog, and a potential source of corruption.

I'd do that first, then see if you can have the back end database put on a network share that doesn't have a lot of other activity.

Also, you said that the main form pulls back about 10% of the data. Seriously, that is too much. I prefer for the first form to be a completely unbound search interface, and then only ever open one record at a time on the main data entry form.

Denis

claymation957 · Jan 14, 2009

I seem to recall the depth of your directory tree (to your network BE) having a real impact also- i.e. the deeper the file is in the folder structure the more of a penalty you pay.

SydneyGeek · Jan 14, 2009

True, I'd forgotten that. Yes, that can be a major factor. Try to avoid going more than 2 levels down from the root directory.

Incidentally, that means that My Documents and the Desktop are not great locations either, because they are usually buried deep down the tree.

Denis

james_lankford · Jan 14, 2009

seriously ??
I've never heard that before
I don't know a lot about access though
why in the world would that make a difference?

let's say the database was 10 directories below the root directory on the network, and let's say each user mapped a drive letter to that 10th level folder instead of to the network root directory

so it's x:\mydatabase.mdb
instead of h:\dir1\dir2\dir3\dir4\...\dir10\mydatabase.mdb

would it spped it up ?

SydneyGeek · Jan 14, 2009

I'll see if I can dig up the reference, but there is some useful information in these links:
http://support.microsoft.com/default.aspx/kb/889588
This page mentions the levels. Apparently it's been known for a while, just not broadly.
http://www.granite.ab.ca/access/performancefaq.htm

Denis

clut · Jan 14, 2009

Thanks for all your replies:

The databse is stored two levels down in the network, and the interface is stored locally, 1 level down (although I imagine the interface location would not matter).

I have created a shared network resource that sits in the same building as the majority of the people who will be using the database. Whilst the resource is on the same slow network as before, the router upstairs is intelligent enough to bounce all the traffic straight back and not direct it half way up and down the country needlessly.

Help with slow linked tables

clut

Board Regular

Excel Facts

alansidman

Well-known Member

SydneyGeek

MrExcel MVP

clut

Board Regular

SydneyGeek

MrExcel MVP

claymation957

New Member

SydneyGeek

MrExcel MVP

james_lankford

Well-known Member

SydneyGeek

MrExcel MVP

clut

Board Regular

Similar threads

Forum statistics

Share this page

Help with slow linked tables

Board Regular

Excel Facts

Well-known Member

MrExcel MVP

Board Regular

MrExcel MVP

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock