Create a linked table in Access 2007 using VB

[sphinney]

I have data in a few external Access tables that I want to link to my Access 2007 database. Depending on situation (and permissions of the person using the database) I want to dynamically link to one or more of the external tables when a specific user form opens. Then I want to delete the links when the form closes.

How does one go about creating and deleting a linked table using VB? I know the name of the database files and the tables I want to link but I'm not sure which objects & methods to use (so I don't know where to look in the the Access help file).

Could someone point me in the right direction? I don't need the complete code, just an object and/or method name so I know where to look in the help files. I've been digging for a couple of hours at this point and I'm pretty frustrated that I can't figure this one out on my own.

Thanks,
sphinney

[mshmyob]

Here is some simple code to link your tables for Access. This is hard coded so you need to know in advance where your BE is to be located.

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
 

To use this you must have at one time manually linked the tables. After that you can use this code to relink them anytime to any server/directory. Works with runtime also.

Hope this gets you started.

cheers,

[sphinney]

Thank you! This certainly does get me started. Much obliged.

Here is some simple code to link your tables for Access. This is hard coded so you need to know in advance where your BE is to be located.

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
 

To use this you must have at one time manually linked the tables. After that you can use this code to relink them anytime to any server/directory. Works with runtime also.

Hope this gets you started.

cheers,

[mshmyob]

You are welcome. Good luck.

cheers,

Thank you! This certainly does get me started. Much obliged.

[neelsfer]

where do you copy this code to? how do you link it to button

[mshmyob]

You can put the code anywhere. The way I have shown it was putting it in a function in the form and then calling the function with the OnClick event of the button.

You could also put it in a module and call it that way or you can remove the 1st and last line (the function lines) and just put the whole code in the OnClick event of the button.

cheers,