|
|
Access Articles:
Using DAO with Microsoft® Access 2000 or
Higher
|
List
All Access Articles
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.
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
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 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.) Further
Help:
* Microsoft Knowledge Base
Article - 186311
|
Copyright Jamie
Czernik 2004 -
|
|
