Copy a record from one database to another

[Phantom1]

I have a 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 tblProductTransaction WHERE TransactionID = " & cboID.Text, Conn 'Conn is connection to Database
rsTransactionArchive.Open "tblProductTransaction", ArchiveConn 'ArchiveConn is connection to Archive
ArchiveConn.Execute "INSERT INTO rsTransactionArchive rsTransaction"
End Sub

ArchiveConn.Execute "INSERT INTO rsTransactionArchive rsTransaction" results in "Syntax error in INSERT INTO statement". How should I proceed with this?

[Phantom1]

[Nightwalker83]

This might help.

[Phantom1]

There is a problem. The record in tblProductTransaction is a child record of 2 parent records (1 from tblProduct and 1 from tblTransaction), and these 2 parent records are each child records of other tables (the tblProduct record is the child of a tblSupplier record, the tblTransaction record is the child of a tblCustomer record), and so on. This means I have to copy every parent record to Archive then save the child record.

Would an alternative solution be to remove all the relationship from Archive so that all records are on the same level? Archive becomes flat, then, which makes it possible to save a child record from Database to Archive without worrying about the parent record in Database, while Database is relational.

[Nightwalker83]

Not sure if this would help?

[abhijit]

I have a 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 tblProductTransaction WHERE TransactionID = " & cboID.Text, Conn 'Conn is connection to Database
rsTransactionArchive.Open "tblProductTransaction", ArchiveConn 'ArchiveConn is connection to Archive
ArchiveConn.Execute "INSERT INTO rsTransactionArchive rsTransaction"
End Sub

ArchiveConn.Execute "INSERT INTO rsTransactionArchive rsTransaction" results in "Syntax error in INSERT INTO statement". How should I proceed with this?

Is this in SQL Server or access? The recordset object you're trying to pass to the insert statement is not going to work that way.

You'll have loop through the columns in the record and then get each value and form a complete insert statment.

The statement in the quotes should look something like this.

insert into rstransactionArchive(column1,column2) values(value1,value2);

The code you have is going to throw a run-time error.

[FunkyDexter]

Would an alternative solution be to remove all the relationship from Archive so that all records are on the same level?

That decision depends on the business really but it sounds like a reasonable suggestion to me. Just be aware of the implications.

An archive database isn't really intended to be read, or at least, not trivially. On that basis I think it's reasonable to just copy the transaction record across without storing all it's lookup data. The record in the archive database will then be fairly meaningless but, if you wanted to read them, you could restore them to the transactional DB at which point they'd "re-link" to their look up data automatically. Alternatively, when querying the archive DB you could link to teh lookup data via a linked server.

The major implication with this aproach is that you'd have to be careful deleting or updating the lookup data in teh main database. Your archive won't have any foreign keys to it so the DBMS isn't going to enforce referential integrity for you.

E.G. The DBMS would prevent you from deleting a salesman from the system as long as he had an order record, but once that order record has been archived off the DBMS will allow you to delete him and you won't then be able to restore that order record to read it at a later date.