small linked database - unusably slow

[jflachman]

The performance on my database is so bad it is unusable.

I have a small database - It has 9 tables and very little data because it is still under development.

The database is split into a front end and back end. The back end is on the network, the front end is on my desktop.

When I try to open a table from the front end, the table can take 30-45 seconds to open. When I open the same table from the back end, still on the network, it takes ~1 second. Opening a form can take minutes.

I read that setting the subdatasheet to [None] will help. I changed the property and saved the table design. However, when I open up the table design again, it's says [ Auto]'. Consequently, I can't tell if this will help.

So, I have two questions:
1) Why are these linked tables so slow?

2) If this is about the subdatasheet property, why can't I change it to [None] and have it 'stick'?

Thanks for your help.

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[dhookom]

You can only change the subdatasheet property if you open the backend mdb. Your times still seem very long. There might be an issue with some antivirus application.

Duane
Hook'D on Access
MS Access MVP

 

[jflachman]

I am trying to chagne the subdatasheet property in the backend mdb. It still doesn't stick.

Some additional info:
- If I put the backend on my desktop, it works fine
- When the BE is on the network, my network bandwidth is still < 10-20% and my MSACCESS cpu utilization is < 10%


Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[dhookom]

Do you get an error message that the design view is read-only? Do you have exclusive use of the MDB?

Duane
Hook'D on Access
MS Access MVP

 

[jflachman]

There were three steps to my solution:
1) Turn off the autocorrect option in Tools/Options/General
2) In the Backend file, set the subdatasheet property to [none]. This required step 1 in order to work.
3) Use a UNC file path rather than a mapped network drive for the location of the backend file.

Ultimately, item 3) may cause problems later because of how our company does the network, but it works for now.

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[Ed2020]

There are some good Access performance tuning tips here:

http://www.granite.ab.ca/Access/performancefaq.htm

The persistent connection to the backend made the biggest difference for me.

I also made a big improvement by changing to UNC file paths for linked tables (\\ServerName\ShareName\Filepath...) rather than mapped drives ("DriveLetter:\ShareName\Filepath...). It may be worthwhile trying the same.

Ed Metcalfe.

Please do not feed the trolls.....

 

[GingerR]

have you COMPACTED?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html