ACC2000: Compile Error After Converting Database with Old DAO Code

• Microsoft Access 2000

SYMPTOMS

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

CAUSE

Versions earlier than Microsoft Access 2000 provided ways to allow older syntax used in earlier versions to compile and run. For example, the object model for DAO in Microsoft Access 2.0 changed significantly from version 1.0; however, DAO in Access 2.0 would still allow the code from 1.0 to compile and run without the need to reference a type library. In Microsoft Access versions 7.0 and 97, the DAO 2.5/3.0 and DAO 2.5/3.5 compatibility type libraries were included. These libraries were used by default for converted Access 2.0 databases, and therefore still allowed the older legacy code originating from Access version 1.0 to work in Access versions 7.0 and 97.

Microsoft Access 2000, however, has no compatibility type libraries, and 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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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 the number 14 shows in the message box, indicating the number of fields in the Orders table.



8. Close Northwind.mdb and transfer it to another computer running Access 2000.



9. Open the Access 97 version of Northwind.mdb in Access 2000.



10. Press CTRL+G to bring up 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 

Additional query words: prb

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb