Networking 2 computer with Access

[Guest]

I have several questions.
We have a small business and we use Access as our database. We have two
computers. The "main" computer has Access 2002 and the "other" computer is
newer so it have Access 2003. Can I network the computers and still use
Access on both even though they are different versions? Or should I put the
same version on both computers?
Are there any problems that can arise with using Access on networked
computers at the same time? Like if the "main" computer changes one record
while the "other" computer changes a different record?
Any other tips you can offer would be great!

Thanks!

 

[Allen Browne]

If these computers are connected by a network wire (typically a blue CAT 5
cable), it will work perfectly well. There are problems if you use wireless.

The best approach is to split the database into 2 files, as explained here:
http://allenbrowne.com/ser-01.html
There is a built-in wizard to split it for you.

Once split, each computer can have its own copy of the program mdb, while
the data mdb goes on the "main" computer. This allows the 2 computers to use
the same data.

Access is designed to be multi-user. It even offers your choice of locking
strategies. I suggest you stay with the way it installs by default (called
optimisitic locking.) If it ever happens that 2 users attempt to edit the
same record at the same time, when the 2nd one goes to save their changes,
Access pops up a dialog that explains the user has 3 options:
- Save record
- Copy to Clipboard
- Drop Changes.
If you ever see the dialog, choose Drop changes, close the form, and open it
again. You now have the changes the other user made, and so you can go ahead
and do your edit again.

Those of us who work with Access for a living build networked databases all
the time. Some of them have millions of records and dozens of users. My
experience is that a well-written, well-configured Access application on a
reliable network with stable power will work for years without a problem.

 

[Ken Snell \(MVP\)]

If you have a split database application, meaning there is a "back end" file
with the data, and a "front end" file with the forms, queries, reports,
etc., then put the backend file in a shared folder on the network or on one
of the PCs, and the two computers would each have its own copy of the
frontend file (which then are linked to the backend file). Then using
different ACCESS versions on the two computers will not be a problem.

If you haven't split the database, then using two different versions of
ACCESS may result in error messages because each will change the References
to its own version, and it may prevent the 2002 computer from opening the
file after the 2003 computer has used it. See these websites for more info
on splitting files:
http://www.granite.ab.ca/access/splitapp/index.htm
http://www.allenbrowne.com/ser-01.html

 

[Guest]

Allen,

Would you mind to elaborate a little on the "There are problems if you use
wireless." statement.

I am curious as to what experiences you have had along the wireless line and
what diference the wireless connections made as opposed to the wired ones.

Mr B

 

[Allen Browne]

The one thing that will corrupt an Access database quicker than anything
else is the interrupted write.

If the workstation is updating the data across the network, and the
connection fails/disconnects - even temporarily - you end up with a partial
write. If you are lucky, only the index is corrupted. That's the most common
because all databases cache their indexes. It's also the least drastic: once
you are aware it's bad, you can compact/repair the database, which rebuilds
the index. In the mean time, though, the database may be giving wrong
results, e.g. returning only 1000 records out of a 10000 record table when
sorted/filtered on the field with the bad index.

If you are unlucky, the write may have been interrupted at the point when
the memo field was updated but the address linking it to the main record was
not, or even when the index pointing to a record(s) is bad. (Compare
cross-linked files on a FAT drive.)

A workaround is to run the wireless machines as terminal server sessions, so
the session is actually running on the server, and doesn't just fall off in
the middle of a write. More info:
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

Less significant that the corruption, the performance of a Access database
can be significantly slower for the user as well.

 

[Guest]

Thanks for the overview. I assumed that these were the things you were
refering to, but I wanted the explanation.

Mr B