How To Prepare An Access Database For Shared Use?

[David Portwood]

Right out of the box (immediately after Access installation) if I create a
table and form for data entry will multiple data entry people be able to
share this form? Do I have to first prepare the database in some manner to
be shared? Are Access databases exclusive by default?

 

[Storrboy]

Right out of the box (immediately after Access installation) if I create a
table and form for data entry will multiple data entry people be able to
share this form? Do I have to first prepare the database in some manner to
be shared? Are Access databases exclusive by default?

It's generally considered a "mortal sin" to share a database as opposed to
serving the data from a "back-end". Sharing the same file introduces
numerous problems with corruption, record locking and file accessibility.

Look at this link for details about splitting a database, or search the
various groups about splitting a database.
http://www.granite.ab.ca/access/splitapp/index.htm

 

[John W. Vinson]

Right out of the box (immediately after Access installation) if I create a
table and form for data entry will multiple data entry people be able to
share this form?

Yes... but see below.

Do I have to first prepare the database in some manner to
be shared?

No... but see below.

Are Access databases exclusive by default?

No.

Note that a shared database should ideally be "split" into a "frontend" - a
..mdb or .mde file with the Forms, Reports, Queries and other user interface
stuff; and a "backend" containing only the tables. The frontend will have
linked tables. Tools... Database Utilities... Database Splitter Wizard will
split a database for you.

It's not absolutely essential to do so, but it's good practice; sharing a
frontend or a unitary database works, but can adversely affect performance and
stability.

John W. Vinson [MVP]

 

[David Portwood]

Thanks, guys. I will try creating two .mdb files, one with the table(s) and
one with the user interface objects. I assume that when I create the form
the wizard will ask me what is my source table and I can specify a table in
the first mdb file. Hopefully it's no more complicated than that.

 

[John W. Vinson]

Thanks, guys. I will try creating two .mdb files, one with the table(s) and
one with the user interface objects. I assume that when I create the form
the wizard will ask me what is my source table and I can specify a table in
the first mdb file. Hopefully it's no more complicated than that.

A tiny bit more complicated. Instead of creating all your forms first, create
the tables (properly normalized!!!) in the backend. Then, in the frontend, use
File... Get External Data... Link to link to the tables.

You can then treat the linked tables just as regular tables, using them as the
Recordsource for forms and reports.

John W. Vinson [MVP]

 

[Albert D. Kallal]

Try reading the following article of mine:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

In the above, I speak well of what happens when you need to add a new table,
or change designs in a split enviroment......

creating of forms will not change, and you have a "list" of tables just like
you always had..but they are linked...

 

[David Portwood]

Thanks again, guys. I'll keep all this in mind.

Thanks, guys. I will try creating two .mdb files, one with the table(s)
and one with the user interface objects. I assume that when I create the
form the wizard will ask me what is my source table and I can specify a
table in the first mdb file. Hopefully it's no more complicated than that.