"sharing" Access (and Other) files

[Guest]

I have numerous files, both in Acces and Excel, that I want to make available
to the 2 other users on my small network. For these particular forms
(documents), I want them to be able to enter data and update the file at the
same time. How do I do this? Also, how does it work if 2 of us have the
same file open and working on it at the same time...or is that possible?
Thanks.

 

[Guest]

Rich:

You can share both Access databases and Excel workbooks so that multiple
users can update them, but its done slightly differently in each.

In Access you first split the database into a 'front end' containing the
forms, reports queries etc and a 'back end' containing just the tables. The
built in database splitter wizard will do this for you, creating links to the
tables in the front end. The back end is then installed on a share on the
network and separate copies of the front end along with Access itself are
installed on each user's machine. After installing the front ends you can
refresh the links to the back end in each case using the built in Linked
Table Manager, but from then on the links will be automatic unless you move
the location of the back end file. Each user will then be able to update the
data on the back end.

Even in a single user environment splitting a database is recommended as it
has a number of advantages over using a single file.

Access includes record locking to handle situations where two users might be
trying to update the same row in a table simultaneously. Locking can either
be 'optimistic' or 'pessimistic'. The former allows two users to edit the
same record but includes functionality to resolve conflicts. The latter
locks a record as soon as a user starts to edit it, so conflicts are avoided.
Optimistic locking used to be used most commonly because with pessimistic
locking not only the current record was locked but the complete 'page'
containing the record; this could lock quite a lot of records. Now, however
(since Access 2002 I think) true record locking is possible, so pessimistic
locking tends to be the favoured method.

My knowledge of Excel is limited, but I know that you can create a 'shared
workbook' and install it on a share. Multiple users can then update it.
Conflict resolution functionality is built into Excel. Have a look at the
topic on 'Collaborating' in Excel Help for more details. For further advice
on using Excel across a network you might like to post in the Excel
discussion group.

Ken Sheridan
Stafford, England

 

[Larry Linson]

I have numerous files, both in Acces and Excel, that I want to make available
to the 2 other users on my small network. For these particular forms
(documents), I want them to be able to enter data and update the file at the
same time. How do I do this? Also, how does it work if 2 of us have the
same file open and working on it at the same time...or is that possible?
Thanks.

In Access 2000 and later, you must have Exclusive Use of the Database to
change the _design_, e.g., create a new Table or Query, modify the design of
an existing Form or Macro.

As long as the file is shared and the Database is open in Shared mode,
multiple users can view, add, delete, and update the data, that is "use" the
Database.

Other than that, I have nothing to add to Ken's excellent discussion.

Larry Linson
Microsoft Access MVP