Access: Persistence of Reference Problems

Back in 2002 I wrote an Access Archon article dealing with reference issues, among them the problem of certain references being upgraded when a database is opened in a higher version of Access, but not downgraded when it is again opened in a lower version. You might think that over the last 16 years Microsoft would have addressed this annoying problem, but no such luck. The situation is exactly the same as then; only the version numbers have changed.

Demonstration of the Problem

I made a database in Access 2010, containing only a module with four procedures and three macros to run some of the procedures. In addition to the standard references, the database has references to Outlook, Excel and Word, as well as ADO 2.8 (in Access 2007 and higher, DAO support is provided by the Microsoft Office Access database engine Object Library). These references are set in the References dialog opened from the Visual Basic window:

The References dialog, with references set to Office applications

The version number in the References dialog is not the familiar one, Office 2010, but rather v. 14.0. See the table later in this article for information on version numbers. There is no v. 13 of any Office application, presumably for superstitious reasons.

Each of the three macros in the test database runs a procedure that pops up a message box displaying relevant information:

Informative messages in Access 2010

Messages with data related to referenced object libraries

If you don’t have references to Word or Outlook, you will get errors. If there is no Word reference, and you run mcrTestWordAutomationCode, this error message will pop up:

Missing reference error

The Visual Basic window opens, with the line of code that sets the reference to the Word application object highlighted. This is fairly informative, but the errors that occur with wrong version references are more cryptic.

The 2010 database can be opened in a higher version of Access, and its references will automatically upgrade to the higher version, if needed. Thus, if the database is opened in 2016, the macros can be run, and will produce the same messages, only cosmetically different than in older versions (the invisible title bar is not an improvement, in my opinion):

Informative messages in Access 2016

However, if the database that has been opened in a higher version is then reopened in a lower version of Access – for example, opening the database called Test Automation Code 2010 opened in 2016.accdb in Access 2010 – then you will see the “User-defined type not defined” error when you run the macros that reference the Word and Outlook object models. In a more realistic situation, where you have a large database with lots of Automation code referencing one or more other Office applications using early binding, you will get more puzzling error messages, such as this one, which will likely appear when the database is reopened in a lower version:

An error on opening a database in a lower version following opening in a higher version

On running code in the database, you may get this message:

 That is reasonable, but the following one is not:

A peculiar error message resulting from wrong version references

You may get errors referencing various perfectly legitimate functions such as Mid, Len, Left, Right or Date, possibly with a line of code highlighted:

strLongDate = Format(Date, "mmmm d, yyyy")

There is nothing wrong with this line of code; the error is most likely the result of a missing or wrong version reference. To fix this problem, open the References dialog. If there are any references marked “MISSING” (as shown below), uncheck them, and check the references corresponding to the appropriate version of any object libraries used in your code (such as Excel, Word or Outlook).

Version numbers of the object libraries don’t exactly correspond to the version numbers of the applications; here is a table that matches them up for the most common Office applications (the Office path is the standard path; if you installed Office to a custom location, it will be different on your computer). The table lists the versions (user-friendly and as used in the References dialog) and the file name and path of the object library file, for Access versions from 97 through 2016:

Application Version	Object Library Name in References dialog	File Name and Path
Office 97
Access 97	Microsoft Access 8.0 Object Library	C:\Program Files\Microsoft Office\Office\msacc8.olb
Excel 97	Microsoft Excel 8.0 Object Library	C:\Program Files\Microsoft Office\Office\excel8.olb
Outlook 98	Outlook 98 Type Library	C:\Program Files\Microsoft Office\Office\msoutl85.olb
Word 97	Microsoft Word 8.0 Object Library	C:\Program Files\Microsoft Office\Office\msword8.olb
Office 2000
Access 2000	Microsoft Access 9.0 Object Library	C:\Program Files\Microsoft Office\Office\msacc9.olb
Excel 2000	Microsoft Excel 9.0 Object Library	C:\Program Files\Microsoft Office\Office\excel9.olb
Outlook 2000	Microsoft Outlook 9.0 Object Library	C:\Program Files\Microsoft Office\Office\msoutl9.olb
Word 2000	Microsoft Word 9.0 Object Library	C:\Program Files\Microsoft Office\Office\msword9.olb
DAO 3.51	Microsoft DAO 3.51 Object Library	C:\Program Files\Common Files\Microsoft Shared\DAO\dao350.dll
ADO 2.1	Microsoft ActiveX Data Objects 2.1 Library	C:\Program Files\Common Files\system\ado\msado21.tlb
Office XP
Access 2002	Microsoft Access 10.0 Object Library	C:\Program Files\Microsoft Office\Office10\msacc.olb
Excel 2002	Microsoft Excel 10.0 Object Library	C:\Program Files\Microsoft Office\Office10\excel.exe
Outlook 2002	Microsoft Outlook 10.0 Object Library	C:\Program Files\Microsoft Office\Office10\msoutl.olb
Word 2002	Microsoft Word 10.0 Object Library	C:\Program Files\Microsoft Office\Office10\msword.olb
DAO 3.6	Microsoft DAO 3.6 Object Library	C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
ADO 2.5	Microsoft ActiveX Data Objects 2.5 Library	C:\Program Files\Common Files\system\ado\msado25.tlb
Office 2003
Access 2003	Microsoft Access 11.0 Object Library	C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
Excel 2003	Microsoft Excel 11.0 Object Library	C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
Outlook 2003	Microsoft Outlook 11.0 Object Library	C:\Program Files\Microsoft Office\OFFICE11\MSOUTL.OLB
Word 2003	Microsoft Word 11.0 Object Library	C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB
DAO	Microsoft DAO 3.6 Object Library	C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
ADO	Microsoft ActiveX Data Objects 2.1 Library	C:\Program Files\Common Files\System\ado\msado21.tlb
Office 2007
Access 2007	Microsoft Access 12.0 Object Library	C:\Program Files (x86)\Microsoft Office\Office12\MSACC.OLB
Excel 2007	Microsoft Excel 12.0 Object Library	C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE
Outlook 2007	Microsoft Outlook 12.0 Object Library	C:\Program Files (x86)\Microsoft Office\Office12\MSOUTL.OLB
Word 2007	Microsoft Word 12.0 Object Library	C:\Program Files (x86)\Microsoft Office\Office12\MSWORD.OLB
DAO	Microsoft Office 12.0 Access database engine Object Library	C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\ACEDAO.DLL
ADO	Microsoft ActiveX Data Objects 2.1 Library	C:\Program Files (x86)\Common Files\System\ado\msado21.tlb
Office 2010
Access 2010	Microsoft Access 14.0 Object Library	C:\Program Files\Microsoft Office\Office14\MSACC.OLB
Excel 2010	Microsoft Excel 14.0 Object Library	C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Outlook 2010	Microsoft Outlook 14.0 Object Library	C:\Program Files\Microsoft Office\Office14\MSOUTL.OLB
Word 2010	Microsoft Word 14.0 Object Library	C:\Program Files\Microsoft Office\Office14\MSWORD.OLB
DAO	Microsoft Office 14.0 Access database engine Object Library	C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLL
ADO	Microsoft ActiveX Data Objects 2.8 Library	C:\Program Files\Common Files\System\ado\msado28.tlb
Office 2013
Access 2013	Microsoft Access 15.0 Object Library	C:\Program Files\Microsoft Office 15\Root\Office15\MSACC.OLB
Excel 2013	Microsoft Excel 15.0 Object Library	C:\Program Files\Microsoft Office 15\Root\Office15\EXCEL.EXE
Outlook 2013	Microsoft Outlook 15.0 Object Library	C:\Program Files\Microsoft Office 15\Root\Office15\MSOUTL.OLB
Word 2013	Microsoft Word 15.0 Object Library	C:\Program Files\Microsoft Office 15\Root\Office15\MSWORD.OLB
DAO	Microsoft Office 15.0 Access database engine Object Library	C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEDAO.DLL
ADO	Microsoft ActiveX Data Objects 2.8 Library	C:\Program Files (x86)\Common Files\System\ado\msado28.tlb
Office 2016
Access 2016	Microsoft Access 16.0 Object Library	C:\Program Files (x86)\Microsoft Office\Root\Office16\MSACC.OLB
Excel 2016	Microsoft Excel 16.0 Object Library	C:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE
Outlook 2016	Microsoft Outlook 16.0 Object Library	C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
Word 2016	Microsoft Word 16.0 Object Library	C:\Program Files (x86)\Microsoft Office\Root\Office16\MSWORD.OLB
DAO	Microsoft Office 16.0 Access database engine Object Library	C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
ADO	Microsoft ActiveX Data Objects 2.8 Library	C:\Program Files (x86)\Common Files\System\ado\msado28.tlb

Note that the file paths may differ on your computer, depending on whether you have 32-bit or 64-bit Office, or if you are running Office 365 rather then the regular Office version of Access. To view the file paths on your computer, run the ListReferences procedure; the file paths for all referenced object libraries will be listed in the Immediate Window (the list below is for Access 2010):

Name: VBA Path: C:\Program Files\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL Name: Access Path: C:\Program Files\Microsoft Office\Office14\MSACC.OLB Name: stdole Path: C:\Windows\System32\stdole2.tlb Name: DAO Path: C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLL Name: Outlook Path: C:\Program Files\Microsoft Office\Office14\MSOUTL.OLB Name: Word Path: C:\Program Files\Microsoft Office\Office14\MSWORD.OLB Name: Excel

Path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
 Name: ADODB Path: C:\Program Files\Common Files\System\ado\msado28.tlb

 

There are two possible solutions to the problem of references not downgrading (neither of them great).

1. Make sure that any database with references to the Word, Excel or Outlook object models is run only in one version of Office.
2. Modify all code to use late binding instead of early binding by declaring all Automation objects as Object, and remove any features or code that won’t work in the higher version of Access. This takes a lot of time, and makes your code much less comprehensible. To see what I mean, here are two versions of the same line of Automation code (it selects a line of text in a Word document so a style can be applied to the selected text):

Early Binding version

gappWord.Selection.MoveDown 5, 1, 1

Late Binding version

gappWord.Selection.MoveDown unit:=wdLine, Count:=1, Extend:=wdExtend

The early binding version is longer, but much more comprehensible.

Unfortunately, until Microsoft ensures that any reference that has been upgraded can also be downgraded, you will need to one of these unsatisfactory solutions is needed.

 

VBA Code

Public Function TemplateDir() As String

On Error GoTo ErrorHandler

   Dim appWord As Word.Application

   Set appWord = CreateObject("Word.Application")
   TemplateDir = appWord.Options.DefaultFilePath(wdUserTemplatesPath) & "\"
   MsgBox "Word template folder: " & TemplateDir

ErrorHandlerExit:
   Exit Function

ErrorHandler:

   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Public Function OfficeVersion() As String

On Error GoTo ErrorHandler

   Dim appWord As Word.Application

   Set appWord = CreateObject("Word.Application")
   OfficeVersion = appWord.Version
   MsgBox "Office version: " & OfficeVersion

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function


Public Function CurrentOutlookFolder()

On Error GoTo ErrorHandler

   Dim appOutlook As Outlook.Application
   Dim exp As Outlook.Explorer
   Dim fld As Outlook.MAPIFolder

   Set appOutlook = CreateObject("Outlook.Application")
   Set exp = appOutlook.ActiveExplorer
   Set fld = exp.CurrentFolder
   CurrentOutlookFolder = fld.Name
   MsgBox "Current Outlook folder: " & CurrentOutlookFolder

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 91 Then
     MsgBox "Please run Outlook and try again"

   Else
     MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
     Resume ErrorHandlerExit
   End If

End Function

Public Sub ListReferences()

   Dim ref As Access.Reference

   For Each ref In Access.References
     Debug.Print "Name: " & ref.Name
     Debug.Print "Path: " & ref.FullPath
   Next ref

End Sub

References

The code in the sample database needs the following references (in addition to the default references):

Microsoft Outlook 14.0 Object Library

Microsoft Word 14.0 Object Library

Supporting Files

The zip file containing this article, in Word 2007-2010 format, plus the supporting file(s), may be downloaded from accarch253.zip,

Document Name	Document Type	Place in
Test Automation Code 2010.accdb	Access 2007-2010 database (can also be used in higher versions of Access)	Wherever you want
Test Automation Code 2010 opened in 2016.accdb	Access 2007-2010 database (can also be used in higher versions of Access)	Wherever you want