Appending related records to two related tables 1

[Francis]

Can anyone help with this?<br>I have two tables, related via an index where the index is an autonumbered field in one of the tables.<br>I then want to append a collection of related records to both tables, retaining the relationships.<br>At present I do this record by record, appending the first one to the table with the autonumbered index, finding out the new index, then appending the records in the related table, changing the index to the new one.<br>This is very slow - is there some SQL that can do this automatically?<br>Thanks<br>

 

[DougP]

Yes<br>But first keep in mind that the Autonumber Index is not known (as you found out) until the records are added.<br>So I don't think it could be done in one SQL statement.<br><br>I know a code way that will do it.<br>'Open database<br>Set 2 recordsets one for each table<br>In a Do loop...<br>Add new record to one with autonumber first<br>update new record<br>Get Autonumber form above record<br>Add record to second table with number from above<br>update record<br>Loop <br>close record sets<br>

 

[Francis]

Thanks, in fact that is what I am doing now. I just thought that it seems so inefficient that maybe I was missing a way to do it all in SQL.

 

[Guest_imported]

Francis, could you send me a copy of your code, I'm trying to do something like this, too.<br><br>TIA<br><br>Mark<br><A HREF="mailto:mrothstein@iitri.org">mrothstein@iitri.org</A>

 

[Francis]

Something like this,<br>Sub AppendRelatedRecords()<br>Dim db As Database<br>Dim recSourceMain As Recordset<br>Dim recSourceSub As Recordset<br>Dim recDestMain As Recordset<br>Dim recDestSub As Recordset<br>Dim NewKeyVal As Long<br>'change sqlSourceMaindata to a query to your data source<br>Set db = OpenDatabase(&quot;YourDBName&quot<br>Set recSourceMain = db.OpenRecordset(&quot;SELECT * FROM sqlSourceMaindata&quot;, dbOpenForwardOnly)<br>&nbsp;'change sqlDestMaindata is to query to your data dest<br>Set recDestMain = db.OpenRecordset(&quot;SELECT * FROM sqlDestMaindata&quot;, dbOpenDynaset)<br>&nbsp;'change sqlDestSubdata is a query to your data dest<br>&nbsp;&nbsp;&nbsp;&nbsp;Set recDestSub = db.OpenRecordset(&quot;SELECT * FROM sqlDestSubdata&quot;, dbOpenDynaset)<br>&nbsp;&nbsp;&nbsp;&nbsp;'do this for each main record to be copied<br>&nbsp;&nbsp;&nbsp;&nbsp;Do While Not recSourceMain.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'change sqlSourceSubdata to a query to your data source<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set recSourceSub = db.OpenRecordset(&quot;SELECT * FROM sqlSourceSubdata WHERE KeyName = &quot; & recSourceMain!KeyName, dbOpenForwardOnly)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CopyRecord NewKeyVal, &quot;KeyName&quot;, recSourceMain, recDestMain<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'do this for each sub record to be copied<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do While Not recSourceSub.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CopyRecord &quot;&quot;, &quot;&quot;, recSourceSub, recDestSub<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;recSourceSub.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;recSourceMain.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>recDestSub.Close<br>recDestMain.Close<br>recSourceMain .Close<br>Set recDestMain = Nothing<br>Set recDestSub = Nothing<br>Set recSourceMain = Nothing<br>Set recSourceSub = Nothing<br>db.Close<br>Set db = Nothing<br>End Sub<br><br>Sub CopyRecord(NewKey As Long, KeyName As String, recCopyFrom As Recordset, recCopyTo As Recordset)<br>Dim i As Integer<br>recCopyTo.AddNew<br>'enumerate the fields<br>For i = 0 To recCopyTo.Fields.count - 1<br>'if the field is the autonumber keyfield then remember the new Keyname<br>&nbsp;&nbsp;&nbsp;&nbsp;If recCopyTo.Fields(i).name = KeyName Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NewKey = recCopyTo(KeyName).Value<br>&nbsp;&nbsp;&nbsp;&nbsp;Else 'copy over the data<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;recCopyTo(recCopyFrom.Fields(i).name).Value = recCopyFrom.Fields(i).Value<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>Next<br>recCopyTo.Update<br>End Sub

 

[elizabeth]

An alternate method is to append all to the first table, then use a FindUnmatched-type append query to append all the items not found (based on that autonumber field) <b>in </b>the second table <b>to</b> the second table.

 

[JumpinJimRivers]

Hi Francis, I found this very old thread you posted and I think the code you gave for appending records and their associated sub records is what I'm looking for, but my VBA knowledge isn't that great and I was wondering if you could help me figure out somehting. At first I just replaced the variables in the SQL with the correct names of the source and destination queries, but now it looks like I should replace the &quot;KeyName&quot; with my autonumber ID somehow, but replacing all of them gives me error messages too. Can you tell me what I need to do? I would appreciate it much!

-Blaine

 

[Francis]

I had forgotten completely that I had posted the above.
Yes, Keyname must be changed to the name of the key in your primary table, the auto numbered one.
The secondary table uses a non auto numbered field with the same Keyname - duplicates allowed.
The code I posted was generic, so CopyRecord works with any table. You may not need that can could then use specific field names avoiding the need to pass the key field name.
That is not very clear, sorry I am too busy at this moment, but I hope that gives you an idea.