Upgrading Access 97 Application to Access 2010

Those who know me know that I will not let anything get in the way of moving to new technologies such as Office 2010, Windows 7, IE9 etc. Not even a “business critical” Access 97 Application. Of course there are virtualization ways to get around this but for me…well those are a last resort to deal with compatibility. Ideally I want no vestige of IE6, Windows XP, Access 97 etc in my environment, virtualized or not.

So the first thing when I load this database I get this error: “Your Microsoft Access database or project contains a missing or broken reference to the file ‘dao2535.tlb’ version 3.5

In fact i got it almost over 100 times it seemed. I held down the enter key and it flash by rapidly each time ‘searching for referenced file…’ There didn’t seem to be anyway to stop this, even holding down Shift when opening the DB to open in design mode I got the same thing.

 

Eventually it loaded after I risked repetitive stress injury from non-stop hitting ENTER. Then it broke into the debugger:

 

 

I clicked the “Stop” button to stop execution. A table is created called “Conversion Errors” with details of all the problems:

I used Alt+F11 to go back to VBA Editor and then clicked Tools –> References. I deselected the Microsoft DAO 2.5/3.51 Compatibility Library.

 

Of course this library is the one that defines Recordset so what can I do that I removed it? Go find the old DAO 2.5/3.51 library? I didn’t have the file on hand. So I tried adding this:

So that got rid of the  “User-Defined type not defined”. This time I got a new error:

 

Followed by

Followed by

The database is hard coded to load data from the specific location and I was testing on an isolated machine. So to test it I used SUBST command to simulate the P:\ drive

 

This at least got me now to a launch screen, but more errors

 

This time I opened up the DB in design mode by holding SHIFT down while launching the file then hit ALT+F11 to bring into VBA Editor. From the VBA menu I hit Debug –> Compile. This showed the user-defined type “database” as not recognized, so added the following library:

 

OK so now when I compile I’m not getting any unknown references. But I get error:

 

 

Well this is because the renamed the control to a friendly name without updating the code. It has been changed from Combo37 to Project_Task_Number. Probably never noticed this because you don’t see these errors unless you compile the DB. I can’t remember far enough back if Access 97 had a compile option…

So then I found a lot more sort of errors like this, stuff just from not compiling, leaving old code around etc after forms had changed. These wouldn’t have affected application execution those as these bits of code never got executed. Then presto:

I was getting somewhere now!

I hit Ctrl+Break and then chose Debug to find out where this query was. I got it added myself to the table…and

YAY! Application is UP! Tested the functions, and they’re all working no errors!

I did also want to replace all the fonts from “Time Romans” to “Segoe UI” to make it look a little less Windows 3.1 like and a little more  WPF-like but with 100+ forms and I couldn’t see any Search & Replace options for Font names I just left it as is.

Microsoft also has a number of tools that may help with this process including:

Access 2.0 Converter for Access 2003

if you’re unfortunate to have Win3.1 and Access 2.0 still running…

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=91C944F8-F3AB-41E9-902A-2255A66E1C4A

Access 2003 Conversion Toolkit

To assist in migrating old databases from Access 97 to Access 2003 format

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=2E861E76-5D89-450A-B977-980A9841111E&displaylang=en

Migrating from Access 2003 to Access 2010

http://office.microsoft.com/en-us/access-help/migrating-to-access-2010-from-access-2003-HA101886851.aspx

Office 2010 Tool: Office Environment Assessment Tool

Available from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ea33f615-188d-464e-bc64-3ab06aa43961&displaylang=en

• Currently installed applications and installation environment.
• Add-ins currently in use by Office clients.
• Programs that are not registered as add-ins but still interact with Office programs.
• Report: Environmental assessment (potential upgrade issues).
• Report: Add-ins assessment—list of third party programs and information about the compatibility of those programs with Microsoft Office 2010.
• Supported Operating Systems: Windows 7;Windows Server 2003;Windows Server 2008;Windows Vista;Windows XP Service Pack 2
• This tool collects information for the following Office applications:
• Microsoft Office 97
• Microsoft Office 2000
• Microsoft Office XP
• Microsoft Office 2003
• 2007 Microsoft Office system
• Microsoft Office 2010
• In addition, you must have the following installed:
• Microsoft .NET Framework 2.0
• Microsoft Office Excel 2007 or Microsoft Excel 2010

Microsoft Office Code Compatibility Inspector

Available from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=23c8a7f6-88b3-48ef-9710-9742340562c0&displaylang=en

The Microsoft Office Code Compatibility Inspector for is designed to help an enterprise or small business update Visual Basic for Applications (VBA) and Visual Studio Tools for Office code so that it is compatible with Office 2010. The Inspector does not correct code; it inspects code, and then comments specific lines of code that reference items in the object model that have changed in some way or have been deprecated. (In VBA code, the tool also comments Declare statements for 64-bit compatibility.) You can then modify the code based on the comments, or use the links that are provided in the comments to view topics on the Web that are pertinent to a particular line of your code.

Again, it is up to you to modify the code if you determine that it needs to be modified.

The key features of the Office Code Compatibility Inspector include the following:

• It consists of four add-ins that load, one add-in per application, in Excel 2010, PowerPoint 2010, Word 2010, and Visual Studio 2008.
• It runs on a per-user or a per-document basis. For VBA, users open a document and run the tool from the Developer tab. For Visual Studio projects, the developer runs the tool from the Tools menu.
• It scans the projects in the current document or Visual Studio Solution by using a simple text search, looking for parentObject.Property combinations, and scans Declare statements for 64-bit compatibility (in VBA).
• It adds comments in the code where it finds other potential issues.
• It displays a summary and a detailed report of its findings after it completes its inspection.

You cannot use this tool to inspect code from Microsoft Outlook or Microsoft Access. However, you can use the tool to inspect Excel, PowerPoint, and Word code for interaction with the Outlook object model.

Office 2010 MIgration Planning Manager

Available from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=81D208F0-7ACE-499C-8975-728C648B8B43

This toolset includes the OMPM File Scanner (Offscan.exe), a command-line tool that scans files for conversion issues and stores the results in XML log files in a configurable location and, optionally, can bundle these log files into .cab files. The OMPM File Scanner performs two kinds of scans:

• A light scan that quickly identifies the Office documents on a user’s computer or network file system.
• A deep scan that you can perform on Office documents to crawl document properties that provide indicators of potential conversion issues.
• A macro scan that quickly identifies the count of potential issues in VBA code included in document macros, or from 64-bit Office 2010 changes. Macro scanning is available only for Microsoft Excel, PowerPoint, and Word files.

Important: Files from Microsoft Outlook, Microsoft FrontPage, Microsoft OneNote, and Microsoft InfoPath are not included in either the light or deep scan.

In addition it provides the following components:

• A set of utilities that you can use to import the XML log files that are generated by the OMPM File Scanner into one or more Microsoft SQL Server 2000, Microsoft SQL Server 2005, or Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2008, Microsoft SQL Server 2008 Express Edition, Microsoft SQL Server 2008 R2, or Microsoft SQL Server 2008 R2 Express Edition databases.
• A Microsoft Access 2010–based reporting solution that provides different reports for your analysis and enables you to define file sets for automated processing.
• The Office File Converter (OFC), which lets you do bulk conversions of specific Office files that are in version 97 through 2003 formats to the Office 2010 file formats.
• The Version Extraction Tool (VET), which lets you extract multiple saved versions of a single Word 97–2003 document to individualfiles in Word 2010.

 

If you are using 64-bits of Office: http://technet.microsoft.com/en-us/library/ee681792.aspx