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?
ouestqueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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 Access MVP)Database Architect / Systems AnalystCommented:
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)President / OwnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
<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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.