Updating a disconnected hiearchical recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ivan Debono - 26 Oct 2004 12:10 GMT

HI all,

I have a parent-child recordset where the primarkeys are autonumbers and the
child recordset has a foreign key to the parent, example:

Parent ID: 4
ChildID: 1000
Child_ParentID: 4

These recordsets are flagged as adEditAdd. I initially set the StayInSync to
false.

Now I want to do an insert into another database, but instead of keeping the
original ID's I need to get new ones (so I do a SELECT MAX), and setting the
id's with the new values. The records in the child recordset are somehow
being lost when I set the Child_ParentID to the new value.

The RecordCount of the child recordset is initially 3. I set the foreign key
value and the RecordCount goes down to 2. Editmode remains adEditAdd and the
AbsolutePosition and AbsolutePage are set to Unknown.

Has this problem got to do with the order of the updates or whethere to use
Update vs UpdateBatch??

Ivan

Reply to this Message

Val Mazur - 27 Oct 2004 02:43 GMT

Hi Ivan,

When you change any ID (on parent or child), this record goes out of scope
for the parent or child. For example as soon as you change ID for the
parent, it will not see any previously related children anymore. I believe
you could try to use bookmarks to the parent and child records and reference
them based on bookmark, but I have never tested this way and it might not
work. Another way is first to set reference to the recordset, which holds
children. Then change ID for the parent. Now, since you still have a
reference to the child recordset, change IDs for the children. It should
work.

Signature

Val Mazur
Microsoft MVP

> HI all,
>
[quoted text clipped - 28 lines]
>
> Ivan

Reply to this Message

Ivan Debono - 27 Oct 2004 07:36 GMT

I tried changing id's, synchronising records, etc...

The only way that worked was to persist all chapters before chaning
anything. Do the changes on the parent, batch update. Then go through the
persisted chapters, do the changes, batch update. Had to do this recusively
though because I don't know how many levels deep a hierarchy can go :)

Ivan

> Hi Ivan,
>
[quoted text clipped - 40 lines]
> >
> > Ivan

Reply to this Message

Val Mazur - 30 Oct 2004 02:34 GMT

Hi Ivan,

Initially I thought you need to update recordset locally, not is a database.
Yes, in a case of updating hierarchical recordset against database, then
provider does batch update ONLY for the parent level and you would need to
use recursion to update all the children. This is by design and, I believe,
described in a documentation for ADO

Signature

Val Mazur
Microsoft MVP

>I tried changing id's, synchronising records, etc...
>
[quoted text clipped - 60 lines]
>> >
>> > Ivan

Reply to this Message

VB Forum / Database Access / October 2004

Updating a disconnected hiearchical recordset