Use of the hidden reference to DAO. (1 Viewer)

ChrisO · Dec 3, 2005

Use of the hidden reference to DAO.

Two functionally similar procedures: -

Code:

Option Explicit
Option Compare Text


Public Sub Case1()
    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tblMyTable", dbOpenDynaset)
    
    Do Until rst.EOF
        MsgBox rst!SomeText
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

End Sub


Public Sub Case2()
    
    With CurrentDb.OpenRecordset("tblMyTable", 2)
        Do Until .EOF
            MsgBox .Fields("SomeText")
            .MoveNext
        Loop
        .Close
    End With

End Sub

In case1 everything has been declared as DAO (something or other).
This incurs two penalties.
The first is obvious and minor, the code is longer.
The second is that it requires a reference to DAO.

In case2 the code is shorter and requires no reference to DAO.
It does not matter if references are or are not declared for DAO or ADO or at which priority they might occur.
It simply doesn’t matter.

What this boils down to is that, by writing more code to disambiguate the difference between DAO and ADO, our code has become dependent on references.

If we can avoid the disambiguation we also avoid reference problems and if we avoid reference problems our code becomes more portable.

Have fun testing and regards,
Chris.

modest · Apr 27, 2006

This is a must read:

I disagree with this practice. You are saying to ambiguate the code by not referencing what data type the variable is. That is WRONG.

In versions before Access 2000, the DAO object library was selected by default. This means by just declaring a variable "Dim rs As Recordset" it would automatically be selected as a DAO.Recordset.

Access 2000 and 2002 set ADO as the default object library (e.g. Dim rs As Recordset would be the same as Dim rs As ADODB.Recordset).

Access 2003 preselects both libraries. Here is the IMPORTANT thing. If you have the following code:
Dim db As Database
Dim rs As Recordset

Only DAO has a database object (ADO is without), but they both have Recordset objects. So what does this mean? There is an ambiguity issue here that may force Access to result in errors.

By DISAmbiguating the code, Access and programmers will know whihc object refers to which library. Doing this can also make your code run faster because Access doesn't have to examine the library list to figure out which library to use.

By ambiguating the code, you are STILL relying on a reference, only you are using the default reference, which may cause more problems then you realize. Not to mention, it is very important for us as programmers to know which variables we are using. There is a reason why we are not all using Variants instead of String or Long.

ChrisO · Apr 27, 2006

“You are saying to ambiguate the code by not referencing what data type the variable is.”

No, I’m not saying that at all. What I’m saying is don’t create or use a variable in the first place and if no variable is created then it will not need disambiguation.

When no variable is created then no reference is made to the library and when no reference is made to the library then it does not matter what’s in the library.

We can then use a non-declared pointer to the hidden DAO object with the line: -

With CurrentDb.OpenRecordset("tblMyTable", 2)

The hidden DAO object is available in all versions of Access on or after A2000, A97 doesn’t need it. For example, the above line of code will work in all >= A97 versions irrespective of declared references…it just doesn’t matter.

So the method is, don’t declare the variables and use With to create a pointer to the hidden DAO object.

Regards,
Chris.

Use of the hidden reference to DAO. (1 Viewer)

ChrisO

Registered User.

modest

Registered User.

ChrisO

Registered User.

Similar threads

Users who are viewing this thread