Solved

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

Posted on 2012-03-15
6
871 Views
Last Modified: 2012-03-19
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
Comment
Question by:ouestque
6 Comments
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 125 total points
ID: 37727604
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 125 total points
ID: 37727839
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 37728847
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37729427
<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
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 125 total points
ID: 37730676
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37730683
Selection is in the Outlook object model too.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now