Use of the hidden reference to DAO.

Two functionally similar procedures: -


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.

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.

“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.