Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to locate linked tables automatically.

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:\bekdocs\ARB Files\"                  '*** Laptop         ***
             Else                                                    '*** BEK Desktop    ***
               zGetDBPath = "G:\bekdocs\ARB Files\"                  '*** Winows 7 Path  ***
             End If
         Case "Owner"                                                '*** BEKHP        ***
             zGetDBPath = "\\BEK-PC\BEKDocs\ARB Files\"           '*** Network Path   ***
         Case "Wyboo Manager"                                        '*** Harry          ***
             zGetDBPath = _
                 "C:\Users\Wyboo Manager\Documents\ARB Files\"       '*** Windows 7 Path ***
         Case "Property Manager"                                     '*** Martin         ***
             zGetDBPath = "\\Wyboomanager-pc\ARB 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 drive\path information. You could modify it to search for the backend file first then use that drive\path info to relink.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to look for the linked table in the folder where the front end is located?

  4. #4
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    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...rDownloads.asp

    The download is named "J Street Access Relinker".

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks RG I got this to work.
    BZ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •