Access EXTREMELY slow on network [not responding]

[mister3]

I'm working on an Access aplication that works fine at home (on a network), but when I bring it into work, it is soooo slow. The work server is hardly being utilized 14% max. The no more than 10 users are all on 100 base-t NICS running on 100 base-t switches. Access is either real slow, and tends to hang and show [not responding] in task manager. Even just opening tables or forms can take a couple of minutes. I'm guessing there's a network issue going on, but before I spend a lot of time beating on the network, I'd like to make sure that there isn't something I should be doing to optimize access. Also, I have my Applications.mdb copied to the local hard drive of each machine, only the data.mdb resides on the server. I've seen some "timeout" related settings in the options dialog, but haven't been brave enough to play with them.

Any guesses,
Bill

 

[StewartGW]

Recommend you split your database application and place the database on the backend the objects on the front end.

The most common reason to split a database is that you are sharing the database with multiple users on a network. If you simply store the database on a network share, when your users open a form, query, macro, module, or report, these objects have to be sent across the network to each individual who uses the database. If you split the database, each user has their own copy of the forms, queries, macros, modules, and reports. Therefore, the only data that must be sent across the network is the data in the tables. I believe this is what is happening to your application.

You can go to the following site to obtain instructions on how to manually split a database.

http://support.microsoft.com/support/kb/articles/Q304/9/32.ASP

Gary

 

[mlocurci]

Will this work with Access 2000?

 

[mister3]

Gary,
I'm sorry, I didn't express myself well enough.

each user DOES have their own copy of the forms, queries, macros, modules, and reports. Only the .mdb with the shared tables resides on the server.

I originally laid out the project in this fashion thinking it should most definitely keep the speed up, but it has not had that effect.

mlocurci,
yes this is being done in access 2000

 

[Sawatzky]

Dear mister,

check how the locking-options are set in the database. if 1 user locks all the records in the tables he uses, you will surely have a timeout for the other people.

check how many tables are linked for a single query, the more linked tables you have , the slower it will be.


regards astrid

 

[mister3]

astrid,
I'll check the record locking (I believe "Default Record Locking" is set to "No Locks&quot.

Some of the queries do have up to 5 tables. I'll see if I can optimize those.

What's really weird is that Access will sometimes hang when I'm working on a form on the local drive when the links are pointing to data on the network. If I copy the data to my local drive, everything works fine.

 

[mulligh]

I had similar problems when I installed one of Microsoft's hotfixes. You might see if you have installed any around the time this started happening.

 

[mister3]

mulligh,
We applied mso2000 sp2 around the beginning of the project, so there wasn't really any baseline on this network to compare to. There was some weirdness where we had to copy msjro.dll onto all of the machines because even after applying sp2, it was really old and not registering with VBA.

Sound familiar?
Bill

 

[mailzero28]

I experienced the exact same problems recently. I'm sorry if I'm looking at this in a too simplistic way, but this is what I did:

I moved the data tables to a Partition on the server that was doing nothing (the previous partition was the mail server).

I then created a blank database, and imported into it all the forms/queries/reports and macros and then re-established the connection to the data tables - It was amazing how much crap was left behind in the original file.

Finally, I made the application program compact on closing - thereby ensuring it was as fresh as the day it was born.

I don't know if this will help - but the client has noticed a very dramatic increase in performance and is now happy with his program.

Just one other thing - Is your server which the data.mdb resides running a screen saver? Apparently, this can degrade performance by up to 40% (well so Eddie told me - but he can be a bit of a bluffer some times).

bye

Martin.

 

[mister3]

Martin,
I've been extremely obsessed with compacting and repairing both the application and the data databases. I've seen the improvements that can come from that. I've never actually copied everything into a blank database, if time allows, I'll try that tomorrow.

As far as being on it's own partition, can that help even if the server seems to be sitting around picking its allegorical nose? Server utilization is usually 0%-4% with peaks as high as 20%. I was assuming it was a problem on Access's part, but I can investigate what else is running on the server.

Thanks,
Bill