Open a Different Application File From Within Access

[philip.mccollum]

Hi
I'm relatively new to using access.

I have a created a form to show each record of an inventory of parts. Each part will have an associated autocad and PDF drawing (There will be 1000s of parts eventually). Initially when creating the record I need to be able to search for the Autocad and PDF files for each record and easily find it when going through back records. I then want to be able to open the file through Access by either clicking the hyperlink or a button so as to be able to easily retrieve the drawing.

I,m not very familiar with code or what to do with it but I have managed to find and use a ‘browse to file’ code that someone has created and shared online.
https://www.youtube.com/watch?v=rNHLea359p4

I,ve somehow managed to get this working for myself but now I need some way of opening the file in its native program.

https://www.dropbox.com/s/mwvsuwukkt...esign.pdf?dl=0

I,ve attached a link to dropbox for my current database.

Many Thanks


Phil McCollum

[ranman256]

Paste this code into a module, and it will open ANY file in its native application.


usage: OpenNativeApp "c:\folder\file.pdf" , will open it in acrobat
and
OpenNativeApp "c:\folder\file.doc" , will open the doc in Word, etc.

so you would use: OpenNativeApp txtBox
depending what is in the text box, it will open in its own app.

'Attribute VB_Name = "modNativeApp"
'Option Compare Database
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long

Private Declare Function GetDesktopWindow Lib "user32" () As Long
Const SW_SHOWNORMAL = 1
Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&




Public Sub OpenNativeApp(ByVal psDocName As String)
Dim r As Long, msg As String


r = StartDoc(psDocName)
If r <= 32 Then
    'There was an error
    Select Case r
        Case SE_ERR_FNF
            msg = "File not found"
        Case SE_ERR_PNF
            msg = "Path not found"
        Case SE_ERR_ACCESSDENIED
            msg = "Access denied"
        Case SE_ERR_OOM
            msg = "Out of memory"
        Case SE_ERR_DLLNOTFOUND
            msg = "DLL not found"
        Case SE_ERR_SHARE
            msg = "A sharing violation occurred"
        Case SE_ERR_ASSOCINCOMPLETE
            msg = "Incomplete or invalid file association"
        Case SE_ERR_DDETIMEOUT
            msg = "DDE Time out"
        Case SE_ERR_DDEFAIL
            msg = "DDE transaction failed"
        Case SE_ERR_DDEBUSY
            msg = "DDE busy"
        Case SE_ERR_NOASSOC
            msg = "No association for file extension"
        Case ERROR_BAD_FORMAT
            msg = "Invalid EXE file or error in EXE image"
        Case Else
            msg = "Unknown error"
    End Select
'    MsgBox msg
End If
End Sub

[ItsMe]

Another approach is to use the Shell() function. Here is an example using the old Adobe Reader 9. All you need to know is the path to the application's exe file.

Dim stAppName As String
Dim strFilePath As String

    strFilePath = "\\ServerName\Folder\FileName.extension"
    stAppName = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe " & strFilePath

    Call Shell(stAppName, 1)

[ranman256]

but SHELL will ONLY work on Adobe (shown above)

[ItsMe]

but SHELL will ONLY work on Adobe (shown above)

Not quite sure what it is you are trying to say. The Shell() function is a VBA function and does not have anything to do with Adobe.
https://msdn.microsoft.com/en-us/lib.../gg278437.aspx

Perhaps I did not explain, well enough, how it can open different files using different applications. In the example code I provided, you will have to adjust the variable named, "stAppName", to equal the path to the desired exe file.

Here is another example using the Shell() function to open an Excel file. Please, understand that the strings will have to be edited to match the correct paths for the desired application and file.

Dim stAppName As String
Dim strFilePath As String
    strFilePath = "C:\Test\ExcelFiles\ExcelFiles.xlsx"
    stAppName = "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE " & strFilePath

    Call Shell(stAppName, 1)

[Perceptus]

I would use the VB Shell call also. There is much you can do with Shell. I think Shell also returns the HWND of the window to perform further operations if you need.

[philip.mccollum]

Guys
Thanks for the responses. 'ItsMe' - I tried your approach. The native app will be AutoCAD.
What I did:-
Opened form in design view
highlighted hyperlink field that contains the filepath. The filepath in my case will be on a server mapped as M:\ so an example filepath will be M:\Design Users\SYSTEMS DEVELOPMENT\2. FINAL DIE DRAWINGS\UK SYSTEMS\ALUMINIUM\SAPA\75.10 - 3-6311.dwg
On the property sheet I drilled into - On Click [Event Procedure]
Visual Basic opens
Pasted in code
Changed the application line
Code is as follows:
Private Sub FinalDieDwgLink_Click()
Dim stAppName As String
Dim strFilePath As String
strFilePath = "\\ServerName\Folder\FileName.extension"
stAppName = "C:\Program Files\Autodesk\AutoCAD LT 2015 " & strFilePath

Call Shell(stAppName, 1)

End Sub

If I then go back to form view and click the hyperlink I get a Runtime Error
Runtime Error 53

File Not Found

I also tried a autocad file within C:\ but this doesn't work either.

[philip.mccollum]

'ItsMe' - I see that I need to specify the file path too. So my code for example would look like


Private Sub FinalDieDwgLink_Click()
Dim stAppName As String
Dim strFilePath As String
strFilePath = "C:\Program Files\Autodesk\AutoCAD LT 2015\Sample\Mechanical Sample\Data Extraction and Multileaders Sample.dwg"
stAppName = "C:\Program Files\Autodesk\AutoCAD LT 2015 " & strFilePath

Call Shell(stAppName, 1)

End Sub

This still gives a runtime error

[philip.mccollum]

'ItsMe' - I see that I need to specify the file path too. So my code for example would look like


Private Sub FinalDieDwgLink_Click()
Dim stAppName As String
Dim strFilePath As String
strFilePath = "C:\Program Files\Autodesk\AutoCAD LT 2015\Sample\Mechanical Sample\Data Extraction and Multileaders Sample.dwg"
stAppName = "C:\Program Files\Autodesk\AutoCAD LT 2015 " & strFilePath

Call Shell(stAppName, 1)

End Sub

This still gives a runtime error

[philip.mccollum]

Also. If I did get this 'ItsMe' method working do I have to specify a filepath for each file. Each record will have 2 files and there will be 1000's of records.

[philip.mccollum]

Ranman
I,ve copied the code into a module. Where do I go from there?
Thanks
Phil

[philip.mccollum]

I,ve copied Ranman's module code. But I'm not sure how to proceed from there. Heres the link to my database. Can someone please take a look.
https://www.dropbox.com/s/p51bz6o82c...er1.accdb?dl=0

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...24#post1438624

[ItsMe]

C:\Program Files\Autodesk\AutoCAD LT 2015 is not the path to the executable. You will need to place the file name at the end.

C:\Program Files\Autodesk\AutoCAD LT 2015\*****.exe

[philip.mccollum]

Hi Ajax. Apologies. I had asked the question in another forum as well. I have deleted it from access programmers.