Help storing links to .pdf files in MS Access

Hi.  I'm pretty sure this is a basic thing, but I just can't decide how to do it.

I'm planning an Access database and one of the tables will be very simple -- a Quote Number, an Expiration Date, and possibly a link to a .pdf document.   Each quote will have a relevant .pdf, and all of the .pdf documents will be stored in a folder named "PDF_QUOTES" one level below the the database file.  Each pdf file will be named with the quote number, so the record might be (12345, 9/01/06, \PDF_QUOTES\12345.pdf)

I will then have a form on which I'd like to have the user be able to click a button that would cause Adobe Reader to open and display the matching .pdf file.   There will be no need to display the .pdf file 'inside' the form.

What's the easiest way to  do this?  I'd really like the user to be able to type in the quote number and expiration date, and have Access just "know" that there will be a .pdf document in the appropriate place...

FYI, I don't have a good grasp of VBA when it comes to Access, so if there's going to be any coding involved, I'll need some help!

Who is Participating?
rockiroadsConnect With a Mentor Commented:

ShellExecute API code is handy, I often use it and never had a problem with it.

This simpler code might just work for you also

FollowHyperlink <<pathoffile>>

Now the code by Patrick should work

Lets see if we can debug

You say you have a form, what is the field called that holds the quote number?
is it   QUOTE_NUMBER? or perhaps QUOTENUMBER?
We need the name of the field/control, not database field

ok, what we do is  build the filename

Dim sFile as String

sFile = CurrentProject.Path & "\PDF_QUOTES\" & Me.Quote_Number & ".pdf"

'Now we verify it exists
msgbox "CHECKING FOR FILE " & vbcrlf & sFile

if dir$(sFile) = "" then
    msgbox "Unable to find " & sFile
    exit sub
end if

'Now u can either run ShellEx passing in filename or FollowHyperlink

FollowHyperlink sFile
ShellEx sFile

Patrick MatthewsConnect With a Mentor Commented:
Hi kenferrell,

Add this code in a *regular* VBA module (not a form or a report module):

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
  (ByVal hWnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Declare Function ShellExecuteForExplore Lib "shell32.dll" Alias "ShellExecuteA" _
  (ByVal hWnd As Long, ByVal lpOperation As String, _   ByVal lpFile As String, lpParameters As Any, _
  lpDirectory As Any, ByVal nShowCmd As Long) As Long

Public Enum EShellShowConstants
    essSW_HIDE = 0
    essSW_MAXIMIZE = 3
    essSW_MINIMIZE = 6
    essSW_SHOWNORMAL = 1
    essSW_SHOWNA = 8
    essSW_SHOWDEFAULT = 10
    essSW_RESTORE = 9
    essSW_SHOW = 5
End Enum

Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&
Private Const SE_ERR_ACCESSDENIED = 5        ' access denied
Private Const SE_ERR_DDEBUSY = 30
Private Const SE_ERR_DDEFAIL = 29
Private Const SE_ERR_DDETIMEOUT = 28
Private Const SE_ERR_DLLNOTFOUND = 32
Private Const SE_ERR_FNF = 2                ' file not found
Private Const SE_ERR_NOASSOC = 31
Private Const SE_ERR_PNF = 3                ' path not found
Private Const SE_ERR_OOM = 8                ' out of memory
Private Const SE_ERR_SHARE = 26

Public Function ShellEx( _
        ByVal sFIle As String, _
        Optional ByVal eShowCmd As EShellShowConstants = essSW_SHOWDEFAULT, _
        Optional ByVal sParameters As String = "", _
        Optional ByVal sDefaultDir As String = "", _
        Optional sOperation As String = "open", _
        Optional Owner As Long = 0 _
    ) As Boolean
Dim lR As Long
Dim lErr As Long, sErr As Long
    If (InStr(UCase$(sFIle), ".EXE") <> 0) Then
        eShowCmd = 0
    End If
    On Error Resume Next
    If (sParameters = "") And (sDefaultDir = "") Then
        lR = ShellExecuteForExplore(Owner, sOperation, sFIle, 0, 0, essSW_SHOWNORMAL)
        lR = ShellExecute(Owner, sOperation, sFIle, sParameters, sDefaultDir, eShowCmd)
    End If
    If (lR < 0) Or (lR > 32) Then
        ShellEx = True
        ' raise an appropriate error:
        lErr = vbObjectError + 1048 + lR
        Select Case lR
        Case 0
            lErr = 7: sErr = "Out of memory"
            lErr = 53: sErr = "File not found"
            lErr = 76: sErr = "Path not found"
            sErr = "The executable file is invalid or corrupt"
            lErr = 75: sErr = "Path/file access error"
            sErr = "This file type does not have a valid file association."
        Case SE_ERR_DDEBUSY
            lErr = 285: sErr = "The file could not be opened because the target application is busy. Please try again in a moment."
        Case SE_ERR_DDEFAIL
            lErr = 285: sErr = "The file could not be opened because the DDE transaction failed. Please try again in a moment."
            lErr = 286: sErr = "The file could not be opened due to time out. Please try again in a moment."
            lErr = 48: sErr = "The specified dynamic-link library was not found."
        Case SE_ERR_FNF
            lErr = 53: sErr = "File not found"
        Case SE_ERR_NOASSOC
            sErr = "No application is associated with this file type."
        Case SE_ERR_OOM
            lErr = 7: sErr = "Out of memory"
        Case SE_ERR_PNF
            lErr = 76: sErr = "Path not found"
        Case SE_ERR_SHARE
            lErr = 75: sErr = "A sharing violation occurred."
        Case Else
            sErr = "An error occurred occurred whilst trying to open or print the selected file."
        End Select
        Err.Raise lErr, , App.EXEName & ".GShell", sErr
        ShellEx = False
    End If

End Function

That code comes from:

Then, in the Click event on your form, use a line like:

ShellEx CurrentDB.Path & "\PDF_Quotes\" & [QuoteID] & ".pdf"



kenferrellAuthor Commented:

I copied that code into a VBA module and named it "ShellEX".  The module is showing under the "modules" tab in the main database window.  I restarted, just to make sure I had it saved correctly.

I created a command button on the form and added the line:

ShellEx CurrentDB.Path & "\PDF_Quotes\" & [QUOTE_NUMBER] & ".pdf"

to the "on click" event in the properties window (notice I modified the field name to match my table).

When I go back to Form View and Click on the command button, however, I get this error:
"Microsoft Access can't find the macro 'ShellEx Current.DB.'   It then goes on to suggest the macro doesn't exist or is new but hasn't been saved.

Am I doing something wrong?
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Patrick MatthewsCommented:

No, I gave you a bad example.  From your Click sub:

SomeVariable = ShellEx(CurrentDB.Path & "\PDF_Quotes\" & [QUOTE_NUMBER] & ".pdf")

That should kick off the PDF.  The function will return True if it succeeded and Flase if it failed.


kenferrellAuthor Commented:
OK -- sorry if I really don't know anything...

I just copied exactly what you listed (SomeVariable = ShellEx(....
right into the "On Click" event property, and I get the same kind of errror
"Microsoft Access can't find the macro 'SomeVariable = ShellEx Current.DB.'"

Am I misunderstanding?  Do I need to create a Macro that calls the ShellEx function and then assign that Macro to the button or something?

Sorry -- my background is just really limited in this area....
kenferrellAuthor Commented:
Sorry -- Misread your post...  I created the sub under the 'expression builder' and entered the SomeVariable = ....  line into the VBA editor under the Private Sub CommandButton_Click()  heading.

Now, when I click the button, it calls the subroutine, but it gives me a Compile Error on ".Path", saying "Method or data member not found"

Any ideas?
kenferrellAuthor Commented:
Just got a little further by changing the CurrentDB method to CurrentProject, which does allow the .path property....
But now when I run the sub, I get another Compile Error on ShellEx, saying "Expected variable or procedure, not module"

kenferrellAuthor Commented:
OK, last update ...

The problem above was that I had named the module the same name as one of the functions inside it.  So I changed that and now I don't get any error messages at all!

But, the file doesn't open.

I click the button, and nothing happens.
kenferrellAuthor Commented:
Thanks for the comments.   OK, Let's see.

The quote number is stored in a field called [QUOTE_NUMBER] in a table called [tblQUOTE_NUMBERS].  On that table, I have a form built called [frmQUOTE_DETAILS].  That form has a text box control called [QUOTE_NUMBER] which has as it's control source the field I just described.  Does the fact that the text box control and the underlying field share the same name cause a problem here?

Is that what you're asking?

Now, as far as the rest of the code you've suggested, I can understand what you're getting at.  What I don't understand is where to put it.  Should that all lie inside the button's Click() subroutine?

kenferrellAuthor Commented:
I put the code just as you suggested under the CLICK() subroutine  (only using the "FollowHyperlink" commanc, not the ShellEX command) and it worked perfectly on my home computer....  I even tried opening one that didn't exist and the error message worked.

Then, I bring the whole thing to my office computer (still running off the same flash drive I was on at home) and try this on my work version of Access...  The DB opens fine, and when I click the command button, Acrobat tries to open -- but doesn't open the file -- and then immediately closes and returns me to the database.   Nothing is improved if I copy the file and subdirectory to a local hard drive.  
Interestingly enough, if Acrobat Reader is already open with another document (in the background) and I click on the command button, Acrobat Reader takes focus, but does not open the document that's been referenced...

This would lead me to believe that there's something in the company's roll out of Access that is keeping this from running correctly -- but I'm getting no error messages of any sort...  

Any ideas?
Does Acrobat Reader work well by itself?

Can u open these PDF's manually?
kenferrellAuthor Commented:
Yup -- I can open the documents without any problem...  
ok, one test

create new access db, need two two modules creating

paste the shellexec code into one
and the following into another

public function TestPDFOpen
    Dim sFile as String

    sFile = "C:\ee\aa.pdf"   'point to valid pdf

    msgbox "Test Open PDF 1"
    TestPDF1 sFile

    msgbox "Test Open PDF 2 - Close Acrobat Down Now"
    TestPDF2 sFile

    msgbox "Test Open PDF 3 - Close Acrobat Down Now"
    TestPDF3 sFile
end function

public function TestPDF1(byval sFile as String)
    FollowHyperlink sFile
end function

public function TestPDF1(byval sFile as String)
    ShellEx sFile
end function

public function TestPDF1(byval sFile as String)
    'Specify path of your reader executable here
    Shell "C:\Progra~1\Adobe\Acroba~1\Reader\AcroRd32.exe " & sFile
end function

After each open test, ensure u close Acrobat down. Double check task manager to see no processes are running

This is merely a test of Access opening PDF's

kenferrellAuthor Commented:
OK -- I completed this.  Had to make a couple of changes -- you defined function TestPDF1 three times, so I changed these to TestPDF1, TestPDF2, and TestPDF3.  I then modified the file path and the path to the Reader executable as instructed.

Here's the results.

TestPDF1 (FollowHyperlink)  -- Same as before:  Reader tries to open and then immediately closes.
TestPDF2 (ShellEx) -- this works.  Reader opens to the file just as expected.
TestPDF3 (Shell)  -- this works.  Reader opens to the file just as expected.

Any idea why the "followhyperlink" code wouldn't work?
ok, does this prove that ShellEx is working? I did this cos u said u was having no joy but it looks okay now
FollowHyperlink was really made for url's but works okay for other files also. Obviously not so well for pdf's.
kenferrellAuthor Commented:
Well, I think ShellEx was probably working, but I might not have been coding the function call correctly....

Incidentally, our IT guy suggested this

Call Shell("CMD /C """ & sFile & """", 0)

as a work-around to FollowHyperlink...  seems to work pretty smoothly, and I don't need ShellEx.

Thanks again!
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.

All Courses

From novice to tech pro — start learning today.