multi-user database

C

Chester B

Thanks for reading this post. I have 6 users all on a local network. I need
them to be able to simultaneously access a database that tracks information
on our current customers. They will need to have simultaneous editing access
to the same record when necessary. If they change the same field in that
record while they both have it open, I'd like some sort of prompt. I'm
familiar with the basics of Access and database theory (via extensive
Googling of 'Microsoft Access, and trial and error). Do I need to split the
database, or do I need to replicate? Are there any other alternatives?
Thanks for reading this far,

Chester
 
A

Albert D.Kallal

Well, for any amount of reliability operation, you need to split. Please
read the following, as it explains why you do this:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

(I like articles that explain why...in place of just "telling" you to do
something !!).
They will need to have simultaneous editing access
to the same record when necessary.
Click to expand...

Nope, you can not do. You have to come up with a different design. Either
you let others view while one edits, or you move out the data to another
table.

To have two people editing the same record at the same time is just not
possible. I can recall over the years "many" applications where two people
want to grab and edit the same customer, but NEVER did I need to allow that
to occur at the same time (just no way to control who changes what, and who
gets the final say! I written more reservation systems then I can now
remember...and those types of systems are systems where "many" people might
be trying to grab the LAST seat on a bus for example. I always been able to
manage this problem.

You may certainly have this requirement, but I suspect another approach is
possible here. Perhaps your data is not normalized, and broken up into
tables. Without question, we could have a design where two people fill
"orders" out for the same customer, but that would not be a problem with a
normalized design.

At the end of the day, you might very well have a design where two people
need to edit the same record, and thus your design needs a change.
 
C

Chester B

Thanks for the info. Here's a better description of what I need to do:

Each customer's file has a list of required documents. Each document needs
to have 2 dates; one for when it was ordered/requested, and one for when it
was received. Some documents will need more dates than that, in case we have
to reorder with changes. Since we have multiple people working on different
parts of the same file at the same time, the users need to be able to input
dates for different documents into the same file. They will not always need
to do it at the EXACT same time, but it comes up often enough that I don't
want it to cause problems.

As an alternative, is there a way to have it be able to open the same
record, and when the user tries to edit, simply give a message that the
record is being modified by another user, and prompt for cancel/overwrite
changes? I think I saw another Access database do something like this, but
for the life of me I can't remember when or where, or if maybe I just drink
too much (or not enough).

The only other way I see is to change the table layout. Currently, I have
tblMainInfo that has the customers' basic info: name, address, etc. That
table is linked by their file number to tblDocumentTracking, which has fields
for each document: DocOneOrdered, DocOneRecd, etc. These two tables are
one-to-one. I could try changing tblDocumentTracking to one where each
record represents an individual document; however, that brings up other
problems. For one, I'm pretty sure that it will not only take up much more
space (I would need 20+ records per file, instead of the current one record),
but it will probably be slower too, where queries and reports are concerned;
that worries me because it already runs like Grandma on valium.

Thanks again for the input.
 
J

jahoobob

Chester said:
Thanks for the info. Here's a better description of what I need to do:

Each customer's file has a list of required documents. Each document
needs
to have 2 dates; one for when it was ordered/requested, and one for
when it
was received. Some documents will need more dates than that, in case
we have
to reorder with changes. Since we have multiple people working on
different
parts of the same file at the same time, the users need to be able to
input
dates for different documents into the same file. They will not always
need
to do it at the EXACT same time, but it comes up often enough that I
don't
want it to cause problems.

As an alternative, is there a way to have it be able to open the same
record, and when the user tries to edit, simply give a message that
the
record is being modified by another user, and prompt for
cancel/overwrite
changes? I think I saw another Access database do something like this,
but
for the life of me I can't remember when or where, or if maybe I just
drink
too much (or not enough).

The only other way I see is to change the table layout. Currently, I
have
tblMainInfo that has the customers' basic info: name, address, etc.
That
table is linked by their file number to tblDocumentTracking, which has
fields
for each document: DocOneOrdered, DocOneRecd, etc. These two tables
are
one-to-one. I could try changing tblDocumentTracking to one where
each
record represents an individual document; however, that brings up
other
problems. For one, I'm pretty sure that it will not only take up much
more
space (I would need 20+ records per file, instead of the current one
record),
but it will probably be slower too, where queries and reports are
concerned;
that worries me because it already runs like Grandma on valium.

Thanks again for the input.
Click to expand...
Create seperate tables for the varoius dates. Tie each date to the
tblMainInfo via a customer number or some other record id. You could
then have e.g:
CustNo Name Address
1 Joe Black 1 Apple St.

Then you date table would have
CustNo DateOrdered
1 1/1/05
2 1/12/05
1 3/5/05
1 5/25/05

The first date assicated with a CustNo would be the ordered date and
any subsequent date(s) would ba a reorder date which could be handled
by queries.
If you wanted to you could add another field to the date table that
would tell whether a date was an order or reorder.
This way, since each date is placed in a different record, any number
of users can be entering a date into the table.
Hope this helps.
 
Top