McAfee SECURE sites help keep you safe from identity theft, credit card fraud, spyware, spam, viruses and online scams
Categories Related products

Expert Zone

Print article

Compile error in Access: User-defined type not defined | Expert Zone

If you are used to programming in Access 97 then you may encounter a problem if you upgrade to Access 2000 or higher and you wish to continue to use the DAO object model in your VBA. If you try to run or compile your code you will probably be faced with the following error: "Compile error: User-defined type not defined" and you will notice that one of your DAO variables will be highlighted.



........................................................................................................................................................................................

Click on the link for more information about AccessFIX: AccessFIX will restore Access when you can't open database files.

........................................................................................................................................................................................

Fortunately this is easily remedied. The problem here is that your database does not have a reference set to the Microsoft DAO Object Library. With Access 97 this reference was already set by default, however Access 2000 and later versions have a reference set to the Microsoft ActiveX Data Objects Library instead. To set the necessary reference simply select Tools - References from the VBE code editor as shown in Figure 1.

Figure 1
Figure 1

If you scroll down the list of available libraries then you will eventually find the Microsoft DAO 3.6 Library as shown in Figure 2. (Version 3.6 is the newest version available). All you have to do is check the box next to this library and click on the OK button. You should now be able to compile your code by selecting Debug - Compile from the main menu.

Figure
Figure 2

Occasionally the problem might be that Access is unable to locate the reference, which will be indicated by the word "Missing" shown next to the reference location. If this is the case then uncheck the reference and close the references dialog. Next, open the references dialog again, locate the library from the list and re-check the reference that was missing.

While on the subject, it is also good practice to refer explicitly to your DAO variables with the term "DAO." as shown in the example function below, incase you decide to add ADO code to your database later on (this is known as disambiguation - and is shown in the following example).


'---------------------------------------------------------------------------------------
' Procedure : exampleDAOQuery
' DateTime : 31/01/2003
' Author : Jamie Czernik
' Purpose : Opens tblExample using the DAO model, returns the total
' number of records to this function. Safely destroys object variables
' to free up system resources.
'---------------------------------------------------------------------------------------

Function exampleDAOQuery() As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strQry As String

On Error GoTo exampleDAOQuery_Error

    Set dbs = CurrentDb()
   strQry = "SELECT Count(Column1) As numRecords from tblExample" 'SQL
    Set rst = dbs.OpenRecordset(strQry)
    With rst
        exampleDAOQuery = !numRecords 'return number of records to function
        .Close 'close the DAO recordset
    End With
   dbs.Close 'close the DAO database connection
    Set rst = Nothing 'free up system resources
    Set dbs = Nothing 'free up system resources

    On Error GoTo 0
   Exit Function

exampleDAOQuery_Error:

   MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbCritical

End Function


Finally, whether programming in Access 97, 2000 or later it is also recommended that you also explicitly close and destroy your DAO objects after use to free up system resources and prevent database bloat. This is accomplished using the "close" method and setting the object variable to "Nothing", shown at the end of the example function above.

Jamie Czernik BSc.
http://www.jamiessoftware.tk
(This article is provided "AS IS" with no warranties or guarantees.)

 



Jamie Czernik-Jamie Czernik
Jamie Czernik is the webmaster of Jamie’s Software providing downloads, articles and tips for MS Access developers.
 
He has developed a number of applications, including an image management package and a water & sewerage compliance program (utilitycompliance.com) for use in the US. He established Jamie’s Software over 4 years ago and has been helping Access developers with downloads, articles and tips ever since.
   
Please visit Jamie’s Software for more MS Access downloads, articles and tips. 




Author website: www.jamiessoftware.tk/

Copyright ©2000-2013 Cimaware Software. All Rights Reserved


DMCA.com