Now's a fine time to ask but ADO or DAO?

[Fred Wilson]

Hello all,

I have an MS Access 2003 database that I use as a tool to manage some
reports and data dumps from an oracle.

Anyway, which is the better records set to use, ADO or DAO and why?

Thanks,
Fred

 

[Arvin Meyer [MVP]]

If you disambiguate, both can be used. IMO, it would be DAO for most of the
work. ADO is designed to be faster with SQL-Server and for the most part it
is. It does not have all the functionality of DAO, but will do the majority
of what you may need to do. ADO is not being developed any more in favor of
ADO.NET. The 2 are not alike. That alone would put a damper on my deployment
plans.

 

[Arvin Meyer [MVP]]

I agree the general sentiment but I think your post contains a
misstatement: I would say an ADO recordset has much more in the way of
functionality than a DAO recordset: an ADO recordset can be
disconnected, asynchronous (DAO recordsets have no events),
hierarchical, fabricated, used for paging, persisted on disk (including
XML format), etc.

My comment on "all the functionality" was not meant to be specific to
recordsets, but to other things like security, replication, etc. While ADO
does address these things, it's not as complete, and it's through auxiliary
processes like JRO and ADOX.

I use them both, but use DAO more often because I use JET more often than
SQL-Server. I only use ADO with SQL-Server where it is for the most part
faster and, usually, easier.

 

[David W. Fenton]

My comment on "all the functionality" was not meant to be specific
to recordsets, but to other things like security, replication,
etc. While ADO does address these things, it's not as complete,
and it's through auxiliary processes like JRO and ADOX.

And even then, with all those auxiliaries, it doesn't provide access
to everything in Jet. For instance, I find JRO completely useless
for replication, because it lacks way too many features that are
provided in the combination of DAO and the TSI Synchronizer. The
whole ADO thing was very poorly handled by Microsoft and I recommend
against even bothering with ADO in Jet-only scenarios except for the
one or two Jet features that are available only through ADO.

 

[dbahooker]

dude DAO is dead; don't use it for any reason

-Aaron

 

[dbahooker]

I dont believe that there is ANY verbage that is supposed in DAO but
not in ADO

I can add columns; ALTER TABLE ADD COLUMN

use SQL Server; the bottom line is that it's ridiculous to have 2 Data
Access Layers; one for a Junior Database and one for the Enterprise
Level Database.

This stuff is basic stuff that MS taught us what.. 10 years ago?

You kids are overthinking this; don't use DAO for anything ever

-Aaron

[ADO] doesn't provide access
to everything in Jet. For instance, I find JRO completely useless
for replication, because it lacks way too many features that are
provided in the combination of DAO and the TSI Synchronizer. The
whole ADO thing was very poorly handled by Microsoft and I recommend
against even bothering with ADO in Jet-only scenarios except for the
one or two Jet features that are available only through ADO.

As you say, there is some Jet functionality available only to DAO and
some functionality available to only to ADO (more correctly, the MS Jet
4.0 OLE DB provider). I'm willing to believe JRO is useless for Jet
replication. But returning to the OP's question:

I think the answer must be the ADO recordset because it has more
functionality. Sorry to be repetative but disconnected, asynchronous,
fabricated, hierarchical, pesisted on disk (even combinations thereof):
those are things that can be done with an ADO recordset that cannot be
done with a DAO recordset.

Jamie.

--

 

[Larry Linson]

. . . But returning to the OP's question:


I think the answer must be the ADO recordset because it has more
functionality. Sorry to be repetative but disconnected, asynchronous,
fabricated, hierarchical, pesisted on disk (even combinations thereof):
those are things that can be done with an ADO recordset that cannot be
done with a DAO recordset.

It's only "better" if the features you list are required, or useful, in
addressing the business problem -- and they often are neither required nor
useful. And, in my experience, it is mostly or always in situations where
the data is being kept in Microsoft SQL Server where they are useful -- a
case that Arvin already addressed as being appropriate for ADO.

In fact, they are often "useful" only to demonstrate the "advanced"
techniques used by the developer, that is, to "wow" clients with how
up-to-date and accomplished the developer is with the "new stuff." My
clients are 'way too practical to be impressed -- they want a quick,
efficient, low-cost solution to the business problem. And, their "BS
repellent" is to hustle the BSer out the door.

Larry Linson
Microsoft Access MVP

 

[dbahooker]

no Larry.. better means 'more relevent'

'more likely to be supported in the future'

not 'whats going to have the easiest upgrade path to vista' but 'whats
going to run without an upgrade path, plug and play'

from what i've seen.. Vista comes with a new version of ADO; but not a
new version of DAO lol

DAO hasn't been included with Windows for what.. 5 years?


yes; I support your concept of a 'BS repellent'

but finding an ancient Access programmer to write against a library
that has been dead-- for 5 years-- for good reason (because of hangs)

that sets off my 'bs detector' for sure

your premise is that 'old development was in DAO and it's not worth the
effort to change it'

were asking about NEW DEVELOPMENT

whats' easier.

anything that you can do in DAO; I can do in ADO with my eyes closed.

most importantly; ADO provides a much cleaner upgrade to ADOMD - as in
MULTI-DIMENSIONAL

try doing THAT with DAO (and btw, if your obsolete database programmer
doesn't speak SQL Server or Analysis Services; find someone that does)

-Aaron

 

[JK]

Arvin,
(Other please feel free to replay)

Whilst on the subject, I wander if I can increase speed:

I have a table that maintains holidays rules/date around the world, 800
records so far and growing. There are other "supporting" tables needed, but
let leave it out for now.

Background:
-------------
Each holiday has its own rule. Some of the rules are simple (eg 25 Dec, or
4th Thursday in Nov), others are more involved (eg. if an holiday falls on a
weekend, Move it to Monday). Yet others depend on another holiday (eg. 46
days after Easter Sunday).

In addition, each rule has its own calendar (Gregorian, Hebrew, Islamic so
far) with or without a cross-over, e.g Easter Sunday falls on the First
Sunday after the Jewish Passover (with exceptions)

I have the relevant routines to calculate the fields "ThisYearDate" and
"NextHolidayDate" (not always the same, "US presidential Election" has Null
value in isYearDate" (2006) but has a value in "NextHolidayDate", 4 Nov,
2008), *NO PROBLEM* thus far.

Each time a user enter a record, through the OnCurrent event, those two
dates get updated through a combination of a Private and a Public Functions.
The same happens, through AfterUpdate event of any field that affects the
rule (Only Admin can do that), still no problem there.

Regardless of the calendar used to calculate the holiday date, *both*
"ThisYearDate" and "NextHolidayDate" return the date in Gregorian calendar,
eg. Jewish New Year which is the first day of the year in the Hebrew
calendar will return 23 Sep 2006 (this year) and 13 Sept 2007 (next date)

The Problem
--------------
Once a year, soon after new year day, I run an update query to update all
the dates in the table. That query runs for about *15 minutes * with 800
records, not surprising under the circumstances described above.

The Question (finally)
----------------------
Having written those a few years back when I did not understand what
Recordset or DAO means, I wander whether I should modify the routine for a
yearly update to reduce the time of a full update. I am in two minds:

1. If ain't broken, don't fix it
2. If I can speed up the operation dramatically using DAO, I can allow
users to do a general update any time.

Thus far all the calculations are done by whatever DLookup()'s returns.
Bearing in mind that if a holiday date is linked to another holiday, that
other holiday has to be calculated first.

Appreciated your comments

Regards
Jacob

 

[dbahooker]

I'd just prepopulate it for every year form here to kingdom come

I mean; is the day of week for July 2048 going to change?

for the records; this is frequently touched on in the data warehousing
world as a 'date dimension'

what's exciting about analysis services is that you can roll up your
calender in a dozen different directions; based on say 'jewish
calender' and 'fiscal calendar' and 'holiday calender' and 'physical
calender' and just drag and drop to show different versions.

it's free with SQL Server; it's called 'analysis services' but I really
reccomend a book or a class or 3

HTH

-Aaron

 

[David W. Fenton]

You're in my kill file, so I've obviously already concluded that
you're a worthless idiot, but I can't say exactly what type, but
these kinds of statements need rebuttal:

I think the answer must be the ADO recordset because it has more
functionality. Sorry to be repetative but disconnected,
asynchronous, fabricated, hierarchical, pesisted on disk (even
combinations thereof): those are things that can be done with an
ADO recordset that cannot be done with a DAO recordset.

But why in the world would you need those, except in very
specialized cases? Most of those are most helpful with a server
database, which Jet is not, so the benefits of using ADO on a
regular basis are simply not going to be there.

It is patently ridiculous to use a non-native data access method on
a regular basis just because there are a few rare cases where the
non-native method can do more things. Use DAO as a matter of course,
and use ADO in the rare cases where ADO provides something that DAO
does not.

Frankly, I question the value of any of the features you mention
when running with a Jet back end. I've certainly never ever needed
any of them myself in my 10 years of near full-time work with
database applications.

 

[Fred Wilson]

WOW! That was almost like asking about religion or motor oil.

The biggest thing I think I saw here was that because I only use JET
that DAO is more than adequate.

WOW again. Thanks for the input.

 

[aaron.kempf]

Fred

you're full of fucking shit

DAO causes HANGS
Don't use it for anything and don't listen to these DAO dipshits that
are still stuck in the '90s

for that point; anyone that uses MDB should be fired and then spit
upon.. but the bottom line is that it's NEVER acceptable to use MDB for
anything.

if you care enough to build a database; and you look at your calendar
and you see that the year is greater than 2000.. you shoudl realize
that MS has been giving away a freeware version of SQL Server since '99
and MDB is thus completely obsolete and pointless.

Lose the training wheels.

MDB is NOT a valid option.
Moreover; using DAO is even _LESS_ of an option... it HANGS YOUR
FUCKING MACHINE ALL THE TIME

-Aaron

 

[Larry Linson]

I'm interested. Please give an example from your
experience where you used the ADO recordset
features mentioned for data stored in Microsoft
SQL Server that wouldn't also be useful for data
stored in Access/Jet.

Perhaps I wasn't sufficiently clear... the methods you describe as
justifying ADO are, in my experience, not necessary in the database and
other applications that I have addressed in nearly 50 years in the computer
business. Perhaps ADO makes those techniques easier for you, even with Jet,
but because I don't implement business applications in that manner, I don't
find ADO useful.

My clients prefer applications that are implemented in a simple,
cost-effective manner that meets their business requirements. I comply with
their wishes, and that does not include using complex techniques that
require ADO. MDB, DAO, ODBC, and SQL Server works nicely.

Others have said they think that ADP, ADODB, and SQL Server is good, but I
note that the Access team at Microsoft now is back to recommending the
traditional MDB, DAO, ODBC, and SQL Server as the implementation approach of
choice for Access clients.

Larry Linson
Microsoft Access MVP

 

[Fred Wilson]

Aaron,

Thank you for your very sophisticated and informative answer. I'll give
it some thought. However, please keep in mind, that some of us are
limited by the tools and policies used within out organizations. In my
case we are not allowed to have non-standard items installed on our
machines so locally we are limited to MS Office and its components.

Any of the Oracle and or SQL servers that are in our company are either
leased with their associated systems or they have already been validated
as they are operating.

Maybe I am not smart enough to have figured out that some of the hang
ups I've experienced were from DAO but this is what I am comfortable
with for now.

Once again, thanks for your elegant answer, perhaps you could generate a
periodical or other teaching instruments from this vast knowledge you
are demonstrating.

Humbly,
Fred

 

[Fred Wilson]

Aaron,

Thank you for your very sophisticated and informative answer. I'll give
it some thought. However, please keep in mind, that some of us are
limited by the tools and policies used within out organizations. In my
case we are not allowed to have non-standard items installed on our
machines so locally we are limited to MS Office and its components.

Any of the Oracle and or SQL servers that are in our company are either
leased with their associated systems or they have already been validated
as they are operating.

Maybe I am not smart enough to have figured out that some of the hang
ups I've experienced were from DAO but this is what I am comfortable
with for now.

Once again, thanks for your elegant answer, perhaps you could generate a
periodical or other teaching instruments from this vast knowledge you
are demonstrating.

Humbly,
Fred

 

[Fred Wilson]

Aaron,

Thank you for your very sophisticated and informative answer. I'll give
it some thought. However, please keep in mind, that some of us are
limited by the tools and policies used within out organizations. In my
case we are not allowed to have non-standard items installed on our
machines so locally we are limited to MS Office and its components.

Any of the Oracle and or SQL servers that are in our company are either
leased with their associated systems or they have already been validated
as they are operating.

Maybe I am not smart enough to have figured out that some of the hang
ups I've experienced were from DAO but this is what I am comfortable
with for now.

Once again, thanks for your elegant answer, perhaps you could generate a
periodical or other teaching instruments from this vast knowledge you
are demonstrating.

Humbly,
Fred

 

[Arvin Meyer [MVP]]

Jacob,

I commend you for your tenacity in developing this. I'm sure it was quite a
task putting all the formulas together to build this database. Someday, I'd
like to take a look at the way you did it.

The answer to your question became clear in the very last sentence of your
post. As soon as you used the word "DLookup" I knew that you could probably
increase the speed drastically. Here's some code I wrote to replace DLookup
almost 10 years ago, and updated last year. The idea was given to me by
Trevor Best who wrote a bunch of lookup replacement functions for Access
2.0. This function is pretty generic, so you can use it a lot.:

Public Function FastLookup(strFieldName As String, strTableName As String,
strWhere As String) As Variant
'******************************************************************************************************* ' Name: FastLookup ' Purpose: Fast replacement for DLookup ' Inputs: strFieldName As String ' strTableName As String ' strWhere As String ' Returns: Variant ' Author: Arvin Meyer ' Date: April 9, 1997 ' Updated: June 15, 2005 ' Usage: ' If FastLookup("FieldName", "TableName", "FieldName ='" &Me.txtControlName & "'") = Me.txtControlName Then ' MsgBox "This value exists. Please choose again", vbOKOnly,"Duplicate!" ' Me.txtControlName.SetFocus ' Exit Sub ' End If ' '***************************************************************************************************************On Error GoTo Error_HandlerDim db As DAO.DatabaseDim rst As DAO.RecordsetDim Temp As VariantSet db = CurrentDb If strWhere = "" Then Set rst = db.OpenRecordset("Select [" & strFieldName & "] From [" &strTableName & "]", dbOpenSnapshot) Else Set rst = db.OpenRecordset("Select [" & strFieldName & "] From [" &strTableName & "] Where " & strWhere, dbOpenSnapshot) End If If Not rst.BOF Then rst.MoveFirst Temp = rst(0) Else Temp = Null End If rst.Close FastLookup = TempExit_Here: On Error Resume Next rst.Close Set rst = Nothing Set db = Nothing Exit FunctionError_Handler: MsgBox Err.Number & ": " & Err.Description Resume Exit_HereEnd Function--Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com"JK" <[email protected]> wrote in messagenews:[email protected]...> Arvin,> (Other please feel free to replay)>> Whilst on the subject, I wander if I can increase speed:>> I have a table that maintains holidays rules/date around the world, 800records so far and growing. There are other "supporting" tables needed, butlet leave it out for now.>> Background:> -------------> Each holiday has its own rule. Some of the rules are simple (eg 25 Dec, or> 4th Thursday in Nov), others are more involved (eg. if an holiday falls ona> weekend, Move it to Monday). Yet others depend on another holiday (eg. 46> days after Easter Sunday).>> In addition, each rule has its own calendar (Gregorian, Hebrew, Islamic sofar) with or without a cross-over, e.g Easter Sunday falls on the FirstSunday after the Jewish Passover (with exceptions)>> I have the relevant routines to calculate the fields "ThisYearDate" and"NextHolidayDate" (not always the same, "US presidential Election" has Nullvalue in isYearDate" (2006) but has a value in "NextHolidayDate", 4 Nov,2008), *NO PROBLEM* thus far.>> Each time a user enter a record, through the OnCurrent event, those twodates get updated through a combination of a Private and a Public Functions.The same happens, through AfterUpdate event of any field that affects therule (Only Admin can do that), still no problem there.>> Regardless of the calendar used to calculate the holiday date, *both*"ThisYearDate" and "NextHolidayDate" return the date in Gregorian calendar,eg. Jewish New Year which is the first day of the year in the Hebrewcalendar will return 23 Sep 2006 (this year) and 13 Sept 2007 (next date)>> The Problem> --------------> Once a year, soon after new year day, I run an update query to update allthe dates in the table. That query runs for about *15 minutes * with 800records, not surprising under the circumstances described above.>> The Question (finally)> ----------------------> Having written those a few years back when I did not understand whatRecordset or DAO means, I wander whether I should modify the routine for ayearly update to reduce the time of a full update. I am in two minds:>> 1. If ain't broken, don't fix it> 2. If I can speed up the operation dramatically using DAO, I can allowusers to do a general update any time.>> Thus far all the calculations are done by whatever DLookup()'s returns.Bearing in mind that if a holiday date is linked to another holiday, thatother holiday has to be calculated first.>> Appreciated your comments>> Regards> Jacob>>>> "Arvin Meyer [MVP]" <[email protected]> wrote in message> If you disambiguate, both can be used. IMO, it would be DAO for most of>> the work. ADO is designed to be faster with SQL-Server and for the most>> part it is. It does not have all the functionality of DAO, but will dothe>> majority of what you may need to do. ADO is not being developed any more>> in favor of ADO.NET. The 2 are not alike. That alone would put a damperon>> my deployment plans.>> -->> Arvin Meyer, MCP, MVP>> http://www.datastrat.com>> http://www.mvps.org/access>> http://www.accessmvp.com>>>> "Fred Wilson" <[email protected]> wrote in message>> Hello all,>>>>>> I have an MS Access 2003 database that I use as a tool to manage some>>> reports and data dumps from an oracle.>>>>>> Anyway, which is the better records set to use, ADO or DAO and why?>>>>>> Thanks,>>> Fred>>>>>>>

 

[aaron.kempf]

Maybe MS Access team reccomends that a FAT LAZY RETARDED SENILE
OBSOLETE OLD MAN continue to use MDB format. Maybe they pity you and
they don't think that you have the mental capacity to leave the '90s.


and furthermore, RE:

traditional MDB, DAO, ODBC, and SQL Server as the implementation
approach of

choice for Access clients.

#1) traditional isn't a FAVORABLE WORD. How about 'obsolete' instead.

#2) you fail to mention OLE DB... if you combine MDB and SQL tables
then you're combining 2 different libraries -- and dependencies-- ODBC
and OLE DB. So you have MORE DEPENDENCIES THAN WE DO. You can't do a
clientside TRACE. you can't have a simple consistent 'custom linked
table manager' because you're pulling from 2 different sets of
dependencies; OLEDB and ODBC. It's ridiculous to claim that is
practical or simple.

#3) ADO has been included in Windows for years and years and years and
years.
DAO hasn't been included in Windows for years and years and years
and years.

#4) DAO causes hangs... I don't need to explicity close jack shit your
buggy ass library is DEAD.

#5) MDB can't scale to 25 megabytes for a dozen users. I've had
scalability / stability problems for at least a dozen clients with
'merely 25 mb of data' and a handful of users.

#6) 1 gigabyte isn't enough for a LOT of databases... how do you get
around this? ADP doesn't have this limit. I mean seriously.. what do
you do?

#7) MS Access doesn't have a practical 'Database Tuning Advisor' it
doesn't include any enterprise - level tools like this.

#8) MS Access doesn't have a practical 'Database Profiling' it doesn't
include any enterprise - level tools like this.

#9) SQL Server supports TRIGGERS.. MDB doesn't support triggers.

#10) count the steps to have a form bound to a sproc with 2 parameters
in MDB vs the same task in ADP. it's about 100 steps for you to call
a simple sproc with parameters-- and it's barely a single step for me.


Maybe MS Access team reccomends that a FAT LAZY RETARDED SENILE
OBSOLETE OLD MAN continue to use MDB format. Maybe they pity you and
they don't think that you have the mental capacity to leave the '90s.

But where I come from; ADP is easier to develop than MDB dozens of
reasons.

I don't think that there is a knowledgable person in the world that
seriously thinks that MDB is a serious competitor to SQL Server.

Yes; I would rather you dipshits build in MDB format than in XLS for
example.

But the fact of the matter is that you con your customers into buying
CRIPPLEWARE; ADP is easier to manage; easier to secure.. it doesn't
have a 1gb limit ROFL.. it has free versions for the database SERVER
and most importantly

SQL SERVER WILL UTILIZE ADVANCES IN HARDWARE, INCLUDING LARGE MEMORY
CAPACITIES AND DUAL CORE PROCESSORS.

WITH THE DROP IN HARDWARE PRICES; THE PRACTICALITY OF HAVING A SINGLE
PROCESSOR DUAL CORE DATABASE SERVER WILL FIT ALL OF THE DATABASE
PROCESSING NEEDS OF MANY COMPANIES.

ANYONE THAT USES MDB IS STUCK IN THE 90s..

And I for one-- am sick and tired of being woken up at 3am to deal with
MDB locking problems.

With Access Data Proejcts; you can use Query Hints like 'with (nolock)'
that make multi-user applications practical.

We have real ETL tools; what do you have.. Docmd.TransferDatabase??

ROFL

My OLAP shit is 100,000 times more powerful than any reporting app that
you've built using MS Access.

And I'm talking to each and every one of you MDB idiots.

My OLAP shit is 100,000 times more powerful than any reporting app that
you've built using MS Access.

If MS Access included a free Olap client-- similiar to MS Excel did
with 'offline cubes' and it was practical and simple and performant?

then maybe it would be a decent solution.

but there are 100s of reasons to use SQL Server instead of your silly
little MDB.

And I'm SICK AND FUCKING TIRED OF THE DAO BULLSHIT COMING OUT OF YOUR
MOUTH
DAO

 

[aaron.kempf]

Larry;

I think that you say things like this so that you can profit
financially-- you are justifying your own antiquated systems-- when ADO
is superior in every fashion.

DAO isn't stable against Access; it isn't practical against SQL Server;
and it just flat out doesn't work against the Oracles and DB2 of this
world.

That to me; means that DAO is an awful friggin choice.

I would like to see that in writing; from someone at Microsoft.
Because I just flat out don't believe you.

because my friend and he works at MSFT... he says the opposite- that
ADP is a great solution for many small medium and larger businesses..
and that MDB can't scale to meet the needs of most medium sized
business

I find the usage of a library-- for example DAO-- that didn't ship in
Windows XP and hasn't been included in MDAC in FIVE YEARS-- I find that
just REVOLTING.

get a fucking clue jackass there is no DAO revival

there is a new version of ADO in Vista.. is there even a new version of
DAO in Vista?
Or just the new and improved flavor?



-Aaron