Split database data entry problem

[TOMAC]

I have a backend database on our network accessed by 2 front end users with
their own copy of the front end database. Some tables are linked to the
backend and both users add data to one of these linked tables.

The problem we're having is when one user is entering data in their copy of
their front end, the other front end database gets an hourglass until he has
finished entering his data.

I have no table or record locks on, the database opens in shared mode and it
does not open using record level locking.

Any ideas why this is happening?

 

[Tim Ferguson]

I have no table or record locks on, the database opens in shared mode
and it does not open using record level locking.

Well, you do have locking; it would be a mess otherwise. If you just
accept the defaults you can't complain when it doesn't suit you. There is
a number of things you can do to reduce collisions:

- Base your forms on queries that collect a single record rather than
whole tables; this leaves the rest of the table open for other users

- Use snapshots rather than dynasets whenever possible. Never, ever, ever
open a table dynaset -- the db engine has no option but to lock everyone
else out until you have done with it.

- Set the locking type explicitly. Use Pessimistic locking if you are
doing data entry, which avoids error messages saying the record has been
changed by someone else; use Optimistic Locking if you are predominantly
looking things up and only occasionally updating. Use record-level locks
if you are manipulating one record at a time (but pay attention to the
first hint above).

- Close recordsets (dynasets) as soon as possible.

Hope that helps


Tim F

 

[TOMAC]

Tim - Not sure what you mean by "Base your forms on queries that collect a
single record rather than whole tables". We are adding records in a form.

 

[Tim Ferguson]

Tim - Not sure what you mean by "Base your forms on queries that
collect a single record rather than whole tables". We are adding
records in a form.

All bound Access forms are based on a RecordSource... If you accept the
designer wizard' suggestion to use just a table name, you commit every
field in every record to the possibility of being edited: affecting
everyone else who might want to use the same table. OK, Access itself can
make some canny judgements about which records in the table are
_probably_ safe for other users but you will get collisions.

On the other hand, if you take control and change the defaults, you can
use something like

SELECT ThisField, ThatField, TheOtherField
FROM MyTable
WHERE MyKeyValue=1034

so that you only lock the fields you need in the one record you need, and
everyone else knows that the rest of the table is safe. You get the value
1034 (or whatever) from your search form or record selector or whatever
method your users decide which record they want to interact with.

For a data entry only form, an equivalent version is something like

SELECT ThisField, ThatField, TheOtherField
FROM MyTable
WHERE FALSE

which does not lock any records! It is possible that setting the
DataEntry property to True achieves the same thing, but I'm afraid that
I'm too lazy to test for it. I simply build "Create a new record" into
the Select A Record form.

Hope that helps


Tim F

 

[TOMAC]

Thanks Tim, I appreciate you taking the time.

I set my form up so that when a user clicks the "Enter new record" button, I
set the record source of the data entry form to a select query from my main
table where the ID is null (New record). On clicking the Save button, I set
the record source back to my default which is just a local table with 1 lilne
of bogus data.

Do you think this eliminate the collisions?

 

[Tim Ferguson]

I set my form up so that when a user clicks the "Enter new record"
button, I set the record source of the data entry form to a select
query from my main table where the ID is null (New record). On
clicking the Save button, I set the record source back to my default
which is just a local table with 1 lilne of bogus data.

It depends: have you checked with your users? Are they still seeing the
hourglass? Check out the settings in your form for the RecordLocks
property, and also the program Options->Advanced for the Default Record
Locking setting.

Your original post sounded like the setting was All Records.

I don't understand the reason for this "local table with 1 bogus record":
bogosity is generally A Bad Thing and should not be necessary. If the
user has another new record to enter, just start another new record; if
she has stopped entering record, then close the form. Anyway...


All the best


Tim F