Enable multi user editing of database

[CBeedon]

I have been using Access for a few years now but just the basics. I have
downloaded the 'Sales contact management' database template and have deleted
and added a few things to suit my requirements. I need to be able to allow 2
users access (which i have done) but neither of us are able to update or save
the database whil we are both in it. I believe i have to set up a 'Access
project' and connect it to a SQL server but am unsure what one of these are
and how to set this up. Both myself and the other user and working from the
same network and server. Is an SQL server something i can create? Please can
anyone help with this. Thanks

 

[Ken Sheridan]

You don't need to create an Access project; it can be done using native
Access .mdb files by slitting the database into front and back ends.

Firstly spit the database by means of the built in database splitter wizard.
This will create a back end file of the same name as your current file with
'_be' added to the file name. It will also create links to the tables in the
back end in your current file, which now becomes the front end.

Move the back end to a shared folder on the server. Install copies of the
front end on each user's machine, which should also have Access itself
installed. In each front end update the links to the back end at its new
location by means of the built in linked table manager.

Each user can now open their own front end, sharing access to the tables in
the back end. You may on occasions find that conflicts occur where two users
are trying to edit the same record simultaneously, but Access will
automatically handle this. Its generally considered best nowadays to adopt a
'pessimistic locking' strategy by setting the RecordLocks property of your
data entry forms to 'Edited Record', and making sure that the 'Open database
using record-level locking' option is checked in the Tools | Options |
Advanced dialogue accessible from the main menu bar. I'm not sure if the
location differs in later versions than mine, but I think this is now the
default anyway. In early versions of Access 'page-level locking' was used
which meant several records could be locked, not just that being edited,
which made 'optimistic locking' the preferred strategy, but that no longer
applies.

Ken Sheridan
Stafford, England