Question Locked

append recordsets

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Using DAO 3.6 in VB6.0, I have multiple Access DBs that possess identical table and field names. I would like to append the corresponding recordsets to the original rs. I've Been strugling with this code for a while to no avail. Would someone please help with this.

ie.: rs.addnew(now, what)?

regards,

David Jensen

6 replies

by breezehillret

Thanks for the advice! I've got to say that I'm not much of an SQL guru... What would an appropriate SQL query with append look like?

regards, David

by robaaaaaa2

OK, something like:

strMyQuery = "INSERT INTO MyNewTable (myfield1, ..... MyField10 ) SELECT MyOldTable.MyField1, ..... MyOldTable.MyField10 FROM MyOldTable "

MyDBConnection.execute strMyQuery

by chitownjerry

Are you trying to move data from one database to another?

If that's the case and you have recordsets open for the identical tables in 2 databases, it would go something like this:

Let's say rsSource is the source table and rsTarget is the the one you want to append into..

Do while Not rsSource.EOF
rsTarget.AddNew
rsTarget!Field1=rsSource!Field1
rsTarget!Field2=rsSource!Field2
rsTarget!Field3=rsSource!Field3
...
rsTarget.Update

rsSource.Movenext
Loop

If you wanted to automate this, you could have a subroutine that you pass a table name to that would loop thru all fields, something like this:

Do While NOT ds1.EOF
rsTarget.AddNew
For nCtr = 0 To rsSource.Fields.Count - 1
rsTarget(nCtr) = rsSource(nCtr)
Next
rsTarget.Update
rsSource.MoveNext
Loop

BTW.. stop using DAO.. use ADO.

Good luck!

Jerry

by breezehillret

Thanks Jerry, I'll give that a whirl... My code was written some time ago and I'm adding the multi db scenario and trying to keep from having to rewrite all the previous code. Otherwise I'd go with ADO. I'm actually writing the recordsets to a listview box for viewing rather than to the db.

This discussion is closed to new replies.