ADO vs. DAO
Jack Hudson
still see MVP's giving code solutions using DAO. Not being critical here,
simply curious to know why ADO solutions are not given more frequently. Any
observations y'all like share?
Regards, Jack Hudson
Rick Brandt
DAO was never obsolete just not the shiny new thing on the shelf. The
overwhelming consensus that I see in these groups is that ADO offers no
advantage when working against Jet data and has some disadvantages. Since most
posters in these groups are working against Jet data it makes sense to continue
posting DAO solutions.
I for one almost never use Jet tables but still use DAO because it works for
everything I need and have never felt any reason to learn something new.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
We're old farts?
What Rick said is generally true. I'm more comfortable in DAO as
that's what I've been using for a long time. And ADO requires two
lines of code not one.
The only thing about ADO that is useful to me is that it will allow
you to reset the autonumber seed with DAO won't. And that is pretty
minor and seldom used.
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
Albert D. Kallal
news:jJXOh.10269$JZ3....@newssvr13.news.prodigy.net...
ADO was a newer object model, and is not really tied to JET.
In some ways, the ADO object model is a bit cleaner then DAO. (the two areas
that I notice the most is are when you do a update in ADO, the record
pointer does not move...in DAO it does, and that tends to be a bit of pain
(actually quite a bit of pain). Also, in ADO if you move to the next record,
you don't have to issue a update, were as in DAO you do. And, if memory
servides me correct, the "edit" method is optional in ADO. These about the
only two, or three real things as to why I think ADO is a bit cleaner then
DAO.
However, it is ADO that really be put out to pasture, and we are now being
told to use ado.net.
However, for ms-access, we continue to receive new versions of JET..and
access 2007 has new data types. So, in effect, DAO continues to get
enhancements and new features (if you consider DAO as part of the jet
engine). ADO it not receiving any such enhancements.
In access 2007, the JET odbc direct ability was removed. So, this means that
JET is still being changed, and this is one example of a feature that has
been put to pasture. This issue does favour using ADO in this case (as then
you can connect to sql server, and by-pass jet. However, jet + odbc via dao
still functions as usual, and pass-though queries will still by-pass jet,
and thus you get much the same result.
In addtion, workgroup secirty for access 2007 has been removed. So, in
effect, this is two areas which reduce the advantage of using DAO over that
of ADO.
However, in both cases, dao, or ado hitting sql server perform about the
same.
so, in a funny way, about 7 years ago, we were told to use ADO, but we all
just continued on using DAO. As time passed, lo, and behold...today, DAO is
still strong, and in the ms-access community DAO is stronger then is ADO,
and DAO still enjoys good support from Microsoft.
So, it likely more developers are dropping ADO and moving to ADO.NET then we
who continue to use DAO.
If your developing the application in mind with eventual moving to sql
server, then there is some advantages to using ADO in your application. And,
we have a good number of vb6 developers who now jumped into ms-access, and
they are also more familiar with ADO. So, what is really nice is that both
dao, and ado enjoy widespread support in ms-access. For the most part those
being told to throw out, and stop using DAO were actually a bit
miss-informed.
It is not really much a problem or issue, DAO tends to be the choice when
building ms-access application (because that is the way we always done
it..and it tends to be a bit less code in a good number of cases -- sans my
two examples).
You certainly can use ADO also. ADO is another layer on top, and does
abstract out the database more then DAO. So, if you eventually plan to
change the database engine, ADO is still a better choice.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
Jamie Collins
> > For years now MS has suggested usingADOas DAO is obsolete. However, I still
> > see MVP's giving code solutions using DAO. Not
> > given more frequently.
>
> DAO was never obsolete
That certainly true but MSFT did give the impression that DAO was no
longer MSFT's data access component of first choice e.g.
Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
Using ADO vs. DAO
"In previous versions of Access, Data Access Objects (DAO) was the
primary data access method. That has now changed. Although DAO is
still supported, the new way to access data is with ADO."
> The
> overwhelming consensus that I see in these groups is that ADO offers no
> advantage when working against Jet data and has some disadvantages. Since most
> posters in these groups are working against Jet data it makes sense to continue
> posting DAO solutions.
There is an overwhelming preference for DAO, which is surely the
simple explanation as to why we see more DAO solutions posted i.e. it
is in proportion to greater code/knowledge base. Also note that we see
many MVP wannabes in these groups and MVPs prefer DAO, imitation being
the sincerest form of flattery; put another way: you may prefer ADO
yourself but exclusively posting ADO is not the path to glory here.
Take, for example, the oft requested task of seeding an incrementing
autonumber: there is no shame is posting the DAO solution (create
column with autonumber property, insert (seed - 1) rows, delete all
rows) and the straightforward and elegant ADO solution (create a
column with the autonumber property seeded appropriately) is rarely
seen.
However, "overwhelming consensus that... ADO offers no advantage" must
be a MMM (misleading statement, misstatement or misunderstanding)
because there are some functionality accessible to ADO and not DAO;
willingness to accept Hobson's Choice is a huge advantage <g>. It may
be a popular misconception that ADO offers no advantage but 'popular'
does not equate with 'truth'!
> I for one almost never use Jet tables but still use DAO because it works for
> everything I need and have never felt any reason to learn something new.
I don't think many people would recommend DAO for non-Jet tables. From
personal experience DAO+Oracle was disastrous and mapping data types
between DAO and SQL Server couldn't be made to fit :(
Jamie.
--
Jamie Collins
wrote:
> ADO requires two
> lines of code not one.
>
> The only thing about ADO that is useful to me is that it will allow
> you to reset the autonumber seed with DAO won't.
CurrentProject.Connection.Execute "ALTER TABLE YourTable ALTER
autonumber_col INTEGER IDENTITY(1, 1);"
That's _one_ line of code by my reckoning.
Jamie.
--
Jamie Collins
wrote:
Albert,
Yours is the best reflection on ADO vs DAO I've seen in the Access
2007 era and sincerely thank you for it.
A couple of points:
> it is ADO that really be put out to pasture, and we are now being
> told to use ado.net.
>
> it likely more developers are dropping ADO and moving to ADO.NET then we
> who continue to use DAO.
You seem to be using 'we' to mean 'developers across all MS platforms'
e.g. Visual Studio and Office. I think you comments about ado.net are
misleading in the context of ms-access because AFAIK there is no
practical way of using ADO.NET in an ms-access project whereas ADO
classic remains a viable option.
> access 2007 has new data types. So, in effect, DAO continues to get
> enhancements and new features (if you consider DAO as part of the jet
> engine) .ADO it not receiving any such enhancements.
Out of interest, are you recommending the use of the Access2007 multi-
value data types in a standard ms-access project (i.e. other than
sharepoint integration etc)? I've read your opinions on pick, which of
course has the required predicates, but I wondered where you stand on
the Access/Jet implementation of multi-value types. I don't see anyone
in these groups evangelizing or even advocating them. It is relevant
to this thread because you are using them to add to the case for DAO.
Another thing to note is that DAO has still not got enhancements for
some of the Jet 4.0 features (e.g. creating CHECK constraints); ADO
(via the OLE DB providers) has received such enhancements.
Jamie.
--
Douglas J. Steele
news:1175247164.2...@b75g2000hsg.googlegroups.com...
> On Mar 30, 12:19 am, "Rick Brandt" <rickbran...@hotmail.com> wrote:
>> > For years now MS has suggested usingADOas DAO is obsolete. However, I
>> > still
>> > see MVP's giving code solutions using DAO. Not
>> > being critical here, simply curious to know whyADOsolutions are not
>> > given more frequently.
>>
>> overwhelming consensus that I see in these groups is that ADO offers no
>> advantage when working against Jet data and has some disadvantages.
>> Since most
>> posters in these groups are working against Jet data it makes sense to
>> continue
>> posting DAO solutions.
>
> be a MMM (misleading statement, misstatement or misunderstanding)
> because there are some functionality accessible to ADO and not DAO;
> willingness to accept Hobson's Choice is a huge advantage <g>. It may
> be a popular misconception that ADO offers no advantage but 'popular'
> does not equate with 'truth'!
While it's true that there's functionality available only in ADO, there's
also functionality available only in DAO.
See MichKa's article "What does DAO have that ADO/ADOx/JRO do not have (and
might never have!)" at http://www.trigeminal.com/usenet/usenet025.asp
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
Jamie Collins
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> While it's true that there's functionality available only in ADO, there's
> also functionality available only in DAO.
Absolutely correct and thanks for pointing out my omission...
> See MichKa's article "What does DAO have that ADO/ADOx/JRO do not have (and
> might never have!)" athttp://www.trigeminal.com/usenet/usenet025.asp
...but from a quick glance, not only is that article obviously biased,
it contains a number of MMMs (misleading statement, misstatement or
misunderstanding). How many problems must I highlight for you to
desist in linking to this article <g>?
"Creating users and groups in a way that allows you to recreate them
in case an MDW file is lost...fails in ADO which does not allow you to
specify PIDs" is a clear misstatement: unlike DAO, ADO may be used to
execute Jet SQL DCL. See:
Advanced Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx
[Quote]
To use the PID with a CREATE USER statement, list the PID value after
the password.
Copy Code
CREATE USER Tim pwd H3sJaZ9k2m
[Unquote]
"Securing Access project objects such as forms, reports, or macros...
fails in ADOx because it does not properly map the expected constants
for permissions to execute, read changes, and write changes to these
object types" could be a misstatement. There is certainly the *syntax*
to do this: from the above article:
[Quote]
The keywords used with the GRANT and REVOKE statements are as
follows:
...
The CONTAINER keyword is used to specify any of the container objects,
such as tables, relationships, forms, or reports.
[Unquote]
but whether it works via ADO I have not tested.
"ADO...has no AllPermissions property and requires you to separately
enumerate the user and all of their groups" is misleading because,
again, the ADO solution is to execute Jet SQL DCL. From the above
article again:
[Quote]
Using PUBLIC will set permissions for the default Users group account
so that everyone will get the assigned privileges
...
ALL PRIVILEGES: Allows a user all permissions, including
administrative, on a specified table, object, container, or database.
[Unquote]
This applies to setting permissions; I don't know what "Retrieving
implicit permissions" means in context.
"Capability to set and change Jet options without making registry
changes (works in DAO through DBEngine.GetOption and
DBEngine.SetOption, fails in ADO, which has no such analogue)" A clear
misunderstanding. There are direct equivalents using settings in the
OLE DB provider. See:
ADO Provider Properties and Settings
Microsoft Jet 4.0 Provider Properties
http://msdn2.microsoft.com/en-us/library/aa140022(office.10).aspx
Table 5. Provider-specific ADO Connection Object Session properties
>From a quick glance the DAO.SetOptionEnum equivalents are found here;
the first three certainly are covered by Jet OLEDB:Shared Async Delay,
Jet OLEDB:Flush Transaction Timeout and Jet OLEDB:Implicit Commit Sync
respectively.
I'm hope four is enough because I'm out of time!
I think some are simply not a fair comparison:
"Allowing a separate Jet session to run using a special object in the
object model" Erm, why is that relevant to database access components
e.g. why isn't opening a second connection acceptable in context?
"Allowing the creation/change/deletion of any and all properties
through the JPM" Most of the properties seem to relate to the Access
user interface rather than the Jet layer i.e. these properties were
omitted _by design_.
In the interest of fairness, I think the point about locking (the
article labours the point with three items to make essentially the
same point) is actually worse than implied e.g. pessimistic locking of
objects fails through ADO.
Jamie.
--
Dirk Goldgar
Jamie Collins <jami...@googlemail.com> wrote:
> On Mar 30, 11:54 am, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>> While it's true that there's functionality available only in ADO,
>> there's also functionality available only in DAO.
>
> Absolutely correct and thanks for pointing out my omission...
>
>> See MichKa's article "What does DAO have that ADO/ADOx/JRO do not
>> have (and might never have!)"
>> athttp://www.trigeminal.com/usenet/usenet025.asp
>
> ...but from a quick glance, not only is that article obviously biased,
> it contains a number of MMMs (misleading statement, misstatement or
> misunderstanding). How many problems must I highlight for you to
> desist in linking to this article <g>?
Very interesting post, Jamie. However, most of your rebuttals involve
executing Jet SQL statements via ADO, contrasted with manipulating
programming objects via DAO. I'm not convinced this is a fair
comparison. Any process that lets you pass SQL statements to the
database engine will give you access to those features of the database
engine that are supported in that engine's dialect of SQL. That's
different, though, from an object library that provides classes,
methods, and properties to manipulate those features. Both DAO and ADO
can execute SQL statements to accomplish many of the tasks the article
refers to -- at least, reading your post I am now aware that they can --
but DAO provides Jet-specific programming objects to accomplish those
same ends. To a SQL-oriented person, that may be of no importance, but
to a code-oriented person it can be easier to create objects and
manipulate properties than to look up uncommon SQL syntax.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Albert D. Kallal
> You seem to be using 'we' to mean 'developers across all MS platforms'
> e.g. Visual Studio and Office. I think you comments about ado.net are
> misleading in the context of ms-access because AFAIK there is no
> practical way of using ADO.NET in an ms-access project whereas ADO
> classic remains a viable option.
Sure, I 100% with the above. I was using "we" as a general term
for developers. I was kind of just pointing out that likely more
people are dropping ADO as compared to DAO right now because
that is the new upgrade path for the .net folks.
So, it is apples and oranges, but the message we kind of get
today is that ado.net is the next new thing...and message was
much the same for ado over dao. It not a real big issue because
Microsoft has strongly supports dao in ms-access.
>
>> access 2007 has new data types. So, in effect, DAO continues to get
>> enhancements and new features (if you consider DAO as part of the jet
>> engine) .ADO it not receiving any such enhancements.
>
> Out of interest, are you recommending the use of the Access2007 multi-
> value data types in a standard ms-access project (i.e. other than
> sharepoint integration etc)? I've read your opinions on pick, which of
> course has the required predicates, but I wondered where you stand on
> the Access/Jet implementation of multi-value types. I don't see anyone
> in these groups evangelizing or even advocating them. It is relevant
> to this thread because you are using them to add to the case for DAO.
I of course am actually a big fan of the muiti-values. to me, many
applications after a number of years begin to start falling apart at the
seams. Fro example, a person has a customer record, and they need to store
their favourite foods. You see the "amateur" developer start adding fields
like food1, food2, food3, or perhaps just one field of food, and in side the
field they type in data like
Apple Pie, Steak, Cheesecake
In both cases, the data is terribly un-normalized. When you use multi-value
fields, your actually normalizing the data. What that means is that after 5
years of a person modifying the database, you don't have to throw it out,
and call in a bunch of high priced oracle experts. So, in a way, those
multi-value fields allow someone to build correctly formed database
structures, and they don't even know it. In fact, I think *any* field should
be able to go multi-value, and the option in the "other" tab that stats what
the enter key does should have a option "allow multi-values on enter key".
(the option now have enter key = new line, I want to see it go: enter key =
new multi value).
In other words, uses should be able to add multi-values to any field, and do
it without barely thinking. The industry falsehood has always been that
multi-value systems are not correctly relational, and not normalized. This
is not true at all. One of the great big secrets of multi-value systems (of
many of which been around for 20, or even 30+ years) is that the designs and
data of the applications where VERY easy to extend over time..so, they did
not being to fall apart at the seems like non normalized applications do
after many years of modifications. As a result, there is TON of old
multi-value systems that continue to run today, when most of the other
vendors from that period have long disappeared. Those old muiti-value
systems continue to run, because their data allowed the applications to be
flexible, and stay *more* normalized then the competing systems of that time
period.
So, I not yet really promoting multi-value fields, but I do believe in them
strongly because they make people actually normalize data without even a
clue to this fact.
In a2007 a person can setup a database with "favourite" foods, and in effect
without having to write one line of code, they actually built that many to
many relation with a junction table without even knowing they were supposed
to -- I think that a huge leap forward for these users.
Users can't think in terms of relational databases, but they sure know that
they want to save a list of favourite foods in their database, and a2007
lets them do that without even realizing they are doing it the right way.
Now, as a seasoned developer, I know how to build that favourite foods list,
so, the really big question becomes will I use this feature? Well, not
likely, not quite yet. However, for a lot of little pick lists, and things
like what category a contact belongs to, those auto-matic pick lists that
a2007 has can actually save developer time also. The problem for seasoned
developers is that the tables and relationships are not exposed in the
relationships windows (however, in dao..and in reocrdset code...they are!!).
This "exposed" part is much a sticking point for me.
> Another thing to note is that DAO has still not got enhancements for
> some of the Jet 4.0 features (e.g. creating CHECK constraints); ADO
> (via the OLE DB providers) has received such enhancements.
Sure, there is a few things in ado that was nicely improved..and mostly done
for those folks coming from sql server. On the other hand..not many of us
use the constraints features. And, the create procedure is really nothing
more
then a select, or saved query anyway.
The popularity of DAO today simply shows that MS could not get rid of it,
and what really amazing is that MS is working hard to keep us happy on this
front. In other words, they simply looked at the market, and access
develops...and said, gee...dao is popular...so, we keep it alive and well.
The beauty is that ms-access gives you a great choice with dao, or ado. I am
thankful to MS for this extra effort and continued support of dao....but,
either one is a fine choice in access.
David W. Fenton
MS has changed its mind. It is now recommending all DAO for Jet
data, as it should have been from the beginning.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
> On Mar 30, 12:19 am, "Rick Brandt" <rickbran...@hotmail.com>
> wrote:
>> > For years now MS has suggested usingADOas DAO is obsolete.
>> > However, I still see MVP's giving code solutions using DAO.
>> > Not being critical here, simply curious to know whyADOsolutions
>> > are not given more frequently.
>>
>> DAO was never obsolete
>
> That certainly true but MSFT did give the impression that DAO was
> no longer MSFT's data access component of first choice e.g.
>
> Intermediate Microsoft Jet SQL for Access 2000
> http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
>
> Using ADO vs. DAO
> "In previous versions of Access, Data Access Objects (DAO) was the
> primary data access method. That has now changed. Although DAO is
> still supported, the new way to access data is with ADO."
Assuming that documentation was produced for the release of Office
2000, that information is EIGHT YEARS OLD.
Microsoft has changed its tune. That is obvious by the way they've
changed the default references in newly created databases.
>> The
>> overwhelming consensus that I see in these groups is that ADO
>> offers no advantage when working against Jet data and has some
>> disadvantages. Since most posters in these groups are working
>> against Jet data it makes sense to continue posting DAO
>> solutions.
>
> There is an overwhelming preference for DAO, which is surely the
> simple explanation as to why we see more DAO solutions posted i.e.
> it is in proportion to greater code/knowledge base.
It's a perfectly logical preference when most of the discussion here
is about using Jet data. ADO *never* made any sense for Jet data,
and anyone who understands what a database abstraction layer is
should see that (ADO is a database abstraction layer; DAO is an
interface to a particular database engine that has hooks that allow
it to be used to access lots of other db engines).
> Also note that we see
> many MVP wannabes in these groups and MVPs prefer DAO, imitation
> being the sincerest form of flattery; put another way: you may
> prefer ADO yourself but exclusively posting ADO is not the path to
> glory here.
Posting ADO code for Jet problems would certainly not be the "path
to glory" because it would show that you're not really competent to
be giving advice.
> Take, for example, the oft requested task of seeding an
> incrementing autonumber: there is no shame is posting the DAO
> solution (create column with autonumber property, insert (seed -
> 1) rows, delete all rows) and the straightforward and elegant ADO
> solution (create a column with the autonumber property seeded
> appropriately) is rarely seen.
Microsoft made a decision (not on logic, but on marketing) to leave
certain new Jet 4 features out of DAO and put them in ADO, instead.
That is a flaw in MS's strategy, not in DAO.
Now that Jet is again a live development platform, I expect the
ACCDB version of DAO to gain lots of new features to keep up with
the development of the ACCDB Jet engine.
> However, "overwhelming consensus that... ADO offers no advantage"
> must be a MMM (misleading statement, misstatement or
> misunderstanding) because there are some functionality accessible
> to ADO and not DAO;
And there are things in DAO that ADO can't do (hence the need for
JRO).
> willingness to accept Hobson's Choice is a huge advantage <g>. It
> may be a popular misconception that ADO offers no advantage but
> 'popular' does not equate with 'truth'!
ADO is slower than DAO for Jet data. It lacks features that require
you to use *two* external libraries instead of one. It's not native
to the db engine.
It makes *no* sense whatsoever to use ADO for Jet except for the
small handfull of features that Microsoft chose to put in ADO and
not in DAO 3.6. A short list (not complete):
1. UserRoster
2. Byte field and Decimal field alteration
3. Autonumber seed manipulation
>> I for one almost never use Jet tables but still use DAO because
>> it works for everything I need and have never felt any reason to
>> learn something new.
>
> I don't think many people would recommend DAO for non-Jet tables.
It depends on the context. DAO is very good with external data
sources because *Jet* is very good.
> From
> personal experience DAO+Oracle was disastrous and mapping data
> types between DAO and SQL Server couldn't be made to fit :(
In both cases an ADO driver for the specific db engine may be much
better (more full-featured and faster) than the corresponding ODBC
driver. For SQL Server, yes, ADO makes a lot of sense, sure. But for
other db engines, it's a choice that needs to be made based on how
well things work with the drivers provided for it.
David W. Fenton
> Another thing to note is that DAO has still not got enhancements
> for some of the Jet 4.0 features (e.g. creating CHECK
> constraints); ADO (via the OLE DB providers) has received such
> enhancements.
You're confusing the ACCDB version of DAO with the Jet 4 version of
DAO. The former is maintained by the Access group. The latter is
maintained by the Windows group (because Active Directory stores its
data in Jet 4 format). So far as I can figure, the Jet 4 version of
DAO is not being updated any longer, and my guess is that as soon as
Windows gets its database-based file system (formerly WinFS), that
Jet will be dropped from Windows and Jet 4 will be officially dead.
At that point the only version of Jet will be the ACCDB version
(though Access will still likely include the default Jet 4 support,
including support for Jet 4's DAO library).
David W. Fenton
news:Oo2krAvc...@TK2MSFTNGP04.phx.gbl:
> The industry falsehood has always been that
> multi-value systems are not correctly relational, and not
> normalized.
My issue is that the ACCDB is storing the values behind the scenes
in properly normalized data structures and displaying it as a single
field, but you have no access to that data structure.
What happens when you start needing to have multiple attributes for
your values? With a multi-value field, you don't have access to the
structure. But with an explicit many-to-many structure, it's a piece
of cake to add your attributes to the join table.
That said, I can think of many cases where I could have used a
multi-value field. And, indeed, I have stored denormalized
multi-value data in a few applications (and provided a listbox
interface for changing the data stored in the fields), and the new
multi-value field would have made that a lot easier.
David W. Fenton
news:Oo2krAvc...@TK2MSFTNGP04.phx.gbl:
> The popularity of DAO today simply shows that MS could not get rid
> of it, and what really amazing is that MS is working hard to keep
> us happy on this front.
It's not just a matter of popularity. It's a matter of logic. It's
much easier to develop a db-engine specific interface than it is to
develop an abstraction layer that works with multiple db engines.
ADO was a very worthy successor and replacement for ODBC. It added a
lot of things that ODBC had needed for a *very* long time (because
db engines had evolved well beyond the original design of ODBC). But
it couldn't possibly replace something like DAO, which was
specifically designed for Jet. And the reason DAO is so powerful is
that Jet is so powerful, with its ability to intelligently
communicate with all sorts of db engines other than itself. No, it's
not always perfectly efficient because it guesses wrong, but so does
ADO!
Tony Toews [MVP]
>> ADO requires two
>> lines of code not one.
>>
>> The only thing about ADO that is useful to me is that it will allow
>> you to reset the autonumber seed with DAO won't.
>
>CurrentProject.Connection.Execute "ALTER TABLE YourTable ALTER
>autonumber_col INTEGER IDENTITY(1, 1);"
>
>That's _one_ line of code by my reckoning.
Ah, I wasn't very clear. The execute takes one line but the
openrecordset requires two lines.
Vladimír Cvajniga
============================================================
Eleven Reasons Not to Migrate to ADO
by Helen Feddema
--------------------------------------------------------------------------------
a.. Maturity: DAO is a mature technology, with the bugs worked out. ADO,
on the other hand, is brand-new and still quite buggy. Many developers (and
ordinary users!) would prefer to wait out the v. 1.0 stage, and defer using
ADO in real-life applications until it is more stable.
b.. Your code library: If you have been doing Access development for a
while, you probably have a stock of DAO code segments you can pop into new
projects; switching to ADO will require learning new syntax and developing
code from scratch. Why abandon your investment of time and effort?
c.. Working with Access 97: If you are writing code (say VBS code on an
Outlook form) that may work with either Access 97 or Access 2000, DAO will
work, but ADO won't, since Access 97 doesn't support ADO. Outlook forms are
substantially the same in all versions of Outlook, and Outlook VBS code can
run in all versions of Outlook too, so long as recently introduced language
elements are avoided. Using DAO to communicate with Access from Outlook VBS
ensures that your code will run with either Access 97 or Access 2000.
------------------------------------------------------------------------------
Check out our Visual Basic Special!
------------------------------------------------------------------------------
d.. Form and report recordsets: Form and report recordsets in Access 2000
still use DAO exclusively, so you must use DAO to work with recordsets and
recordset clones. Since you must use DAO for these purposes (for example, to
synchronize a form with a record selected in a combo box in a form header),
it is a lot simpler to just use DAO for all your coding, to avoid confusion
between the two object models.
e.. Class prefixes: If you work with both the DAO and ADO object models,
you must prefix all objects that belong to both object models with the
appropriate class name or you may have an error in running your code. This
happens because of objects (like the Recordset object) which belong to both
object models, but which have different properties or methods in DAO and
ADO. If you stick to the DAO object model, you don't need to use class
prefixes; just make sure that DAO is checked in the References dialog and
ADO is not checked.
f.. ISAM and ODBC connectivity: DAO is designed specifically for the Jet
database engine, and thus incorporates ISAM and ODBC connectivity, making
back-end providers look as much like native Jet as possible. This allows for
easier coding (no need for lots of special cases).
g.. Performance: DAO is much more efficient at using the Jet engine than
ADO--as much as 5 to 10 times faster. Faster performance is always welcome!
Additionally, all DAO objects you create use the same Jet session, while in
ADO every Connection object and ADO Data control uses a separate Jet
session, which can lead to Jet running out of resources. This is an
important consideration, at least if you need to use lots of objects in a
project.
h.. Distributing applications: When using DAO, only a small set of files
must be distributed to users' computers. When distributing ADO, all of ADO
and OLE DB (including ODBC) must be included in the setup files package. The
smaller the distribution package, the better, especially if you must use
floppy disks for the setup files.
i.. Wild Cards and Stored Queries: Because ADO uses different query
wild-card characters than DAO, stored queries created in DAO won't work in
ADO (at least if they use wild cards). So, if you don't want to have to
replace all the wild cards used in queries with the new ADO wild cards,
stick to DAO.
j.. Security: DAO has a full Security model, but ADO has no way to specify
the PID. If the System.mdw file is deleted, you can't recreate security
accounts from scratch, and if you don't have a usable backup System.mdw, you
are stuck.
k.. If it ain't broke....: Sometimes it seems as though Microsoft changes
things just for the sake of change. Why else would the set of developer
tools that accompanies Access have a different name and acronym with every
release? This set of tools was called ADK for Access 1.1, ADT for Access
2.0, ODT for Access 95, ODE for Access 97, and now MOD for Access 2000. It's
not exactly the same, but DAO is a robust and highly functional object
model. So long as you are working with Access data sources, there is no need
to move to ADO yet.
"Jack Hudson" <jhh...@sbcglobal.net> píše v diskusním příspěvku
news:jJXOh.10269$JZ3....@newssvr13.news.prodigy.net...
Vladimír Cvajniga
============================================================
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
Return to: vb.oreilly.com
--------------------------------------------------------------------------------
O'Reilly Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
International | About O'Reilly | Affiliated Companies
"Jack Hudson" <jhh...@sbcglobal.net> píše v diskusním příspěvku
news:jJXOh.10269$JZ3....@newssvr13.news.prodigy.net...
Vladimír Cvajniga
"Vladimír Cvajniga" <nos...@thank.you> píše v diskusním příspěvku
news:OZ7E6uz...@TK2MSFTNGP04.phx.gbl...
RoyVidar
<0dpq03hsa3ftnn6ko...@4ax.com>:
> "Jamie Collins" <jami...@googlemail.com> wrote:
>
>>> ADO requires two
>>> lines of code not one.
>>>
>>> The only thing about ADO that is useful to me is that it will allow
>>> you to reset the autonumber seed with DAO won't.
>>
>> CurrentProject.Connection.Execute "ALTER TABLE YourTable ALTER
>> autonumber_col INTEGER IDENTITY(1, 1);"
>>
>> That's _one_ line of code by my reckoning.
>
> Ah, I wasn't very clear. The execute takes one line but the
> openrecordset requires two lines.
>
> Tony
<grin>
From the help file - Execute Method (ADO Connection):
"For a row-returning command string:
Set recordset = connection.Execute(CommandText, RecordsAffected,
Options)
Return value
Returns a Recordset object reference."
defaults to forwardonly, readonly, though.
dim rs as adodb.recordset
dim s as string
s = "select * from mytable"
set rs = currentproject.connection.execute(s, , adcmdtext)
debug.print rs.getstring
For other cursor/locktype, I think, one would need to either rely upon
implicit instantiation (shudder), or use more than one line.
dim rs as new adodb.recordset
dim s as string
s = "select * from mytable"
rs.open s, currentproject.connection, adopenkeyset, adlockoptimistic,
adcmdtext
debug.print rs.getstring
(air code)
--
Roy-Vidar
(PeteCresswell)
> Not being critical here,
>simply curious to know why ADO solutions are not given more frequently. Any
>observations y'all like share?
I've dabbled in ADO - especially when going against SQL Server or Oracle and
retrieving multiple recordsets in one server hit.
But my experience is that writing DAO takes a *lot* less code and once I read
that DAO works better for JET back ends (which most of my projects are), I
decided to stick with DAO unless I could come up with a reason to use ADO (as in
SQL Server-based apps).
--
PeteCresswell
MikeR
Mike
(PeteCresswell)
>In my experience, DAO is WAAAAAAY faster than ADO for Access dbs.
If it is as it seems - an extra layer on top of DAO - it seems almost
inevitable.
(I'm guessing that the thingie that we supply to ADO that lets it read DAO
really just lets ADO translate it's requests into DAO-speak and relay them to
JET).
--
PeteCresswell
David W. Fenton
news:4k1013d6o4cob1pqh...@4ax.com:
> (I'm guessing that the thingie that we supply to ADO that lets it
> read DAO really just lets ADO translate it's requests into
> DAO-speak and relay them to JET).
I don't believe so. I think ADO communicates with Jet directly. But
Access communicates with ADO through an intermediate layer, the name
of which I've forgotten.
Jamie Collins
wrote:
> You're confusing the ACCDB version of DAO with the Jet 4 version of
> DAO.
I am not. I think the mention of 'Jet 4.0' has confused you so allow
me to restate my point in the context of Access 2007 and ACCDB only:
The ACCDB version of the engine has CHECK constraints**: to create one
you must use ANSI-92 Query Mode SQL DDL syntax. You can do this using
the Access 2007 user interface, which uses ANSI-92 Query Mode by
default, or you can use the ACCDB version of the OLE DB provider, with
uses ANSI-92 Query Mode only.
The ACCDB version of DAO uses ANSI-89 Query Mode only (is incapable of
ANSI-92 Query Mode) and, unlike ADO, cannot use the OLE DB provider,
consequently you cannot create a CHECK constraint in the ACCDB engine
using DAO :(
**You need CHECK constraints to be able to create sequenced primary
keys (lowercase, meaning constraints that prevent duplicate data --
and I don't mean the front end forms LOL) e.g. a constraint in a
simple history table to prevent this:
INSERT INTO SalariesHistory (employee_ID, start_date, end_date,
salary_amount)
VALUES (1, #2001-01-01 00:00:00#, #2001-12-31 23:59:59#, 50000)
;
INSERT INTO SalariesHistory (employee_ID, start_date, end_date,
salary_amount)
VALUES (1, #2001-12-01 00:00:00#, #2002-12-31 23:59:59#, 75000)
;
otherwise for the period December 2001 employee_ID = 1 would seemingly
be in receipt of two salary amounts:
SELECT employee_ID, salary_amount
FROM SalariesHistory
WHERE employee_ID = 1
AND #2001-12-15 00:00:00# BETWEEN start_date AND end_date;
would return two rows for 50000 and 75000 respectively, being a
business rule violation in the context of most history tables. A table-
level CHECK constraint is required to prevent overlapping periods.
Jamie.
--
Jamie Collins
wrote:
> > Intermediate Microsoft Jet SQL for Access 2000
> > http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
>
> 2000, that information is EIGHT YEARS OLD.
MSDN articles that are archived content (e.g. some I've seen relating
to Jet 3.51) are marked thus. That article remains the best reference
resource for Access SQL DDL. The Access 2007 Help contains far too
many inaccuracies as regards Access SQL syntax :(
> Microsoft has changed its tune. That is obvious by the way they've
> changed the default references in newly created databases.
Not as obvious as saying, "In previous versions of Access, Data Access
Objects (DAO) was the primary data access method. That has now
changed. Although DAO is still supported, the new way to access data
is with ADO." Show me an article that says "We've changed our tune
about ADO in favour of DAO" as explicitly as that!
> ADO *never* made any sense for Jet data,
> and anyone who understands what a database abstraction layer is
> should see that (ADO is a database abstraction layer; DAO is an
> interface to a particular database engine
Anyone who uses the SQL language understands that abstraction is a
good thing! The point of having an optimizer is that I use SQL to tell
it *what* I want and the optimizer uses its knowledge of the data
engine to determine *how* to achieve this. Both DAO and DAO must go
via the optimizer, that's abstraction.
DAO is faster than ADO but this is only noticeable in, what, less than
one percent of SQL operations across all Access users? If I told you
something could be achieved even faster than DAO by cracking the file
with a hex editor, would this be even better than an interface? Would
you use it in production code?
> Microsoft made a decision (not on logic, but on marketing) to leave
> certain new Jet 4 features out of DAO and put them in ADO, instead.
> That is a flaw in MS's strategy, not in DAO.
MS's strategy may have been the cause but a deficient DAO is a very
real result.
> Now that Jet is again a live development platform, I expect the
> ACCDB version of DAO to gain lots of new features to keep up with
> the development of the ACCDB Jet engine.
I expected it too but they didn't deliver, did they. Do you not see
that DAO (largely) skipped a step, let's call it the Jet 4.0? Why do
you think MSFT will ever catch up this step if they didn't do it for
Access2007? Also consider it is not just support for engine
functionality that DAO lacks, it's the enhanced properties/methods/
events/objects put into ADO still lacking in DAO (not to mention the
fundamental flaws in the DAO object model which one must code around
to prevent memory leaks etc).
> > there are some functionality accessible
> > to ADO and not DAO;
>
> And there are things in DAO that ADO can't do (hence the need for
> JRO).
We've been here before, David. JRO is part of ADO. But yes, even when
you consider ADO as a whole there are some things that DAO can do that
ADO can't. For the overwhelming majority if Access/Jet engine
functionality both DAO and ADO do it equally well, so it's a lifestyle
choice which one you use.
> [ADODB] lacks features that require
> you to use *two* external libraries instead of one.
DAO lacks features that require one to use ADODB!
> ADO is slower than DAO for Jet data.
Granted but, as before, for the majority of operations for the
majority of users it will make no difference. Denormalizing sometimes
gives better performance but to always denormalize would be the wrong
conclusion and some would say that the cost of denormalizing is never
justified (as I do with DAO and its flaky object model).
> It's not native
> to the db engine.
To employ a Fentonism, who gives a rat's arse? That's like telling a
father that there's nothing like a mother's love.
> It makes *no* sense whatsoever to use ADO for Jet except for the
> small handfull of features that Microsoft chose to put in ADO and
> not in DAO 3.6.
So, put another way, there is some sense in using ADO, therefore
agreeing that "ADO offers no advantage" is a misstatement.
Jamie.
--
Jamie Collins
wrote:
> > primary data access method. That has now changed. Although DAO is
> > still supported, the new way to access data is with ADO."
>
> changed the default references in newly created databases.
Agreed, both DAO and ADODB are referenced by default so MSFT considers
them to both be valid choices (alternatively, that using both is a
valid choice). And before you say it, DAO has a higher precedence
because there is more Access DAO code that fails to disambiguate
objects variables whereas the overwhelming majority of ADO code
learned from this mistake and fully-qualifies objects variables with
the appropriate library name. This is analogous as to why ADO is
superior to DAO in terms of object model and peoperties/methods/
events. ADO was built on DAO knowledge and experience but there is no
desire for MSFT to now reengineer DAO to capitalize on ADO successes.
Jamie.
--
Jamie Collins
wrote:
David,
Please help me resolve two apparently contradictory comments in your
post:
1) You say ADO *never* made any sense for Jet data because it is not
native to the db engine.
2) You say DAO for other db engines is a choice that needs to be made
based on how well things work with the drivers provided for it.
Q1. If DAO is not native to other db engines, how can using DAO for
them make any sense?
Q2. Can ADO make sense for Access/Jet if the drivers/providers
provided for it work well?
Jamie.
--
Jamie Collins
wrote:
> > For years now MS has suggested using ADO as DAO is obsolete.
> > However, I still see MVP's giving code solutions using DAO. Not
> > being critical here, simply curious to know why ADO solutions are
> > not given more frequently. Any observations y'all like share?
>
> MS has changed its mind.
Please provide a citation for this.
FWIW I can provide citations for the opposite view, albeit articles I
consider to out of date e.g.
Access 2007 Developer Reference: Converting DAO Code to ADO
http://msdn2.microsoft.com/en-us/bb256407.aspx
Where is the equivalent article for porting ADO with the OLE DB
provider to DAOACE? Put another way, why is the sole Access 2007
article on conversion for _from_ DAO to ADO?
I am prepared to accept that MSFT has changed its mind but if it has I
think you should be aware that MSFT is not being very vocal about it,
hence the reason for the OP's question. FWIW I think the lack of
public announcements is because DAO has not been enhanced (e.g. the
aforementioned missing Jet 4.0 step), so both ADO and DAO are required
in the current Access 2007 era.
> [Microsoft] is now recommending all DAO for Jet data
Please confirm that this does not mean that MSFT recommends not using
ADO for Access/Jet data (otherwise why did they create the
Microsoft.ACE.OLEDB.12.0 provider?)
Jamie.
--
Jamie Collins
wrote:
> I think ADO communicates with Jet directly.
Typo?
> But
> Access communicates with ADO through an intermediate layer, the name
> of which I've forgotten.
OLE DB?
Jamie.
--
Jamie Collins
> [ADO seems] an extra layer on top of DAO
>
> (I'm guessing that the thingie that we supply to ADO that lets it read DAO
> really just lets ADO translate it's requests into DAO-speak and relay them to
> JET).
ADO uses DAO?! Your guess is completely wrong. Care to guess how much
remains of your credibility on the subject of ADO vs DAO? (try zero)
Jamie.
--
Jamie Collins
> In my experience, DAO is WAAAAAAY faster than ADO for Access dbs.
> Mike
In my experience, it rarely makes any different in real terms. That
said, I ceased using DAO seriously some years ago, as I suspect is the
case with you and ADO (assuming you ever used ADO seriously), so such
subjective sweeping statements aren't worth the paper they are written
on. Now, if you care to post some code to measure the difference...?
And since when was sheer performance the most important thing about
DMBS? What would you choose between the wrong answer in the blink of
an eye and the correct answer in due course? Denormalized designs
often give the best performance...
Jamie.
--
Jack Hudson
Where does ADO.Net fit into this discussion, i.e., does ADO.Net have a place
in Access VBA?
Again, thanks for the info.
Best regards, Jack
"Jamie Collins" <jami...@googlemail.com> wrote in message
news:1175510304.7...@p15g2000hsd.googlegroups.com...
Douglas J. Steele
news:QtaQh.5172$Kd3....@newssvr27.news.prodigy.net...
> Thanks for all the comments. Still confused, but better informed.
>
> Where does ADO.Net fit into this discussion, i.e., does ADO.Net have a
> place in Access VBA?
Not at the present time it doesn't.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
(PeteCresswell)
>> [ADO seems] an extra layer on top of DAO
>>
>> (I'm guessing that the thingie that we supply to ADO that lets it read DAO
>> really just lets ADO translate it's requests into DAO-speak and relay them to
>> JET).
>
>ADO uses DAO?! Your guess is completely wrong. Care to guess how much
>remains of your credibility on the subject of ADO vs DAO? (try zero)
Who claimed credibility? If anybody got the impression that I did, I would
correct that misapprehension. After all, I *did* say "I'm guessing...."..
--
PeteCresswell