How to Copy a Record from One Table to Another in VB

Last reviewed: February 18, 1996
Article ID: Q110588

The information in this article applies to:

Standard and Professional Editions of Microsoft Visual Basic for Windows, version 3.0

SUMMARY

A physical table in a database has records (rows) and fields (columns). A single record contains a single row of field values.

To copy a record in one table to another table, you need to copy all the fields in the source record to the corresponding fields in the destination record. You can do this by using the Value property of the Fields collection, or by using an SQL statement.

MORE INFORMATION

How to Copy a Record Using SQL

You can use the SQL Insert Into statement to copy specified records from one table into another:

   INSERT INTO ToTableName SELECT * FROM FromTableName

You can also add a WHERE clause at the end to add any selected records:

   INSERT INTO ToTableName SELECT FromTableName.* FROM FromTableName
     WHERE Key = 'Key'

How to Use SQL Statements in Visual Basic

Here's an example showing how to use the SQL statements in Visual Basic code:

  Dim db As database, ds As dynaset
  Set db = OpenDatabase("C:\VB3\FOXTEST", False, False, "foxpro 2.5;")
  db.Execute "INSERT INTO ToTableName SELECT FromTableName.*
     FROM FromTableName"

How to Copy a Record Using the Fields Collection and Value Property

The following loop copies all the fields in the current record in table 1 to the corresponding fields in the current record in table 2:

   Dim MyDB As Database, Tbl1 As Table, Tbl2 As Table
   Set MyDB = OpenDatabase("BIBLIO.MDB")     ' Open Database.
   Set Tbl1 = MyDB.OpenTable("Publishers")     ' Open Table.
   Set Tbl2 = ...

   For i = 0 to Tbl1.Fields.Count - 1
      Tbl2(Tbl1.Fields(i).Name).Value = Tbl1.Fields(i).Value
   Next

The above loop assumes that the fields in table 2 are identical to those in table 1.

Additional reference words: 3.00
KBCategory: kbprg
KBSubcategory: APrgDataOther

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 18, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.