• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 32
  • Last Modified:

MS Access hypelink on deleted file

I MS Access 2013 I have a table tblContracts .In table I have a hyperlink  field “FileLocation” where I store hyperlink to  my pdf files.
On form I have a text box field where hyperlink is show in, hyperlink text is blue color and underlined .

If user click on hyperlink in text box  it takes him  to folder where file is located.
On click event of text box txtMyHyperlink I have:
Application.FollowHyperlink Me.txtMyHyperlink
It works fine if file exist, however if file is deleted I got error.
It would be good first to check if file exist in that location. However to check if file exists I have to use hyperlink path string that has  # on both side something like: #\\somefoldername\somesubfolderName\filename#
It is interesting how hyprlink text looks like, at the end of path is file name however  that file name do not have extension .pdf, just file name.

 I can see all path if I use debug.print to  check path variable.

Problem is if file is deleted and user click on hyperlink in form field then access throw error .
How to avoid this error that is long and confusing to user,  instead  gave user msg “File do not exist or file is moved to another location.
0
Taras
Asked:
Taras
  • 8
  • 4
  • 2
  • +1
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can check for the existence of a file like this:

If Dir(Me.txtMyHyperlink) <> "" Then
  '/ the file does exists
  Application.FollowHyperlink Me.txtMyHyperlink
End If
0
 
TarasAuthor Commented:
That hyperlink in "Me.txtMyHyperlink" that is coming from table will always exist. It value is from table field and  from time when it is was  entered in table.
However real file that hyperlink is referring to  is meanwhile removed or deleted.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Taras,
remove the hyperlink target
set Display as Hyperlink -- always
to avoid the extra stuff that also gets saved, store as TEXT not a hyperlink

put this on the click event of a command button next to the file to open it, and maybe also on the control double-click event:
   Dim sPathFile As String
   With Me.Me.txtMyHyperlink
      If Not IsNull(.Value) Then
         sPathFile = Me.txtMyHyperlink '"c:\path\filename.ext"
      End If
   End With
   Call FollowHyperlink_File(sPathFile)

Open in new window


'here is a procedure you can put into a general module to call from anywhere

Public Sub FollowHyperlink_File(psPathFile As String)
'180202 strive4peace
    On Error Resume Next
    If Len(Dir(psPathFile)) > 0 Then
      Application.FollowHyperlink (psPathFile)
      If Err.Number <> 0 Then
          MsgBox "ERROR opening file: " & psPathFile & vbCrLf & Err.Description _
            , , "Error opening file: " & Err.Number
      End If
      On Error GoTo 0
    End If
End Sub

Open in new window

have an awesome day,
crystal
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
However real file that hyperlink is referring to  is meanwhile removed or deleted.
The Dir command determines if the file exists. If your textbox control named "txtMyHyperlink" contains the file path, then using DIR with that value will tell you if the file exists at that path.

Have you tried the sample to see if it works?
0
 
TarasAuthor Commented:
I mentioned that hyperlink Path string has #.......# in it.
How dir command will recognized any Dir with #....# against real dir that do not have ## in its name.
That Dir with #....# in its name never  will be found??
0
 
TarasAuthor Commented:
Scott I tried the sample it is not working
I Just added else part to your sample and put message "File do not exist"  then I tried it on hyperlink to file that exist. It gave me that message "File do not exist" event file exists.
0
 
PatHartmanCommented:
Since the vast majority of time, the file will still exist, I would catch the error rather than trying to prevent it.  This is something I learned when I was creating CICS transactions that were used by thousands of users where every second counted and data reads are the most expensive operation we do.

In your error procedure:

Select err.Number
   Case ????   'whatever the error number you get for a missing file
        MsgBox "File Not found.",vbOKOnly
        Resume Next
    Case Elxe
        Msgbox Err.Number & "--" & Err.Description
            Resume Exit_Proc
    End Select
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see - you actually have a Hyperlink datatype field.

As Crystal suggests, you should convert that to Text, and then fixup the data to hold ONLY the full path to the item. You can then use DIR to determine if that file exists.

Alternatively you could parse the hyperlink:

If Dir(Split(Me.txtMyHyperlink, "#")(1)) <> "" Then
  '/ the file exists
End If

the Split function retrieves the second "element" of the array, which is the file name.
0
 
TarasAuthor Commented:
hyperlink path looks like : #\\SomeFolderName\SomeSubfolderName\filename#
I assume that split will keep second # in path string what is not good.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You assume wrong.

The split function take a string and "splits" it at the delimiters you indicate. A string like this:

display text # file name # any reference within the file

which is passed into this function:

Msgbox Split("display text # file name # any reference within the file", "#")(1)

Would return a value of "file name" (without the quotes)


Try it in your application and let us know the results.
0
 
TarasAuthor Commented:
I got this error:

7971MicrosoftAccess cannot follow the hyperlink to
'#\\SomeFolderName\SomeSubFolderName\FileName#1'

It interesting that number "1" a the end of path  after FileName. I do not know where it comes from.

I mentioned earlier that path do not have file extension .pdf just file name I do not know why it was saved in database on that way.
When hyperlink was working it was opening folder where file was seating it was not opening file.
0
 
PatHartmanCommented:
@Taras,  I see that you are all wrapped up in prevention and not interested in catching so I'll go away.
0
 
TarasAuthor Commented:
Scott I tried it and get this error

Error Opening File:7971
Error opening file:#\\SomeFolderName\SomeSubFoldername\FileName#

7971MicrosoftAccess cannot folloow the hyperlink to
'#\\SomeFolderName\SomeSubFolderName\FileName#1'
0
 
TarasAuthor Commented:
Pat I will try it on Monday when I am back, if preventing to happen is not working I will try to catch error number and avoid it.
0
 
TarasAuthor Commented:
Thank you a lot McDaniel it worked finally as you suggested first time server was down and I got that strange error msg but later on it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now