Advice - re stability and multiple users

  • Thread starter Thread starter Gail
  • Start date Start date
G

Gail

Hi guys,

I have put together an Access database to record Incidents
& Hazard and Staff Training and appointments. The plan is
place the database on a shared drive where 5 people from
the unit will be able to use it.

Question 1.
Advice on multiple users and do I need to put into place
some record locking - pretty new to this stuff.

Question 2.
How stable is Access when it comes to file corruption and

Question 3.
What sort of numbers of records will it cope with?
TIA
 
Answers in-line below.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Gail said:
Hi guys,

I have put together an Access database to record Incidents
& Hazard and Staff Training and appointments. The plan is
place the database on a shared drive where 5 people from
the unit will be able to use it.

Question 1.
Advice on multiple users and do I need to put into place
some record locking - pretty new to this stuff.
Click to expand...

5 concurrent users shouldn't be a problem. Access should take care of
locking for you. However, recognize that your application should be split
into a front-end (containing the queries, forms, reports, macros and
modules), linked to a back-end (containing just the tables). Only the
back-end should be on the server: each user should have his/her own copy of
the front-end, preferably on his/her hard drive.
Question 2.
How stable is Access when it comes to file corruption and
Click to expand...

It depends a lot on your hardware. Access is very sensitive to faulty NIC
cards. You might find the "Corrupt Microsoft Access MDBs FAQ" Tony Toews has
at http://www.granite.ab.ca/access/corruptmdbs.htm to be informative.
Question 3.
What sort of numbers of records will it cope with?
Click to expand...

Access doesn't have a record limit per se. Rather, it has a file size limit
(1 Gb for Access 97 and earlier, 2 Gb for Access 2000 and newer.) However,
since you can link to multiple back-end MDB files, that's not a
hard-and-fast limit either.
 
Question 1.
Advice on multiple users and do I need to put into place
some record locking - pretty new to this stuff.
Click to expand...

Record locking is pretty much automatic. There are some settings you
can tweak, but Access is multiuser just as it comes out of the box.

Definitely DO split the database as Douglas suggests.
Question 2.
How stable is Access when it comes to file corruption and
Click to expand...

It's not perfect. Current backups (defined as "a backup that is recent
enough that deleting your database and restoring from the backup is a
moderate hassle rather than a catastrophe") are ABSOLUTELY essential;
regular compaction (Tools... Database Utilities... Repair and Compact
Database) is essential.
Question 3.
What sort of numbers of records will it cope with?
TIA
Click to expand...

If you're getting over one to five million records in the largest
table, start thinking seriously about moving to client/server. If you
get over 100,000 pay close attention to indexing and optimizing your
query design. Under that level, good query optimization is a favor to
your users but the database will function even without it.
 
Back
Top