DAO vs ADO

[aziz rasul]

Is there any advantage in switching from using DAO to ADO and can you uses both within the same event procedure\module?

 

[The_Doc_Man]

can you uses both within the same event procedure\module?

yes

Is there any advantage in switching from using DAO to ADO

depends. Pat Hartman says that the newest versions of Access have switched BACK to DAO for a lot of things because ADO didn't do everything that DAO did. Therefore, I would suggest that this answer depends strongly on whether you need to interact with anything else that uses your application's data. I.e. Active Data Page stuff probably wants ADO. But if you aren't using that feature, and if you don't have anything else that needs to interact at that particular level, I'd consider it more important to use one and ONLY one of those two, then STICK to it. Matter of programming consistency, which isn't ALWAYS good, but often is useful. And it makes the references easier.

One last suggestion: If you decide you want to mix references, decide which one has priority, because if the same object is named in both DAO and ADO, whichever one is highest in your reference list wins the race. Unless you explicitly qualify EVERY item that could appear in either library. If you intend to be "loose" with definitions, beware the consequences of an unqualified reference to databases or recordsets.

 

[aziz rasul]

Thanks for that. I can confidently carry on with DAO knowing that I'm not missing anything by using ADO.

 

[WindSailor]

Here is a link for more info on DAO and what can be expected in the future.

http://www.access-programmers.co.uk/forums/showthread.php?t=74485&highlight=drops+jet

 

[aziz rasul]

Thanks WindSailer.

 

[Pat Hartman]

I posted a reply to this topic - http://www.access-programmers.co.uk/forums/showthread.php?p=346341#post346341 - that you might be interested in. I'll be talking to Clint tonight and again in January. If you have anything you'd like me to communicate to him, post it here ASAP or I can send it to him later and perhaps he will address the issue in January. He has been reluctant to tell us too much about Microsofts direction with Access but he did tell me last month that Microsoft swiched the default library back to DAO in A2003 due to overwhelming response from customers regarding the problems that having ADO as the default caused.

 

[mresann]

One last suggestion: If you decide you want to mix references, decide which one has priority, because if the same object is named in both DAO and ADO, whichever one is highest in your reference list wins the race.

I would go so far as to explicitely reference EVERY object used in VBA. A little extra typing in the beginning saves hours of deciphering in the future.

 

[SteveA]

IMHO - DAO is only still around to appease the many users of Access that have been using Access since the 90's. There are many performance issues that are not well documented, and it's transactional support is quite limited in comparison to ADO.

ADO is most definitely the most portable standard - eg .Net and VB6 use ADO (different versions). If you intend to integrate with clustered SQL servers, I would strongly recommend you familiarise yourself with ADO. ADO 2.7 and above provide strong support for clustered SQL servers, which is quite common in larger organisations.

The main issue that most people have with DAO/ADO is that many objects between the two libraries share names. With most coding examples and the Northwind Database itself, fully qualified names are rarely used. By not explicitly defining the library they are using, their code suffers when attempting to move between technologies.

I went through this experience back when I upgraded from Access 97 and Access 2000. Since then I have never looked back. ADO has not caused me any issues and has built a skillset that has been readily transferrable to other applications outside the Office suite.

I have also learnt (the hard way of course) that you should always explicitly reference your objects and never depend on default values or default behaviours. These have been known to change between versions and can often lead to defects that are very hard to identify and resolve.

Anyway, just a humble thought on a quiet Monday morning.

Cheers everyone
Steve