Linking to a Table

[EricH]

Can anyone help me with the VBA Coding to open the file select window and link a table (of known name) from one of several different access databases?

TIA

 

[Aivars]

Hi, TIA!

Here is procedure for table links creating to different Access database.
You can write following codes in the Form Load or CommandButton Click procedure:

Private sub Form_Load()
dim strNewLinkPath as string

strNewLinkPath = "C:\Temp\MyDB.mdb"
'Removing links to "C:\Temp\MyDB.mdb"
'if its exist
call LinksDelete(strNewLinkPath)
'Creating links to "C:\Temp\MyDB.mdb"
call LinksCreateToSource(strNewLinkPath)
end sub

Procedure for links creating (you may copy and paste this into any module of your DB):

Public Sub LinksCreateToSource(strLinkSourceDB As String, Optional prpProgressBar As Object)
On Error GoTo Err_LinksCreateToSource
Dim dbs As Database
Dim tdf As TableDef
Dim TdfCount As Long
Dim i As Long

'Open source DB
Set dbs = DBEngine.Workspaces(0).OpenDatabase(strLinkSourceDB)
'Counting tables in the source DB
'for setting progressbar Max property
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> &quot;MSys&quot; Then 'Do not link to the System tables
TdfCount = TdfCount + 1
End If
Next tdf
'prpProgressBar is progress bar from your form (optional)
'you can omit this parameter.
'If you have the progressbar on your form then
If Not prpProgressBar Is Nothing Then
prpProgressBar.Max = TdfCount
prpProgressBar.Visible = True
End If
'Check all tables in source DB (dbs)
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> &quot;MSys&quot; Then 'Do not link to the System tables
'Progressbar updating
i = i + 1
If Not prpProgressBar Is Nothing Then
prpProgressBar.Value = i
End If
'Creating links
DoCmd.TransferDatabase acLink, _
&quot;Microsoft Access&quot;, strLinkSourceDB, acTable, tdf.Name, tdf.Name
End If
Next tdf
'Close source DB
dbs.Close
Set dbs = Nothing
'Progressbar hidding
If Not prpProgressBar Is Nothing Then
prpProgressBar.Visible = False
End If

Exit_LinksCreateToSource:
Exit Sub

Err_LinksCreateToSource:
MsgBox &quot;Error No &quot; & Err.Number & vbLf & Error$, , &quot;Sub LinksCreateToSource&quot;
Stop
Resume Exit_LinksCreateToSource

End Sub

Following function delete links from your DB. You can point DB what links you want to delete or delete all links:

Public Sub LinksDelete(Optional strConnectString As String = &quot;&quot
'If strConnectString is omited all links will be removed
Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
If tdf.Connect <> &quot;&quot; Then 'Check for linked tables
'Check for pointed links
If InStr(1, tdf.Connect, strConnectString, vbTextCompare) > 0 Then
'Removing links
DoCmd.DeleteObject acTable, tdf.Name
End If
End If
Next tdf
End Sub

I hope this will solve your task.

Aivars
LOL My summer holidays will start next week! Hurrah!