Access 97 to Access 2000/2002 conversion issues/problems/bugs

Access 2.0 and Access 97 to Access 2000/2002 conversion problems.

(Updated 2010-09-27)

These are the problems I've encountered in VBA code as I convert databases from Access 97 to Access 2000. I've also mentioned some Access 2.0 problems. Note that some of the syntax comes from Access 2.0. I'm sure there are lots of other quirks which, because I didn't do things that way, that I've missed.

Make sure you do a Compile and Save All in the original version of Access to ensure there are no problems.

If Access 97 Ctrl+G to open the debug window >> Debug >> Compile and Save All Modules. If Acess 2000 or newer then Ctrl+G to open the immediate window >> Debug >> Compile. Fix any errors which pop up..

Msg: You can't use the ApplyFilter action on this window.
Replace
DoCmd.Requery
with
Me.Requery

Msg: The command or action 'SaveRecord' isn't available now.
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, , A_MENU_VER20
(This is the Access 2.0 format of the below command.)
or
DoCmd.RunCommand acCmdSaveRecord
by themselves no longer work as if the record doesn't need to be saved. You can add the following line:
if me.dirty then
before the above lines.

Or just replace the above lines, especially the first one as it takes a second or two to figure out what's all happening, with
If Me.Dirty = True Then _
Me.Dirty = False
This could fit on one line but I choose not to.

Error 3420 - Object invalid or no longer set.

In your code you are opening the database object to do things such as open a recordset.

Set MyDB = DBEngine.Workspaces(0).Databases(0)
or
Set MyDB = CurrentDB.
.... some VBA code here
MyDB.Close

You'd been taught that everything you open you must close. Except if its the current database object. You could get away with that in Access 97 but not Access 2000 and newer. So remove the MyDB.Close line from your code.

API Calls

API calls must be moved from 16 bit to 32 bit calls. Most of the time it's just a matter of replacing integer with long. You might come across some DLLs which don't convert. However searching at google.com or groups.google.com should clear up those problems. Please email me with any such so I can add them to this page. Also see ACC: How to Convert API Calls from 16-bit to 32-bit - 147781

DAO 2.x/3.x Compatibility Library

Thanks to Brendan Reynolds for pointing this out. The DAO 2.x/3.x Compatibility Library reference is set when you convert from A2.0 to A97. However it won't work in newer versions of Access as Microsoft never created the DAO 2.x/3.6 Compatibility Library. Access 2000 and newer use DAO 3.6. (Yes, it is Jet 4.0. No idea why DAO is 3.6.) Old DAO 2.x code will have to be updated to use DAO 3.x syntax. That's not technically difficult, but it is something of a chore. You also have no idea of how many lines of code need updating until you're done.

To fix the code remove the DAO 2.x/3.x Compatibility Library reference and click on Compile and Save All or Compile depending on your version of Access.

The biggest problem I've found was code which used rs.fieldname which had to be replaced with rs!fieldname. Or rs.Fields("fieldname"). Brendan also mentions "The BeginTrans, CommitTrans and Rollback methods are now methods of the Workspace object instead of the Database object. " and "The third problem has something do with the way you open or assign QueryDefs."

Microsoft Links

Microsoft Office Access 2003 Conversion Toolkit

[ Access | Main ]

Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony

Contact

Tony's Blog

Table of Contents