Solved

# Help storing links to .pdf files in MS Access

Posted on 2006-07-23
410 Views
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!
0
Question by:kenferrell
• 10
• 4
• 2

LVL 92

Assisted Solution

Patrick Matthews earned 100 total points
ID: 17164928
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 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_NOASSOC = 31
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 lErr = 53: sErr = "File not found" Case ERROR_PATH_NOT_FOUND lErr = 76: sErr = "Path not found" Case ERROR_BAD_FORMAT 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 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: 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 Comment ID: 17165044 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 LVL 92 Expert Comment ID: 17165052 kenferrell, 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. Regards, Patrick 0 Author Comment ID: 17165060 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 Comment ID: 17165140 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 Comment ID: 17165156 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 Comment ID: 17165253 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 LVL 65 Accepted Solution rockiroads earned 400 total points ID: 17165600 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 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

'or
ShellEx sFile

0

Author Comment

ID: 17166908
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 Comment

ID: 17167852
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

LVL 65

Expert Comment

ID: 17170557
Does Acrobat Reader work well by itself?

Can u open these PDF's manually?
0

Author Comment

ID: 17170755
Yup -- I can open the documents without any problem...
0

LVL 65

Expert Comment

ID: 17172401
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 Comment

ID: 17178215
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

LVL 65

Expert Comment

ID: 17178247
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 Comment

ID: 17179276
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…