How to copy a table from one database to other database?

Question

Using VB 6 and Access 2003

I want to copy a table from one database to other database.

Database1

Table1
Table2

Database2

Table3

Above, I want to copy the Table3 to Database-1

Expected Output

Table1
Table2
Table3

How to write a code?

Need VB6 Code Help.

Answer 1

Using ADOX to copy the structure of the data would probably be the easiest way.

Dim sourceCat As New ADOX.Catalog
Dim targetCat As New ADOX.Catalog

Set sourceCat.ActiveConnection = connSource
targetCat.ActiveConnection = connTarget

Dim sourceTable As ADOX.Table
Set sourceTable = sourceCat.Tables("TableName")

Dim newTable As New ADOX.Table
Set newTable.ParentCatalog = targetCat
newTable.Name = sourceTable.Name

Dim sourceCol As ADOX.Column
Dim newCol As ADOX.Column

For Each sourceCol In sourceTable.Columns
    Set newCol = New ADOX.Column
    newCol.Name = sourceCol.Name
    newCol.Type = sourceCol.Type
    newCol.DefinedSize = sourceCol.DefinedSize
    newCol.ParentCatalog = targetCat

    newTable.Columns.Append newCol
Next sourceCol

targetCat.Tables.Append newTable

This is a fairly basic example, it ignores all indexes and column properties (such as autoincrement).

A much more complete example can be found here.

Answer 2

Be aware that you cannot be sure you have extracted all of a table's schema even when using both ADO (which you need for CHECK constraints, WITH COMPRESSION, etc) and ACEDAO (which you need for complex data types, etc).

Here's an example of such a table:

Sub CantGetCheck()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat

    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String
      Sql = _
      "CREATE TABLE Test " & _
      "(" & _
      " data_col INTEGER NOT NULL, " & _
      " CONSTRAINT data_col__be_positive  " & _
      "    CHECK (data_col >= 0), " & _
      " CONSTRAINT data_col__values " & _
      "    CHECK ( " & _
      "           data_col = 0 OR data_col = 1 OR data_col = 2 " & _
      "           OR data_col = 3 OR data_col = 4 OR data_col = 5 " & _
      "           OR data_col = 6 OR data_col = 7 OR data_col = 8 " & _
      "           OR data_col = 9 OR data_col = 10 OR data_col = 11 " & _
      "           OR data_col = 12 OR data_col = 13 OR data_col = 14 " & _
      "           OR data_col = 15 OR data_col = 16 OR data_col = 17 " & _
      "           OR data_col = 18 OR data_col = 19 OR data_col = 20 " & _
      "          ) " & _
      ");"
      .Execute Sql

      Dim rs

      ' 5 = adSchemaCheckConstraints
      Set rs = .OpenSchema(5)

      MsgBox rs.GetString

    End With

    Set .ActiveConnection = Nothing
  End With

End Sub

The output shows that while the definition for the constraint named data_col__be_positive can indeed be extracted, the data_col__values definition cannot (because it exceeds 255 characters).

So really the solution is to always retain the code you used to create and subsequently alter the table. For me, using SQL DDL scripts for the purpose make a lot of sense (I do not need the few features that are not creatable via DDL).