Copy a record from one database to another

[Phantom1]

I have a Microsoft Access database called Database which the VB6 program uses. I want to archive specific transaction records, i.e. send them from Database to another database called Archive which is of the same structure (same table names, fields, relationships) as Database.

The user selects an ID from a combo box and the matching record is archived on clicking on the command button cmdArchive.

This is what I have written so far.

Private Sub cmdArchive_Click()
Dim rsTransaction as New ADODB.Recordset, rsTransactionArchive as New ADODB.Recordset
Call OpenDatabase 'function to open Database
Call OpenArchive 'function to open Archive
rsTransaction.Open "SELECT * FROM tblTransaction WHERE TransactionID = " & cboID.Text, Conn 'Conn is connection to Database
rsTransactionArchive.Open "tblTransaction", ArchiveConn 'ArchiveConn is connection to Archive
ArchiveConn.Execute "INSERT INTO rsTransactionArchive rsTransaction"
End Sub

ArchiveConn.Exe cute "INSERT INTO rsTransactionAr chive rsTransaction" results in "Syntax error in INSERT INTO statement". How should I proceed with this?

I have tried this:

Private Sub cmdArchive_Click()
Call OpenArchive
Dim rsTransactionArchive As New ADODB.Recordset
rsTransactionArchive.Open "tblTransaction", ArchiveConn, , adLockOptimistic
ArchiveConn.Execute "INSERT INTO tblTransaction SELECT * FROM tblTransaction WHERE TransactionID = " & CInt(cboID.Text) & " IN ('Project.mdb')"
End Sub

Nothing is copied to Archive.

[Rabbit]

Link to the archive table in your main database. Then you can use a standard insert query.

[roshan ban]

You just need to create a two record set; One for Selecting All Record and Other For inserting into another table of the database
Then Use Do while rs1.Eof = false of try If rs1.Eof = false
rs2.addnew
rs2("FieldName" )=rs1("FieldNam e")
rs2.update
Loop or End if

[Killer42]

I find the easiest way to develop a query like this is to do it in Access, then switch to SQL view and copy the SQL it built for you.