Recommended solution to multiple users editing same record?

[Gary Childress]

Hi all,



First off, thanks everyone for all the great advice and helpful posts and information here. This forum is such a great resource!

I'm looking for a suitable solution to the problem of corruption and error messages resulting from multiple users editing the same record simultaneously. We are using Access 2010. I currently have a single database file on the server. I and one other person both have shortcuts to that database on our computers so that we can both use the database. However, if we both happen to be in the same record at the same time and make changes, one of us will get a series of annoying error messages asking if we want to "save to clipboard" etc. I've heard that spliting the database into "front end" and "back end" can help with this problem but do not know the steps for how to set things up that way. Would that be the best way to avoid error messages and corruption? Are there any other solutions anyone can think of? What would be the best solution? I saw where one person on the net is using an "edit" button where records are locked for a user until that user presses a button to edit the record. Not sure if that would be a good method of avoiding this or not.

Does anyone have any advice or recommendations for a database to work smoothly with multiple users on it simultaneously? I'm also thinking of presenting our database to the boss for another department to use. the other department probably has 30 or so people in it who would access the database simultaneously. So far the way I have things set up now, 30 people using the database at one time would probably be a nightmare. Is Access even a practical database application for a department of 30 people to use?

Thanks for any advice!

Gary

[Missinglinq]

First off, having multiple users sharing a single, non-split database, sitting on a network drive, is the sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past eight years have seen literally dozens and dozens of reports of non-split databases, using links to a single file, causing all kinds of problems, as well as massive data loss! The really insidious thing is that a non-split app can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

If your Database isn't really important, which is to say if data-loss isn't important, and the app being down won't cost you production time loss, then by all means leave it non-split.

Splitting an Access database isn't as scary as it sounds; Access will do most of the heavy-lifting for you! Here are two excellent tutorials that shows how to use the Wizard to Split a database as well as how to do it manually:

Splitting your Access database into application and data

or

Sharing - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com

Linq ;0)>

[Gary Childress]

Many thanks for the links!