ADO and DAO

What are the differences between DAO and ADO?
puthuvinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puthuvinAuthor Commented:
Please send the answer of this question immediately.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jyokumCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

yronnenCommented:
DAO is obsolete. It is not being developed since 1998 or so, it was replaced by ADO. Don't use DAO.

0
davidmwilliamsCommented:
ADO is more lightweight and supports many "non-database" data sources like e-mail.

Further, ADO is optimised for disconnected data sets.
0
DabasCommented:
Historically, DAO has existed since Microsoft allowed programming languages like VB to have MS Access data abilities. (I think that started with VB4)

DAO stands for Data Access Objects and although it can be used for several non MS Access Databases, it is nearly alwasy used for Access only. In short, it is the native way of working with an MS Access Database.

Between DAO and ADO, Microsoft introduced RDO, which stands for Remote Data Objects. The objective was to introduce a Database interface that was not mainly MS Access based, but that would be easy to use with other Databases, such as the emerging SQL Server.

ADO, standing for Activex Data Objects is the current way of doing things (unless you want to talk about ADO.NET which is not necessarily the same, and is even more "modern").

As one of the previous experts has mentioned, if you have the choice, ADO should be the way to go, for various reasons, one of the main ones being scalability. Maybe today your database is in MS Access. If in the future you would like to scale the whole application upwards, say to SQL Server or Oracle or other database, chances are that if you use DAO you will need to rewrite quite a lot of your code. If you use ADO, probably all you will need to change is your connection string, leaving most of the rest intact.

Programatically, in DAO you work with Databases and Recordsets.
In ADO you work with Connections, Commands and Recordsets.

To Open the table MyTable in Database MyDb, this is the way you would go about it in each of the alternatives:

DAO:

Dim db as DAO.Database, DAO.rs as Recordset

Set db = OpenDatabase("MyDb")
Set rs = db.OpenRecordset("MyTable")

ADO

Dim cn as ADODB.Connection, rs as ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & "MyDB" & ";"
cn.Open

Set rs = New ADODB.Recordset
rs.Open "Select * from MyTable", cn

Hope this answered your question!!!

Dabas

0
Jeff TennessenAssistant Vice PresidentCommented:
I agree with the general statements made here that ADO is the better choice in most situations. However, DAO is still faster than ADO when going against a Microsoft Jet data source (the database engine in MS Access) and using the Seek statement to find a single row. For most operations, the difference is too small to be noticed; but, if you're sure that any upsizing of your application's data store is far off, and squeezing every ounce of performance out of the app is essential (especially in a loop), it may be worth testing DAO performance vs. ADO before making a final decision.

Jeff
0
jyokumCommented:
I'm pretty sure DAO won't work with the newer versions of MS Access (2000+)
0
DabasCommented:
To JTennessen's comment:

I am a recent "convert" from DAO to ADO and find that there is not much difference between using Seek in DAO or using "Select * from table where ..." (find the one value you are after). ADO is supposed to use the indexes to find your value as fast as possible. As you yourself say, the difference is too small to be noticed, specially with the speed of modern computers.
If speed is an issue, then I will second the motion of testing, which is difficult as it depends on many other factors such as network cards, amount of users using the system at the same time etc.
For somebody starting, which is the case here, I would still recommend ADO, mainly because it is easier to then "transport" the codes to non-Access Databases.

As somebody who is in the process of doing so presently, I definitely would love to sacrifice a the minimal difference in speed rather than rewriting big chunks of code.

To jyokum's comment:

DAO 3.6 handles MS Access 2000 perfectly!

Dabas
0
Jeff TennessenAssistant Vice PresidentCommented:
Dabas, I agree with you in most circumstances, but there are still operations where it does make a difference. For example, try an ADO SELECT statement vs. a DAO Seek on a Jet table with millions of records. The difference is quite noticeable. True, I've read that ADO SELECT uses the table's indexes and, in theory, finds data just as fast as DAO Seek, but our testing has not borne this out.

Your comments re: other factors (NICs, load, etc.) are right on target, which is why I definitely encourage performance testing all possible solutions under the conditions in which they will normally be used.

Again, I'm not trying to argue that DAO is a better general choice than ADO; just that, in certain exceptional circumstances, it may be worth testing it for performance considerations.

Jeff
0
Zlatin ZlatevTechnical ArchitectCommented:
@jyokum,
I am using DAO with no problems in Access 2000
0
Zlatin ZlatevTechnical ArchitectCommented:
@puthuvin, according to me Dabas has provided most useful information in his comments.
0
archekryptCommented:
ADO kicks @$$ over DAO!!!
ADO is MORE flexible, but somehow DAO is sometimes more speedy...(don't ask)
just a thought...

but personally use ADO, less restrictions, or just use both intertwined!
that could work...
0
rasenieroCommented:
when using dao and ado data controls, it's the same!

when you love encoding it i prefer ado! i love to call queries! it's just fun!


disadvantage with ado is when you do this:

sql  = "[tabl] = '" & txt &"'"

- i hate that! i doesn't matches the record unlike dao!

you have to make a condition in a loop to look for it! tedious!

much better when you use ado you practise sql queries!
0
DabasCommented:
puthuvin:

1) This question was asked quite a while ago, yet you still have not accepted an answer, nor asked for further clarification.

2) You already have another "Ask and Run" incident in your history.

May I suggest you do the fair thing and choose one of the answers above.

Dabas
0
jyokumCommented:
please select a comment as the solution or request a close in the Community Support section
http://www.experts-exchange.com/Community_Support/
0
DabasCommented:
jyokum:

I doubt we will be that lucky!
Have you asked for closure in CS yourself?

Dabas
0
jyokumCommented:
well it certainly won't happen if you don't ask
0
DabasCommented:
Have YOU asked in CS yourself?
Does not help if you do so here!
0
jyokumCommented:
oops, misread your post.  I just requested close in CS.
0
Computer101Commented:
A request for deletion or PAQ has been made.  If no response or you feel this is in error, comment.  If no objection, I or another Moderator will handle this question in three days.  If you, the questioner have not answered comments from experts concerning your question, we will ask that you address those before action is taken.

Computer101
E-E Admin
0
DabasCommented:
Computer101 or Moderator:
I suggest PAQ with points to be shared between the experts that have spent time giving meaningful answers to what is a FAQ.


Dabas
0
Computer101Commented:
PAQ = Previously Asked Question

C101
0
DabasCommented:
FAQ = Frequently Asked Question
0
SpideyModCommented:
Answered (sorry remaining experts, but the first answer was VERY thorough and very complete)

SpideyMod
Community Support Moderator @Experts Exchange
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.