For best results: this site
requires that cookies be enabled for proper operation - see Legal Page for more info
Select Any of These
Compile Error After Converting Database w/ Old DAO Code in ACC2000
ID: Q199064
The information in this article applies to:
- Microsoft Access 2000
Moderate: Requires basic macro, coding, and
interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
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
