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

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