ACC2000: Compile Error After You Convert Database with Old DAO Code

If you open a database that was created in an earlier version of Microsoft Access, and you try to run or to compile code that uses Data Access Objects (DAO), you may receive a compile error.

Versions of Access earlier than Access 2000 can successfully compile and run older syntax. For example, the object model for DAO in Microsoft Access 2.0 is significantly different from the object model for DAO in Access 1.0; however, DAO in Access 2.0 still allows the code from Access 1.0 to compile and run. You do not have to reference a type library. Microsoft Access 7.0 and 97 include the DAO 2.5/3.0 and DAO 2.5/3.5 compatibility type libraries. Access 7.0 and 97 use these libraries by default for converted Access 2.0 databases. Therefore, the older code from Access 1.0 still works in Access versions 7.0 and 97.

Microsoft Access 2000, however, has no compatibility type libraries. Therefore, DAO code that uses some of the older syntax may not run.

Update your code to the current DAO syntax. For more information about and examples of how to update legacy DAO code, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "DAO Object Library Compatibility" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Steps to Reproduce Behavior

1. On a computer running Access 97, open the sample database Northwind.mdb.
2. Create a new module called DAOTest.
3. Type the following procedure:

   Sub TestOpenRec()
      Dim dbs As Database
      Set dbs = CurrentDb
      Dim dyn As Dynaset
   
      Set dyn = dbs.CreateDynaset("Orders")
      MsgBox dyn.Fields.Count
   End Sub
   					

4. On the Tools menu, click References.
5. Make sure that "Microsoft DAO 3.5 Object Library" is not selected; then, select Microsoft DAO 2.5/3.5 Compatibility Library, and click OK.
6. Press CTRL+G to open the Debug window.
7. In the Debug window, type the following line, and then press ENTER:

   TestOpenRec
   						

   Note that no errors are returned and that the number 14 is displayed in the message box. This number indicates the number of fields in the Orders table.
8. Close the sample database Northwind.mdb, and then copy the Access 97 Northwind database that you just had open to another computer that is running Access 2000.
9. Open the Access 97 version of Northwind.mdb in Access 2000.
10. Press CTRL+G to open the Immediate window.
11. In the Immediate window, type the following line, and then press ENTER:

    TestOpenRec
    						

    Note that you receive the following error message:
    Compile Error: User-defined type not defined.
12. On the Tools menu, click References. Note that there is no compatibility library available.
    
    For the above DAO example to work in Access 2000, rewrite the procedure as in the following example that uses the object, Recordset, instead of the Access version 1.0 object, Dynaset:

    Sub TestOpenRec()
       Dim dbs As DAO.Database
       Set dbs = CurrentDb
       Dim rst As Recordset
    
       Set rst = dbs!Orders.OpenRecordset(dbOpenDynaset)
       MsgBox rst.Fields.Count
    End Sub