Multiple users

[nancy54]

Hello,
Not sure if this is an Access issue. We use a few MS Access databases at my company where multiple end users may be logged on at the same time. One DB works fine, but the Sample Log DB will not let more than one PC (or user) be opened with out getting an error message. We can't figure why this happens.
Nancy

 

[Ranman256]

are these databases split?
you cant really use them unless you have them split on the network.

 

[nancy54]

Ranman256
Yes, they are separate Access databases. They are all on the same drive, but in separate folders.

 

[GinaWhipp]

Hmm, not separate databases, *split* databases... Frontend - Backend. The Frontend should be on everyone's local and the Backend on the Server. Is that what you have?

 

[nancy54]

No, we do not. Our IT department does not support MS Access. We treat it like any other MS Office program.

 

[isladogs]

Unlike any other Office program, Access does provide support for multiple users. However this can only be done safely in the way Gina raised. Shared backend on the server and each person having their own copy of the front end on their own workstation.

Any other configuration will at some point cause major issues - most likely data corruption. The symptom you described should be considered an early warning. It WILL get worse.

 

[GinaWhipp]

Well, they don't need to support Access for you to split the file. Since you can already put a file on the Server that takes care of the Backend. And, as everyone already has Access you should have no problem putting Frontends on their local machines. (See ridders post for way).

That said, if you insist on doing it this way then make sure everyone opens the file as a *Shared* file and not in *Exclusive* mode. However, be fore warned that this will lead to issues so make sure you are getting daily back-ups (at a minimum) because when that file gets corrupt (and I said when not if) it may not be recoverable.

 

[Minty]

To expand further on the explanations above, all other MS office applications are loaded individually on the end users machines.

However if they all try opening the same Word or Excel Document they won't be able to edit it.

This is effectively what you are currently doing with your shared Access Database. Hence all the advice saying move the FE application (the database forms etc) to the
end user locally, and share the data only in the Backend (just the tables)

 

[nancy54]

Gina, I think many people have opened to the Exclusive mode. How can we change that?

 

[GinaWhipp]

It is under Options > Client Settings

 

[Vassago]

You're still better off creating a front-end and using linked tables to the backend that way not everyone is using the same database at the same time. It WILL become corrupt, even if not using Exclusive mode.

 

[The_Doc_Man]

One other issue to check, and you didn't describe it but I will give you a forewarning:

Everyone who will be using a database with split FE/BE will need "MODIFY" permissions on the folder and all files therein. Which is why each shared database really should be in its own folder. Note that there are TWO levels of permissions - the broad-brush categories such as READ, MODIFY, FULL CONTROL, etc. - and over 20 individual permissions such as "Pass Thru" "Read Attributes" "Write file" etc. When you right click on a folder and look at its permissions, the broad-brush options show up and you can just choose MODIFY. You don't need to go "Advanced" to get to the detailed permissions.

The reason you would have a read-only database or other exclusion is that if the FE copy (that is on the end-user's machine) is unable to create or modify the .LDB file in the same folder as the BE database, then you can't lock anything so it opens READ ONLY.

The inability to open the DB at all points to the Exclusive mode problem, but often if your IT department is being a bit prissy, you will run into the permissions problem.

As to "Our IT department does not support MS Access." ... I hate to break it to you, but they have nothing to support except sharing permissions. A "pure" Access (i.e. both FE and BE are .ACCDB or .MDB files) is just another app that shares files. Excel and Word allow "collaboration" even if they don't allow simultaneous editing. If your IT department sets up shared areas with permissions as described, that is really all they can do for you. Access goes the extra mile on its own. So other than assuring a decent file sharing setup, don't be too hard on the IT department as long as they don't get in your way.

If you search this forum for articles on database permissions, you might find some other useful discussions on this subject.

 

[Galaxiom]

However if they all try opening the same Word or Excel Document they won't be able to edit it.

FWIW It is possible to turn on shared use of Excel documents. That is what they were doing at the company I work for when I started. It is a hideous concept so I won't mention where it can be turned on.

The instructions, if conflicts arose on save, indicated to try pasting the new rows a bit further down the sheet and keep trying until the save was allowed.

 

[The_Doc_Man]

Ouch! G, I understand why you don't want to talk about it.

 

[nancy54]

Thanks all. This has been a big help!

 

[Minty]

FWIW It is possible to turn on shared use of Excel documents. That is what they were doing at the company I work for when I started. It is a hideous concept so I won't mention where it can be turned on.

The instructions, if conflicts arose on save, indicated to try pasting the new rows a bit further down the sheet and keep trying until the save was allowed.

I think I did know about this, but had deliberately wiped it from my memory using mind bleach when I read how it handled conflicts ....