Corruption from Multiple Users

[MatthewR]

I designed a desktop database, and I understand that it could become corrupted if multiple users are using it at once.

My question is, if there are multiple people using the database, but only 1 person is editing records, is there still a risk of corruption? In other words, if one user is editing records and the other is merely viewing reports, can that create corruption?

(I'd rather not split the database because only 1 user needs to use this database at any given time. On rare occasions, another user might try to open it and use it at the same time as another user. I plan to program the database to prevent that user from either editing the data or entering the database altogether, whichever I need to do.)

Thanks for your help.

[pbaldy]

In my view, our job is making other people's jobs easier. I'd say split it and be done with it, then when your users rave about how it helps them, you're ready for increased usage (and the promotion). You aren't making their jobs easier if they can't do what they want, when they want (or at least you aren't making them as easy as you could).

To your question, I don't really know. I wouldn't risk it. If you decide to leave it un-split, and at the end of the day it's your decision, I'd recommend an aggressive backup strategy.

[MatthewR]

Thanks for the tip, but still, I'd rather not split this specific database.

Assuming I don't split it, the question is- when a user is already on, and another user tries to enter, do I prevent them from entering the database altogether or do I just prevent them from editing?

If you're not sure, then I'll probably just prevent them from entering the db altogether to be on the safe side.

[pbaldy]

The fact that I'm not sure doesn't mean nobody is. What little research I've done seems to always result in the same conclusion. Somebody experienced corruption with a shared db, they're not sure why but they don't bother taking the time to figure it out because splitting it solves the problem. If you don't want to split it, keeping the second user out is probably safest.

[ranman256]

databases will corrupt even with 1 person editing it.

[MatthewR]

Well... I'm in a situation where I'm about to leave this job. If I split the database, the issue is that nobody here will have any idea how to create new front ends for new users.

The database does, however, makes regular automatic backups. And there is at least 1 person here who is savvy enough to be able to revert to a backup copy of the db if it gets corrupted.

In an emergency, they could call the IT Department, but they're a last resort because they're a pain to deal with. And since they didn't design the database, they might not be in the best position to deal any issues that arise.

[jwhite]

Check out the /excl command line switch. Also, when an Access database is opened, Access created a locked file as well (.ldb for pre-2007). If the switch doesn't help, have a batch file check for the ldb file and not open the db if it exists. Splitting is the best option, however. You stated users wouldn't have any idea how to create a new front end for new users. If you split the database, you would have a "Master" FE database stored away. When a new user comes along, they get a copy of the "Master" and away they go.

[MatthewR]

Thank you for the advice on creating FE's. I still have a 2nd fear- that the main file will be moved, or the directory will change its name, and that will sever the link between the FE's and BE, causing everyone to panic and lose their minds. :/

With respect to opening the database in exclusive mode- when a 2nd user tries to open it, is there a way for the 2nd user to see the Windows UserName of the person who has it open? Or will they just get a general error message that the db is in use?

[jwhite]

The user attempting to open a database via shortcut using /excl that is already open will get "Could not use 'path/dbname'; file already in use.", and the window will close without opening the database for the second user. It does not report the "username". If you are using workgroup security, then it would be possible to read the .ldb file to determine who is using it. Or, you could write a simple function that will put the logged-in user name in a simple text file on login, and delete the file on exit. If the file exists, it is in use and you could read it to determine the person who has it open. Although, splitting the DB so each user has their own FE is the better route.

[MatthewR]

Okay, got it. Thank you.