linking tables in runtime

[phill86]

Hi,

I am trying to use the modRefreshTableLinks module in the samples database which links the tables to the north wind database. How do i get this to work with my own database ie which lines of code do i have to change for it to look up my tables?

Thanks Phill

[mshmyob]

To link tables via code take a look at the following:

Function RefreshLinks() As Boolean
Dim collTbls As Collection
Dim i As Integer
Dim strTbl As String
Dim dbCurr As Database
Dim dbLink As Database
Dim tdfTables As TableDef
Dim strBeFile As String
Dim collTables As New Collection
Dim tdf As TableDef
 
' get the current linked table definitions
    Set dbCurr = CurrentDb
    dbCurr.TableDefs.Refresh
' end
 
    'First get all linked tables in a collection
    For Each tdf In dbCurr.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
            End If
        End With
    Next
    Set collTbls = collTables
 
' change this string to your drive/directory/filename               
strBeFile = "c:\any directory\backend file name.mdb"
 
            Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
 
  ' start linking your tables - start from the last and work your way down
     For i = collTbls.Count To 1 Step -1
            strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
                Set tdfTables = dbCurr.TableDefs(strTbl)
                With tdfTables
                    .Connect = ";Database=" & strBeFile
                    .RefreshLink
                End With
    Next
   End Function
 

This sample is hard coded for the directory of where your back end tables are. Before using this code you would first have to manually link the tables.

cheers,

Hi,

I am trying to use the modRefreshTableLinks module in the samples database which links the tables to the north wind database. How do i get this to work with my own database ie which lines of code do i have to change for it to look up my tables?

Thanks Phill