MS Access - Save relative path in table and open file on clicking the link

Hi,

I have a form that have two field call "Source" and "Destination" taken form tblSou. The source field save the source of document and the destination field save the destination path (which is a server path mapped to local PC's in all the users). Now there are two thing that i want to achieve:

1. Save relative path. (how to save relative path? For example the path is (W:\\Document\test.pdf) how to save it's relative path?)

2. On table when click on that relative link it should open the file. (When the link is click in the table i.e the destination field it doesn't open that document , how to do that?)

Is it possible through VBA by getting the root directory of the folder and appending the filename?

Any help would be appreciated.

Thank you.
LVL 1
WSStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
Hi,
1: a relative path is always relative to something. So what should the path relative to?
This means that for example you have a static folder definition (C:\MyApplication\Documents\) and you always save documents into that folder. In this case you really don't need to save the full path it is enough to save the part of the path after "MyApplication\Documents". For example in case of full path C:\MyApp\Documents\Important\FirstDocument.docx you save only Important\FirstDocument.docx.
To achieve this you need only to remove static path of your full path with a replace function:
RelativePath = Replace(FullPath, StaticPath, "")

Open in new window


2. To get a root directory of a folder you have to use an external (but windows built-in) library in you VBA project of Access: Microsoft Scripting Runtime.
Dim Fs As New Scripting.FileSystemObject
Dim Root As String

Root = Fs.GetParentFolderName("C:\MyApp\Documents\Important\FirstDocument.docx")

Open in new window

WSStudentAuthor Commented:
@Mate, thanks for explaning so well, i was a bit confuse but now it's more clear to me. I have 2 question here :

1. In (Replace(FullPath, StaticPath,) , how FullPath and StaticPath will be declared? I have static folder as W:\Document\ and after this there are 10 folder's where user will save the file in anyone of them.

2.I have a server in which Document Library is build which is mapped to my PC. In this scripting runtime "("C:\MyApp\Documents\Important\FirstDocument.docx")" , i will replace it as W:\Document\ , here for FirstDocument.docx it should get the destination path which will be like test.pdf or whatever is there, how that can be done?

Thank you again.
WSStudentAuthor Commented:
In the fields Source and Destination i have code , in Source it open folders and user select document and then in Destination user select the destination location where user copy the file. How can i incorporate the above code in this? any idea?
Private Sub txtSource_Click()
 Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    
    Set Dialog = FileDialog(msoFileDialogFilePicker)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtSource.Value)
        .Title = "Select file to copy"
        Selected = .Show
        If Selected <> 0 Then
            Me!txtSource.Value = .SelectedItems.Item(1)
        End If
    End With
    
    Cancel = True
End Sub

Private Sub txtTarget_Click()
Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    
    Set Dialog = FileDialog(msoFileDialogSaveAs)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtTarget.Value)
        .Title = "Name saved file"
        Selected = .Show
        If Selected <> 0 Then
            Me!txtTarget.Value = .SelectedItems.Item(1)
            If Not IsNull(Me!txtSource.Value) Then
                 FileCopy Me!txtSource.Value, Me!txtTarget.Value
            End If
        End If
    End With
    
    Cancel = True
End Sub

Open in new window

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download your eBook!
PatHartmanCommented:
I usually store the path separate from the file name.  The application has a settings table that allows users to define locations for specific document types and I use them to concatenate with the actual file name when I open the file using the FollowHyperlink Method.

Private Sub cmdOpenDoc_Click()
    Dim strInput As String
    
On Error GoTo Err_cmdOpenDoc_Click
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If

    If Right(Me.ScannedDocPath, 1) = "\" Then
        strInput = Me.ScannedDocPath & Me.txtFullDocName
    Else
        strInput = Me.ScannedDocPath & "\" & Me.txtFullDocName
    End If
    
    Application.FollowHyperlink strInput, , True

Exit_cmdOpenDoc_Click:
    Exit Sub

Err_cmdOpenDoc_Click:
    Select Case Err.Description
        Case 2501
            Resume Next
        Case 490    '   cannot open file
            MsgBox "This file cannot be found.  Please check its name and path.", vbOKOnly + vbInformation
            Exit Sub
        
        Case Else
            MsgBox Err.Description
            Resume Exit_cmdOpenDoc_Click
    End Select
    
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WSStudentAuthor Commented:
Thank you PatHartman.
PatHartmanCommented:
You're welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.