Copy a record from one database to another-VBForums
Results 1 to 7 of 7

Thread: Copy a record from one database to another

  1. #1

    Thread Starter
    Lively Member Phantom1's Avatar
    Join Date
    Nov 2011
    Posts
    64

    Exclamation 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...

  2. #2

    Thread Starter
    Lively Member Phantom1's Avatar
    Join Date
    Nov 2011
    Posts
    64

    Re: Copy a record from one database to another

    Learning to Program on Earth until I go into Outer Space...

  3. #3
    Web developer Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    12,485

    Re: Copy a record from one database to another

    This might help.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    Please consider giving me some rep points if I help you a lot.
    Links to my code examples can now be found on my website: My websites
    Please rate my post if you find it helpful!
    Technology is a dangerous thing in the hands of an idiot! I am that idiot.

  4. #4

    Thread Starter
    Lively Member Phantom1's Avatar
    Join Date
    Nov 2011
    Posts
    64

    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...

  5. #5
    Web developer Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    12,485

    Re: Copy a record from one database to another

    Not sure if this would help?
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    Please consider giving me some rep points if I help you a lot.
    Links to my code examples can now be found on my website: My websites
    Please rate my post if you find it helpful!
    Technology is a dangerous thing in the hands of an idiot! I am that idiot.

  6. #6
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,193

    Re: Copy a record from one database to another

    Quote Originally Posted by Phantom1 View Post
    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

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    3,089

    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.
    When one of my minions says, "Hey, he's just one guy, what can he do?" I say "This"... and shoot them.

    The problem with putting your lair in a volcano is keeping your robot army from melting.

    I know that the human being and the fish can coexist peacefully - George Bush

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.