Solved

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

Posted on 2012-03-15
6
911 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 48

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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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 58

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
Technology Partners: 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

691 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