Solved

Help storing links to .pdf files in MS Access

Posted on 2006-07-23
16
407 Views
Last Modified: 2012-06-27
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
Comment
Question by:kenferrell
  • 10
  • 4
  • 2
16 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
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 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
            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

by:kenferrell
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

by:Patrick Matthews
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

by:kenferrell
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

by:kenferrell
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

by:kenferrell
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

by:kenferrell
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

by:
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

FollowHyperlink sFile
'or
ShellEx sFile

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:kenferrell
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?

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?

Thanks!
0
 

Author Comment

by:kenferrell
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

by:rockiroads
ID: 17170557
Does Acrobat Reader work well by itself?

Can u open these PDF's manually?
0
 

Author Comment

by:kenferrell
ID: 17170755
Yup -- I can open the documents without any problem...  
0
 
LVL 65

Expert Comment

by:rockiroads
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)
    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

0
 

Author Comment

by:kenferrell
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

by:rockiroads
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

by:kenferrell
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now