-
Apr 27th, 2012, 05:20 AM
#1
Thread Starter
Lively Member
Copy a record from one database to another
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.
Code:
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?
Last edited by Phantom1; Apr 28th, 2012 at 03:58 AM.
Learning to Program on Earth until I go into Outer Space...
-
Apr 28th, 2012, 12:54 AM
#2
Thread Starter
Lively Member
Re: Copy a record from one database to another
Learning to Program on Earth until I go into Outer Space...
-
Apr 28th, 2012, 03:14 AM
#3
Re: Copy a record from one database to another
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Apr 28th, 2012, 04:02 AM
#4
Thread Starter
Lively Member
Re: Copy a record from one database to another
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.
Learning to Program on Earth until I go into Outer Space...
-
Apr 28th, 2012, 08:57 PM
#5
Re: Copy a record from one database to another
Not sure if this would help?
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Apr 30th, 2012, 05:09 AM
#6
Re: Copy a record from one database to another
Originally Posted by 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.
Code:
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.
Code:
insert into rstransactionArchive(column1,column2) values(value1,value2);
The code you have is going to throw a run-time error.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Apr 30th, 2012, 07:33 AM
#7
Re: Copy a record from one database to another
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|