FredixBlog

Nopath() function returns the filename without the path.
Its little sister, FilePath, returns the path without the filename.

Public Function NoPath(TheText)
Dim ch, result As String
Dim pos As Integer
pos = Len(TheText)
While ch <> "\" and pos > 0
    ch = Mid(TheText, pos, 1)
    result = ch & result
    pos = pos - 1
Wend
NoPath = Right(result, Len(result) - 1)
End Function</p>
Public Function FilePath(ThePath)
Dim result As String
result = Left(ThePath, Len(ThePath) - Len(NoPath(ThePath)))
FilePath = result
End Function

• Relink all tables
• Truncate long path names
• Clear all line breaks
• Create ODBC DSN
• ShellWait()

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

• Truncate long path names
• Clear all line breaks
• Create ODBC DSN
• Splitting File and Path of folder: NoPath() & FilePath()
• Répartir les tâches du crontab unix
• Gibson plus solide que Fender?
• Display a counter and remaining time in a loop
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Coordinates of the third point