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

Want More?

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