[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2012-03-15
6
Medium Priority
?
917 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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
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!

 
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 500 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 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