Importing tables programatically (how to?)

[BrazilTechie]

Hi everyone.

I wonder if you could help me with a hint on how to import tables from a second database having one open programatically.

Thanks.

 

[AccessAce]

This may not be the best way, but it will get the data into your new database. You will have to recreate the indexes, primary keys, and relationships, but the stuff will get from A to B.

Public Function getTables()
Dim db, db2 As Database
Dim rst As Recordset
Dim tbl As TableDef
Dim tbl2 As TableDef
Dim sql, name As String

Set db = opendatabase("PATH\FILENAME.mdb")
Set db2 = CurrentDb

'Create new tables using Select Statements
For Each tbl In db.TableDefs
    'Ignore all MSys tables
    If Mid(tbl.name, 1, 4) = "MSys" Or Mid(tbl.name, 1, 1) = "~" Or Mid(tbl.name, 1, 1) = "L" Then

    Else
        sql = "INSERT INTO [" & tbl.name & "] Select * FROM [i:\abl\abldbs\abl_md\ABL_MD.mdb].[" & tbl.name & "]"
        DoCmd.SetWarnings False
            DoCmd.RunSQL sql
            Debug.Print tbl.name
        DoCmd.SetWarnings True
    End If

Next tbl


End Function