Two users in the same database at the same time

[Patty]

I created a database in Access 2007. I located it on my server so two people
can access it at the same time, but when one is in the database the other
can't update a record or sometimes even open it. It tells them that they can
only save it if you are the soul person using it. I'm not talking about
design changes, just general changes, like address, phone... Why can't this
be done?

 

[John W. Vinson]

I created a database in Access 2007. I located it on my server so two people
can access it at the same time, but when one is in the database the other
can't update a record or sometimes even open it. It tells them that they can
only save it if you are the soul person using it. I'm not talking about
design changes, just general changes, like address, phone... Why can't this
be done?

It can, and it's perfectly routine! Access databases (nominally) allow up to
255 concurrent users; in practice, forty or fifty is perfectly practical.

However, you should use a "split" database design: see
http://www.granite.ab.ca/access/splitapp.htm for a thorough discussion. This
has a "backend" database containing only the tables, on a shared network
folder; each user has their own copy of the "frontend", containing links to
the tables, and all the forms, reports, queries and code.

 

[Jerry Whittle]

Also make sure both, actually all, users have Read, Write, Create, and Delete
privileges to both the database file(s) AND the entire folder that holds the
database file out on the server.

 

[Patty]

thanks for your help. Can you slit the database if you have already created
and started using it without causing problems?

 

[David W. Fenton]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

Also make sure both, actually all, users have Read, Write, Create,
and Delete privileges to both the database file(s) AND the entire
folder that holds the database file out on the server.

Delete permission is not necessary.

 

[Armen Stein]

On Wed, 13 Jan 2010 13:36:02 -0800, Patty

You should be able to do what you're trying to do. Check your
permissions as others have suggested.

thanks for your help. Can you slit the database if you have already created
and started using it without causing problems?

Yes, and it's a best practice. Here's KB article that describes how.

http://support.microsoft.com/kb/304932

If you do the split, you're welcome to use our free J Street Access
Relinker on our J Street Downloads page: http://ow.ly/M56Q

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John Spencer]

My understanding (and I could be mistaken) is that you don't need delete
permission on the database, but you do need delete permission on the folder.
Otherwise, you can end up with a lock file that does not get deleted and
subsequent problems due to the lock file not being deleted when it should be.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

I don't believe it's an issue. Remember that Access 2.0 didn't delete the
locking file.

Of course, if you have code that relies on the non-existence of the file,
you'll run into problems...

 

[David W. Fenton]

My understanding (and I could be mistaken) is that you don't need
delete permission on the database, but you do need delete
permission on the folder. Otherwise, you can end up with a lock
file that does not get deleted and subsequent problems due to the
lock file not being deleted when it should be.

It doesn't matter if the LDB file is not deleted. With DELETE
permission removed on the folder, you revert to the behavior of Jet
before Jet 3.x, i.e., where the LDB file was left between sessions.

When I've implemented security without deletion permission, I do
give admin users delete permission, so that the users with
administrative permissions (I use custom NTFS security groups on the
server specific to the Access database to manage user
privileges/permissions) will delete the LDB if they are the last
user out of the back end.

This worked fine, though occasionally the LDB file does get
corrupted and has to be deleted explicitely by an admin.

 

[David W. Fenton]

if you have code that relies on the non-existence of the file,
you'll run into problems...

In that case, you can convert your code to try to delete the LDB
file. If it's in use, you'll get a trappable error.

 

[Douglas J. Steele]

In that case, you can convert your code to try to delete the LDB
file. If it's in use, you'll get a trappable error.

Even if you don't have Delete permission?

Or will it be a different error if it's in use than if it isn't?

 

[David W. Fenton]

Even if you don't have Delete permission?

Or will it be a different error if it's in use than if it isn't?

Good question!

Maybe the real way to do it is to actually not use the LDB file as a
proxy for someone having the MDB open, and instead use the Windows
API to check if the MDB is actually open:

http://vbnet.mvps.org/index.html?code/fileapi/createfile_inuse.htm

I can't endorse the code there (I've never used it), but it's a
start.