kcwallace

I am stumped. I have an Access97 front-end running on a network that runs perfect from my computer. The problem is a form that takes 1 second on my computer will take up to a minute every other users computer. Can anyone explain this to me? I recently re-wrote all of the forms and underlying code hoping this would work, but it didn't. I have several other DB's running on the same network, but they work perfectly. I would attach the database but since you won't have access to our network it would be useless.

Timbo

if your computer is faster than the other machines then I suspect that you need to optimise your SQL and db relationships.
Large & nested queries, complex/multiple joins and unwieldy recordsets are usually the main culprits.

I'm also assuming that the front-end(s) reside on each users machine and not the network...

kcwallace

the front end resides on the network.

I tested it on on a 1Ghz w/256 Mb RAM machine and the performance was still slower. Mine is only 300Mhz w/192 Mb RAM.

I am confused.

Timbo

each user needs to have their own local machine front-end connected to the single back-end which resides on the network.
I'm confident that the app performance of th other users will equal yours once you do this.

kcwallace

I never run a copy on my, or any, local machine. When I open 1 paticular DB (out of 20+) from the server a form takes 1 second from my workstation. The exact server version from everyone else's workstation take about 1 minute. The other 20+ run at approximately the same speed as mine. This one ran perfectly until a week ago.

Running local versions of the database are nothing but trouble, i.e. versioning, etc.

Timbo

what happened just under a week ago?..

The suggestion of splitting the database in a client-server app is the standard in these situations. It may be that difficulties arising from the current structure weren't observed before due to the small size of the db. A weeks worth of heavy use may have seen an end to that...

diver

Let me throw out something that you might think is ridiculous, and that's okay...

I did a study last year at John Hopkins University, and learned some things from a guy who really knows his Access. I also ran into a guy at our sister company who also knows his Access. Both gave me big warnings. They basically said, "don't use Access on more than 20 machines, or you will be very, very sorry". Since then, I have questioned various consultants about this issue, and hands down, it appears that Access was not really designed to effeciently work over a network with that many users. I don't want to burst your bubble, it's just something that I have heard for a long time.

-Diver

MattVB

I have heard the same thing. Access starts to crumble after like 10 users.....

diver

Matt,

That's the interesting part. Microsoft claims you can attach 50 users, but I've been told that you want to plan for less that 20. You could be right, that it would start hitting the wall at 10 users, but it probably depends on the code, environment, etc.. Either way, when someone reaches this point, or if they plan to reach this point, there needs to be some serious consideration of using Access.

-diver