Using VBA to determine which program to use

[option]

Hi all!

What I am looking to do is have my Access DB determine which version of this program Reflections to open. By default, we want it to run Reflection 8, but not all of our users have 8. Instead, a majority of them have Reflection 4. So if I could have my DB do something like:

if reflection8 is installed
use reflection8
if not
use reflection4

or something to that nature. Thanks in advance!

 

[DJkarl]

Hi all!

What I am looking to do is have my Access DB determine which version of this program Reflections to open. By default, we want it to run Reflection 8, but not all of our users have 8. Instead, a majority of them have Reflection 4. So if I could have my DB do something like:

if reflection8 is installed
use reflection8
if not
use reflection4

or something to that nature. Thanks in advance!

Found this online a while ago, it returns the path to the program associated with a specific file type. It includes an example of how to call it. Place this code into a public module.

Option Compare Database
Private Const cMAX_PATH = 260
Private Const ERROR_NOASSOC = 31
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&
Private Const ERROR_OUT_OF_MEM = 0
Private Declare Function apiFindExecutable Lib "shell32.dll" _
    Alias "FindExecutableA" _
    (ByVal lpFile As String, _
    ByVal lpDirectory As String, _
    ByVal lpResult As String) _
    As Long
    
Function fFindEXE(stFile As String, _
                    stDir As String) _
                    As String
'Usage Example:
'   ?fFindEXE("test.xls","c:\temp")
'
Dim lpResult As String
Dim lngRet As Long
    lpResult = Space(cMAX_PATH)
    lngRet = apiFindExecutable(stFile, stDir, lpResult)
    
    If lngRet > 32 Then
        fFindEXE = lpResult
    Else
        Select Case lngRet:
            Case ERROR_NOASSOC: fFindEXE = "Error: No Association"
            Case ERROR_FILE_NOT_FOUND: fFindEXE = "Error: File Not Found"
            Case ERROR_PATH_NOT_FOUND: fFindEXE = "Error: Path Not Found"
            Case ERROR_BAD_FORMAT:  fFindEXE = "Error: Bad File Format"
            Case ERROR_OUT_OF_MEM:  fFindEXE = "Error: Out of Memory"
        End Select
    End If
End Function

 

[option]

Fantastic! I'm going to try that out now, thanks!!!

 

[mattkorguk]

DJKarl - I don't suppose you've found something which looks at the file itself to determine the program to use? The above suggestion works great, very useful, although if a user has two versions of the application installed (I have 3 versions of Access installed for instance) it will only return the associated program. So even if it's an Access 2003 Db it still returns "C:\Program Files\Office97\Office\MSACCESS.EXE".
Does anyone know if there's a way to check the version of Access a Db has been created with?
Thanks.

 

[LPurvis]

It isn't that the file will ever tell you what executable to use (and hence the location) - but it's aware of the file version under which it was created. You'd then use the appropriate version to launch.

Obviously when you're looking for Access versions you don't to be using automation if you can help it.
(i.e. instantiating an Access application instance - as you're then left with the problem of which version to launch - the newest available obviously implies greatest compatability - but not necessarily the ability to directly open it - and there's the overhead involved).

If you do it on a data level though it's much simpler - but you're still needing to make sure you using a version of DAO that can access the MDB format you're aiming at.

Dim db as DAO.Database
Set db = OpenDatabase("C:\Whatever\YourMDB.mdb")
Debug.Print db.Properties("AccessVersion")
'returns for each file format : the value
'2007 : 9.50
'2002-2003 : 9.50
'2000: 8.50
'97: 7.53

Obviously 2007 can be determined by other means lol (A great big whapping file extention lol).
But also that it's Jet version has advanced
i.e.
?CurrentDb.Version
'returns 12
whereas previously it was 4.0 and 3.0 (note not 3.5!)

That alone is really enough to determine a version which is capable of opening the file you're looking at.

ADO offers less direct help (unsurprisingly - it's provider specific).
The connection object offering the "Jet OLEDB:Engine Type" property which returns 5 for Jet 4 - and 4 for Jet 3.
(Don't ask - I don't know, it's probably just rounded up lol).

I've never tried examining the file on a binary level for the info.
But it's there as listed for the taking by other means.

 

[mattkorguk]

That's something to look into, thanks Leigh for your prompt reply. Have a good weekend.