VBA code to open MS Access forms using XP and Windows 7 1

[carolla]

Hello, we are in the process of switching over from Windows XP 32-bit to Windows 7 64-bit and the computers that have been upgraded are not using the same path. How can I reference both paths so everyone can access these databases during the transition? This is the path for the Windows 7 computers:
C:\Program Files (x86)\Microsoft Office 12\Office12\MSAccess.exe

This is the code I currently have with the path that we have been using with XP:

[highlight #BABDB6]Private Sub cmdArchivedReports_Click()
On Error GoTo Err_cmdArchivedReports_Click

Dim stAppName As String

If Me.cmbArchivedReports = "2011 Calls" Then
stAppName = "C:\Program Files\Microsoft Office 12\Office12\msaccess.exe ""G:\ PreventionInspection\Archived\ArchivedCallDatabases\Calls2011.mdb"""
ElseIf Me.cmbArchivedReports = "2012 Calls" Then
stAppName = "C:\Program Files\Microsoft Office 12\Office12\msaccess.exe ""G:\ PreventionInspection\Archived\ArchivedCallDatabases\Calls2012.mdb"""
ElseIf Me.cmbArchivedReports = "2013 Calls" Then
stAppName = "C:\Program Files\Microsoft Office 12\Office12\msaccess.exe ""G:\ PreventionInspection\Archived\ArchivedCallDatabases\Calls2013.mdb"""

Else
MsgBox "Please select a Call Database from the list.", vbOKOnly + vbExclamation, _
"Error"
Exit Sub
End If

Call Shell(stAppName, 1)

Exit_cmdArchivedReports_Click:
Exit Sub

Err_cmdArchivedReports_Click:
MsgBox Err.Description
Resume Exit_cmdArchivedReports_Click

End Sub[/highlight]

Thank you

 

[strongm]

You just need the following function:

[blue]Private Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
Private Const MAX_FILENAME_LEN = 260

[green]'Pass full path to file you are interested in[/green]
Private Function GetAssociatedExePath(strFilename As String) As String
    Dim strResult As String
    Dim result As Long
    strResult = Space(MAX_FILENAME_LEN)
    result = FindExecutable(strFilename, vbNullString, strResult)
    If result > 31 Then
        GetAssociatedExePath = Left$(strResult, InStr(strResult, Chr$(0)) - 1)
    Else
       GetAssociatedExePath = "No associated executable found"""
    End If
End Function[/blue]

An example calling it:

msgbox GetAssociatedExePath("G:\ PreventionInspection\Archived\ArchivedCallDatabases\Calls2011.mdb"")

 

[carolla]

Thank you Strongm,
Thanks for getting back to me so quick. I haven't had a chance to try this yet and just going off shift, but will get on it as soon as I'm back to work.
Cheers

 

[PHV]

Why not simply this ?
If Me.cmbArchivedReports = "2011 Calls" Then
stAppName = "[highlight #FCE94F]msaccess.exe[/highlight] ""G:\ PreventionInspection\Archived\ArchivedCallDatabases\Calls2011.mdb"""

and so on ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

Well quite! There's me over engineering ...

 

[carolla]

Thanks folks, I'll let you know how I make out when I'm back to work.

 

[carolla]

PHV...It worked!! Thank you so much
Have a great weekend,
Cheers