Access: Persistence of Reference Problems

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


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 dialogFile Name and Path
Office 97
Access 97Microsoft Access 8.0 Object LibraryC:\Program Files\Microsoft Office\Office\msacc8.olb
Excel 97Microsoft Excel 8.0 Object LibraryC:\Program Files\Microsoft Office\Office\excel8.olb
Outlook 98Outlook 98 Type LibraryC:\Program Files\Microsoft Office\Office\msoutl85.olb
Word 97Microsoft Word 8.0 Object LibraryC:\Program Files\Microsoft Office\Office\msword8.olb
Office 2000
Access 2000Microsoft Access 9.0 Object LibraryC:\Program Files\Microsoft Office\Office\msacc9.olb
Excel 2000Microsoft Excel 9.0 Object LibraryC:\Program Files\Microsoft Office\Office\excel9.olb
Outlook 2000Microsoft Outlook 9.0 Object LibraryC:\Program Files\Microsoft Office\Office\msoutl9.olb
Word 2000Microsoft Word 9.0 Object LibraryC:\Program Files\Microsoft Office\Office\msword9.olb
DAO 3.51Microsoft DAO 3.51 Object LibraryC:\Program Files\Common Files\Microsoft Shared\DAO\dao350.dll
ADO 2.1Microsoft ActiveX Data Objects 2.1 LibraryC:\Program Files\Common Files\system\ado\msado21.tlb
Office XP
Access 2002Microsoft Access 10.0 Object LibraryC:\Program Files\Microsoft Office\Office10\msacc.olb
Excel 2002Microsoft Excel 10.0 Object LibraryC:\Program Files\Microsoft Office\Office10\excel.exe
Outlook 2002Microsoft Outlook 10.0 Object LibraryC:\Program Files\Microsoft Office\Office10\msoutl.olb
Word 2002Microsoft Word 10.0 Object LibraryC:\Program Files\Microsoft Office\Office10\msword.olb
DAO 3.6Microsoft DAO 3.6 Object LibraryC:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
ADO 2.5Microsoft ActiveX Data Objects 2.5 LibraryC:\Program Files\Common Files\system\ado\msado25.tlb
Office 2003
Access 2003Microsoft Access 11.0 Object LibraryC:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
Excel 2003Microsoft Excel 11.0 Object LibraryC:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
Outlook 2003Microsoft Outlook 11.0 Object LibraryC:\Program Files\Microsoft Office\OFFICE11\MSOUTL.OLB
Word 2003Microsoft Word 11.0 Object LibraryC:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB
DAOMicrosoft DAO 3.6 Object LibraryC:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
ADOMicrosoft ActiveX Data Objects 2.1 LibraryC:\Program Files\Common Files\System\ado\msado21.tlb
Office 2007
Access 2007Microsoft Access 12.0 Object LibraryC:\Program Files (x86)\Microsoft Office\Office12\MSACC.OLB
Excel 2007Microsoft Excel 12.0 Object LibraryC:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE
Outlook 2007Microsoft Outlook 12.0 Object LibraryC:\Program Files (x86)\Microsoft Office\Office12\MSOUTL.OLB
Word 2007Microsoft Word 12.0 Object LibraryC:\Program Files (x86)\Microsoft Office\Office12\MSWORD.OLB
DAOMicrosoft Office 12.0 Access database engine Object LibraryC:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\ACEDAO.DLL
ADOMicrosoft ActiveX Data Objects 2.1 LibraryC:\Program Files (x86)\Common Files\System\ado\msado21.tlb
Office 2010
Access 2010Microsoft Access 14.0 Object LibraryC:\Program Files\Microsoft Office\Office14\MSACC.OLB
Excel 2010Microsoft Excel 14.0 Object LibraryC:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Outlook 2010Microsoft Outlook 14.0 Object LibraryC:\Program Files\Microsoft Office\Office14\MSOUTL.OLB
Word 2010Microsoft Word 14.0 Object LibraryC:\Program Files\Microsoft Office\Office14\MSWORD.OLB
DAOMicrosoft Office 14.0 Access database engine Object LibraryC:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLL
ADOMicrosoft ActiveX Data Objects 2.8 LibraryC:\Program Files\Common Files\System\ado\msado28.tlb
Office 2013
Access 2013Microsoft Access 15.0 Object LibraryC:\Program Files\Microsoft Office 15\Root\Office15\MSACC.OLB
Excel 2013Microsoft Excel 15.0 Object LibraryC:\Program Files\Microsoft Office 15\Root\Office15\EXCEL.EXE
Outlook 2013Microsoft Outlook 15.0 Object LibraryC:\Program Files\Microsoft Office 15\Root\Office15\MSOUTL.OLB
Word 2013Microsoft Word 15.0 Object LibraryC:\Program Files\Microsoft Office 15\Root\Office15\MSWORD.OLB
DAOMicrosoft Office 15.0 Access database engine Object LibraryC:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEDAO.DLL
ADOMicrosoft ActiveX Data Objects 2.8 LibraryC:\Program Files (x86)\Common Files\System\ado\msado28.tlb
Office 2016
Access 2016Microsoft Access 16.0 Object LibraryC:\Program Files (x86)\Microsoft Office\Root\Office16\MSACC.OLB
Excel 2016Microsoft Excel 16.0 Object LibraryC:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE
Outlook 2016Microsoft Outlook 16.0 Object LibraryC:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
Word 2016Microsoft Word 16.0 Object LibraryC:\Program Files (x86)\Microsoft Office\Root\Office16\MSWORD.OLB
DAOMicrosoft Office 16.0 Access database engine Object LibraryC:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
ADOMicrosoft ActiveX Data Objects 2.8 LibraryC:\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 NameDocument TypePlace in
Test Automation Code 2010.accdbAccess 2007-2010 database (can also be used in higher versions of Access)Wherever you want
Test Automation Code 2010 opened in 2016.accdbAccess 2007-2010 database (can also be used in higher versions of Access)Wherever you want

Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.