make mdb multi-user

[Joan]

Hi,
I have built an mdb database and split the database so that the tables are
in the back end and the forms, reports, etc are in the front end. Using the
Jet database engine, what do I need to do now to make the database so that
it can support concurrent multiple users in a network environment? Am in
need of some good advice on how to proceed from here.

I have done some reading on Microsoft Access 2000 Client/Server Development
but read that access projects (.adp) require using either Microsoft SQL
Server 7.0 or MSDE (Microsoft Data Engine) and using MSDE requires a faster
processor, more memory, and is optimized for only five users whereas Jet is
generally best for no more than 25 to 50 users if they will be frequently
adding and updating data. At this time, SQL Server is not a consideration
because of the expense. The way that I understand it, the mdb format and
using the Jet database engine is more like a file/server solution and has
inherrent drawbacks with regard to reliability and additional network
traffic. I've also read that replication would not be a good idea if you
always need your data to be up to the minute current which I do.

There will be a minimum of 5 users in the business environment for which I
have built this database with the probability that this number will grow if
they extend the capability for salesmen to log on to check inventory amounts
and record sales.

Am thinking that for now, I should just go with the file/server option
using the Jet database engine but am not sure what exactly I should do to
make this work optimally. Any advice will be very appreciated.

Joan

 

[chas]

Hi Joan,

place a copy of the front-end database on each users
machine and ensure that the linked tables are pointing to
the same back-end database (use a UNC path).

hth

chas

 

[Joan]

Chas,
Can two or more users use the same database concurrently by just doing
this? Will the database lock a complete table or just a record in the table
when one user has a record open? How does this work?

Joan

 

[chas]

Hi Joan,

As long as you open the database in shared mode you can
have multiple users of Access databases 'straight out of
the box'. You can test opening a database multiple times
by opening up Access more than once on your PC and then
opening the same front-end database at the same time.

You can encounter locking 'situations' but how JET (the
database engine behind Access) reacts is determined by the
choices you make and the options you set. It is possible
to lock a complete table if you want, or an individual
record in Access 2000 onwards (sometimes), more often a
few adjacent records can become 'locked' due to the way
that JET stores data on disk. You can also specify
whether a record is locked as you start to edit the record
or when you come to save it.

I can't imediately put my finger on any helpful MS
articles but if you search Access help for 'locks'
(without quotes) you should get an overview of the topic.
Some other keywords/phrases are:

Row-level locking
Optimistic locking
Pessimistic locking

Forms have a Record Locks property, and choosing
Tools>Options>Advanced gives database wide locking options.

hth

chas

 

[Joan]

Chas,

Thanks Chas, for answering my questions. I also followed your advice and
searched Access help for 'locks'. Your reply and the what I found in Access
help was all very helpful in enabling my database to be used by two or more
users concurrently.

Thanks again.

Joan