Dynamically linking Tables in Access

[hianupam]

I am trying to come up with a MDB that has linked tables to two other MDB's
The only problem is that the path to the two MDB's keep changing. So I am thinking of coming up with a form that asks the location for the two mdb's and links to all the tables to it. Any Idea's, Starting points will be greatly appreciated.
THanks,
Anupam

 

[Russell Hauf]

This would be a great place to start:

http://www.mvps.org/access/tables/tbl0009.htm

hth,

Russell

 

[hianupam]

Thanks, I will post my solution when I come up with it

 

[hianupam]

Here is the solution that I came up with.

I created a form with two Command buttons that promts the users to select an input and an output database. And then once I had the paths i proceded to link them.

Check to see that the input and output databases are not the same

Function chkSameDB()
  If Me.txtDispInputDb.Value = Me.txtDispOutputDb.Value Then
    chkSameDB = True
  Else
    chkSameDB = False
  End If
End Function

Check to see that the file selected is actually an access file

Function chkFname(infile) As Boolean
    If Right(infile, 3) <> "mdb" Then
        chkFname = False
    Else
        chkFname = True
    End If
End Function

Open a dialog box to help user select a input file and ...

Private Sub cmdInput_Click()
    cmnDialog.ShowOpen
    g_inputfile = cmnDialog.FileName
    If chkFname(g_inputfile) Then
        Me.txtDispInputDb.Value = g_inputfile
    Else
        MsgBox ("Please select an Access Database")
    End If
End Sub

...an output file

Private Sub cmdOutput_Click()
    cmnDialog.ShowOpen
    g_outputfile = cmnDialog.FileName
    If chkFname(g_outputfile) Then
        Me.txtDispOutputDb.Value = g_outputfile
    Else
        MsgBox ("Please select an Access Database")
    End If
End Sub

Here is where everything comes together

Private Sub cmdLinkFiles_Click()

Set db1 = DBEngine(0).OpenDatabase(Me.txtDispInputDb.Value)
Set db2 = DBEngine(0).OpenDatabase(Me.txtDispOutputDb.Value)
Set db = CurrentDb
If chkSameDB Then
  MsgBox ("Input and Output Databases cannot be the same")
  Me.txtDispInputDb.SetFocus
Else
For Each tbl In db.TableDefs
    tblname = tbl.Name
    If Left(tblname, 4) <> "MSys" Then
        DoCmd.DeleteObject acTable, tblname
    End If
Next tbl
For Each tbl In db1.TableDefs
    tblname = tbl.Name
    If Left(tblname, 4) <> "MSys" Then
        Set tdf = db.CreateTableDef(tblname)
        tdf.Connect = ";DATABASE=" & Me.txtDispInputDb.Value
        tdf.SourceTableName = tblname
        'MsgBox (tdf.SourceTableName)
        db.TableDefs.Append tdf
        RefreshDatabaseWindow
    End If
Next tbl
For Each tbl In db2.TableDefs
    tblname = tbl.Name
    If Left(tblname, 4) <> "MSys" Then
        Set tdf = db.CreateTableDef("OutPut_" & tblname)
        tdf.Connect = ";DATABASE=" & Me.txtDispOutputDb.Value
        tdf.SourceTableName = tblname
        'MsgBox (tdf.SourceTableName)
        db.TableDefs.Append tdf
        RefreshDatabaseWindow
    End If
Next tbl
End If
MsgBox ("Tables Linked,Please Click on the Tables Tab to View Them")
End Sub

 

[Russell Hauf]

Glad you got it to work. You may want to try putting the call to chkSameDB before you do your Set statements - this will make it faster and avoid memory problems that may occur.

-Russell

 

[hianupam]

Thanks. Hadn't thought about that !!!!