To every intermediate to advanced Access developer: Watch out! There's a major surprise waiting in Access 2000. As I see it, anyone who's already created an application in Access that involves some VBA code will open Access 2000, enter the same code they've always used, and it won't work. No explanation why, no suggestions on how to fix it, but just plain failure. Nice to know we're all headed for failure, isn't it?
Here's the problem: you create a new Access database, and import or create some tables. You create a bound form, and want to work with its recordset programmatically. So, as you've done for years, you enter this code in some event procedure:
Dim rst As Recordset
Set rst = Me.RecordsetClone
You compile your code, and everything's fine. You go to run your code, however, and you get a runtime error on the second line of code. What's wrong here? This code has worked in every version of Access since 2.0. Why does it fail now?
Congratulations! You've just been bit by the "we really can't decide if this product supports ADO or DAO" problem. When you create a new Access database in Access 2000, Access assumes you want to use ADO, and so it sets a reference to ADO 2.1 for you. When you declare rst as a recordset, you're telling Access that you want to create an ADO recordset. So far, so good. In the next line of code, you attempt to assign the RecordsetClone property of a form into your ADO recordset. You would assume that since Access included a reference to ADO 2.1 (and no reference to DAO 3.6), Access would retrieve an ADO recordset when you use the RecordsetClone property, right? Not so. In an .MDB file, the RecordsetClone property of a form is always a DAO recordset, even if you only have a reference to ADO in the Tools > References dialog. Therefore, when you write the code like you've always written it, Access tries to assign a DAO recordset into a variable that's expecting a reference to an ADO recordset, and you get a runtime error.
What about the new Recordset property of a form, you ask? This property, new in Access 2000, lets you set or retrieve the actual recordset used to fill the form (not a copy, which is what the RecordsetClone property returns). It lets you assign an open recordset to a form, after it's open, and this capability opens up a whole new way of using forms. So, you figure this is a new property, so it must return an ADO recordset as well, right? Wrong again. In an .MDB file, the Recordset property of a form returns a DAO recordset. (In an Access Data Project -- an .ADP file, using SQL Server or MSDE as its data source -- Access always does things the right way, and provides an ADO recordset when you request the RecordsetClone or Recordset property.)
How do you work around this problem? If you want to work with a form's recordset programmatically, in an .MDB file, you need to set a reference to the DAO library (use the Tools > References menu to add that reference). And now everything will work fine, right? Still wrong. Because you still have a reference to ADO, and it's most likely above the DAO reference in the Tools > References dialog, Access still thinks you want to create an ADO recordset when you dimension your variable. To completely avoid the problem, you must also disambiguate the reference, like this:
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
As a matter of fact, I've gotten in the habit (and I think you should, too) of always prefacing any DAO or ADO object reference with the appropriate library name. Not only does this make it completely clear what type of object you're creating, but it also makes your code run somewhat faster. For example, Access doesn't have to figure out what library it should use to create a Recordset object. If you supply the full name (DAO.Recordset) then Access knows, immediately, which type library is supplying the information.
What happens if you convert an existing Access application to Access 2000? In that case, you're safe. Access simply updates your reference to DAO to be the most current version, DAO 3.6, and leaves it at that. Your code should continue to work the way it did before. Once you've solved this problem, have fun digging into the issues involved with the form's Recordset property, and when it provides rows that can be edited (as opposed to being read-only). That's another can of worms, based on the differences between DAO and ADO recordsets.
And don't think that these are the only conversion/upgrade issues you'll run across. Access 2000 is far more stable than Access 95 was, but it's reminiscent of that version in many ways. On the other hand this one doesn't crash often. If you survived Access 95, you'll remember a different behavior with that particular flavor. Access 2000 is stable, reasonably zippy, and fun to use. It's just a bit confused about its true data access identity.