Absolute link to relative link with VBA Access

Question

I'm trying to link my reports but unfortunately it doesn't work I need the relative path and not the absolute path so that the user can access the reports. the problem I manage to write the link in .\ but the program should always update something before .\ so that in the end I get the main path plus the relative path so that the user can access the reports no matter where they are located.

Sub Sample()
    Dim FileSystem As Object
    Dim HostFolder As String

    ' Setzen Sie den HostFolder auf das Basisverzeichnis, wo Ihre Datenbank und Berichte liegen
    ' Passen Sie den Pfad entsprechend an
    HostFolder = "C:\Users\fouas\Music"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
End Sub

Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next SubFolder
    
    Dim File
    For Each File In Folder.Files
        ' Überprüfe, ob die Datei eine PDF-Datei ist
        If LCase(Right(File.Name, 4)) = ".pdf" Then
            ' Hier die Logik einfügen, um Autor, Titel, Datum und Link aus dem Dateinamen zu extrahieren
            Dim pdfData As Variant
            pdfData = Split(File.Name, "_") ' Annahme: Dateiname hat Format "BerichtNr_Schlusswort_Datum_Titel_Autor.pdf"
            
            If UBound(pdfData) >= 4 Then
                Dim pdfLink As String
                pdfLink = File.Path ' Pfad zur PDF-Datei
                
                ' Extrahiere die Daten aus pdfData
                Dim BerichtNr As String
                Dim Schlusswort As String
                Dim Datum As String
                Dim Titel As String
                Dim Autor As String
                BerichtNr = pdfData(0)
                Schlusswort = pdfData(1)
                Datum = Replace(pdfData(2), ".", "/")
                Autor = pdfData(3)
                Titel = pdfData(4)
                
                ' Hier den Code einfügen, um die relative Verknüpfung zu erstellen
                ' Der relative Pfad ist der Pfad von HostFolder zum aktuellen Bericht
                Dim relativePDFLink As String
                relativePDFLink = ".\" & Replace(pdfLink, HostFolder & "\", "")
                
                ' Öffne die Access-Datenbank
                Dim db As DAO.Database
                Set db = OpenDatabase("C:\Test§\Tab1_test.accdb") ' Passe den Pfad zur Access-Datenbank an

                ' Öffne die Tabelle in der Access-Datenbank
                Dim rs As DAO.Recordset
                Set rs = db.OpenRecordset("Tabelle1", dbOpenDynaset) ' Passe den Namen deiner Access-Tabelle an

                ' Füge die Daten in die Access-Tabelle ein
                rs.AddNew
                rs("BerichteNr").Value = BerichtNr
                rs("Schlusswort").Value = Schlusswort
                rs("Datum").Value = Datum
                rs("Titel").Value = Titel
                rs("Autor").Value = Autor
                rs("PDFLink").Value = relativePDFLink ' Verknüpfung ist jetzt relativ
                rs.Update

                ' Schließe die Recordset und die Datenbank
                rs.Close
                Set rs = Nothing
                db.Close
                Set db = Nothing
            End If
        End If
    Next File
End Sub

Answer 1

So, guessing you Hostfolder remains the same. It is best to store the name of the pdf sepperatly :

Get the name of the pdf and store this in your database.

Public Function GetFileName(ByVal path As String) As String
    'Test : ? GetFileName("C:\path\to\your\file.pdf")
    '
    Dim fileName As String
    Dim fso As Object
    
    ' Create a FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Extract the file name from the full path
    fileName = fso.GetFileName(path)
    
    ' Create a FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Extract the file name from the full path
    fileName = fso.GetFileName(path)
    
    GetFileName = fileName

End Function

Get the relative path of your end user.

Public Function GetFileName(ByVal path As String)
    'Test : ? GetFileName("C:\path\to\your\file.pdf")
    '
    Dim fileName As String
    Dim fso As Object
    
    ' Create a FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Extract the file name from the full path
    fileName = fso.GetFileName(path)
    
    ' Create a FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Extract the file name from the full path
    fileName = fso.GetFileName(path)
    
    GetFileName = fileName

End Function

Now combine this if you want to open the file.

Public Function OpenFile(ByVal strFileFolder As String, ByVal strFileName As String) As String
    
    'Test : ? OpenFile(GetRelativePath(),GetFileName("C:\Users\MZENNER\Documents\toDeleteAsap.pdf"))
    'Test : ? OpenFile(GetRelativePath(),"toDeleteAsap.pdf")
    '
    Dim filePath As String
    
    filePath = strFileFolder & strFileName
    Shell "explorer.exe """ & filePath & """", vbNormalFocus
    
End Function