DAO vs ADO

[Tinso]

What are the advantages of ADO. I have used DAO for years and only dabbled in ADO. I am thinking of converting my commonly used classes but need a good reason to.

 

[ADoozer]

take a look at

thread222-13378

good luck

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383

 

[Transcend]

This is from msdn:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

Transcend

 

[Tinso]

good info, thanks
I was not aware that DAO only worked on old Access/Jet dBs.
Given that we will soon be upgrading our dB's to Access 2000 (and SQL Server if we can find the $) I guess it is time to upgrade.

 

[CCLINT]

>I was not aware that DAO only worked on old Access/Jet dBs.

It also works on new Access/Jet mdbs.
But use ADO, esp. if you plan on upgrading to SQL Server.

 

[DirectDrive]

My opinion is ADO is the better was to go regardless of if you can get away with DAO.
ADO allows connections to most common databases without the need for DSNs. When the day comes that your boss says that the company is migrating everything to Oracle, SQL Server, My SQL, etc, you will have no worries since you already know ADO.
The ability to develop app's without having to worry about DSN distribution is also a beautiful thing.

 

[mjk9564]

DAOs do work with ACCESS 2000 and are faster. I am updating a program that our company has been using for a couple years with DAOs to ADOs. So, we can goto SQL Server 2000. I have been running into slow update problems when the ADOs are running multiple connections through seprate classes. But, DAOs did it fine.

 

[strongm]

I think a minor - but important - correction is necessary (and this is a very pedantic pint). Rather than saying "DAOs do work with ACCESS 2000 and are faster" it would be better to say "DAOs work faster with JET databases"

 

[stoolpigeon]

You are right strongm and that is the only thing you need to really think about when you are considering between the two. DAO was written for JET. If you use anything else go with ADO. If you are moving away from JET at some point use ADO. But if it will always be a JET database you are working with- use ADO. It will perform better.

 

[stoolpigeon]

for the love of pete- the next to the last sentence in my post should read
"But if it will always be a JET database you are working with - use DAO"

 

[strongm]

>and this is a very pedantic pint

Ok, I admit it - I've been to the pub tonight...

 

[ADoozer]

LOL

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383

 

[Tinso]

Just a postscript:
I converted one of my applications from DAO 3.6 to ADO. The run updates about 8000 records in one table with a long integer primary key and about 10,000 records in another table with a compound index (long integer and a short string). The ADO version takes about 5 times longer! Lucky I saved the old code!
The database is Access 97, about 25 meg compacted.

Would SQL Server give a dramatic improvement in performance?

 

[Transcend]

Tinso,

as was stated, DAO will work faster with a Jet db (Access).

But if you are moving to SQL Server, use ADO, and yes you should experience a performance improvement.

Transcend

 

[CCLINT]

How did you do the Update?

Try doing it against the connection object, which is the fastest method under ADO:

conn.CursorLocation = adUseServer
conn.Execute "UPDATE....", ,adExecuteNoRecords

Also, you have the option to run this async.

One other factor which will slow things down is the fact that you are probably using the JET 4 on a JET 3 database.

Using the native JET driver through DAO will still be faster, just because of the extra layer used under ADO and some other factors concerning the JET OLEDB provider, but while using ADO will be a little slower, it shouldn't be 5 times slower for this type of operation.

 

[Tinso]

I think I tried the execute from both the connection and the command. The timing comparison was based on the command object execute. Thanks for the tip about the difference.
The bigger issue is probably the Jet 4 provider on a Jet 3 db. The 'supports index' and 'supports seek' are both false for Jet 4 provider. I was forced to use a rather convoluted 'Find' to navigate to individual records for the compound index.
For now I will stick with the DAO. In a few months the corporate environment will be upgrading. Jet 4 will be part of the upgrade.
How is ADO.Net for speed?

 

[Transcend]

I've been playing around a bit now with asp .net, datasets and datagrids and the like.

Binding a dataset to a grid in .net is a hell of a lot quicker than looping through a recordset ... so i'd say faster.

Transcend

 

[Tinso]

quicker to program or quicker to run ?

Likewise I have been playing around with .Net, but only at home. I can't compare the performance in the home environment. Home machine is faster, database is on the local hard drive etc.

.Net seems to have more layers ??

any idea what kind of licencing costs for a corporate setup of SQL Server?

the LAN/WAN has thousands of machines but I doubt if more than a dozen would be in the db at one time. Intranet ASP would be nice as well?

 

[BobRodes]

There are a few more points to make in this discussion. It is true that DAO was optimized around Jet, and RDO around ODBC. As such, these are the best object libraries to access their respective middle layers, at least with respect to performance.

The main point about ADO is that it is a layer that sits on top of OLE DB, which is a set of COM components that can be used to write data providers. Data providers are basically analogous to data drivers in the DAO/Jet/ODBC world.

So, anyone can write a data provider that is optimized to their data source. (Of course, they could further optimize to same by writing a proprietary API, but it would be harder to access it.)

This means that there is only one object library to have to know to go through a minimum of layers to get to the back end.

Other pros to ADO: a flatter object hierarchy makes for less objects running around in your code. Each object's methods are substantially overloaded to allow maximum flexibility in accessing their functionality. (Note, for example, the number of ways you can set up, say, recordset.open). Finally, the ability to build recordsets on the fly and not have them tie to a database allows ADO to work with non-relational data.

Cons: ODBC is an open standard. OLE is based on COM, and therefore is a proprietary standard. (Resistance is futile.....you will be assimilated......) There can be performance problems, especially if you don't evaluate carefully which layers you are going through. An egregious example would be to use the OLE DB Provider for Jet to access an ODBC database.

Bob Rodes

 

[sdraper]

"Just a postscript:
I converted one of my applications from DAO 3.6 to ADO. The run updates about 8000 records in one table with a long integer primary key and about 10,000 records in another table with a compound index (long integer and a short string). The ADO version takes about 5 times longer! Lucky I saved the old code!
The database is Access 97, about 25 meg compacted."
I was under the impression that Access 97 didn't support ADO? Am I confused? yep...

Sam