Automatically Relink Frontend to 2 Backends via form

[bobbyer]

I use two access databases. 1 Rolodex (Client Info and related jobs, etc) - 2 is Timeclock (Employee timesheets with hours, rates etc and all necessary info for creating the various forms and reports for distribution). From time to time I have to take the above home to make changes to the frontend. Right now I use the built in Linked Table Manager and would like to be able to automate the process.
All frontend and backends are located in the same folder at Office (on the server) or my Desktop when I’m at home. I’m only dealing with a total of 2 locations, 2 frontend and 2 backends. (Right now, I’m just testing my home usage (C) not the server in the office (G) as I don’t know what the path is yet.
I created a table that contains 4 records:
C:\users\bobbye\desktop\rolodex_be
C:\users\bobbye\desktop\timeclock_be
G:\ rolodex_be
G:\timeclock_be

The Rolodex uses one backend called Rolodex_be. I created a form to select a file and run a relink function that I found online and it works fine.
The Timeclock uses both the Timeclock_be and the Rolodex_be backends. I copied the same function, table and form to Timeclock. I selected the timeclock_be on the form thinking it would run through the function and connect to the timeclock_be (It didn’t) and then run it again to connect the Rolodex_be but it didn’t work.
How do I get Timeclock FE to connect to both backends? I’m not a programmer so please keep your response as simple as possible

Here is the program I used.
(I made LnkDataBase Public so that I could change the name of the backend as needed via a form

Sub Relinktables()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 1 Then 'Only relink linked tables
        If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
            If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
                strTable = tdf.Name
                dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
                dbs.TableDefs(strTable).RefreshLink
            End If
        End If
    End If
Next tdf
End Sub

[NeoPa]

Hi Bobby.

Welcome to Bytes.com :-)

I have just the thing for you, but I will need to dig it out as I developed it for a previous client and no longer have access to their servers to get it easily as I used to. That is to say - I don't know if I still have access and wouldn't feel comfortable jumping on their systems without specific permission anyway.

It's late in the day here now but I'll try to get on to it tomorrow. I'm sure I have a usable copy somewhere. Essentially, as long as all linked databases are in the same folder the main project (FE) file is in then it finds them and relinks seamlessly.

If you don't here back before the weekend then feel free to bump this thread to grab my attention and kick me into finding what you need.

[NeoPa]

Hi Bobby.

This was harder to find than I'd expected :-(

Let's start anyway with the code to relink a table (originally with a link that points to a single old file) to point to the new version, found in the same folder the current database was opened from. NB. I understand you may have multiple tables linked to such files so there will be more to follow which allows you to handle calling this procedure for all the various tables.

'RelinkTable() ensures that the specified (Jet-linked) table, currently linked
'   to one file, will be relinked to a file with the same name, but in the same
'   folder where this current database was opened from.
'   Returns True if the relink was successful (or False if not).
Public Function RelinkTable(ByVal strTable As String, _
                            Optional ByRef dbVar As DAO.Database) As Boolean
    Dim strPath As String, strOldPath As String, strConnect As String
 
    If dbVar Is Nothing Then Set dbVar = CurrentDb()
    With dbVar
        strPath = Left(.Name, InStrRev(.Name, "\"))
        With .TableDefs(strTable)
            If (.Attributes And dbAttachedTable) = 0 Then Exit Function
            strPath = strPath & Mid(.Name, InStrRev(.Name, "\") + 1)
            If Not Exist(strPath) Then Exit Function
            strOldPath = Split(Split(.Connect, "DATABASE=")(1), ";")(0)
            If strPath <> strOldPath Then _
                .Connect = Replace(.Connect, strOldPath, strPath)
            On Error Resume Next
            Call Err.Clear
            Call .RefreshLink
            RelinkTable = (Err = 0)
        End With
    End With
End Function

NB. I've tried to ensure none of the code here requires access to anything else that I haven't shared so please report at any time if you find the code as written doesn't compile (Before Posting (VBA or SQL) Code provides some basic instructions for compiling etc).

[NeoPa]

Now we simply need to call this for all of your linked tables where this is required. This can be as simple as a string containing a list of the table names but let us know if you're thinking of another way of identifying the tables required.

'RelinkHomeTables() ensures all tables that need to be relinked to Jet/ACE
'   databases held in the same folder as the current database get so relinked.
Public Sub RelinkHomeTables()
    Dim lngCount As Long
    Dim strArray() As String
    Dim varItem As Variant
    Dim dbVar As DAO.Database
 
    Set dbVar = CurrentDb()
    strArray = Split("TableA,TableB,TableC,TableD", ",")
    For Each varItem In strArray
        If RelinkTable(strTable:=CStr(varItem), dbVar:=dbVar) Then _
            lngCount = lngCount + 1
    Next varItem
    If lngCount < UBound(strArray) Then
        'You have a problem.
    End If
End Sub

NB. I also updated the earlier post to handle checking first that the new database is not the same as the original one before trying to relink.