How to specify which version of Access to use for OLE automation

Many Access developers have multiple version of Access installed on their development machines.  This generally won't cause too many problems, however if you have combinations of Access 2000 / 2002 (XP) / 2003 then you may encounter problems when using OLE Automation trying to obtain an instance of a lower version of Access than the last version that was installed.

For example, consider the following scenario: I have a client that has Access 2000 and Access 2003 on the same machine and we need to use OLE automation to obtain an instance of Access 2000 (please note: in most circumstances Access 2003 can be used in place of Access 2000 so this scenario won't happen very often).  The normal methods will not work - for example;  Late binding: CreateObject("Access.Application.9") - Specifying the Access Version in the ProgID string will not help for Access 2000 or above - the latest version of Access that was installed will be used.

So what's the reasoning behind this?

When you specify a ProgID such as "Access.Application", the OS resolves this identifier (using the registry) into a CLSID (CLSID is a GUID that identifies a COM class object).  The OS then looks up the CLSID in the registry to find the path of the registered type library and corresponding EXE.

The CLSID is usually unique to the type library of each application, however for backwards compatibility reasons, the CLSID does not change between Access versions from 2000 onwards.  The CLSID for the type library of Access versions from 2000 and above is "{73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9}".  Therefore, if you specify the ProgID "Access.Application.9" or "Access.Application.11", the OS resolves to the *same* CLSID shown above.  For this reason, since Access 2003 was the latest version of Access to be installed on my clients machine, the CLSID resolves to Access 2003 even though we specified Access 9 (2000) in the ProgID.

There is no simple solution to this problem. 

Solution 1 (not recommended)

You can overwrite the LocalServer32 value in the registry key HKEY_CLASSES_ROOT\CLSID\{73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9}\LocalServer32 and specify the full path to the specific version of Access you want to use instead.  You would also need to delete/rename the data/cached version of the value in HKEY_CLASSES_ROOT\CLSID\{73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9}\LocalServer32\LocalServer32 otherwise changing the EXE path won't have any effect. 

Not a very good solution.

Solution 2 (much preferred)

Open the specific version of Access using the VBA Shell function, specifying the path of the database file you want to open in the command line, then bind to the Access instance at runtime using the GetObject() function passing in your filename.  So for example, the following would work for my client:

Shell """C:\PROGRAM FILES\MICROSOFT OFFICE 2000\OFFICE\MSACCESS.EXE"" C:\FileName.mdb"
Set objAccess = GetObject("C:\FileName.mdb").Application
	
'Here objAccess is an instance of Access 2000...

Note: You could skip opening a file and instead just pass the "Access.Application" progID to GetObject instead.  However, you can't guarantee to bind to the newly opened version of Access, therefore if my client already had Access 2003 opened, it wouldn't work.

The main issue with this solution is that you would need to know the full path of the specific version of Access you want to create an instance of.  But we can get this info from the registry...   I've created a module that will handle all this for you which is below.  Please Note: If you use this code, you should add some extra error handling! 

To use this code, create a new module in VB (or VBA), then copy & paste the code below into the new module.  In you application, you can simply call the main function as follows:

Set objAccess = CreateOfficeInstance("Access.Application.9", "C:\FileToOpen.mdb")

Note: You must specify the file name of the file you want to open for this to work properly!

Option Explicit

' ModCreateOfficeInstance by Wayne Phillips 2005 - © EverythingAccess.com
' Written on 23/11/2005 for automating a specific version of Access
' Updated on 07/03/2012 to allow for delayed binding and better error handling.
    
Private Declare Function RegCloseKey Lib "advapi32" _
                        (ByVal hKey As Long) As Long

Private Declare Function RegOpenKey Lib "advapi32" _
                         Alias "RegOpenKeyA" _
                        (ByVal hKey As Long, _
                         ByVal sSubKey As String, _
                         hKey As Long) As Long

Private Declare Function RegQueryValueEx Lib "advapi32" _
                         Alias "RegQueryValueExA" _
                        (ByVal hKey As Long, _
                         ByVal sKeyValue As String, _
                         ByVal lpReserved As Long, _
                         lpType As Long, _
                         lpData As Any, _
                         nSizeData As Long) As Long

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Function FnGetRegString(ByVal hKeyRoot As Long, _
                        ByVal strSubKey As String, _
                        ByVal strValueName As String, _
                        ByRef strRetVal As String) As Boolean

    Dim lngDataSize As Long
    Dim hKey As Long

    Const ERROR_MORE_DATA = 234
    Const ERROR_SUCCESS = 0

    strRetVal = ""

    If RegOpenKey(hKeyRoot, _
                 strSubKey, _
                 hKey) = ERROR_SUCCESS Then

        'Calculate the length of string we need to initialize as a buffer...
        If RegQueryValueEx(hKey, _
                         strValueName, _
                         0&, _
                         0&, _
                         ByVal strRetVal, _
                         lngDataSize) = ERROR_MORE_DATA Then

            'Initialize the string buffer to a fixed length
            strRetVal = String(lngDataSize + 1, 0)

            If RegQueryValueEx(hKey, _
                        strValueName, _
                        0&, _
                        0&, _
                        ByVal strRetVal, _
                        lngDataSize) = ERROR_SUCCESS Then

                'We got the value, trim the extra null terminator
                strRetVal = Left(strRetVal, InStr(1, strRetVal, Chr(0)) - 1)
                FnGetRegString = True

            End If

        End If

        Call RegCloseKey(hKey)

    End If

End Function

Private Function FnGetAssociatedPathFromProgID(ByVal strProgID As String) As String

    Dim strOutputPath As String

    Const HKEY_CLASSES_ROOT = &H80000000
    
    If FnGetRegString(HKEY_CLASSES_ROOT, strProgID & "\shell\Open\command", "", strOutputPath) = True Then
        FnGetAssociatedPathFromProgID = strOutputPath
    End If
    
End Function

Public Function CreateOfficeInstance(ByVal strProgID As String, _
                            ByVal strFilePath) As Object

    Dim strCommandLine As String
    Dim strPath As String
    
    'Get the class GUID (CLSID) from the input ProgID
    strPath = FnGetAssociatedPathFromProgID(strProgID)

    If Len(strPath) > 0 Then

        'The returned path is actually a command line string with parameter for file name as %1
        strCommandLine = Replace(strPath, "%1", strFilePath)

        Shell strCommandLine, vbMinimizedNoFocus

        'Attempt to bind to the instance...
        On Error GoTo RetryDelay
        Set CreateOfficeInstance = GetObject(strFilePath).Application
        
    Else
    
        Err.Raise 76, , "CreateOfficeInstance: failed to get path information for ProgID '" & strProgID & "'"
        
    End If
    
    Exit Function
    
RetryDelay:
    
    ' Binding to the created instance might fail if the external process hasn't yet loaded the
    ' document / file and registered itself in the COM Running Object Table, so we will retry
    ' several times after a short delay, before failing.
    
    Dim RetryCount As Long
    If RetryCount < 10 Then
        Sleep 500   ' Wait half a second and try again (upto 10 times)
        RetryCount = RetryCount + 1
        Resume
    Else
        On Error GoTo 0
        Err.Raise 429, , "CreateOfficeInstance: failed to bind to specific instance of '" & strProgID & "'"
    End If
    
End Function

*Microsoft Access is a trademark of Microsoft Corporation in the United States and other countries*

IMPORTANT: This document may not be reproduced in part or whole without prior consent from the author.

www.everythingaccess.com