Multiple User Design

[jewll]

I'm trying to get my head around how another guy I work with has created a database in Access 2003, he hasn't split it, we all log in through Citrix Remote Access to the same file and we can all log on and use the same forms at the same time... It works perfectly the whole 3 years I've been here.. sure he is not revealing the full way he has done this. I also have his bypass password so i've seen the tables and forms are all located in the same place... anyone know how he has done this?

[ssanfu]

If you have a non-split dB on a "server" (read common access folder) that allows multiple users add/edit/delete data at the same time, the dB WILL be corrupted - not a question of IF, just WHEN.

I have seen reports/blogs/questions in forums of non-split dBs running for years with no problems - then the dB can't be accessed or data is constantly (daily) corrupted. Once the corruption starts, it continues until the dB won't open.

The only sane option is to have a multi-user design: to have the BE on a "Server" or "Host" and EACH user has their own copy of the FE on their computer.


If I were going to sell or publish a dB, then yes I would create an accde.
But as far as creating an accde for in-house, I don't. You can add code to limit access to forms and remove the database window to limit access to tables, etc.
But in-house, this is an administrative problem. Everyone is aware (and has signed a form) that mucking around in the FE or BE is grounds for termination.
If someone want/need changes to the FE, follow the procedures and justify the request.

BTW, "Mucking around" is the technical term for adding/editing forms/queries/reports by someone other than the dB designer/IT dept.


My $0.02 .........

[June7]

I also developed a multi-user non-split db because that's what they asked for. I warned them of the risks but they insisted "Keep it simple, there's something better coming along and this is temporary". It's very small - there is only one form/subforms/subsubforms for the users to interact with and everyone (staff of 6) has full access. There is 0 code - no macros and no VBA. It's been running for couple years without issue. No idea when 'better' is coming.