Slow database over network.

[BBrian]

I posted about this before and have learned a good bit since, but the database is still slow!

I've taken over from another IT guy who designed the database that's being used in a school. The data itself is sitting on a Windows 2008 server and clients are Windows 7 using Access Runtime 2010, though when tested with XP, things were also slow. It is also nice and fast when opening the .accdr on the server itself.

I've been reading the granite.ab.ca Microsoft Access Performance FAQ but it's not quite clear enough for me.

When the symptoms encountered indicate that performance is acceptable with a single user in the database but drops significantly when two or more users are in the database, the problem may be caused by interaction with the LDB file.

I copied the database to my VM and to a Windows 7 computer for testing. When the .accdr and the database were on the same machine (i.e. I mapped a local drive) performance improved significantly. When I mapped the Windows 7 shared folder and accessed it over the network, performance was poor. So it doesn't quite fit with the above quote, but I'm trying every fix I can.

I created a form, called frmKeepOpen, based off a table that only has four entries. Then in the opening page's OnOpen event, I added "DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden". When I closed and reopened the (now renamed) .accdb, it wouldn't open.

If I understand correctly, that command is trying to keep a bound form open the whole time the .accdr is being used. As it stands, our welcome page stays open all the time. Is this not adequate?

I've recorded a video of how slow the database is (to show and to measure times). It took 50 seconds to open .accdr to welcome screen and 3 minutes more to open "add student" form. I put some breakpoints in the VBA of the add student form and the delay is all before the VBA starts to execute. The student list form doesn't take long to load. Doing any kind of database editing is slow.

youtube /watch?v=P0jrLd2OE1E

Any advice/explanations would be greatly appreciated.

 

[CBrighton]

As long as your welcome form is bound to a table in the backend, this should be enough to keep a persistent connection open.

However, accessing something over a network will always be slower than accessing it locally.

I have extreme performance issues with split databases here (our servers are offsite) and I've pretty much given up on getting decent split db performance until I can convince the powers-that-be that an SQL server is actually a good thing.


Still, GL with your problem. Hopefully you will have more luck than I have had (plus by the sounds of it you have more modern hardware and software, I am stuck on an xp machine with office 2k3 linked to a server on the other aside of the country).

 

[Simon_MT]

In your video when adding a Student it still takes time end if it does an EOF.

Hive off a segment of the data and put into a small file on the Server proper (not windows 7 shared directory) and persistently link to FE.

Best place for database a small volume and as high up the directory tree as possible. I try to assign a mapped Server Drive where the data actually resides X:/Data.accdb

For the poor fellow who would get better response times if he couriered the data requests - tell your boss that the site holding the data should get themselves a Terminal Server so you go to the data rather than the other way round!

Simon

 

[toddbailey]

I must agree Access is just a pain to work with.
I recently started a contract job the company uses Access.
Why is Access so buggy after over 10 years in development?

 

[Simon_MT]

There is no reason why Access has to be slow. You just explore ways to improve the performance. It can be done. I managed to get trans-Altantic connectivity and handle thousands of images, it was the later that required more bandwidth. So don't give up.

Simon