Problems with mutli user Access front & back end - help!!

[DanEllacott]

Hey people,

This is all fairly new to me so sorry, if I'm in the wrong area or if this question is asked elsewhere (I did try to find an answer).

We use an Access front end and back end.

The front end is 76mb and the back end is 250mb (ish).

The back end contains 56,000 records in the main table but is made up of multiple linked tables about 65 in total. Bundles and bundles of queries and stacks of forms...

We have about 12 users who need to access the db during the day. Some are running Access 2000 some 2010.

We have issues with forms being non-responsive, corruption and sluggish response times when queries are run.

Are we pushing Access to its limits?
Are we causing the problem ourselves by having different versions of Access?
Any suggestions?!

Thanks for any help or suggestions that you might have...

 

[Pat Hartman]

I'm going to guess your app is an .mdb or the A2k users wouldn't be able to use it.

Make sure each user has his own personal copy of the FE located on his hard drive. Users should NOT share a single copy of the FE. Only the BE is actually shared.

I think the issues are more likely caused by file sharing between multiple versions of windows but in any case, consistancy is best.

 

[DanEllacott]

Hey thanks for that!

Each user has a FE on their own hard drive locally, and yes it's an .mdb

 

[thommo41]

We had HUGE problems doing something like this, when we had the backend on a Novell file server. Are you running the backend on a Microsoft server? What is the O/S for the backend server? To be fair, our files were never that sort of size (back end less than 20MB, front end much bigger as it had all the forms, graphics etc).
As the above post, make sure the front ends are copied to each end user, but you covered that off. With our Novell server, we had lots of tweaking parameters that could be set, maybe your backend server has similar scope for tweaking? Basically, a server that serves word documents is optimised way different to a server purely running a C/S database...

 

[DanEllacott]

Thanks thommo41.

Our server is running Windows 2003 R2.

I guess we've got too many variables here? Old server OS, various client OS, various versions of Access...

????

 

[pr2-eugin]

Dan I had similar problems with Access in different versions of Access.. I found this website, followed some/most of the steps for Improving Access Performance.. See if this helps... Specially,
- Subdatasheet Name property set to [Auto]
- Track name AutoCorrect
- Use of DSum, DCount, DLookup, etc in form after splitting

 

[DanEllacott]

pr2-eugin nice one, will give it a look. Thanks.

 

[thommo41]

Dan, if it helps, since troubleshooting our issues, our server OS is win2003 r2 and we have no issues now!