ADO vs. DAO 3

[perrymans]

ALright, little help here. I am running Access 2000, and may upsize to XP or 2003 (most of my users are there). What's the deal between ADO and DAO?

When I look for ADO info, I get a lot about ADO.NET, web driven content. But not much said about databases, other than that is wht you would use if building a VB front end.

I find myself setting the dbs as DAO.Database more and more in my coding (though I am not prof. programmer). Is DAO more flexible? Is ADO a one-shot capture of information (not a live link to the tables?). Even the ADO literature says it's not the greatest, then what is?

And most importantly, which is better to use in a pure Acces environment (FE and BE in Access)?

Thanks. Sean.

 

[cheerio]

DAO is really an interface to the Access JET database engine.

ADO is more generic - designed to work with a very wide range of data formats. It also works with a wider range of front ends such as web based forms.

It's probable that in a pure Access environment the DAO code would be faster. However, your project may be less scaleable if the volume of data grows so that you have to use a different backend.

If your users are all on Access 2k or above I would favour ADO for new development.

 

[SteveR77]

To follow up on what cheerio said.

DAO (Data Access Objects) is an application program interface (API) available with Microsoft's Visual Basic that lets a programmer request access to a Microsoft Access database. DAO was Microsoft's first object-oriented interface with databases. DAO objects encapsulate Access's Jet functions. Through Jet functions, it can also access other Structured Query Language (SQL) databases.
To conform with Microsoft's vision of a Universal Data Access (UDA) model, programmers are being encouraged to move from DAO , although still widely used, to ActiveX Data Objects (ADO) and its low-level interface with databases, OLE DB. ADO and OLE DB offer a faster interface that is also easier to program.


Steve

 

[perrymans]

Thanks guys. I'll try to begin weening myself off of DAO then. Is there a problem using ADO if an Access 97 user gets one of the databases?

Thanks again. Sean.

 

[TheAceMan1]

How are ya perrymans . . . . .

No matter what, always bear in mind that ADO is geared towards Servers.

As it is now, if I write any routines which involve a Server, I use ADO, otherwise DAO. Also I've noticed that in writing the same DAO functionality in ADO more code is produced to achieve the same result.

Yes . . . . it appears as if ADO was meant to replace DAO. Hence updating DAO routines with ADO becomes more important with time.

Access97 is not compatble with ADO.

TheAceMan

 

[Tranman]

perrymans,
Just to clear up a little confusion...I have been using ADO with Access 97 for several years--ever since ADO came out.

By default, Access 97 contains a reference to the DAO and not the ADO, so if you want to use ADO in 97, you will need to add a reference to the "Microsoft ActiveX Data Objects n.n Library" to your project.

(the latest version (the n.n part) is 2.7)

Tranman

 

[markphsd]

Hey, here's a comparison of a basic connection:
You can leave out the dao and adodb if you are only referencing one of the libraries, or if the library is refernced higher then the other one.

Dim DB As Dao.Database
Dim rst As Dao.Recordset

Set DB = CurrentDb
Set rst = DB.OpenRecordset("tblProducts", dbOpenDynaset)

or

Dim cnn as adodb.connection
Dim rst as new adodb.recordset

set cnn = currentproject.connection
rst.open cnn, "tblProducts", adOpenStatic, adOpenReadOnly


Almost then same amount of code. I feel ado is slightly easier after you get used to it. I converted one of my app's, took about 2 days, there was a lot of code. I haven't noticed any difference in speed. But it does seem a little more reliable(probably only because i was tooling around with other code.)

You have to get used to the different connection options. For example to get a record count you have to use adOpenStatic, or you don't have to use .edit... you can just edit, then .update. Getting used to it only takes about a a hours. But to get it right you should read up on it.

And if Microsoft is dumping the Jet db in favor of msde.. then you'd better brush up!


Mark P.

Bleh