FredixBlog

This function relinks the tables to the database designed by its path.

Option Compare Database
Option Explicit

Public Sub RelinkTables(ThePath)
   Dim dbf As Database
   Dim tdf As TableDef
   Dim count, totcount
   
   DoCmd.Hourglass True
   Set dbf = CurrentDb()
   totcount = dbf.TableDefs.count
   If IsNull(ThePath) Then
    DoCmd.Hourglass False
    MsgBox "please choose a database source.", vbCritical
    Exit Sub
 End If
 For Each tdf In dbf.TableDefs
        'Debug.Print NoPath(tdf.Connect), tdf.Connect
        If tdf.Connect <> "" Then
            tdf.Connect = ";DATABASE=" &amp; ThePath & NoPath(tdf.Connect)
            On Error Resume Next
            tdf.RefreshLink
            If Err <> 0 And Err <> 3024 And Err <> 3219 And Err <> 3151 and Err <> 3022 Then
                DoCmd.Hourglass False
                MsgBox Error$, vbCritical
                Exit Sub
            End If
            On Error GoTo 0
         End If
        count = count + 1
        Forms![Relink]![Progress] = count & "/" &; totcount & " linked"
        DoEvents
    Next tdf
    DoCmd.Hourglass False
    MsgBox "Successfully linked to specified data source.", vbInformation
End Sub

Public Function NoPath(TheText)
Dim i, ch, result
i = Len(TheText)
While ch <> "\" And i > 0
    ch = Mid(TheText, i, 1)
    If ch <> "\" Then result = ch & result
    i = i - 1
Wend
NoPath = result
End Function

No related posts.