Difference between "As Recordset" and "As DAO.Recordset" 3

[Greggie]

There is much discussion/debate about whether DAO or ADO is best for a multi-user split database. Basically, before I get into that subject too much, I need to clarify what I'm using. I have been declaring recordsets as "As Recordset" rather that "As ADODB.Recordset" or "DAO.Recordset" (and to clarify, I'm using MS-Access 2007).

If I want to use DAO, should I be using "As DAO.Recordset" or is "As Recordset" the same?

The reason I ask is that I want to know if i should be recoding to use DAO. (My front end and back end are both Access 2007).

 

[dhookom]

You should try to be explicit with your recordset type. I believe the order the libraries are listed in the Tools->References will determine which type of recordset is created.

I have almost always used DAO but that might be because I have been using Access since before ADO.

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

If you are referencing both the ADODB and DAO libraries, and you declare:

Dim rs As Recordset

Access decides which type of Recordset you mean based on which library is referenced first in the list.

The general rule of thumb is that when using an Access database backend, DAO is more efficient than ADO (but I'm not sure if that still holds true for the new 2007 format). When using any other type of backend (for example, SQL Server), ADO is preferred.

There are other factors to consider, however. If you plan to some day upgrade your Access backend to SQL Server, it's probably best to just start off with ADO. I tend to use ADO even with an MDB back end just because I find it more flexible, and it is easier to remember how to use one library than two different but very similar ones. For most operations you will not notice a difference using one over the other.

 

[lameid]

What JoeAtWork said.

I, like Duane, tend to use DAO because I've used Access since before there was ADO.

One more thought if you are considering moving to SQL Server at a later date, do not convert to an ADP file, stick with the native Access format. You gain little and you will have to relearn how to do some things.

 

[Greggie]

Thanks everyone who has responded. Very much appreciated. I felt silly asking the question but I'm very happy you took the time to respond.

Also, from my research since I posted my question, DAO is the best when working with Access as the back end.