none
Link tables RRS feed

  • Question

  • Hi all,

    Is it possible to link to tables in the back end after moving the back end to a different location?

    What I am trying to do, is after the first installation of the database, it will check the .ini file. If this file is not found, the program will open a "Settings" form where some details are entered, for example the server name, location of the front end and the location of the back end. This info is then stored in the .ini file.

    So no matter where the backend is located, the code should refresh the links to wherever the backend resides. The front end will read the .ini file and get the full path and filename of the backend, then refreshes the linked tables.

    Is this possible (and good programming practice) to do it this way?

    Thanks

    Deon

    Tuesday, June 11, 2019 11:28 AM

Answers

  • Hi all,

    I found a little piece of code on the internet below which does exactly what I needed. I cannot remember where I found it, so my apologies for not giving the author the credit". After a few code changes, it worked perfect:

    '*******************************************************************
    '*  This module refreshes the links to any linked tables  *
    '*******************************************************************
    
    
    'Procedure to relink tables from the Common Access Database
    Public Function RefreshTableLinks(Text2) As String
    
    On Error GoTo ErrHandler
        Dim strEnvironment As String
        'strEnvironment = GetEnvironment
        strEnvironment = Text2
    
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
    
        Dim strCon As String
        Dim strBackEnd As String
        Dim strMsg As String
    
        Dim intErrorCount As Integer
    
        Set db = CurrentDb
    
        'Loop through the TableDefs Collection.
        For Each tdf In db.TableDefs
    
                'Verify the table is a linked table.
                If Left$(tdf.Connect, 10) = ";DATABASE=" Then
    
                    'Get the existing Connection String.
                    strCon = Nz(tdf.Connect, "")
    
                    'Get the name of the back-end database using String Functions.
                    strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "\") - 1)))
    
                    'Debug.Print strBackEnd
    
                    'Verify we have a value for the back-end
                    If Len(strBackEnd & "") > 0 Then
    
                        'Set a reference to the TableDef Object.
                        Set tdf = db.TableDefs(tdf.Name)
    
                        If strBackEnd = "\ETS_be.accdb" Then
                            'Build the new Connection Property Value - below needs to be changed to a constant
                            tdf.Connect = ";DATABASE=" & strEnvironment
                        Else
                            tdf.Connect = ";DATABASE=" & CurrentProject.path & strBackEnd
    
                        End If
    
                        'Refresh the table links
                        tdf.RefreshLink
    
                    End If
    
                End If
    
        Next tdf
    
    ErrHandler:
    
     If Err.Number <> 0 Then
    
        'Create a message box with the error number and description
        MsgBox ("Error Number: " & Err.Number & vbCrLf & _
                "Error Description: " & Err.Description & vbCrLf)
    
    End If
    
    End Function
    

    • Marked as answer by Deon SA Friday, June 14, 2019 6:12 AM
    Friday, June 14, 2019 6:11 AM

All replies

  • I do something very similar.

    You can take any relinking code (for instance: http://access.mvps.org/access/tables/tbl0009.htm) and modify it as req'd to suit your needs.

    What I do is:

    • Check if the existing links work
    • If not, try read the ini file and relink based on that path
    • If even that fails, pull the default values from my db and relink based on that path.

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, June 11, 2019 12:01 PM
  • Hi Daniel,

    I tried the code you proposed, but it is not doing what I require.

    I need to relink to the new location of the backend. The proposed code does not do that. It keeps on refreshing the existing links only, even though I select a different location.

    If I remove the back end from the original location, an error occur telling me the back end is not found.

    Any other suggestions?

    Thanks

    Deon

    Thursday, June 13, 2019 12:47 PM
  • Any other suggestions?

    Hi Deon,

    I use a definition table in each application, where the current link-path is stored.

    When an application is opened with the information for a link-path that is different from the stored one, a routine is run that loops (backwards) through the TableDefs-collection, replaces the old path in the Connect property to the new path, and refreshes the link (.Refreslink).

    Imb.

    Thursday, June 13, 2019 2:31 PM
  • Yes it is possible. I found this code many years ago and have used it numerous times in various applications. I didn't write it but it has always worked for me. You can download the FindAnyFile.bas file on the OneDrive link below. Open your VBA widndow and use Insert > File to import it into your ACCESS file. It is a version 2007 file. Make sure you replace your BE file name in all the places where it says BACKEND FILE NAME HERE INCLUDING EXTENSION. Then make a call to the Public Function 'GetDefaultConnection' to run it in your autoexec form when the application opens. Make sure you backup your front-end file first.

    I don't have access to a network drive, so this has not been tested on a network so it may take some time to find the file on a network drive. Also, you may need to tailor the 'DefaultBackEnd =' connection string line to your own needs. Hope this can help you.

    https://onedrive.live.com/?id=9981FDC9E7DC7D83%21284&cid=9981FDC9E7DC7D83

    Thursday, June 13, 2019 2:43 PM
  • Hi all,

    I found a little piece of code on the internet below which does exactly what I needed. I cannot remember where I found it, so my apologies for not giving the author the credit". After a few code changes, it worked perfect:

    '*******************************************************************
    '*  This module refreshes the links to any linked tables  *
    '*******************************************************************
    
    
    'Procedure to relink tables from the Common Access Database
    Public Function RefreshTableLinks(Text2) As String
    
    On Error GoTo ErrHandler
        Dim strEnvironment As String
        'strEnvironment = GetEnvironment
        strEnvironment = Text2
    
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
    
        Dim strCon As String
        Dim strBackEnd As String
        Dim strMsg As String
    
        Dim intErrorCount As Integer
    
        Set db = CurrentDb
    
        'Loop through the TableDefs Collection.
        For Each tdf In db.TableDefs
    
                'Verify the table is a linked table.
                If Left$(tdf.Connect, 10) = ";DATABASE=" Then
    
                    'Get the existing Connection String.
                    strCon = Nz(tdf.Connect, "")
    
                    'Get the name of the back-end database using String Functions.
                    strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "\") - 1)))
    
                    'Debug.Print strBackEnd
    
                    'Verify we have a value for the back-end
                    If Len(strBackEnd & "") > 0 Then
    
                        'Set a reference to the TableDef Object.
                        Set tdf = db.TableDefs(tdf.Name)
    
                        If strBackEnd = "\ETS_be.accdb" Then
                            'Build the new Connection Property Value - below needs to be changed to a constant
                            tdf.Connect = ";DATABASE=" & strEnvironment
                        Else
                            tdf.Connect = ";DATABASE=" & CurrentProject.path & strBackEnd
    
                        End If
    
                        'Refresh the table links
                        tdf.RefreshLink
    
                    End If
    
                End If
    
        Next tdf
    
    ErrHandler:
    
     If Err.Number <> 0 Then
    
        'Create a message box with the error number and description
        MsgBox ("Error Number: " & Err.Number & vbCrLf & _
                "Error Description: " & Err.Description & vbCrLf)
    
    End If
    
    End Function
    

    • Marked as answer by Deon SA Friday, June 14, 2019 6:12 AM
    Friday, June 14, 2019 6:11 AM