How to Copy Table from One Database to Another in VB Prof 3.0

Last reviewed: June 21, 1995
Article ID: Q108147
The information in this article applies to:
  • Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SUMMARY

Below is an example of how to copy a table from one database to another using the Professional Edition of Visual Basic version 3.0.

MORE INFORMATION

Sample Program

The following sample code contains two functions taken almost unchanged from the VISDATA sample project, from the code module VISDATA.BAS. The Command1_Click procedure shown below invokes these two functions, CopyStruct and CopyData. NOTE: The VISDATA.MAK project file is installed in the Visual Basic SAMPLES\VISDATA directory.

This example assumes that the databases have Microsoft Access format. The same techniques apply to the other supported database types.

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add a command button to Form1. Add the following code to the Command1 Click event:

       Sub Command1_Click ()
          Dim dbsource As database
          Dim dbdest As database
          ' The following hard-coded database names could be changed to
          ' selections from a text box, list box, or combo box to make the
          ' program more generic:
          Set dbsource = OpenDatabase("c:\vb3\biblio.mdb", True, True)
          Set dbdest = OpenDatabase("c:\vb3\test1.mdb", True, False)
          Print CopyStruct(dbsource, dbdest, "titles", "ctitles", True)
          Print CopyData(dbsource, dbdest, "titles", "ctitles")
          dbsource.Close
          dbdest.Close
       End Sub
    
    

  3. Add the following code to the General Declarations section of Form1:

       'Place the following Function statement on one, single line:
       Function CopyStruct (from_db As Database, to_db As Database,
          from_nm As String, to_nm As String, create_ind As Integer) As Integer
    
          On Error GoTo CSErr
    
          Dim i As Integer
          Dim tbl As New Tabledef    'table object
          Dim fld As Field           'field object
          Dim ind As Index           'index object
    
          'Search to see if the table exists:
          namesearch:
          For i = 0 To to_db.TableDefs.Count - 1
             If UCase(to_db.TableDefs(i).Name) = UCase(to_nm) Then
                If MsgBox(to_nm + " already exists, delete it?", 4) = YES
                Then
                   to_db.TableDefs.Delete to_db.TableDefs(to_nm)
                Else
                   to_nm = InputBox("Enter New Table Name:")
                   If to_nm = "" Then
                      Exit Function
                   Else
                      GoTo namesearch
                   End If
                End If
                Exit For
             End If
          Next
    
          'Strip off owner if necessary:
          If InStr(to_nm, ".") <> 0 Then
             to_nm = Mid(to_nm, InStr(to_nm, ".") + 1, Len(to_nm))
          End If
          tbl.Name = to_nm
    
          'Create the fields:
          For i = 0 To from_db.TableDefs(from_nm).Fields.Count - 1
             Set fld = New Field
             fld.Name = from_db.TableDefs(from_nm).Fields(i).Name
             fld.Type = from_db.TableDefs(from_nm).Fields(i).Type
             fld.Size = from_db.TableDefs(from_nm).Fields(i).Size
             fld.Attributes = from_db.TableDefs(from_nm).Fields(i).Attributes
             tbl.Fields.Append fld
          Next
    
          'Create the indexes:
          If create_ind <> False Then
             For i = 0 To from_db.TableDefs(from_nm).Indexes.Count - 1
             Set ind = New Index
             ind.Name = from_db.TableDefs(from_nm).Indexes(i).Name
             ind.Fields = from_db.TableDefs(from_nm).Indexes(i).Fields
             ind.Unique = from_db.TableDefs(from_nm).Indexes(i).Unique
             If gstDataType <> "ODBC" Then
                ind.Primary = from_db.TableDefs(from_nm).Indexes(i).Primary
             End If
             tbl.Indexes.Append ind
             Next
          End If
    
          'Append the new table:
          to_db.TableDefs.Append tbl
    
          CopyStruct = True
          GoTo CSEnd
    
          CSErr:
          CopyStruct = False
          Resume CSEnd
    
          CSEnd:
       End Function
    
       'Place the following Function statement on one, single line:
       Function CopyData (from_db As Database, to_db As Database,
          from_nm As String, to_nm As String) As Integer
    
          On Error GoTo CopyErr
          Dim ds1 As Dynaset, ds2 As Dynaset
          Dim i As Integer
          Set ds1 = from_db.CreateDynaset(from_nm)
          Set ds2 = to_db.CreateDynaset(to_nm)
          While ds1.EOF = False
             ds2.AddNew
             For i = 0 To ds1.Fields.Count - 1
                ds2(i) = ds1(i)
             Next
             ds2.Update
             ds1.MoveNext
          Wend
          CopyData = True
          GoTo CopyEnd
          CopyErr:
          CopyData = False
          Resume CopyEnd
          CopyEnd:
       End Function
    
    

  4. Start the program or press the F5 key.

  5. You can check to see if the table was copied correctly to the TEST1.MDB database by opening TEST1.MDB with Microsoft Access or with the Data Manager provided with Visual Basic. You can run the Data Manager program from the Window menu in Visual Basic or from the Windows File Manager run DATAMGR.EXE in the Visual Basic directory.

REFERENCE

The VISDATA.MAK file installed in the VB3\SAMPLES\VISDATA directory loads extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. Refer to the VISDATA source code for examples that show how to use each data access function.


Additional reference words: 3.00
KBCategory: kbprg kbcode
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: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.