copy record and child records

[carlohotmeel]

How can I copy I recordset with its childrecords and their child records?? Please advise, I'm familiar with sql and vba and I'm trying to make function but need some help. Thanks and merry X-mas

 

[dhookom]

Do you have a good reason to duplicate records? This is normally not recommended so it might be appropriate to suggest why you need this and maybe provide some sample records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[carlohotmeel]

Hi, the reason for copying the records is because it's new data. We have a productlist, and each product has its specifications, 1-many, When we add a new product we only want to adjust some specifications.

 

[dhookom]

Have you considered append queries? This would be the most efficient method of copy records from one table to another.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[carlohotmeel]

Yes, appendqueries will be ok, but te point is, I need the new ID, and then append them in a new record with this New ID, and so on. How do I get the new ID?

 

[RoyVidar]

Here's an attempt at the initial append, and fetching the new ID of this record, then continue with the child records.

[tt]dim cn as adodb.connection
dim rs as adodb.recordset
dim strSql as string
dim lngID as long

set cn = currentproject.connection ' or other connection, if needed

strSql = "Insert into YourNewTable (field1, field2, ...fieldN) " & _
"Select field1, field2, ...fieldN from YourExistingTable " & _
"where YourExistingTable.SomeField = SomeValue"
cn.execute strSql,,adcmdtext+adexecutenorecords
set rs = cn.execute("select @@identity",,adcmdtext)
lngID rs.fields(0).value[/tt]

You will probably have to use SQL syntax like above, i e list all columns except the Autonumber column ...

Roy-Vidar