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):


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

1
Path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
1
Name: ADODB
1
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


1
gappWord.Selection.MoveDown 5, 1, 1

Late Binding version


1
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

Want More?

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