ADO examples for a beginner

Question

I am still green when it comes to using MS access. I am attempting to better my-self by using a recordset object instead of using DLookups. My plan is to use these for reading and manipulating data in MS access 2007.

From a lot of the reading I have done, I cannot tell whether to use ADO or DAO?

My access DB is used to store User account info for some of the systems we manage in our office. This will be used by only a few admins to maintain user information

I am having a rather hard time finding hard-and-fast examples or "best practices" that maintain any kind of .consistency. Also, any kind of documentation requires laborious reading of Why to use, and theory of use, before any kind of...

This is what to use.... kind of articles.

I anticipate that these tools will forever live in MS access, since that is all I have at my disposal at this office. In the future I would like to make data-connection strings more open to tools other than MS Access, like SQLite....

However MS Access is what I am driven to. I have been using DLookups to get data from a table, but that is starting to feel juvenile, and not good for retrieving more than 1 column of data from 1 particular row.

Can anyone directly I to some kind of idiots guide to ADO or DAO. I am not a programmer by trade, and would like to get some coding done soon. Everything in this office is To Be done: yesterday. I don't have the time to read long diatribes as to why one is better than the other.

DAO is native to Access and is to be preferred in most cases. — Fionnuala, Mar 26 '12 at 19:31
I agree with Remou. I use DAO for everything I can. I only use ADO when it does something I need that DAO can't do. — HK1, Mar 27 '12 at 01:44

score 1 · Answer 1 · answered Mar 26 '12 at 19:37

1

TheSavo

This is a very touchy topic. :) You will find mixed feelings on this subject.

To start with, you can refer to this old article (which was updated last year)

http://msdn.microsoft.com/en-us/library/ms810810.aspx

At the bottom it states that DAO is officially obsolete, and implies that there will never be a 64-bit version. I have not seen any other article after this date which confirms the fate of DAO.

Most programers when they start out, they start with DAO and then gradually move to ADO. At least that is what I have seen in different forums while answering questions. DAO at one point of time was shunned because of only one reason. The fear that it was going to be obsolete and finally replaced by ADO. But when DAO libraries were updated with Access 2007, the focus was back towards DAO. However it was too late. Most of the experienced developers had already become comfortable with ADO. Even today most of them while answering questions in forums still provide code using ADO. And I don't blame them as they have been using ADO for a long time now. IMHO, DAO should be unusable at some point in the near future before ADO is.

I remember discussing something similar with one of SO members sometime ago on whether to use ADO or DAO. I even discussed this with 7 different MVP's and if you are an MVP and a member of vbforums.com then you can visit this thread http://www.vbforums.com/group.php?do=discuss&discussionid=50&pp=10 to view the discussion. Till the time DAO works or is included with MS Access, you sure can use it. :) However the The consensus in the above discussion was NOT to use DAO.

If you would still like to use DAO then please read on :)

As of now both have their advantages and disadvantages. I would recommend you to have a look at this old link in MSDN which would give you a fair idea on the differences.

Topic: Choosing ADO or DAO for Working with Access Databases

Link: http://msdn.microsoft.com/en-us/library/aa164825%28v=office.10%29.aspx

HTH

Sid

answered Mar 26 '12 at 19:37

Siddharth Rout

147,039
17
206
250

It seems that ADO had a tenancy to be a better option than DAO. Can you provide some soup-to-nut examples of ADO. I see a lot of code snip-uts, but rarely is it decently commented? – TheSavo Mar 26 '12 at 19:41
Yes But like Remou suggested DAO is still a good option in most (I would change 'most' to 'certain') cases. – Siddharth Rout Mar 26 '12 at 19:45
Ok, i tried the Link to the `VBforums` and just created an account. It still won't let me view the link. It now says that I am not in the correct `Social Group`. – TheSavo Mar 26 '12 at 19:47
Yes, you need to be an MVP, Like I mentioned above :) But I have pretty much summed the discussion above :) – Siddharth Rout Mar 26 '12 at 19:48
1

Sorry, but ADO is only suitable if you are using ADPs. DAO is the preferred option for MS Access. – Fionnuala Mar 26 '12 at 19:51
Albert D Kallal: http://stackoverflow.com/questions/1039224/is-it-better-to-use-ado-or-dao-in-access-2007 – Fionnuala Mar 26 '12 at 19:53
A bunch of other Access MVPs http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/b45de632-a494-4951-8ebf-bdc80352f8b9 – Fionnuala Mar 26 '12 at 19:59
Yup, like I mentioned in the beginning of the post, `This is a very touchy topic. :) You will find mixed feelings on this subject.` – Siddharth Rout Mar 26 '12 at 20:02
You reference a site that cannot be reached and an out-of-date article. It says the DAO 3.6 is the last version. This is not true the default library for Access 2010 is Microsoft Office 14.0 Access Database Engine Object Library, which is a DAO library (http://msdn.microsoft.com/en-us/library/15s06t57.aspx) Microsoft has changed course on the topic a number of times and is now back with DAO. – Fionnuala Mar 26 '12 at 20:06
@Remou: September 2011 is not 'out of date' article :) But yes I agree with you on default library for Access 2010. I also agree with you in MS changing course on this topic and hence that may be the reason for mixed feelings. As for `TheSavo`, I have showed him both sides of the coin and I leave him with the choice he makes :) The 2nd link (which is out of date as mentioned in the post above) does put DAO in good light and shows clear advantages of DAO as compared to ADO :) – Siddharth Rout Mar 26 '12 at 20:11
1

@Siddartha Produce at leat one reachable reference from a Microsoft Access MVP that recommends ADO. Access developers use DAO, *for the most part*. – Fionnuala Mar 26 '12 at 20:14
1

let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9336/discussion-between-siddharth-rout-and-remou) – Siddharth Rout Mar 26 '12 at 20:18
4

I'm not an MVP and I don't purport to be but my opinion is that, unless you use an ADP, DAO should be your default choice and ADO should be used only when DAO cannot perform the desired task. I don't believe you can even use ADO for reports. You would probably have to use SQL pass through queries instead. – HK1 Mar 27 '12 at 01:55

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

As everyone is pointing out, there are various opinions on the subject, and Microsoft's own documentation flip-flopping around technologies does not help.

However, if you're just starting, make it easy for yourself and just use DAO: it's the native technology for Access, it works great, it's fast, easy, and it works out of the box without fiddling with references (it even works in 64bit Access, not that you would want to use that though).

With ADO, you'll need to manage connection strings manually in code and it behaves sometimes slightly differently than DAO, and that just adds to the confusion.

I will unequivocally say that in the context of learning to be a better Access developer, you can safely ignore ADO: you will probably never miss it.
I'm yet to find where I would absolutely need to use it in my own code, and I've been developing fairly large applications (50,000+ lines of VBA) in Access for a few years.

The MSDN page you reference does not talk of development under Access, it talks about what Data access technologies are available to programmers at large under Windows.
It's true that you would not be using DAO from another programming platforms, say when building application in C++ or .Net (although DAO blows ADO.Net in terms of raw speed)

It's also true that MDAC doesn't support Jet any more, but that's because: 1) Jet is integrated in all versions of Windows by default and 2) ACE is a new driver that replaces Jet in Access2007/2010 and adds new features while still being compatible with Jet.

Within Access itself, DAO is still the default -and most integrated- way to operate on your database from code, and until Microsoft actually replaces DAO by something else within Access itself, you don't need to worry, it's not like your apps will suddenly stop working.

Getting started

I would really recommend that you get a nice fat book, like:

Access 2010 Programmer's Reference (there is a PDF version).
Access 2010 VBA Programming Inside/Out (also in PDF version)

You don't need to read everything in these of course, but they will contain almost everything you'll ever need, including database samples for each chapters, and you can slowly improve and discover feature by skimming the book and getting more in-depth at your own pace.

ADO examples for a beginner

2 Answers2

Getting started