Upgrading to a New Office Version

Some Office upgrades have included major changes to Access; others have made only a few insignificant changes (though of course, a change that is highly significant to one person may be insignificant to another). From my point of view, the upgrade from Office 97 to Office 2000 was significant, and Access gained several highly useful new features with Office XP. Office 2003, however, hardly changed Access at all, though Outlook had major enhancements that could be significant if an application works with Outlook components. The table below lists the more significant changes to Access in Office 2000, Office XP, and Office 2003.

Office Version	Changes from Previous Version
2000	Objects Bar replaces tabbed database window
	Database object groups
	New object shortcuts
	Name AutoCorrect (this is more a problem than a welcome new feature!)
	Print Relationships Diagram
	ADO object library
	Grouping of form and report controls
	Conditional formatting of form controls
	Snapshot format for distributing reports
	Data Access Pages
	Personalized menus (an Office-wide feature)
	Compact on close
	Convert to Access 97 format
	Projects for working with SQL Server
	Exchange/Outlook Wizard for importing or linking Outlook data (has serious limitations)
XP (2002)	Printer object
	FileDialog object (this is an Office-wide feature)
	Enhancements to Data Access Pages
	Enhancements to PivotTables
	PivotCharts
	Multiple Undo/Redo
	Ask a Question box
2003	Object dependencies
	Form and report error checking
	Propagating field properties
	Smart tags
	Database backup
	XML support
	Multifield sorting in controls
	Enhanced fonts and Help in SQL view

Upgrading an Application to a New Database Format

Many of the new features in any Access version are primarily of interest to people who work in Access databases in Design view (both end users and developers), rather than people who enter and edit data in Access databases. If you (and your client) are considering whether to upgrade an application to the next version of Access, three questions need to be asked:

• Does the new version have a feature that will significantly enhance the application with new functionality?

• Does the new version have a feature that will make the application easier to use or more efficient?

• Will all the people working on the database at the client’s site be upgraded to the new Office version?

If the answer to all these questions is “No,” then it’s not very likely that the client will want to upgrade to the new version of Office, at least not for Access-related reasons. You (as the developer) may want the Print Relationships Diagram in Access 2000, or the FileDialog object in Access XP, or object dependencies in Access 2003—but these features don’t mean a thing to the client who is looking at the overall functionality of the application for running a business or other enterprise, or the clerks who enter and modify data in the application.

On the other hand, if the client wants to display application data in attractive charts and is thrilled by the interactive features of PivotCharts, then an upgrade to Office XP is definitely called for, and if XML support is a welcome enhancement to the application, that calls for an upgrade to Office 2003.

As a developer, you should always upgrade to a new version of Access, so that you can use new features that make it easier to work with database objects in Design view, and also so you can create databases in the new format for clients who have also upgraded to the latest version of Office. However, in Office XP and Office 2003, you can still work with databases in Access 2000 format (with full read/write access) for clients who haven’t upgraded yet.

There are two ways to safely work with different Access database formats on one computer:

• Set up separate boot partitions for different Office versions (this is best when you need to be absolutely sure that every aspect of an application will work in a specific version of Access).

• Leave the database in Access 2000 format, and work with it in Access 2002 or 2003.

Setting Up Separate Boot Partitions for Different Office Versions

Even though you can work with Access 2000 databases in Access 2002 or 2003 (see the next section for details), this is not the exactly the same as working in Access 2000. When working in Office 2000, if your application includes Automation code to work with Word, Outlook, or Excel, you are working with Word 2000, Outlook 2000, or Excel 2000. If you use ActiveX controls in the application (such as the Calendar control or the DateTimePicker control), they are the versions of those controls that come with Office 2000. Because of these considerations, in order to make absolutely sure that your application will work in Office 2000, you need to test it in Office 2000, ideally with the same operating system the client is using.

I recommend installing each version of Office in a separate boot partition, using an application such as System Commander (see the V-Comm Web site, www.v-com.com/, for information on this product), or Boot Magic (included with PartitionMagic (see the PowerQuest Web site, www.powerquest.com/, for information on these products). System Commander and Boot Magic let you set up multiple boot partitions, with different operating systems if desired, and to have different versions of Office running in each partition. On different computers, I currently have the OS/Office combinations listed in the table below:

Operating System	Office Version
Windows XP	Office XP
Windows 2000	Office 2003
Windows ME	Office 2000
Windows XP	Office 2003
Windows 2000	Office XP

As new versions of Windows and Office come out I add new partitions, and as the last client using an older version upgrades, I reformat the older OS/Office partitions and install a newer OS/Office combination. I keep all the operating systems on Drive C and all data on Drive D. I install applications (the ones that give me a choice) on Drive E. This means that when I wipe a boot partition to install a new OS, I don’t lose any data, and I can work with the same documents in all partitions. I still have to install applications in each partition, but I install them to the same folder on the E drive (except for those that work differently with different operating systems, such as Norton SystemWorks), which can save a considerable amount of hard drive real estate.

Working with Access 2000 Databases in Higher Versions of Access

Office XP added a new Access format (the Access 2002 database format), but unlike earlier upgrades users aren’t required to use the new format when creating new databases, nor are users required to upgrade Access 2000 databases to the new format to have full read/write functionality, unless they want to take advantage of new features such as PivotCharts or the Printer object. The Access 2002 format is also available for use in Access 2003, where it is labeled the Access 2002 - 2003 format. You can work with Access 2000 format databases in both Access 2002 and 2003, as long as you don’t use any features not available in Access 2000 and take some care with references.

If you want to create a new database in Access 2000 format while running Access 2002 or 2003, you have to first select Access 2000 as the database format on the Advanced page of the Options dialog, as shown in Figure 8.1.

Figure 8.1

Apart from the obvious limitations of working with an older database format (you can’t use PivotCharts or enhanced PivotTables, or the Printer object), there are a few problems that can occur when an Access 2000 database is opened alternately in Access 2000 and a higher version of Access. This will be the case if your client is working in Access 2000, and you are working in Access 2002 (or 2003), or if the client’s workstations have different versions of Office installed. There is generally no problem with references when opening an Access 2000 database in a higher version of Access; references to the Office 2000 versions of Access, Office, and other Office components such as Word or Outlook will be upgraded from the v. 9.0 object libraries to the v. 10.0 or v. 11.0 object libraries.

However, downgrading doesn’t always work automatically. When an Access 2000 database is opened in Access 2002, then in Access 2000 again, the Access object library reference (and the Office object library reference, if you have one) will be downgraded from a higher version to v. 9.0 without problems, and the DAO 3.6 reference works for both Office versions, so it is OK, but references to other Office object libraries are generally not downgraded. That means that the database now has missing references to Word, Excel, or Outlook 10.0 or 11.0, and they will cause errors when the code is run.

The Test References sample database is an Access 2000 format database with references to the Outlook and Word object libraries. If you open it in Access 2000, its references are initially to the Office 2000 (v. 9.0) versions of various object libraries, as shown in Figure 8.2.

Figure 8.2

The database has two macros (mcrTestOutlookAutomationCode and mcrTestWordAutomationCode) that run functions that set up a reference to the Outlook and Word object libraries, respectively, and display information picked up from Word or Outlook in a message box. The two functions for testing references are:

 Option Compare Database Option Explicit 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 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 

When the references are set correctly, you will get the message boxes shown in Figures 8.3 and 8.4 when running these macros.

Figure 8.3

Figure 8.4

When the sample database is opened in Access 2002 (Office XP), its references are automatically upgraded to the Office XP (v. 10.0) versions, as shown in Figure 8.5.

Figure 8.5

To open the References dialog, select References from the Tools menu in the Visual Basic window.

Now for the real challenge: Close the sample Test References database, then reopen it in Access 2000 once more, and open the References dialog. Now you will see the Outlook and Word references labeled “MISSING,” as shown in Figure 8.6.

Figure 8.6

If you try to run the macros, you will get a compile error, “Can’t find project or library,” with the line that declares the appWord or appOutlook variable highlighted.

You could uncheck the references marked “MISSING” and reselect the v. 9.0 object libraries while the database is open in Access 2000. However, that would only be a temporary fix. If the database is opened in Access 2002 or 2003 and then again in Access 2000, the problem will recur.

Fortunately, there is a way to avoid this problem. You can avoid errors with missing references in Access 2000 databases that will be used in Access 2000 and higher versions by setting references to the Office 2000 (v. 9.0) object libraries from a database that has been opened in a higher version of Access. If you have different Office versions installed in different boot partitions, and you have installed both Office versions to locations accessible in all partitions, this is a straightforward process, because both sets of object libraries can be found on your computer (in different folders). Even if you don’t have a multiboot setup for different Office versions, you can still use this technique, by placing the Office 2000 versions of the Outlook, Word, and possibly other object library files in a folder on your computer (you may have to copy them from another computer or temporarily install Office 2000 to obtain these library files).

The table below lists the object library names and standard locations for the main Office components for Office 97 through Office 2003 (the file paths may be different on your computer, depending on your Windows version and the folder where you installed Office).

Application Version Office 2000	Object Library Name in References Dialog	File Name and Path
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 2000	Microsoft Office 9.0 Object Library	C:\Program Files\Microsoft Office\Office\mso9.dll
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 XP	Microsoft Office 10.0 Object Library	C:\Program Files\Common Files\Microsoft Shared\Office10\mso.dll
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 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	Microsoft Office 11.0 Object Library	C:\Program Files\Common Files\Microsoft Shared\Office11\mso.dll

The DAO and ADO object libraries are not tightly associated with specific Office versions, so you may have DAO 3.51 in Office 97 or Office 2000, or DAO 3.6 in Office 2000 or higher.

Start by copying the Office 2000 object libraries from the location listed in the table to an Office 2000 Object Libraries folder in a location that is accessible to all Office versions (mine is under the main Documents folder on Drive D). Open the Test References database in Access 2002 or 2003 (the process is exactly the same for Office XP and Office 2003). Initially, the References dialog shows v. 11.0 references, as shown in Figure 8.7.

Figure 8.7

There’s no need to set references to Access 9.0 or Office 9.0 in Access 2002 or 2003, because these references will be downgraded automatically when the database is opened in Access 2000. However, Word and Outlook references aren’t downgraded, so uncheck them, then click the Browse button and browse to the folder where the Office 2000 object libraries are located, as shown in Figure 8.8.

Figure 8.8

Select the Outlook object library and click Open, then click the Browse button again and repeat the process to select the Word object library. The new v. 9.0 references now appear at the bottom of the References dialog, as shown in Figure 8.9.

Figure 8.9

The next time the References dialog is opened, you’ll see the v. 11.0 Access object library reference near the top of the dialog, and the v. 9.0 references will be at the bottom of the list of checked references, as shown in Figure 8.10.

Figure 8.10

Now this Access 2000 database can be opened in Access 2000, Access 2002 or Access 2003, and the Office 2000 versions of the Word and Outlook objects libraries will be correctly referenced.