ACC2003/2007 + SQL Server ADO or DAO

[BacktoAccess]

I have done a bit of homework on this topic and have basically
determined that there is not a simple answer to my question but I
thought I would ask to see if there are any new developments that
would lean the community one way or another especially with the Beta
release of Access 2010.

The Background is that I have a fairly extensive Access application.
Currently Access 2003 Front End/Access back end via linked tables that
I am evaluating for migrating the data to SQL Server. I am also in the
process of cleaning up code and writing a lot of class modules in
support of the clean-up. My background is with DAO and I am
comfortable with it.

My question is whether or not there are any strong cases to move to
ADO if I am re-writing the bulk of the code anyway. The factors that I
believe come into play are as follows:

Life Cycle - Will DAO continue to be supported for the foreseeable
future?

Interoperability - Is there a strong case for ADO versus DAO when
working with SQL Server databases? I have read a bit about what each
can and can't do but don't have any practical experience.

Portability - I don't have any expectation that I will move the front
end out of Access in the foreseeable future but it seems as though ADO
may be more portable to other front ends.

Bottom line is that I have been resistant to change for some time and
at this time have a willingness to make some changes to my approach if
there is merit.

I look forward to hearing some opinions.

 

[Douglas J. Steele]

DAO is part of ACE (the new database engine), so I'd say it's a pretty good
bet that it'll be around for some time to come.

ADO, on the other hand, is a dead technology: it's been replaced by ADO.Net
(although there are many differences)

Yes, you probably can do a little more using ADO rather than DAO against SQL
Server, but do you need any additional capability to what you've currently
got?

Portability strikes me as a non-issue, since if you do change your front-end
from Access, you're going to have a more significant rewrite dealing with
different controls in the new technology than worrying about which data
access method you used.

Bottom line: if you're satisfied with the current capabilities of your
application, I don't see a compelling reason to change.

 

[BacktoAccess]

Doug-

Thanks for the response! You hit on all the points I was interested in
feedback on. By the way I just got finished installing Office 2010
Beta and did see that DAO is included. I was thinking that learning
ADO might be a useful exercise but I do see that ADO.NET has replaced
ADO so I guess if I want to leard ADO.net I should learn ADO.net not
ADO.

I am very comfortable with DAO and am sure that I can do everything I
need to with it. Before putting a lot of time into code, I wanted to
get a good feel that DAO would be supported at least through Access
2010 and that there were no major pitfalls to continuing with it.

Thanks Again!

 

[Larry Linson]

At present, you would be learning ADO.NET for something other than your
Access work. Access does not (yet?) support ADO.NET -- the question mark is
because I have seen no discussion from usually-reliable sources about Access
and ADO.NET.

Larry Linson
Microsoft Office Access MVP

 

[Banana]

Just to offer one more POV:

I do use ADO, even though it's dead technology... for corner cases
involving ODBC sources, maybe 5% out of the overall project I may do.
95% of time, I just use DAO, even with ODBC linked tables.

In those 5% of the time, it may be because the requested functionality
is best served using stored procedures, table-valued functions,
dialect-specific functionality, and I can bind ADO recordsets to the
form and have full updatability (providing I conform to certain
conditions, of course) that would otherwise require more time & effort
in DAO-only scenario to implement the same functionality.

And to be explicit, there's nothing wrong with having both DAO and ADO
in the same project as long you disambiguate the references to either
libraries.

Until they provide native support for more modern access technology
(e.g. ADO.NET), ADO just will have to do for those corner cases, even
though it is dead.

With projects involving Jet/ACE engine (e.g. no ODBC linked tables),
there's just no point for involving ADO, IMHO.

 

[Tony Toews [MVP]]

FWIW I agree with Doug and Banana.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[David W. Fenton]

DAO is part of ACE (the new database engine), so I'd say it's a
pretty good bet that it'll be around for some time to come.

DAO is not "part" of ACE. It is the default data interaction
abstraction layer for the ACE, just like it was for Jet. DAO is a
wrapper around Jet/ACE, not part of it.

ADO, on the other hand, is a dead technology: it's been replaced
by ADO.Net (although there are many differences)

Well, it's been replaced in certain scenarios, but "classic" ADO is
still around in certain situations:

1. Access and Office in general

2. Classic ASP.

Probably others, as well.

But I agree it's a dead end. In the case of Classic ASP, it's not a
dead end, because there's no other alternative, but Classic ASP is
itself a dead end.

Yes, you probably can do a little more using ADO rather than DAO
against SQL Server, but do you need any additional capability to
what you've currently got?

ADO may be valuable when you need to bypass Jet/ODBC with SQL
Server. Otherwise, there's no reason not to use DAO.

The basic question:

If you're using linked tables, use DAO.

If you're completely unbound, use ADO, but you've tripled the amount
of work by going unbound, and gained almost nothing in terms of
capabilities and performance.

 

[David W. Fenton]

m:

Before putting a lot of time into code, I wanted to
get a good feel that DAO would be supported at least through
Access 2010

DAO will be supported as long as the Jet/ACE database engine is part
of Access (and probably long afterward for backward compatibility).
Given that the Access development team is making huge investments in
the ACE starting with A2007, I think it's likely that Jet/ACE is
going to be around for at least a decade, and that means that DAO
will be, too.

 

[David W. Fenton]

At present, you would be learning ADO.NET for something other than
your Access work. Access does not (yet?) support ADO.NET -- the
question mark is because I have seen no discussion from
usually-reliable sources about Access and ADO.NET.

I think ADO.NET could only make sense in Access if VBA were replaced
by a version of VB.NET. I think the drive to replace ADO with
ADO.NET came from the fact that a generic data interface layer that
was compatible with the strict code management requirements of .NET
was why ADO was jettisoned so quickly and replaced with ADO.NET. Had
ADO been created 5 years earlier, it likely would have been a
success on its own. Had it been created 3 years later, it would have
been what has ended up being called ADO.NET, and would have been
fully compatible with .NET from the beginning.

At least, that's my read on it.

At the time Microsoft was leading Access developers down the garden
path with the misguided ADO-Everywhere campain, .NET was well under
way, and it should have been clear that ADO was not going to work
with it. This is yet another reason why the ADO promotion still
angers me and makes me incredibly wary of any new technology that MS
happens to be promoting.

 

[David W. Fenton]

I do use ADO, even though it's dead technology... for corner cases
involving ODBC sources, maybe 5% out of the overall project I may
do. 95% of time, I just use DAO, even with ODBC linked tables.

"Even with ODBC linked tables"? Linked tables are the main reason
you'd use DAO, because linked tables are, by definition, Jet, and if
you're using Jet to communicate with the database via ODBC, then you
should use DAO.

To me it's only if you're *not* using linked tables that you'd even
consider using ADO for anything other than what you call the "corner
cases." And if you're not using linked tables, you probably should
ask why you're using Access at all (or why you're using an MDB
instead of an ADP).

[]

With projects involving Jet/ACE engine (e.g. no ODBC linked
tables), there's just no point for involving ADO, IMHO.

Actually, your exception should be "no SQL Server ODBC linked
tables" as no other back end benefits from use of ADO, no?

 

[Banana]

"Even with ODBC linked tables"? Linked tables are the main reason
you'd use DAO, because linked tables are, by definition, Jet, and if
you're using Jet to communicate with the database via ODBC, then you
should use DAO.

Yet I've seen people using ADO with anything ODBC because they thought
it was better. Even worse, people may use ADO against Access itself
because it had linked tables, which is just more work for no gain, hence
my attempt to emphasize that ADO only makes sense when we're using
non-Jet sources _and_ when we actually need ADO's capability where DAO
does not provide this (or at least not without extra codes & workarounds
in DAO/VBA). If both conditions aren't met, then there's no point in
bringing in ADO.

To me it's only if you're *not* using linked tables that you'd even
consider using ADO for anything other than what you call the "corner
cases." And if you're not using linked tables, you probably should
ask why you're using Access at all (or why you're using an MDB
instead of an ADP).

Well, as I said, it's only 5% of the overall development. It's possible
that in one form, I use ADO for a stored procedure against a certain
table, but the same table may be then referenced in a Jet query for
reporting, so technically I don't have to link that table, but I do
because I may access it via DAO interface for other uses.

To blithely replace DAO with ADO is simple madness.

Actually, your exception should be "no SQL Server ODBC linked
tables" as no other back end benefits from use of ADO, no?

I don't think so. I have used ADO with MySQL (again, 5% of overall
development- 95% it's just DAO). Unfortunately, that means I need to use
MSDASQL as MySQL do have an OLEDB driver but it's not supported and
seems to be largely neglected so I opted for ODBC via OLEDB... an extra
layer, but the performance has been satisfactory for the needs and
overall requires less efforts because I can then use stored procedures
for the corner cases.. Again, only in 5% of the time where I actually
want to use stored procedures or where simple data binding is not the
right answer for the needed functionality. 95% other of time, I use DAO
against Jet tables or linked tables and it work well enough.

I could certainly eschew ADO and do DAO 100% of time, but the savings I
get from being able to bind a more complex-formed recordset to a form
with full updatability usually offset the extra work I'd have to do in
DAO & VBA to replicate the functionality and this usually is a good
thing for my clients when I can make a good product with less effort and
less bugs because I'd be reusing components than rolling out my own.

I would love it if they would make changes so the passthrough queries
could be updatable or update the data access library and I would happily
part ways with ADO and use the PT or ADO.NET in the same 5% of the cases.