MS Access Multiuser - Best strategy for MU data entry form with multiple record subform

Author

Best strategy for MU data entry form with multiple record subform

AliKwok

2005-08-22, 3:24 am

Hi All

Just how do developers sensibly handle record locking conflicts in this
situation:

I have a multiuser FE/BE A2002 database. There is a single form for both
data entry and subsequent re-editing. The main form is bound to the Sales
Order header table, and the child form to the Line detail table. I decided to
use a transaction so that the user may enter/edit the entire order and
save/abandon it entirely (which seems to be 'universal' behaviour for this
type of interface). This is of course where it comes unstuck! I assumed
(foolishly) that I could use optimistic, record-level locking and get away
with it. The whole thing works beautifully for a single user, but
practically, only one user at a time may enter/edit data because the
possibilty of multiple dirty rows in the subform appears to force Jet to
switch to page-level locking...help!!
I'm using DAO bound recordsets, because I'm only really familiar with DAO -
could ADO help me out here?
I wondered about using an unbound form, or bound form with unbound controls,
but I can't imagine how I could display the multiple detail rows without the
bound subform in datasheet mode.

This must be a classic programming challenge - forgive my ignorance!

Any and all suggestions appreciated.

Ali Kwok

Allen Browne

2005-08-22, 3:24 am

Why use a transaction? That seems to be the place where you are making this
more difficult that it needs to be.

Instead, set up the relation with a cascading delete between the main order
table and the line items table. If the user "abandons" the order entry (i.e.
deletes the main form entry), the related line order items are deleted as
well. So, no transaction is needed for deletes.

As the user adds new records to the line items, no transaction is needed.

If the user edits records in the line items, no transaction is needed.

You're done, and you're avoided the whole puzzle of trying to manage
simultaneous uncommitted transactions beyond the single-record buffering and
optimistic writes that Access does so well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AliKwok" <AliKwok@discussions.microsoft.com> wrote in message
news:015EABB8-276E-487F-96AC- 92C1227268B1@microso
ft.com...
>
> Just how do developers sensibly handle record locking conflicts in this
> situation:
>
> I have a multiuser FE/BE A2002 database. There is a single form for both
> data entry and subsequent re-editing. The main form is bound to the Sales
> Order header table, and the child form to the Line detail table. I decided
> to
> use a transaction so that the user may enter/edit the entire order and
> save/abandon it entirely (which seems to be 'universal' behaviour for this
> type of interface). This is of course where it comes unstuck! I assumed
> (foolishly) that I could use optimistic, record-level locking and get away
> with it. The whole thing works beautifully for a single user, but
> practically, only one user at a time may enter/edit data because the
> possibilty of multiple dirty rows in the subform appears to force Jet to
> switch to page-level locking...help!!
> I'm using DAO bound recordsets, because I'm only really familiar with
> DAO -
> could ADO help me out here?
> I wondered about using an unbound form, or bound form with unbound
> controls,
> but I can't imagine how I could display the multiple detail rows without
> the
> bound subform in datasheet mode.
>
> This must be a classic programming challenge - forgive my ignorance!
>
> Any and all suggestions appreciated.
>
> Ali Kwok

Albert D.Kallal

2005-08-22, 8:25 pm

First, using transaction only works for you recordset code, and DOES NOT
work for forms.

In other words, starting a transaction has not relation to a form.

> the possibilty of multiple dirty rows in the subform appears to force Jet
> to
> switch to page-level locking...help!!

Not to my knowledge at all does the above happen. However, I don't see why
the above would be a problem, or related to this in any way. You don't
mention what version of ms-access, but the last 3 versions of ms-access do
have record locking as opposed to page locking anyway. (so, anything after
a97 would not be a problem. However, even a97 is not a problem, since there
is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD
TABLES!!).

So, this issue is moot, since why bother locking the child records in ANY
WAY at all? The ONLY way you are EVER going to edit a child record is by
first finding, and bringing the master record/form. The child records will
then display. So, you can't get to the child records unless you edit/find
the MASTER record. (so, really, you only need to lock the master record). I
see ZERO reason as to why you have and child record locking in the first
place.

As mentioned, actually all of the above may be moot, as transactions have
NOTHING to do with forms anyway.

> This must be a classic programming challenge - forgive my ignorance!

Allowing a bail out of a master/child forms is something that ms-access does
not do well. You *can* bind recordsets to a form that are wrapped in a
transaction, but this assumes you have a existing design that will allow
this (and that is not normal the case).

So, keep in mind the issue of transactions, the issue of forms, the issue of
locking...as they are all in face separate issues.

Having said the above, you *can* build your own recordsets in code, and then
ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The
link master/child settings do not work when you do this, but it is simple
matter to put the assigning of the parent id in the child form in the before
insert event of the child form

Me!contact_id = Me.Parent!ContactID

So, you can wrap a form in a transaction, but you will thus need to load up
the one record into the master reocrdset, and load up the child records into
a child reocrdset. This likely also means you will need some setup for the
"adding" of records..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@
msn.com
http://www.members.shaw.ca/AlbertKallal

Sponsored Links