• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

Access/Excel VBA: Dim DAO.recordset vs Dim recordset

I have created and manipulated recordsets in Access 97 through 2010.
I have always done the following and have never had problems--> Dim rst as recordset

Nonetheless, I have seen some programmers use this --> Dim rst as DAO.recordset.

What is the difference between the two?
Should I start using DAO.recordset instead? If so why?
What are the advantages/disadvantages of using DAO.recordset.
Is the DAO really needed in Access VBA?
0
ouestque
Asked:
ouestque
4 Solutions
 
Martin LissRetired ProgrammerCommented:
The only difference it would make if there were another object with a recordset property. If there were VB might choose the wrong one.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
One difference is ... if you have a Reference to both ADO and DAO, then you will need to qualify the recordset object variable with the appropriate technology reference, other wise you are likely to run into problems.

And ... a couple of versions back (<A2003 I think), ADO was the Default Reference, and you had to add DAO. So, it's 'best practice' to qualify object variables such as Recordsets, QueryDefs, TableDefs and so on.  This way, there is *never* any confusion.

And, there are a couple of subtle situations - which escape me at the moment - wherein even if you only have DAO as the only Reference (no ADO), w/o the qualification, confusion results.

Of, you have another syntax when using DAO in particular.

With CurrentDB.OpenRecordset ("SomeTableOrQueryNameOrSQL", dbOpenDynaset)
     If .RecordCount = 0 Then
                 ' no records
                Exit Function ' maybe
      End If
      .MoveFirst
       ' other recordset ops
       ' more code
  End With

  Since CurrentDB only applies to DAO, there is no confusion, and also no need to dim a recordset object variable, or a database object variable for that matter.

Another variation

With CurrentDB
      ' some code here    
      With .OpenRecordset ("SomeTableOrQueryNameOrSQL", dbOpenDynaset)
              If .RecordCount = 0 Then
                 ' no records
                   Exit Function ' maybe
               End If
              .MoveFirst
               ' other recordset ops
               ' more code
       End With
      ' other code ...
  End With


mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Just to add a bit:

When you make a reference in code to something, VBA needs to figure out what it is.  If it's not something that it already knows, it will start working down the references list (tools/references).

For each reference listed, and in the order they are listed in, it will check if that object is part of the reference.

So as has been said above, the ADO lib and the DAO lib both contain a recordset object.

If you just use Recordset, then you have not explicitly indicated which lib that object can be found in so VBA will search the list.  If ADO happens to be listed first in the list, then VBA will assume it's a ADO recordset.

When you use DAO.Recordset, your telling VBA specifially that this is a DAO recordset and to use the recordset object from that lib no matter what is before it in the reference list.

HTH,
Jim.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
<No Points wanted>

Remember you could even do this and it would most likely still work...
'No explicit Datatype set...
Dim rst
Set rst=.........

Here rst is a Variant.

In this case (as jim stated) Access has to really work hard to "figure out" what this is.
This takes time and resources.
To be fair, in a small app the performance may not even be worth worrying about.

But as the app grow and matures, you really want to tighten this up.

In the same way if you brought your car to a mechanic and simply said: "I have a Camry" (without specifying the year)
Sure the mechanic could go outside and look up the year form your windshield sticker, or look up the info form the VIN...
But again, this takes time...

;-)

JeffCoachman
0
 
Helen FeddemaCommented:
This is necessary because the ADO object model also has recordsets.  It is better to be specific, even though it may not always be needed.  Some other objects that appear in more than one object model are:

Selection (Word, Excel)
Folder (Outlook 2007+, FileSystemObject)
0
 
Helen FeddemaCommented:
Selection is in the Outlook object model too.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now