FE relinking

[vicsaccess]

i have a FE with a version checker that automatically updates the users FE if the versions our different, nothing new there, many of you helped me put it together. Now what I'm finding is that for a few of my users the new FE is loosing its links to the BE when they start it up for the first time after the update. Is there a way to either automatically or with a command button relink to the BE? its a simple procedure to relink but i would prefer to not have my users messing with the ribbon commands. so far the research I've done comes up with a few codes that are several pages long that is beyond my current knowledge or i did find one that was shorter and workable but it was to change the path. any suggestions would be appreciated.

[Micron]

I stopped using this; can't remember why for sure but I think it was because I deemed it unnecessary after I opted to shutdown if the user had a rouge copy. Tweak it as you need (you will probably removed the user interaction part and just do the relink), keeping in mind the db was a shared single db due to network constraints. That's why there is a part that 'worries' about someone else being logged in.

RelinkByList [call to the function from somewhere in code]

Function RelinkByList()
'loops thru table tblLinkedTables & compares current link to tblLinkedTables path value
'to ensure links to BE are checked after a database is replaced by a new version
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim currConnect As String, trgtConnect As String, msg As String, tblName As String
Dim result As Integer
Dim Warned As Boolean

On Error GoTo errHandler
Set db = CurrentDb
Set rst = db.OpenRecordset("tblLinkedTables", dbOpenSnapshot)
rst.MoveFirst

Do Until rst.EOF
tblName = rst.Fields("TableName")
Set tdf = db.TableDefs(tblName)
'curConnect is current connection value, trgtConnect is path stored in tblLinkedTables
'if network structure ever changes, 11 may have to be altered to display path properly
currConnect = Nz(Mid(tdf.Connect, 11), "")
trgtConnect = Nz(rst!DataFilePath, "")

If trgtConnect <> currConnect Then
    msg = "Current mapping appears incorrect for '" & tblName & "'." & vbCrLf
    msg = msg & "Re-link this table? CLICK CANCEL TO STOP CHECKING ALL TABLES."
    msg = msg & vbCrLf & vbCrLf
    msg = msg & "Expected path: " & vbCrLf & trgtConnect & vbCrLf & vbCrLf
    msg = msg & "Current path: " & vbCrLf & currConnect
    result = MsgBox(msg, vbYesNoCancel, "CHECKING TABLE CONNECTIONS")
        If result = 2 Then
            Set tdf = Nothing
            Set rst = Nothing
            Set db = Nothing
            Exit Function
        End If
        If result = 7 Then rst.MoveNext 'user said don't change this link
        If result = 6 And LoggedInCount > 1 Then 'user chose to change but others logged in
            If Warned = False Then 'this is the first warning re: logged in users
                msg = "OTHERS APPEAR TO BE LOGGED IN!" & vbCrLf
                msg = msg & "Remapping tables may cause them to lose data." & vbCrLf
                msg = msg & "Continue anyway?"
                result = MsgBox(msg, 52, "LOGGED IN COUNT = " & LoggedInCount)
                Warned = True
                'DoCmd.OpenForm "frmWait"
                'Pause (1) 'without pause, frmWait does not completely display
                If result = 6 Then 'user said to change link anyway
                    tdf.Connect = ";DATABASE=" & trgtConnect
                    tdf.RefreshLink
                End If
                If result = 7 Then GoTo exitHere 'user chose not to change link
            End If
        End If
        If result = 6 And LoggedInCount < 2 Then
            tdf.Connect = ";DATABASE=" & trgtConnect
            tdf.RefreshLink
        End If
      'DoCmd.Close acForm, "frmWait"
End If
''If trgtConnect = "" Then
''    MsgBox "Cannot re-link " & tblName & ": No path specified in table tblLinkedTables.", "TABLE PATH MISSING"
''    'Resume Next
''End If
LoopHere:
rst.MoveNext
Loop

exitHere:
  Set tdf = Nothing
  Set rst = Nothing
  Set db = Nothing
Exit Function

errHandler:
If Err.Number = 3265 Then
    msg = "Table '" & tblName & "' not found in list of linked tables." & vbCrLf
    msg = msg & "The table may be missing from database or mis-spelled" & vbCrLf
    msg = msg & " in list of linked tables." & vbCrLf & vbCrLf
    msg = msg & "Please call a database administrator to check tables information."
    MsgBox msg, vbOKOnly, "TABLE NOT FOUND"
    Resume LoopHere
End If
If Err.Number = 3321 Then
    msg = "Cannot re-link " & tblName & ": No path specified in table tblLinkedTables."
    msg = msg & vbCrLf & "Please call a database administrator to check tables information."
    MsgBox msg, vbOKOnly, "TABLE PATH MISSING"
    Resume LoopHere
End If
MsgBox "Error number " & Err.Number & ": " & Err.Description

'If Err.Number = 3734 Or Err.Number = 2450 Then Resume exitHere

End Function

In case you need the code for the called Pause

Public Function Pause(intSecs As Integer)
Dim Start As Variant
Start = Timer
Do While Timer < Start + intSecs
    DoEvents
Loop
End Function

[vicsaccess]

thank you micron, looks like just what i was looking for.

[Micron]

Welcome! Hope you get it to work.