DAO vs ADO (1 Viewer)

[KitaYama]

I'm just looking at a database built by someone who's not working here anymore.
Going through VBA code, he's been using both DAO and ADO recordsets in different forms and modules. Some modules ADO, others DAO.

BE is a SQL server. FE contains a lot of forms, queries, reports along with DSNless linked tables to the server. No local tables at all.

I did a search on ADO vs DAO, but there were a lot of discussions on this, making me more confused.
While some developers say in Access it's better and faster to use DAO, there were also others saying accessing a SQL server MUST ALWAYS be a ADO.

I'd appreciate hearing your side of story. Which one do you prefer to use and when one should be preferred over the other.
Any link to a reliable discussion suits me fine too.

Thanks.

 

[June7]

DAO is Access default.

DAO can be used to open recordset of linked SQLServer table.

I think ADO is used to manipulate properties of query object, such as Parameters, regardless of source table.

Use ADO for connections to Excel (and Excel VBA uses ADO for connecting to Access) as well as text files.

 

[The_Doc_Man]

Historically, Microsoft originally favored DAO but there was a time when they were starting to emphasize ADP projects which required ADO because it was more suitable for web-like environments. Eventually, ADP fell flat and DAO became preferred again, but for a while the default installation order was to put ADO first even after ADP fell out of favor.

ADO has some interesting abilities that DAO does not, such as memory-based recordsets. However, DAO generally works better for Access, SQL Server, and a few other DB engines.

The best bet is if you are working with something, read its documentation on interfacing to see what it prefers. Odds greatly favor DAO but this is not an exclusive favoring.

 

[ADIGA88]

ADO has some interesting abilities that DAO does not, such as memory-based recordsets.

Thank you for this info, I had a problem with access in regards to separating dependency this is very helpful.

 

[sonic8]

ADO has some interesting abilities that DAO does not, such as memory-based recordsets. However, DAO generally works better for Access, SQL Server, and a few other DB engines.

No, ADO is much better suited for SQL Server clients than DAO, particularly after the removal of ODBC-Direct from DAO.
However, context matters. If you are using an Access frontend with linked ODBC tables, processing will be piped through the local ACE-Engine, which is closely linked to the then preferred DAO library.

 

[sonic8]

Going through VBA code, he's been using both DAO and ADO recordsets in different forms and modules. Some modules ADO, others DAO.

In general, that's perfectly fine. - Use the tool most suitable for the task at hand.
If you are working close to bound Access forms or with other parts of Access depending on the ACE-Engine, DAO is indeed the preferred and in many cases only supported approach. When working directly with data in SQL Server, ADO is usually the better choice.

 

[CJ_London]

Be aware although you can assign an ADO recordset to a form and bind the controls, some standard form functionality won’t work because the form uses DAO. In particular sorting and filtering. But nothing to stop you creating your own sort and filter functions

Also ADO filtering is a bit more limiting for Like and placing of wild cards - you can’t use a * in the middle for example and there is no Between.

but they have plenty of uses. For example connect to db, extract a recordset, disconnect, make changes then run update/insert queries, but you do have more work to do around identifying if the record has been changed by another user whilst the records are disconnected

Another example is to use disconnected recordsets to enable editing of a crosstab or union query - again with code to manage updates/inserts

 

[KitaYama]

you can’t use a * in the middle for example and there is no Between.

Yes, I had a hard time with the difference between * & %

 

[CJ_London]

Not quite what I meant - see this link https://devguru.com/content/technologies/ado/recordset-filter.html

so you can have
like '*something*'
like 'something*'

but not
like '*something'
like 'some*thing'

 

[KitaYama]

Not quite what I meant - see this link https://devguru.com/content/technologies/ado/recordset-filter.html

so you can have
like '*something*'
like 'something*'

but not
like '*something'
like 'some*thing'

Thanks

 

[The_Doc_Man]

No, ADO is much better suited for SQL Server clients than DAO, particularly after the removal of ODBC-Direct from DAO.
However, context matters. If you are using an Access frontend with linked ODBC tables, processing will be piped through the local ACE-Engine, which is closely linked to the then preferred DAO library.

Thanks for the clarification. Since I had never used ODBC-direct, I was not aware of this fine point. All of my Access experience was DAO-based.

 

[KitaYama]

If you are using an Access frontend with linked ODBC tables, processing will be piped through the local ACE-Engine, which is closely linked to the then preferred DAO library.

Just to be sure I've not misunderstood you.
Do you mean as far as I'm using linked tables in Access and I don't work directly with data on the server, it really doesn't matter which one I use?
And both (ADO & DAO) are the same?

Thanks.

 

[sonic8]

Do you mean as far as I'm using linked tables in Access and I don't work directly with data on the server, it really doesn't matter which one I use?
And both (ADO & DAO) are the same?

No, not quite.
ADO and DAO are definitely not the same and there are significant differences in their handling/behavior.

As a general rule:
If you work with something that is "in Access", e.g. forms, reports, linked tables, use DAO.
If you work with data on the SQL Server directly, then use ADO.

There are exceptions to the above rule. E.g., you can also use DAO with Pass-Through-Queries to execute queries directly on the SQL Server. However, ADO is much more versatile and convenient for that purpose. If you want to display the results of such a query in a report however, you must use DAO. If you want to display the data from such a query in a form you also can use ADO, but need to be aware of some limitations. - You see, it all blends into rather large grey area.

I suggest you stick to the two-sentence-rule above, unless you run into a situation where that does not work (well).

 

[KitaYama]

No, not quite.
ADO and DAO are definitely not the same and there are significant differences in their handling/behavior.

As a general rule:
If you work with something that is "in Access", e.g. forms, reports, linked tables, use DAO.
If you work with data on the SQL Server directly, then use ADO.

There are exceptions to the above rule. E.g., you can also use DAO with Pass-Through-Queries to execute queries directly on the SQL Server. However, ADO is much more versatile and convenient for that purpose. If you want to display the results of such a query in a report however, you must use DAO. If you want to display the data from such a query in a form you also can use ADO, but need to be aware of some limitations. - You see, it all blends into rather large grey area.

I suggest you stick to the two-sentence-rule above, unless you run into a situation where that does not work (well).

Million thanks for clarification.

 

[Pat Hartman]

For ODBC BE's I always use DAO in VBA UNLESS it seems to be slow, then I try to optimize the query and failing that, investigate using ADO. Also, due to the ADO/DAO fiasco of A2K or A2002 (it's all a blur), I ALWAYS disambiguate my DAO objects just in case I ever have to add in some ADO code. It is so much easier to remember to do it in your Dim's than to have to find all the things that need changing after the fact.