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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q199064
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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.

MORE INFORMATION

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
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kberrmsg kbprb KB199064
©2004 Microsoft Corporation. All rights reserved.