# 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!

Thanks!
###### Who is Participating?

Commented:
kenferrell

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

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 'or ShellEx sFile 0 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_SHOWMAXIMIZED = 3 essSW_SHOWMINIMIZED = 2 essSW_SHOWNORMAL = 1 essSW_SHOWNOACTIVATE = 4 essSW_SHOWNA = 8 essSW_SHOWMINNOACTIVE = 7 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_ASSOCINCOMPLETE = 27 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)
Else
lR = ShellExecute(Owner, sOperation, sFIle, sParameters, sDefaultDir, eShowCmd)
End If
If (lR < 0) Or (lR > 32) Then
ShellEx = True
Else
' raise an appropriate error:
lErr = vbObjectError + 1048 + lR
Select Case lR
Case 0
lErr = 7: sErr = "Out of memory"
Case ERROR_FILE_NOT_FOUND
Case ERROR_PATH_NOT_FOUND
sErr = "The executable file is invalid or corrupt"
Case SE_ERR_ACCESSDENIED
lErr = 75: sErr = "Path/file access error"
Case SE_ERR_ASSOCINCOMPLETE
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."
Case SE_ERR_DDETIMEOUT
lErr = 286: sErr = "The file could not be opened due to time out. Please try again in a moment."
Case SE_ERR_DLLNOTFOUND
Case SE_ERR_FNF
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
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: http://www.vbaccelerator.com/codelib/shell/shellex.htm

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

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

ShellExecute

Regards,

Patrick
0

Author Commented:
OK,

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?
0

Commented:
kenferrell,

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.

Regards,

Patrick
0

Author 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....
0

Author 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?
0

Author 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"

0

Author 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.
0

Author 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?

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?

Thanks!
0

Author 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?
0

Commented:
Does Acrobat Reader work well by itself?

Can u open these PDF's manually?
0

Author Commented:
Yup -- I can open the documents without any problem...
0

Commented:
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)
end function

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

public function TestPDF1(byval sFile as String)
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

0

Author 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?
0

Commented:
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.
0

Author 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!
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.