How to locate linked tables automatically.

Topic

New Reply

I have an MS Access database that has a linked table. For various reasons the location where the database and linked table is stored changes. Is there a way to store the linked table so that I don’t have to go in and update the location in the linked table manager? Like can the linked table be in the same folder as the accde file and it find the links?

Thanks

Reply | Quote

Replies

B,

Here’s some code I use to dynamically relink tables.

Code:

Option Compare Database
Option Explicit

‘+—————————————————————————+
‘| Version: 6.1                   Programmed by: The Computer Mentor    |
‘| Dated  : 10/20/2010                                                                 |
‘+—————————————————————————+

Public zStatusMsg     As String
Public lTimerInterval As Long
Public Const zCodeVersionNo = “6.1”

‘                            +——————+                 +———-+
‘————— ——-|  ReLinkTable()   |————–| 10/20/10 |
‘                            +——————+                 +———-+
‘Called by: Macro – AutoExec
‘Calls    : [Utilities] zGetDBPath()
‘Globals  : lTimerInterval
‘           zStatusMsg
‘Notes    : 03/04/10 – Added timed relink message vs msgbox w/user action
‘           06/04/10 – Added shared access for Office Computers P-P Lan
‘           06/10/10 – Addes shared access for Computer Mentor P-P Lan

Function ReLinkTable()

   Dim zDBPath             As String
   Dim zDBFullName      As String
   Dim zBEDBFN           As String
   Dim zTableName(12) As String
   Dim iTblCnt              As Integer
   
   GoTo StartLinking

FileDoesNotExist:

   If Err.Number = 7874 Then
     Resume Next
   Else
     MsgBox “Error No: ” & Err.Number & vbCrLf & _
            “Description: ” & Err.Description
   End If

StartLinking:

   zTableName(0) = “Docks”
   zTableName(1) = “Lots”
   zTableName(2) = “Owners”
   zTableName(3) = “PhoneDir”
   zTableName(4) = “StorageLots”
   zTableName(5) = “tblAuxNumbers”  ‘*** Last table in ARB_be.mdb       ***
   zTableName(6) = “Builders”       ‘*** Start of tables in ARBReqs.mdb use index in If iTblCnt = below***
   zTableName(7) = “Letters”
   zTableName(8) = “ARBMembers”
   zTableName(9) = “ARBAssignments”
   zTableName(10) = “Requests”
   zTableName(11) = “RequestTypes”
   
   zDBPath = zGetDBPath()
   
   If zDBPath = “Error” Then
     MsgBox Environ(“USERNAME”) & “: is not an authroized user!”, _
                vbOKOnly + vbCritical, “Error: User Not Authorized”
     ExitDB
   End If
   
   zBEDBFN = “ARB_be.mdb”
   zDBFullName = zDBPath & zBEDBFN
  
   For iTblCnt = 0 To UBound(zTableName) – 1
   
      If iTblCnt = 6 Then            ‘*** Switch back end DB files ***
        zBEDBFN = “ARBReqs_be.mdb”
        zDBFullName = zDBPath & zBEDBFN
      End If
      
      On Error GoTo FileDoesNotExist
      ‘*** Delete TableDef from FRONT end DB
      DoCmd.DeleteObject ObjectType:=acTable, ObjectName:=zTableName(iTblCnt)
   
      ‘*** Copy TableDef from BACK end DB to FRONT end DB – Keep in sync!
      DoCmd.TransferDatabase TransferType:=acLink, _
                             DatabaseType:=”Microsoft Access”, _
                             DatabaseName:=zDBFullName, _
                               ObjectType:=acTable, _
                                   Source:=zTableName(iTblCnt), _
                              Destination:=zTableName(iTblCnt)
      On Error GoTo 0
      
   Next iTblCnt
                             
    zStatusMsg = “Tables have been Re-Linked”
    lTimerInterval = 3000    ‘*** 3 Seconds ***
    DoCmd.OpenForm “frmStatusMsg”, acNormal
    Application.SetOption “Themed Form Controls”, False
    StdMenuToggle “False”
    
End Function    ‘ReLinkTable()

‘                          +———————+                 +———-+
‘————————–|    zGetDBPath()     |—————–| 10/20/10 |
‘                          +———————+                 +———-+
‘Called by: [RelinkTables] RelinkTable()
‘Returns  : STRING = Path to backend DB based on user name and machine name.

Public Function zGetDBPath() As String

   Dim zUName    As String
   Dim zCompName As String
   
   zUName = Environ(“USERNAME”)
   Select Case zUName
     Case “Bruce”
         zCompName = Environ(“COMPUTERNAME”)
         If zCompName = “INSPIRON15-I5” Then                     ‘*** BEK Laptop     ***
           zGetDBPath = “G:bekdocsARB Files”                  ‘*** Laptop         ***
         Else                                                    ‘*** BEK Desktop    ***
           zGetDBPath = “G:bekdocsARB Files”                  ‘*** Winows 7 Path  ***
         End If
     Case “Owner”                                                ‘*** BEKHP        ***
         zGetDBPath = “\BEK-PCBEKDocsARB Files”           ‘*** Network Path   ***
     Case “Wyboo Manager”                                        ‘*** Harry          ***
         zGetDBPath = _
             “C:UsersWyboo ManagerDocumentsARB Files”       ‘*** Windows 7 Path ***
     Case “Property Manager”                                     ‘*** Martin         ***
         zGetDBPath = “\Wyboomanager-pcARB Files”             ‘*** Network Path   ***
     Case Else
         zGetDBPath = “Error”

   End Select

End Function    ‘*** zGetDBPath() ***

Of course this code checks for which machine it is running on and then uses the appropriate drivepath information. You could modify it to search for the backend file first then use that drivepath info to relink.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Is there a way to look for the linked table in the folder where the front end is located?

Reply | Quote

Access MVP Armen Stein has a very good relinker available for free. One of the features allows one to easily relink to a BE file that is in the same folder as the FE.
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

The download is named “J Street Access Relinker”.

Reply | Quote

Thanks RG I got this to work.
BZ

Reply | Quote