Multi-user Database Set Up

[MichelleM via AccessMonster.com]

I am familiar with Access, how to create, tables, forms, queries, and reports
and how they all link together and use for a single-user mode. However, I
have a project to create a multi-user database, I would appreciate any advice
on what I need to consider prior to setting up. Thanks!

 

[Douglas J. Steele]

Access is multi-user right out of the box, but the best practise is to split
the database into a front-end (containing the queries, forms, reports,
macros and modules), linked to a back-end (containing the tables and
relations). Only the back-end should be on the server: each user should have
a copy of the front-end on his/her hard drive.

 

[John W. Vinson]

I am familiar with Access, how to create, tables, forms, queries, and reports
and how they all link together and use for a single-user mode. However, I
have a project to create a multi-user database, I would appreciate any advice
on what I need to consider prior to setting up. Thanks!

See:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

The tutorials will address the issues of splitting the database for multiple
users.

 

[Row Autofit, cell formatting]

I wonder if this might be my problem, I have one access mdb file, that is on
a network server and when two or more people are opened on the file, one
machine or the other gets locked up and will not let the other person print a
report and the database is suppose to open in "Shared Mode" with
record-locking engaged. It sounds like I need to split out the tables to
another mdb, linked to a front-end to prevent this from happening. This is
version 2003.

Access is multi-user right out of the box, but the best practise is to split
the database into a front-end (containing the queries, forms, reports,
macros and modules), linked to a back-end (containing the tables and
relations). Only the back-end should be on the server: each user should have
a copy of the front-end on his/her hard drive.

 

[Row Autofit, cell formatting]

Also, I have different facilities (Same form, report styles, but different
text ). Can I give each facility their own front-end customized to there site
and just link to the master backend?

Access is multi-user right out of the box, but the best practise is to split
the database into a front-end (containing the queries, forms, reports,
macros and modules), linked to a back-end (containing the tables and
relations). Only the back-end should be on the server: each user should have
a copy of the front-end on his/her hard drive.

 

[Douglas J. Steele]

What permissions have you given each user on the folder where the database
is located? They need a minimum of Read/Write on the folder (and ideally
should have Delete as well). The reason for this is that Access creates a
locking file (.ldb file) in the folder when the first user connects, updates
that file as subsequent users connect, and then wants to delete the file
when the last user disconnects. I've heard of situations where users can
create the locking file, but cannot update locking files created by others.

Regarding your other question (about multiple front-ends connecting to the
same back-end), it's certainly possible, but realistically tends to create a
lot of extra work in terms of keeping common stuff in synch in each version.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Row Autofit, cell formatting"

I wonder if this might be my problem, I have one access mdb file, that is
on
a network server and when two or more people are opened on the file, one
machine or the other gets locked up and will not let the other person
print a
report and the database is suppose to open in "Shared Mode" with
record-locking engaged. It sounds like I need to split out the tables to
another mdb, linked to a front-end to prevent this from happening. This is
version 2003.