Is there a way to link/unlink all tables from an mdb at once?

[red22j]

My code is the following: but it has to load each table individually and it takes a really long time - especially because the new file is somewhere else on the network. Is there a way to load all tables from one file at once? Also, is there a way to unload all at once...say all the tables in a given mdb?

Public Sub LinkTables(FileName As String)

Dim NewDatabase As Database
Dim TableCounter As Integer

Set NewDatabase = OpenDatabase(FileName)
TableCounter = 0
While TableCounter < NewDatabase.TableDefs.count
DoCmd.TransferDatabase acLink, _
&quot;Microsoft Access&quot;,FileName, acTable, _
NewDatabase.TableDefs(TableCounter).Name, _
NewDatabase.TableDefs(TableCounter).Name
TableCounter = TableCounter + 1
Wend
End Sub

thanks in advance,
Jon

 

[ryan1]

This is not my code but some body from this area was cool enough to send it to me so i thought i would pass the favor. The code works awesome if used properly.

'Set this up in a click event this calls on the module below

Private Sub cmbDelete_Click()
LinksDelete
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''

'Set this private sub up in a form on a click event this
Private Sub cmbLink_Network_Click()

Dim sNewLink As String
' ReLink the data files to network locations
' Need to set up the strings
sNewLink = &quot;Path of tables you are linking&quot;
Call LinksCreateToSource(sNewLink)
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