[AccessD] Access XP forms bound to ADO recordsets

Check out p61-68 of the ADH v2. It has a good ADO locking discussion
including ADO retry code.
Jim Hale

-----Original Message-----
From: John Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Thursday, March 09, 2006 12:54 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Access XP forms bound to ADO recordsets


Can someone give me an overview of what this involves?  Is the recordset
then "disconnected"?  How do you handle form/subforms?

I have a specific application where the users have a bound form which pulls
a set of records (people) that they will be making calls to.  There is a
bound subform where they enter the results of the call - about 5 or 6
fields, the user's ID, date/time, call status, note etc.  They get a TON of
locking issues with this. The user is almost exclusively ADDING new records
into the call status table, though they do occasionally "correct" info in
the person's record - address / phone etc.

The issue though is that as soon as they try to create a NEW record in the
call status subform, they get the infamous "another person has locked this
record" which is particularly puzzling since they are not editing an
existing record but rather adding a new record.  I changed the note from
Memo to String(255) to avoid "page locking" issues, deleted all of the
indexes on the fields in the call status records etc but the issues still
occur.  

My understanding is that Index data is also stored in pages so that there
might be an issue with the page that an index is attempting to store it's
data in as the record is created is locked by another user creating an index
using that same page.  Of course there is a relationship between the person
and the call status so there is at least one index that simply cannot be
even seen, never mind deleted, at least without removing the relationship.

So I am trying to resolve this continuous nagging locking issue.  I am even
willing to go unbound, but they really want to be able to see the previous
calls in the subform so that they can tell what went on in previous calls.
I thought that if I could bind the main people form and the child call
status forms to ADO recordsets, then the update (and subsequent locking
issues) would be very momentary and the probability of a lock issue
occurring would decrease dramatically.  However I have never done the "ADO
recordset binding" before.

Does anyone have any knowledge of this?

John W. Colby
www.ColbyConsulting.com 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.