White Paper: Microsoft Office VBA References in Microsoft Microsoft Access 2016

 
 

Below is a document Helen Feddema wrote about 12 years ago. She says that the process is the same for 2010/2016 as for 2000/2002 – uncheck the references marked MISSING and reselect the Excel, Outlook and Word references for the version of Office you are using. The problem will recur any time the database is opened in 2016 and then later in 2010.

 
 

 
 

White Paper: Office References in Access 2002 – By Helen Feddema

 
 

Introduction

Access 2002 lets you work with Access 2000 databases without converting them – not just read-only (as with Access 97 databases), but with full read-write capabilities. This is a very useful feature, and I often work with Access 2000 databases in Access 2002. However, I have discovered that there is a problem with references to certain Office components when an Access 2000 database is used alternately in Access 2000 and Access 2002 – basically, Outlook and Word references upgrade when moving from Access 2000 to Access 2002, but don’t downgrade when moving from Access 2002 to Access 2000, leading to a reference error when the database is next opened in Access 2000.

 

Testing

If you work with other Office programs (such as Word, Outlook or Excel), you need to set a reference to the appropriate object library in the References dialog, which is opened from the Tools menu in the VBE window. If you have Office XP and Office 2000 installed in separate partitions (or on different computers), you can test this problem by following the steps below:

 

1. Open (or create) an Access 2000 database in Access 2000.
2. Press Alt-F11 to open the VBE window, drop down the Tools menu and select References to open the References dialog.
3. Check the Excel, Outlook, PowerPoint and Word 9.0 object library references, as shown in Figure A.

 

 

Figure A. References to Office 2000 object libraries in Office 2000 database

 

4. Close the database, then go to your Office XP partition (or computer), and open the same database in Access 2002.
5. Press Alt-F11 to open the VBE window and open the References dialog as described above.
6. Note that the references to the four Office programs have all been upgraded to 10.0, as shown in Figure B.

 

 

Figure B. Upgraded references in an Access 2000 database opened in Access 2002
7. Close the database, go back to the Office 2000 partition (or computer), and reopen the database in Access 2000.
8. Check the references again; note that the four references to other Office programs are all listed as MISSING; they have not been downgraded to the 9.0 versions.

 

Figure C. Missing references back in Access 2000

 

I have occasionally seen an Excel reference downgraded to 9.0 after an Access 2000 database is opened in Access 2002 and then reopened in Access 2000; however, Outlook and Word references (in my experience) are never downgraded. This means that if you open an Access 2000 database with a reference to Outlook or Word (or most likely, any other Office program) in Access 2002, and later you (or someone else) opens the database in Access 2000, there will be a reference error, and the references will have to be reset to the v. 9.0 object library.

 

Curiously, the Office object library is always downgraded (as you can see in the figures); too bad this doesn’t happen with all the Office program references as well.

 

Update

In response to my Office References in Access 2000 article in WAW 3.17, Paul Mitchell wrote that he has experienced a similar problem with Excel 5 and Excel 97. I have myself experienced a similar problem with Outlook references in Word 97 and Word 2000 VBA code. However, the element of working (with full read-write access) on an earlier version’s databases is new to Access 2002, so this is the first time we have had a chance to experience this bug with Access databases alternately opened in two Access versions.

Additionally, the problem appears to be much more severe with Access 2000 databases alternately opened in Access 2000 and 2002, and it affects references to all other Office programs, not just Outlook (Outlook references have had a problem with not downgrading from VBA code in other Office programs for several versions now.) Too bad Microsoft didn’t take the opportunity to fix this problem for Office XP, rather than letting it expand and become even more troublesome in this otherwise very useful new Access feature.

 

What is needed here is that Access (and other Office programs) should detect the currently running version of Office, and reset all references to Office programs to the current version. This does happen for the Office object library reference itself, so it should be possible to fix references to the various Office programs similarly – just making them match the Office object library version would do the trick (except for Outlook, which would need some special handling because of its non-standard numbering).

 

Mr. Mitchell suggests recreating the references programmatically, putting code used to recreate references in a separate module so it can run even if full Compile and Save fails because of broken references.

 

Alan Taylor has a clever solution to this problem: Remove the reference to the Office 10 object library, and replace it with a reference to the Office 9 object library while the Access 2000 database is open in Access 2002. Once you have set a reference to Word 9.0, Outlook 9.0, or whatever other Office component you need in Access 2002, you can alternately open the database in Access 2000 and Access 2002, without reference errors.

 

Of course, this means that you can’t use any new components in the Office 10 object libraries, but you shouldn’t be doing that anyway, in an Access 2000 database, because they will cause errors when the database is opened in Access 2000.

 

The Office 9 object libraries are located in the \Microsoft Office\Office folder; if you have access to this folder when you are working in Office XP, just set references to them using the Browse button in the References dialog; otherwise (for example, if you have the two Office versions in different partitions, and you can’t see the Office 2000 Office folder when you are in the Office XP partition), copy the references to a folder accessible in Office XP, and browse for them in that folder.

 

The names of the Office 9 object libraries are listed below:

• Excel – Excel9.olb
• Outlook – msoutl9.olb
• PowerPoint – msppt9.olb
• Word – msword9.olb

 

You don’t need to set references to Access 9.0 or Office 9.0 in Access 2002, because these references do downgrade automatically when the database is opened in Access 2000.

 

 
 

Do you want to know more about Helen Feddema? Here are a few links to take a look at.

• Our Team: Meet Helen Feddema – One of the most experienced experts in Microsoft Excel and Access, as well as Word and Outlook. Helen is one of the original Access experts on the Excel and Access, LLC staff (Helen Feddema – Manhattan New York Lead Programmer and Trainer).

 

• Visit Helen Feddema’s Website for help in Excel, Access, Outlook and Word. She has a great site, she writes great books, Helen really knows her stuff. Helen is one of the original experts in the Microsoft Office applications, being an expert from the very first versions. Other members of our staff have written books on the Microsoft desktop productivity applications, Helen has over the decades written so many, check her out.

 



 

• Helen Feddema of Access Watch fame has released a new …… (Check out Helen Feddema on Microsoft Office, Word, Access, Excel Watch)

Helen Feddema is one of our Senior Access, Word, Outlook, and Excel & Developer, working out of the Manhattan New York office.

 

 

So many of our clients have had problems with Office 2016 that we have written numerous blog posts on it. A few of them are listed below.

• Should You Upgrade to Microsoft Excel 2016 or Wait for the Release of Excel 2019?
• Microsoft Excel 2013 to Excel 2016 for Windows: What’s the Difference?
• Excel 2016 for Mac Versus Excel 2011 for Mac: The Inside Scoop from Excel Experts

 
 

If you need help with any of the Microsoft applications, and if you would like to work one-on-one with Helen, either in terms of Excel, Access, Word or Outlook consulting or training, contact us today and we will provide service that exceeds your expectations.