join two Data bases

[maddy38]

i am trying to join two data bases by primary key with vb to produce one data base
any ideas
thanks

[peet]

not sure exactly what you want to do, but I think it is something like this...

Private Sub Command1_Click()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    
    Dim sDBFrom As String
    Dim sDBTo As String
    Dim sql As String
    Dim cnn As New ADODB.Connection
    
    sDBFrom = "D:\TEST\From.mdb"
    sDBTo = "D:\TEST\To.mdb"
    
    
    'check to see if the database exist. if not create it.
    If Dir(sDBTo) = "" Then
        'database does not exist, make the database
        Set cat = New ADOX.Catalog
        cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBTo
        Set cat = Nothing
    End If
    
    'setup db connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBFrom
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
 
    'copy all the tables using SQL and Execute
    For Each tbl In cat.Tables
        Debug.Print tbl.Name & "  :  " & tbl.Type
        'Make sure this is a table and not a query / view
        If UCase(tbl.Type) = "TABLE" Then
            'make sure that you do not try to create the system tables,
            'cause they will already be in the db
            If UCase(Left(tbl.Name, 4)) <> "MSYS" Then
                sql = "SELECT * INTO " & tbl.Name & " IN '" & sDBTo & "' FROM " & tbl.Name
                cnn.Execute sql
            End If
        End If
    Next tbl
    
    'close the connection
    Set cat = Nothing
    Set cnn = Nothing
    MsgBox "Import finished", vbInformation
End Sub

this sample copies all tables from one db to another...

[peet]

if you use DAO

Private Sub Command1_Click()
    Dim db As Database
    Dim sDBFrom As String
    Dim sDBTo As String
    Dim td As TableDef
    Dim sql As String
    
    sDBFrom = "C:\TEST\From.mdb"
    sDBTo = "C:\TEST\To2.mdb"
    
    
    'check to see if the database exist. if not create it.
    If Dir(sDBTo) = "" Then
        'database does not exist, make the database
        Set db = CreateDatabase(sDBTo, dbLangGeneral, dbVersion40)
        db.Close
    End If
    
   'open the db where you want to get the data from
    Set db = OpenDatabase(sDBFrom)
 
    'copy all the tables using SQL and Execute
    For Each td In db.TableDefs
        'make sure that you do not try to create the system tables,
        'cause they will already be in the db
        If UCase(Left(td.Name, 4)) <> "MSYS" Then
            sql = "SELECT * INTO " & td.Name & " IN '" & sDBTo & "' FROM " & td.Name
            db.Execute sql
        End If
    Next td
    
    'close the db
    db.Close
End Sub