Upgrading from Access 97 to 2003

December 2005: We have to admit that we've done all our Access development programming up until now using Access 97. We purchased Office 2000 back at the turn of the century but were unimpressed with the changes to Access. It was slower, the files were bigger, the help was useless, and the VBA developer interface was less efficient to use. If a client had a later version of Office, we'd still develop the application in 97 and then just convert at the last minute to Access 2000 before shipping, which fortunately runs on all later versions. We chuckled when we heard how users with Access 2002 were getting problems like duplicate autonumber fields.

Recently a major client decided to install Office 2003 on all its workstations and we decided to get with it and upgrade ourselves. This client's particular application made OLE calls to Word and Excel and so was less easy to adapt to the 97-2000 upgrade path.

Well, after a nighmare experience of upgrading, and lots more grey hairs, we'd like to share our findings to help others. Here are the links we found most helpful.

Useful links

• Read this first: Converting from Access 97. Allen Browne's excellent site is full of tips and good explanations of the things you need to do including Flaws in MS Access.
• Microsoft Access Performance FAQ by Tony Toews at Granite Consulting.
• How To Avoid An Access 2003 Sandbox Storm by Garry Robinson.
• Access Reference Problems by Doug Steele.
• Make sure you install the Jet 4.0 Service Pack.
• Read BUG: Slower performance on linked tables in Access 2000.
• The behavior of the Application Title and the Msgbox() function in Microsoft Access.

Problem with collections

One interesting and related error that cropped up was in relation to storing text values in a collection. We use collections extensively as they are so handy. The following code works fine in Access 97 but failed with a run-time error in 2003:

Public Function MyClients
    Dim colClients As Collection
    Dim varID As Variant
    Dim sClientID As String
 
    ' Get a collection of ClientID's and work through them
    Set colClients = GetUserList
    For Each varID In colClients
        sClientID = CStr(varID)
    ' ...
    Next
End Function
 
Public Function GetUserList() As Collection
' Returns a collection of ClientIDs or `Nothing' if none found
    Dim col As New Collection
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sQry As String
    
    Set db = CurrentDb()
    sQry = "SELECT ClientID FROM tblClients ORDER BY ClientID;"
    Set rs = db.OpenRecordset(sQry, dbOpenForwardOnly, dbReadOnly)
    Do Until rs.EOF
        col.Add rs!ClientID '' <= OK IN ACC97, NOT 2003
        '' FIX WITH: col.Add CStr(rs!ClientID)
        rs.MoveNext
    Loop
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Set GetUserList = col
End Function

The field ClientID has data type Text. The collection `col' was OK inside the function GetUserList, but when passed back to the calling function it was full of invalid items. The count `colClients.Count' shows correctly, but trying to access any item in it caused an error. It was fixed by adding CStr().

Comment

We remember how exciting it was to install Access 97. It was a real improvement over what we had before and everything was better. Have we had any improvement in the last 8 years? Hardly. As Allen Browne says, "Most of the new features in Access 2000, 2002 and 2003 do not work properly, hinder productivity, degrade performance, or are of no use if you store your data in Access tables and use English."

We still haven't found a development tool that beats the Access/VBA programming environment for fast development of good, reliable user-interfaces. We're very disappointed that we get the honour of spending more money to purchase something that works worse than its 8-year-old, 3-versions-ago predecessor.

Good Reference Book

We found the Access Cookbook, Second Edition by Getz, Litwin and Baron, O'Reilly, 2004, ISBN 0-596-00678-0 to be especially useful in dealing with Access 2003.

Contact us

Comments or questions? Please send us an email or visit our Contact Page.   

This page last updated 13 May 2006