IConnectCode

Re-Link Access Tables

Relinking Access tables really isn't difficult, and the technique required hasn't changed in a long time. I'm still using DAO code I got from Ken Getz several years ago to accomplish this task. Here's a small code fragment to automate the relink process:

1:     Relinking Access tables really isn't difficult, and the technique 

2:     required hasn't changed in a long time. I'm still using DAO code 

3:     I got from Ken Getz several years ago to accomplish this task. 

4:     Here's a small code fragment to automate the relink process:

5:     

6:     Dim db As DAO.Database

7:     Dim tdf As DAO.TableDef

8:     Dim strPath As String

9:     strPath = "C:\Program
Files\Microsoft Office\Office\Samples\northwind.mdb;"

10:    Set db = CurrentDb()

11:    For Each tdf In db.TableDefs

12:      If (tdf.Attributes AND dbAttachedTable) = _

13:          dbAttachedTable Then

14:        tdf.Connect = ";DATABASE=" & strPath 

15:        tdf.RefreshLink

16:      End If

17:    Next

18:    

19:    Access uses the tabledef's Connect property to locate the back-end 

20:    database, and hook into the table within that database. Simply 

21:    setting the Connect property is not enough. The RefreshLink method 

22:    instructs Access (actually, Jet!) to rebuild the link to the table.

23:    

24:    Notice also how the tabledef's Attributes property is ANDed with 

25:    dbAttachedTable to determine whether the table is linked. This is 

26:    a good example of a technique I discussed a couple of months ago 

27:    for testing whether and ADO Connect object is open. The bitwise 

28:    AND operator (not to be confused with the logical And used in 

29:    "If..Then..Else constructs) returns the left-side value if it is 

30:    included in the right-side value. 2 AND 3 returns 2, and 4 AND 5 

31:    returns 4, but 2 AND 5 returns zero. It gets confusing, but using 

32:    intrinsic constants such as dbAttachedTable considerably clarifies 

33:    the statement.

34:    

35:    It deserves mentioning that the linked tables do not all have to 

36:    reside in the same back-end MDB. You could use multiple back-end 

37:    databases, if necessary. 

38:    

39:    At different times I've recommended to people that their applications 

40:    routinely break links as the database shuts down, then automatically 

41:    rebuild the links as it starts up again. Here's some code for deleting 

42:    linked tables without deleting local tables that must stay in the 

43:    database:

44:    

45:    Public Sub DeleteLinks()

46:      Dim tdf As DAO.TableDef

47:      For Each tdf In CurrentDb.TableDefs

48:        If Left(tdf.Name, 4) <> "MSys" _

49:        And (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then

50:          CurrentDb.TableDefs.Delete tdf.Name

51:        End If

52:      Next tdf

53:    End Sub

54:    

55:    For instance, you might use the DeleteLinks function to break 

56:    all existing linked tables and rebuild them pointing at a different 

57:    back-end database.

58:    

59:    

Posted by sachauncey at August 6, 2003 04:57 PM