Multiuser Database

[eureka2007]

Is there a simple way to setup a Access 2007 database so that it can be
shared by 2 users on different computers on a network?

 

[John W. Vinson]

Is there a simple way to setup a Access 2007 database so that it can be
shared by 2 users on different computers on a network?

Yes. Open it.

Access is multiuser out of the box.

HOWEVER... for stability, performance, and to avoid problems with data
contention, the database should be split into a "Backend" containing only the
tables, and a "Frontend" containing the forms, reports, queries and code.

See http://www.granite.ab.ca/access/splitapp.htm or
http://allenbrowne.com/ser-01.html or http://support.microsoft.com/kb/304932
for details.

 

[Daniel Torres]

Is there a simple way to setup a Access 2007 database so that it can be
shared by 2 users on different computers on a network?

 

[Pat]

Is there a way to make a database that is on a network full access for me and
read only for the other 5 people?

 

[John W. Vinson]

Is there a way to make a database that is on a network full access for me and
read only for the other 5 people?

Yes, unless the other people are highly skilled with Access and determined to
get at the data to modify it. Give them a .MDE or .ACCDE frontend that
conceals the tables and provides only Snapshot or otherwise read-only queries
as the basis for their forms. In the .mdb format you can also implement Access
workgroup security. Download the Microsoft Access 2000 Security FAQ:

http://support.microsoft.com/kb/207793/en-us

Print it out. Read it carefully. Get a good night's sleep. Read it AGAIN, even
more carefully! Security is tricky (one reason they probably took it out of
the new ACCDB format).

 

[Pat]

Ah, if only I understood what you meant or how to:

"Give them a .MDE or .ACCDE frontend that

conceals the tables and provides only Snapshot or otherwise read-only queries
as the basis for their forms."

What happened is someone went onto the database and deleted ALL of an
employees history (sadly for them the person they deleted was me so it was an
easy catch) but a good clue that 'they' need to NOT be able to do this again
as we have hundreds of employees and I may not catch it as quickly the next
time.
--

Given that we are not astute with the program I would refrain from the
security idea. The solution above, if I can implement, seems to be just what
we need.

Thanks, Pat

 

[John W. Vinson]

Ah, if only I understood what you meant or how to:

"Give them a .MDE or .ACCDE frontend that

What happened is someone went onto the database and deleted ALL of an
employees history (sadly for them the person they deleted was me so it was an
easy catch) but a good clue that 'they' need to NOT be able to do this again
as we have hundreds of employees and I may not catch it as quickly the next
time.

Yow.

It's a good reason (among many) to keep daily reliable backups, obviously!

Your best bet may be to use a split database, and put the backend in a network
folder with read-only access. Give the user a frontend database and a shortcut
which opens it in readonly mode: the shortcut command line would be something
like

%Programfiles%\OFFICE11\MSACCESS.EXE "C:\somepath\mydatabase.mdb" /ro

The /ro switch ensures that it's opened read-only. This will put some
limitations on what the users can do.

Securing the database from inside requires a good bit of work - the supurb
Access Developer's Handbook devotes two full chapters to the subject.
Basically you need to do a lot of settings on Tools... Startup to conceal the
database window, turn off most of the builtin menus and toolbars, open a
startup form that guides the users where YOU want them to go (and noplace
else), and so on. It's rather more than I'm comfortable posting here, and the
steps will be rather different in A2007 than in earlier versions. I'm sure
someone has a webpage describing the steps but I wasn't able to quickly find
one.

 

[Paul Shapiro]

Maybe you could take this one step further to reliably provide read-only
access without having to deal with Access security.

If you're the only user who should have edit permissions, you could keep the
master copy of the backend db separate from the backend db made available to
the other users. Their backend db is marked readonly and they only have read
permissions to the folder from which it is shared. You make your edits to
your personal copy, and when you want those edits available to the other
users you copy the master backend db to the shared public space. Or setup a
scheduled task to copy it every night, and make the copy readonly. You'd
have to test to verify that multiple users could open the shared backend
having only read permissions on the folder. Even if you had to give them
more permissions on the folder, so the .ldb file could be created and
updated, any changes they might figure out how to make would be wiped out
the next time you copy the master data file to the public space. If
something like daily updates was good enough, this might be a very workable
solution.

I think that when security becomes important, SQL Server becomes a much
better option for the backend. It would be completely trivial to introduce
this kind of control in SQL Server.