Multi-User Database

[jserrone via AccessMonster.com]

I would like your opinion on this; I have a ms access database that will be
used in a multi-user environment, across different Teams. Data will be loaded
from text files several times a day in a MS Access table, users will retrieve
the data from the tables and displayed in a form. Their job will be to add
comments and select drop down boxes for actions they have taken. Since the
data is downloaded in the tables and retrieved from the user instead of the
user keying in the data from scratch I would like to know how to best design
this. I’m thinking of having a query run to display the data in the user form
but is there a way that I can have the data refreshed every so often so that
users can be presented with the new records that are being downloaded. What
best practice should I use to avoid conflicts like example splitting the
database or using record locking options

Any help is appreciated! Thanks

 

[Pat Hartman]

Unless you have a huge number of users doing heads-down data entry, you are
unlikely to run into locking issues if you use the default of optimistic
locking. If userA opens a record and makes a change and userB opens the
same record - Access shows a circle with a line in it in the record selector
box. If userB then tries to save the record, he will be allowed to and when
userA goes to save, he will get an error message giving him three choices -
save over B's changes, discard his changes, copy his changes to the
clipboard and decide whether to apply them after seeing the updated version.

Refreshing will update changed data and will show #deleted# in the fields of
deleted records. Requerying will rerun the query and so deleted records
will disappear and added records will show along with any changes to
existing records. If the users stay on the same form all day, you may want
to give them a requery button that they use periodically throughout the day
so they can see any new records. If they are in and out of the database or
move from form to form, you probably won't need the requery option. BTW -
Access does automatically refresh forms at the interval specified when you
designed the form but it does not automatically requery.

 

[jserrone via AccessMonster.com]

Hi Pat, Thanks for your help!
How would you go about builing a button on a form that would refresh the
query?

Thanks

Joe

Unless you have a huge number of users doing heads-down data entry, you are
unlikely to run into locking issues if you use the default of optimistic
locking. If userA opens a record and makes a change and userB opens the
same record - Access shows a circle with a line in it in the record selector
box. If userB then tries to save the record, he will be allowed to and when
userA goes to save, he will get an error message giving him three choices -
save over B's changes, discard his changes, copy his changes to the
clipboard and decide whether to apply them after seeing the updated version.

Refreshing will update changed data and will show #deleted# in the fields of
deleted records. Requerying will rerun the query and so deleted records
will disappear and added records will show along with any changes to
existing records. If the users stay on the same form all day, you may want
to give them a requery button that they use periodically throughout the day
so they can see any new records. If they are in and out of the database or
move from form to form, you probably won't need the requery option. BTW -
Access does automatically refresh forms at the interval specified when you
designed the form but it does not automatically requery.

I would like your opinion on this; I have a ms access database that will be
used in a multi-user environment, across different Teams. Data will be

[quoted text clipped - 17 lines]

Any help is appreciated! Thanks

 

[Pat Hartman]

Add a button to the form. Cancel the wizard because it doesn't have what
you need or if you want it to generate the error handling, choose Form/Close
as the option and let the wizard finish. Insert one line of code (don't
forget to delete anything extraneous that was generated by the wizard).

Me.Requery

Don't forget that requerying the form will also reposition it to the first
record in the set.

Hi Pat, Thanks for your help!
How would you go about builing a button on a form that would refresh the
query?

Thanks

Joe

Unless you have a huge number of users doing heads-down data entry, you
are
unlikely to run into locking issues if you use the default of optimistic
locking. If userA opens a record and makes a change and userB opens the
same record - Access shows a circle with a line in it in the record
selector
box. If userB then tries to save the record, he will be allowed to and
when
userA goes to save, he will get an error message giving him three
choices -
save over B's changes, discard his changes, copy his changes to the
clipboard and decide whether to apply them after seeing the updated
version.

Refreshing will update changed data and will show #deleted# in the fields
of
deleted records. Requerying will rerun the query and so deleted records
will disappear and added records will show along with any changes to
existing records. If the users stay on the same form all day, you may
want
to give them a requery button that they use periodically throughout the
day
so they can see any new records. If they are in and out of the database
or
move from form to form, you probably won't need the requery option. BTW -
Access does automatically refresh forms at the interval specified when you
designed the form but it does not automatically requery.

I would like your opinion on this; I have a ms access database that will
be
used in a multi-user environment, across different Teams. Data will be

[quoted text clipped - 17 lines]

Any help is appreciated! Thanks