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: VBAPath: C:\Program Files\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLLName: AccessPath: C:\Program Files\Microsoft Office\Office14\MSACC.OLBName: stdolePath: C:\Windows\System32\stdole2.tlbName: DAOPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLLName: OutlookPath: C:\Program Files\Microsoft Office\Office14\MSOUTL.OLBName: WordPath: C:\Program Files\Microsoft Office\Office14\MSWORD.OLBName: Excel
Path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXEName: ADODBPath: 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).
- Make sure that any database with references to the Word, Excel or Outlook object models is run only in one version of Office.
- 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, 1Late Binding version
gappWord.Selection.MoveDown unit:=wdLine, Count:=1, Extend:=wdExtendThe 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 |