Best strategy for MU data entry form with multiple record subform

AliKwok,

I was about 80% of an application and something alike happens. Because
Access does not locks records but locks pages of records, you basically have
two options: Enlarge the child records size to a page-size or change the
data files to record-level locking data file. In my case for this reason
and others, I change the whole thing to a different platform. You could
only change the backend to a record-level locking data file such as DBFs and
the problem would be solve with some adjustments.


--
________________
German Saer

Orlando, FL

"David C. Holley" wrote in message
...
Have you considered the suggestion that I made in another post? In the
amount of time spent banging your head on the wall to get your fax
software to work, you could have easily driven the fax across the
street. (ie Sometimes is better to consider a different approach)

AliKwok wrote:
Thanks for your reply Albert.

I have indeed wrapped the form in a transaction by programmatically
assigning the recordsets, and as I wrote, it was working fine for a
single
user, but appears to lock whole pages when 2 users are attempting to
enter
new records, or edit old ones. Version is A2002, as I wrote.
Since my settings are No Locks/Record-level locking and the recordsets'
locking setting is Optimistic, I can only suggest that 1) the
Transaction
must override the optimistic setting, and 2) the potential for multiple
child
records within the scope of the Transaction must override the
record-level
locking. This is only my hypothesis to explain the observed behaviour -
I
havn't had time to test it!

Thanks for your time and thought.

Ali

"Albert D.Kallal" wrote:


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

http://www.members.shaw.ca/AlbertKallal

Because
Access does not locks records but locks pages of records, you basically
have
two options: Enlarge the child records size to a page-size or change the
data files to record-level locking data file.

The last 3 versions of ms-access (2000, 2002, and 2003) ALL support record,
or row locking. You have to go back about 10 years to office 97 for a
version that locks pages. So, your information is incorrect, and VERY
old....

And, while we are at this, this issue is not a ms-access problem, but what
database you are using (in this case, JET). As mentioned, JET for a long
time does support row/record locking. You can use ms-access with JET, sql
server, oracle etc. So, how things works is going to depend on what database
you use, and not the development system called ms-access.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Yes David, thank you, I did.

Forgive me not replying sooner - I was busy running acreoss the road.

Yes, I went back to the drawing board and ended up using local temporary
tables to store new records or changes.
I now performs as I intended in the first place, and the client is happy
with it!

Thank you all for your thoughts (some of which I haven't yet read - will do
so now)

Cheers

AliKwok

"David C. Holley" wrote:

Have you considered the suggestion that I made in another post? In the
amount of time spent banging your head on the wall to get your fax
software to work, you could have easily driven the fax across the
street. (ie Sometimes is better to consider a different approach)

AliKwok wrote:
Thanks for your reply Albert.

I have indeed wrapped the form in a transaction by programmatically
assigning the recordsets, and as I wrote, it was working fine for a single
user, but appears to lock whole pages when 2 users are attempting to enter
new records, or edit old ones. Version is A2002, as I wrote.
Since my settings are No Locks/Record-level locking and the recordsets'
locking setting is Optimistic, I can only suggest that 1) the Transaction
must override the optimistic setting, and 2) the potential for multiple child
records within the scope of the Transaction must override the record-level
locking. This is only my hypothesis to explain the observed behaviour - I
havn't had time to test it!

Thanks for your time and thought.

Ali

"Albert D.Kallal" wrote:


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

http://www.members.shaw.ca/AlbertKallal

The temp tables of course being the alternative. My suggestion was
geared to a back-end workaround that *MIGHT* minimize the number of changes.

So I take it theres a good Irish Pub across the street?

AliKwok wrote:
Yes David, thank you, I did.

Forgive me not replying sooner - I was busy running acreoss the road.

Yes, I went back to the drawing board and ended up using local temporary
tables to store new records or changes.
I now performs as I intended in the first place, and the client is happy
with it!

Thank you all for your thoughts (some of which I haven't yet read - will do
so now)

Cheers

AliKwok

"David C. Holley" wrote:


Have you considered the suggestion that I made in another post? In the
amount of time spent banging your head on the wall to get your fax
software to work, you could have easily driven the fax across the
street. (ie Sometimes is better to consider a different approach)

AliKwok wrote:

Thanks for your reply Albert.

I have indeed wrapped the form in a transaction by programmatically
assigning the recordsets, and as I wrote, it was working fine for a single
user, but appears to lock whole pages when 2 users are attempting to enter
new records, or edit old ones. Version is A2002, as I wrote.
Since my settings are No Locks/Record-level locking and the recordsets'
locking setting is Optimistic, I can only suggest that 1) the Transaction
must override the optimistic setting, and 2) the potential for multiple child
records within the scope of the Transaction must override the record-level
locking. This is only my hypothesis to explain the observed behaviour - I
havn't had time to test it!

Thanks for your time and thought.

Ali

"Albert D.Kallal" wrote:



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

http://www.members.shaw.ca/AlbertKallal

No, Opium Den ;0]

"David C. Holley" wrote:

The temp tables of course being the alternative. My suggestion was
geared to a back-end workaround that *MIGHT* minimize the number of changes.

So I take it theres a good Irish Pub across the street?

AliKwok wrote:
Yes David, thank you, I did.

Forgive me not replying sooner - I was busy running acreoss the road.

Yes, I went back to the drawing board and ended up using local temporary
tables to store new records or changes.
I now performs as I intended in the first place, and the client is happy
with it!

Thank you all for your thoughts (some of which I haven't yet read - will do
so now)

Cheers

AliKwok

"David C. Holley" wrote:


Have you considered the suggestion that I made in another post? In the
amount of time spent banging your head on the wall to get your fax
software to work, you could have easily driven the fax across the
street. (ie Sometimes is better to consider a different approach)

AliKwok wrote:

Thanks for your reply Albert.

I have indeed wrapped the form in a transaction by programmatically
assigning the recordsets, and as I wrote, it was working fine for a single
user, but appears to lock whole pages when 2 users are attempting to enter
new records, or edit old ones. Version is A2002, as I wrote.
Since my settings are No Locks/Record-level locking and the recordsets'
locking setting is Optimistic, I can only suggest that 1) the Transaction
must override the optimistic setting, and 2) the potential for multiple child
records within the scope of the Transaction must override the record-level
locking. This is only my hypothesis to explain the observed behaviour - I
havn't had time to test it!

Thanks for your time and thought.

Ali

"Albert D.Kallal" wrote:



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

http://www.members.shaw.ca/AlbertKallal

How were the brownies? Did you say hi to Rush?

AliKwok wrote:
No, Opium Den ;0]

"David C. Holley" wrote:


The temp tables of course being the alternative. My suggestion was
geared to a back-end workaround that *MIGHT* minimize the number of changes.

So I take it theres a good Irish Pub across the street?

AliKwok wrote:

Yes David, thank you, I did.

Forgive me not replying sooner - I was busy running acreoss the road.

Yes, I went back to the drawing board and ended up using local temporary
tables to store new records or changes.
I now performs as I intended in the first place, and the client is happy
with it!

Thank you all for your thoughts (some of which I haven't yet read - will do
so now)

Cheers

AliKwok

"David C. Holley" wrote:



Have you considered the suggestion that I made in another post? In the
amount of time spent banging your head on the wall to get your fax
software to work, you could have easily driven the fax across the
street. (ie Sometimes is better to consider a different approach)

AliKwok wrote:


Thanks for your reply Albert.

I have indeed wrapped the form in a transaction by programmatically
assigning the recordsets, and as I wrote, it was working fine for a single
user, but appears to lock whole pages when 2 users are attempting to enter
new records, or edit old ones. Version is A2002, as I wrote.
Since my settings are No Locks/Record-level locking and the recordsets'
locking setting is Optimistic, I can only suggest that 1) the Transaction
must override the optimistic setting, and 2) the potential for multiple child
records within the scope of the Transaction must override the record-level
locking. This is only my hypothesis to explain the observed behaviour - I
havn't had time to test it!

Thanks for your time and thought.

Ali

"Albert D.Kallal" wrote:




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

http://www.members.shaw.ca/AlbertKallal