Mile-O
Back once again...
- Local time
- Today, 10:52
- Joined
- Dec 10, 2002
- Messages
- 11,316
Summary: Reasons and resolutions for “User-defined type not found” error when converting from Access 97 to Access 2000 or greater.
When Microsoft released Access 2000 it marked a huge change from Access 97 – it introduced or improved upon its components such as Data Access Pages, Access Projects, and increased security.
One major change – facilitated by Microsoft’s change in direction - was the data access method. Databases at the time used Data Access Objects (DAO), a programming method provided by the Microsoft Jet database engine for accessing and manipulating the information held in databases. Microsoft’s direction ensured that DAO was replaced by ActiveX Data Objects (ADO), an improved method that went further than DAO’s ability to manipulate ODBC (Object Database Connectivity) relational databases to allowing further data manipulation with non-relational OLEDB.
When using the DAO method when coding with VBA in Access 97 a typical piece of code would have been to dimension explicit object variables for both the database and recordset that the programmer intended to access or manipulate. Such code would have looked like this:
As already mentioned, ADO came to be the default data access method with Access 2000 although backwards compatibility allowed users to still reference DAO. The reference to DAO, however, would have to be ensured by the programmer. As it currently stands, running the above code in a database configured for ADO use only will return the following error message:
Compile Error:
User-defined type not defined
To ensure that DAO is used within an Access 2000 (or greater) database you need to:
This, however, only ensures that the database has a reference to DAO and any attempt to run the code above will not immediately result in an error because there is no Database object in ADO so it is evaluated as a DAO keyword, and the Recordset object is present in both DAO and ADO but ADO has the higher priority in the References collection so the Recordset object is evaluated as being ADO, meaning that the code is now interpreted thusly:
There are two solutions to this problem. The first is to remove the reference to the ADO library from the Access database so that it is not in scope. To do this:
The Access 2000 (or greater) database is now configured for DAO use.
The second solution is to disambiguate the data access methods. This is simply done by stating in the object declarations which method you are intending these objects to use:
Other Information
When Microsoft released Access 2000 it marked a huge change from Access 97 – it introduced or improved upon its components such as Data Access Pages, Access Projects, and increased security.
One major change – facilitated by Microsoft’s change in direction - was the data access method. Databases at the time used Data Access Objects (DAO), a programming method provided by the Microsoft Jet database engine for accessing and manipulating the information held in databases. Microsoft’s direction ensured that DAO was replaced by ActiveX Data Objects (ADO), an improved method that went further than DAO’s ability to manipulate ODBC (Object Database Connectivity) relational databases to allowing further data manipulation with non-relational OLEDB.
When using the DAO method when coding with VBA in Access 97 a typical piece of code would have been to dimension explicit object variables for both the database and recordset that the programmer intended to access or manipulate. Such code would have looked like this:
Code:
Dim db As Database ' DAO
Dim rs As Recordset ' DAOAs already mentioned, ADO came to be the default data access method with Access 2000 although backwards compatibility allowed users to still reference DAO. The reference to DAO, however, would have to be ensured by the programmer. As it currently stands, running the above code in a database configured for ADO use only will return the following error message:
Compile Error:
User-defined type not defined
To ensure that DAO is used within an Access 2000 (or greater) database you need to:
- open a module;
- select Tools -> References from the menu;
- find and check Microsoft DAO 3.x Object Library.
This, however, only ensures that the database has a reference to DAO and any attempt to run the code above will not immediately result in an error because there is no Database object in ADO so it is evaluated as a DAO keyword, and the Recordset object is present in both DAO and ADO but ADO has the higher priority in the References collection so the Recordset object is evaluated as being ADO, meaning that the code is now interpreted thusly:
Code:
Dim db As Database ' DAO
Dim rs As Recordset ' ADOThere are two solutions to this problem. The first is to remove the reference to the ADO library from the Access database so that it is not in scope. To do this:
- open a module;
- select Tools -> References from the menu;
- uncheck Microsoft ActiveX Data Objects 2.x Library.
The Access 2000 (or greater) database is now configured for DAO use.
The second solution is to disambiguate the data access methods. This is simply done by stating in the object declarations which method you are intending these objects to use:
Code:
Dim db As DAO.Database ' DAO
Dim rs As DAO.Recordset ' DAOOther Information
- The actual cause of this error is because an object does not exist (has not been referenced);
- It should be noted that when disambiguating with ADO the term ADODB is used instead. A typical ADO example would be:
Code:Dim cn As ADODB.Connection ' ADO Dim rs As ADODB.Recordset ' ADO - It is perfectly okay to have references to both DAO and ADO in the same database – that which has the higher priority as defined in the references collection is immediately assumed when defining objects that both have a similar instance of (i.e. Recordset).
