ADO vs DAO

[awsmitty]

I am using Access 2003. It is a relatively a fresh install; I haven’t
changed much. Does Access 2003 use DAO or ADO? How can I find out? And how
can I change it if I need to

 

[Banana]

I am using Access 2003. It is a relatively a fresh install; I haven’t
changed much. Does Access 2003 use DAO or ADO? How can I find out? And how
can I change it if I need to

Basically you can use either or even both.

Normally, I just use DAO 95% of the time because that's JET/ACE's
"native" interface and thus is better optimized. 5% of the time I do use
ADO (I should note that ADO probably won't be getting any
updates/enhancements since they moved to ADO.NET which is completely
different) are usually when I have a ODBC data source and I need to use
the backend's SQL dialect rather than what is understood by JET without
losing the updatability. (e.g. I can bind a result of stored procedure
to a form for example using ADO)

 

[John W. Vinson]

I am using Access 2003. It is a relatively a fresh install; I haven’t
changed much. Does Access 2003 use DAO or ADO? How can I find out? And how
can I change it if I need to

It defaults to ADO but it can use either.

Open any module in design view, and select Tools... References from the menu.
Check "Microsoft DAO 3.6 Object Library" to use DAO, or "Microsoft ActiveX
Data Objects 2.8" for ADO; you can even do both, but if you do then you must
be very careful to explicitly Dim objects from the proper library. For example
both libraries have a Recordset object... but they are *different* objects.

Microsoft (and quite a few Access developers) now use DAO preferentially. ADO
might have been a good idea but never really caught on, and should only be
used if you specifically need to do so and have a good reason!

 

[Armen Stein]

It defaults to ADO but it can use either.

Hi John, Access 2003 defaults to DAO. Two earlier versions (2000 and
XP/2002) defaulted to ADO, but they saw the error of their ways and
switched back to DAO after that.

See http://allenbrowne.com/ser-38.html and search for the word
"unfortunately".

you can even do both, but if you do then you must
be very careful to explicitly Dim objects from the proper library. For example
both libraries have a Recordset object... but they are *different* objects.

Actually, if you have both DAO and ADO in the Reference list you don't
need to qualify them explicitly (using DAO. or ADODB.), although I'll
agree it's a good practice.

In the case of an ambiguity (like a non-qualified Recordset), Access
will use the first one in the list. If you want the *second* one, you
need to qualify it.

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

In the case of an ambiguity (like a non-qualified Recordset),
Access will use the first one in the list. If you want the
*second* one, you need to qualify it.

I think that's just insane. I don't ever use a reference to ADO, but
I always qualify all my DAO objects when I define them. I just think
it's good coding practice.

 

[awsmitty]

John,

“and should only be used if you specifically need to do so and have a good
reason!â€

John, I don’t even know what I’m doing, much less have a good reason or any
reason for that matter.

I willy nilly just created a module just so I could check it out, ADO or
DAO. Both are check, DAO & ADO. So I assume from what you said, access is
defaulting to ADO, which seems consistent with what I'm observing.

Here's why I ask. I'm still on the "let's automate the dispatch office"
project. I think you're familiar with what I'm trying to do. I am at a
point where I need to take input data the user has entered on a subform and
enter this into a table, a new, empty table I should add. I’m having a heck
of a time. At first I was using, you guessed it, DAO code. I kept getting
errors about object mis-match. I couldn’t figure it out; I tried ADO and
low and behold. I can at least get it to:

Dim cnn1 As adodb.Connection
Dim rst As New adodb.Recordset
rst.ActiveConnection = cnn1
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "tblPatronInfo", CurrentProject.Connection, adOpenStatic

etc.

Which now brings me back to your above quote and another question? Should I
start over? I don’t have any real reason to use ADO, it’s just that was the
default. It may sound ridiculous to start over, but I really haven’t made
that much progress. I have the tables and their structures and their
relationships pretty well lined out. I do think they are "nomalized". The
problem is I’m rather sure they’re all ADO, perhaps even the excel table I
imported from the raw csv file from Mapsco. There really isn’t that much
code at this point. If DAO is preferable, now would be the time to switch.

Let me know what you think.

 

[Arvin Meyer [MVP]]

now brings me back to your above quote and another question? Should I
start over? I don't have any real reason to use ADO, it's just that was
the
default. It may sound ridiculous to start over, but I really haven't made
that much progress. I have the tables and their structures and their
relationships pretty well lined out. I do think they are "nomalized".
The
problem is I'm rather sure they're all ADO, perhaps even the excel table I
imported from the raw csv file from Mapsco. There really isn't that much
code at this point. If DAO is preferable, now would be the time to
switch.

It will probably benefit you to start over, now or at some time in the very
near future. ADO support is slowly waning, and there aren't any more
updates. DAO is (and actually has been ) the preferred method of most
developers.

 

[John W. Vinson]

John,

“and should only be used if you specifically need to do so and have a good
reason!”

John, I don’t even know what I’m doing, much less have a good reason or any
reason for that matter.

I willy nilly just created a module just so I could check it out, ADO or
DAO. Both are check, DAO & ADO. So I assume from what you said, access is
defaulting to ADO, which seems consistent with what I'm observing.

I was mistaken: A2000 and 2002 defaulted to ADO; A95, 97, 2003 and later to
DAO.

Here's why I ask. I'm still on the "let's automate the dispatch office"
project. I think you're familiar with what I'm trying to do. I am at a
point where I need to take input data the user has entered on a subform and
enter this into a table, a new, empty table I should add.

Why?

It's VERY VERY RARE that you would ever create a new table in routine use of a
database. Why do you feel that you need to do so?

I’m having a heck
of a time. At first I was using, you guessed it, DAO code. I kept getting
errors about object mis-match. I couldn’t figure it out; I tried ADO and
low and behold. I can at least get it to:

Dim cnn1 As adodb.Connection
Dim rst As New adodb.Recordset
rst.ActiveConnection = cnn1
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "tblPatronInfo", CurrentProject.Connection, adOpenStatic

All of which can be done with equivalent DAO code... and none of which are
necessary just to create a new table; a MakeTable query will do so with *no
code at all*.

etc.

Which now brings me back to your above quote and another question? Should I
start over? I don’t have any real reason to use ADO, it’s just that was the
default. It may sound ridiculous to start over, but I really haven’t made
that much progress. I have the tables and their structures and their
relationships pretty well lined out. I do think they are "nomalized". The
problem is I’m rather sure they’re all ADO, perhaps even the excel table I
imported from the raw csv file from Mapsco. There really isn’t that much
code at this point. If DAO is preferable, now would be the time to switch.

There is no such thing as "ADO tables" or "DAO tables". The tables in Access
2003 are JET 4.0 tables; you can write VBA code manipulating JET 4.0 tables in
DAO, or in ADO. The table structure is not affected by the code.

Let me know what you think.

I think you may need to step back and reevaluate what you're trying to
accomplish, and whether you haven't perhaps dived too deep into doing things
in a complicated way with code, which could be done more simply - either with
DAO code, or perhaps with no code at all.

 

[awsmitty]

kewl, ... onward, through the fog ==>

I will probably be posting some tme soon some questions about moving data
from subform and subsubform to a table. I am not having much luck at the
moment

Thannnks again

 

[John W. Vinson]

kewl, ... onward, through the fog ==>

I will probably be posting some tme soon some questions about moving data
from subform and subsubform to a table. I am not having much luck at the
moment

Thannnks again

Since the data is NOT in the subform nor in the subsubform, but in a Table, I
would guess that you'll want to either (a) use an Append query, or more likely
(since storing the same data redundantly in two different tables is never a
good idea) change the Recordsource of one or more of these forms so that the
data that you want to see is visible without moving it at all.

 

[Armen Stein]

I think that's just insane. I don't ever use a reference to ADO, but
I always qualify all my DAO objects when I define them. I just think
it's good coding practice.

Hey, careful who you're calling insane!

As I said in the sentence just prior to your clip, I already said it
was good coding practice to qualify. I was just pointing out that it
isn't strictly necessary.

If you have a ton of legacy code that assumes DAO, then you add an ADO
reference below it for some new features, you don't absolutely need to
go back and qualify all that old code for DAO. You just need to
qualify the new ADO code. I'm just saying it will work fine - I've
seen it.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

Hey, careful who you're calling insane!

I didn't call you insane -- I was just saying that what you
*suggested* was insane.

Now if you want me to speculate on the reason why you'd suggest
something insane, that's a different story.

As I said in the sentence just prior to your clip, I already said
it was good coding practice to qualify. I was just pointing out
that it isn't strictly necessary.

If you have a ton of legacy code that assumes DAO, then you add an
ADO reference below it for some new features, you don't absolutely
need to go back and qualify all that old code for DAO. You just
need to qualify the new ADO code. I'm just saying it will work
fine - I've seen it.

I never use an ADO reference, but I hardly ever use ADO.

 

[David W. Fenton]

I am at a
point where I need to take input data the user has entered on a
subform and enter this into a table, a new, empty table I should
add.

You don't need either DAO or ADO. You need a form. Forms are what
you should be using for entering and editing data in Access --
that's the whole point of Access, that you don't need to write
complicated code to manage your data.

Now, many of us do all sorts of things in DAO (and some in ADO), but
that's for things at a substantially higher level of complexity than
simply editing tables.

 

[David W. Fenton]

I will probably be posting some tme soon some questions about
moving data from subform and subsubform to a table. I am not
having much luck at the moment

I would suggest that you go back to the beginning and learn to use
Access interactively, i.e., point and click, using the Access UI to
create tables and then to create forms that allow you to edit your
data and reports that allow you to display and print it.

You need to understand interactive Access well before you ever start
delving into writing VBA code, and either DAO or ADO to manipulate
data. Failing to do that means you'll be flailing around with
complexities that you could avoid if you just learned the Access way
of doing things.

 

[Tom Wickerath]

Hi AWSmitty,

Here's why I ask. I'm still on the "let's automate the dispatch office"
project. I think you're familiar with what I'm trying to do. I am at a
point where I need to take input data the user has entered on a subform and
enter this into a table, a new, empty table I should add.

You should not need to enter your data into "a new, empty table". Doing so
suggests that you are working with a non-normalized database design, a.k.a.
an "Access spreadsheet". You should spend some time gaining an understanding
of database design and normalization before attempting to build something in
Access (or any RDBMS software for that matter). Here are some links to get
you started. Don't underestimate the importance of gaining a good
understanding of database design. Brew a good pot of tea or coffee and enjoy
reading!

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
(See the first download titled "Understanding Normalization")

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>

Be sure to read the first two papers, by Michael Hernandez, author of
"Database Design for Mere Mortals".

I’m having a heck
of a time. At first I was using, you guessed it, DAO code. I kept getting
errors about object mis-match. I couldn’t figure it out; I tried ADO and
low and behold. I can at least get it to....

If you had the ADO reference with a higher priority, versus the DAO
reference, then I'm willing to bet that you encountered run-time error 13:
Type Mismatch, because you were getting an ADO recordset by default, but
attempting to use DAO code with this recordset. Here is an article that
explains why it is so very important to fully qualify these objects:

ADO and DAO Library References in Access Databases
http://www.accessmvp.com/TWickerath/articles/adodao.htm


Like David Fenton states in a different posting in this thread, spend your
time learning to use Access with the point and click GUI (Graphical User
Interface) first, before concerning yourself with the complexities of VBA
code, be it DAO, ADO or the Microsoft flavor of the month.

You will be best served to study database design, along with learning what
characters and reserved words to avoid using, when assigning names to
anything in Access. Here are some links to help you learn for laying a proper
foundation:

Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp →
http://www.xoc.net/standards/rvbanc.asp#Access

Using a Naming Convention
http://msdn2.microsoft.com/en-us/library/aa164529(office.10).aspx

Reserved Words
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Good Luck, and I hope you enjoy your venture in using Access!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________