ADO or DAO

[EH101]

Hi,
I am building a new database (mdb) and I am not sure if I should use DAO or
ADO?
Thanks

 

[Douglas J. Steele]

If you're strictly using an mdb, DAO is usually the way to go.

An argument can be made if you might eventually upsize to another DBMS, then
ADO would be better. However, generally proper upsizing requires far more
work than simply changing the ADO connection string.

 

[EH101]

Thanks Douglas,
in that case DAO will be fine as the DB is a proof of concept and will only
be around for a few months.

Cheers.

 

[Tony Toews [MVP]]

in that case DAO will be fine as the DB is a proof of concept and will only
be around for a few months.

There are a large number of "proof of concept" Access databases that
have been in production for years.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

If you're strictly using an mdb, DAO is usually the way to go.

An argument can be made if you might eventually upsize to another DBMS, then
ADO would be better. However, generally proper upsizing requires far more
work than simply changing the ADO connection string.

But DAO works fine against SQL Server which is the most likely upsized
database engine. Or am I mistaken?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Douglas J. Steele]

But DAO works fine against SQL Server which is the most likely upsized
database engine. Or am I mistaken?

Yes, DAO works against SQL Server. However, I find even with SQL Server, I
like using ADO.

 

[David W. Fenton]

Yes, DAO works against SQL Server. However, I find even with SQL
Server, I like using ADO.

If writing from scratch, sure, but I see no reason to start mixing
in ADO when upsizing an app that uses only DAO.

 

[Tony Toews [MVP]]

Yes, DAO works against SQL Server. However, I find even with SQL Server, I
like using ADO.

But why?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Douglas J. Steele]

But why?

For one thing, I like using the Command object to run stored procedures that
don't return recordsets. Yes, I know you can use a pass-through query and
set the ReturnsRecords property to False, but the ADO approach just makes
more sense to me.

That having been said, fortunately I don't usually have to upsize existing
applications. And when I go against SQL Server, I prefer using pass-through
queries, so that implies I usually have to use unbound forms. If I were
upsizing a bound application, I'd have little choice but to leave it the way
it was, unless I wanted to do a major rewrite.

 

[Jamie Collins]

But why?

One reason could be data type mapping. DAO's DataTypeEnum is very
limited e.g. DAO 3.6 has 21 data types (ADO 2.8 has 40) and just one
is for text, consequently DAO cannot differentiate between SQL
Server's CHAR, VARCHAR, NCHAR and NVARCHAR and Jet's NCHAR and
NVARCHAR. On the other hand, ACEDAO has support for the multi-valued
data types (dbComplexInteger, dbComplexDecimal, etc) which is absent
from ADO. But then DAO has not heard of the scale and precision
properties of the DECIMAL type which was introduced into the Jet
engine two versions prior. Do you get the impression that ADO and DAO
were designed to solve different problems and that recommending one
over the other should be on a strictly case-by-case basis...?

Jamie.

--

 

[Alt255]

There are a large number of "proof of concept" Access databases that
have been in production for years.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

I still have some DBs that were a temporary fix still in use 11 yeares
later.