Special Edition Using Microsoft Office Access 2003

In the Real World The Access Upgrade Blues

Previous upgrades to Access (except the 1.0 to 1.1 transition) undoubtedly increased the market for mood-altering substances both legal and illegal among Access developers. Migrating from Access 97 to Access 2003 isn't a piece of cake. The larger and more complex your Access application, the more taxing the process. Access Basic detritus from Version 2.0 or earlier plagues the conversion of mature Access applications. (Many developers didn't upgrade Access 2.0 applications to Access 95 because of performance issues.) Thus, the more history behind your code, the greater the chance that it breaks when upgrading.

The obvious temptation is not to upgrade any Access 9x application to Access 2002. This approach is viable only for applications you distribute as self-contained runtime versions generated by the Office 9x Developer Edition's Setup Wizard. Access 97 runtime versions install roughly 50MB of Access 97, Jet 3.51, DAO 3.51, and other obsolete dependency files on Office 2003 users' PCs. The Access 97 runtime baggage probably won't be a problem for today's desktop PCs, but road warriors' older laptops often have much less available disk space than modern desktops.

If you avoid temptation and bite the upgrade bullet, take the opportunity to optimize your VBA code. Make sure that the first line in every module is Option Explicit, substitute variable declarations for explicit references to objects used more than once in your code, and minimize use of the Variant data type. Add .Value instead of using the default property value of Access and Jet objects. Search for all instances of CreateObject, which results in late binding, and replace the call with a referenced object variable declaration, such as Private objName As ObjectLibrary.ObjectName, for a class member to force early binding. When you complete the optimization process, your Access 2002 application might execute as quickly as its 16-bit Access 2.0 predecessor. (But don't count on it.)

After you've converted your application to Jet 4.0 and DAO 3.6, and then gone through an exhaustive testing process, your next decision is whether to move from DAO to ADO and ADOX 2.1. OLE DB, and ADO are Microsoft's current recommendations for database connectivity, but surfing the current ADO/ADOX versions demonstrates no significant performance improvement or other benefits that justify the time and effort required for conversion. ADO/ADOX 2.x don't offer full parity with DAO 3.6 for Jet databases, and many workarounds for missing DAO features are inelegant, at best.

Don't let the preceding admonition discourage you from adopting ADO for new Access projects. DAO 3.6 is Microsoft's last iteration of this venerable object model. Microsoft is directing virtually all of its data connectivity investment to improving and expanding the capabilities of OLE DB, ADO, and especially the .NET Framework's ADO.NET. You can expect upgrades to ADO on a less frequent basis than new Office or Visual Studio versions because of Microsoft's emphasis on .NET technologies.

Web-based applications require OLE DB and ADO to support scripting, DHTML, and XML; DAO is extra baggage for ADP. If you employ object-oriented programming methodology, your classes must use ADO to assure object compatibility across all Access application types. Take the time to learn OLE DB fundamentals and gain expertise in ADO. The ADO learning curve isn't as steep as you might think. Your investment will pay handsome dividends when a later version of Access adopts the .NET Framework's Common Language Runtime (CLR) as an alternative to VBA 6.0.

Finally, make the transition from Jet to SQL Server 2000 and its successors. The limitations of Jet 4.0 are here to stay. SQL Server 2000 delivers industrial-strength reliability and scalability to your Access 2003 applications.