Using ADO in an mdb application

[Bill Murphy]

I have a large and complex Access 2000 application where the front end and
back end mdb files are split. Both mdb's currently reside on a Citrix
server, and the performance is very good. However, I need to move the
backend mdb data to SQL Server, and I'm concerned about what this will do to
performance. Because of the complexity I do not want to rewrite the front
end as an adp application. Instead, it would continue as an mdb with ODBC
linked tables to SQL Server.

I would like to make some modifications to the front end app to improve
performance in SQL Server. I'm considering changing from DAO to ADO where
possible. My understanding of ADO is that it provides a direct connection
to a SQL Server, avoiding the use of ODBC and therefore improves
performance. Does this mean the queries actually are passed to SQL Server
and are executed there instead of on the client PC? Does it also mean
network traffic is minimized since an entire data set does not have to be
passed back to the front end app for processing on the user's local PC?

I use DAO in a lot of VB code throughout the application where I create a
DAO recordset and process the data record by record. I could replace this
DAO code with ADO code.

I have a lot of queries created in Access also used throughout the
application. Some of these are executed in VB code, and I believe I could
execute them with ADO code, again avoiding ODBC. Other queries are used
behind forms and reports, so I assume these will have to continue to use
ODBC.

I would appreciate any thoughts.

Bill

[Robert Morley]

I can't respond to all of your points, since I don't have experience with
SQL Server via ODBC. I *can* respond to some of them, though, so here's
what I can tell you, having just gone through a similar migration a couple
of years ago:

> Because of the complexity I do not want to rewrite the front end as an adp
> application. Instead, it would continue as an mdb with ODBC linked tables
> to SQL Server.

That's definitely the easiest way to start out. I chose to go directly to
an ADP app, but I knew what was involved and had sufficient time to convert
most of the project before-hand. Starting with this will avoid a number of
ADP/SQL issues, like migrating crosstab queries, for example (which don't
exist on SQL Server 2000 and below, though with a lot of effort, they can be
faked).

> I'm considering changing from DAO to ADO where possible.

Not a bad idea. ADO is actually slower than DAO when it comes to Jet
databases, but probably won't make much difference in a SQL Server
environment (others would have to comment on that), and will offer you some
advantages like asynchronous processing and disconnected recordsets.

Do be aware that there are things that SQL Server doesn't support, such as
DAO's Index/Seek methods. ADO is capable of it as well, but since SQL
Server itself isn't, you'll need to write those statements as Find
statements, or whatever other method is appropriate.

> My understanding of ADO is that it provides a direct connection to a SQL
> Server, avoiding the use of ODBC and therefore improves performance. Does
> this mean the queries actually are passed to SQL Server and are executed
> there instead of on the client PC?

Yes. You may also want to consider migrating some (eventually all,
probably) of your existing queries to get even better performance. But in
the mean-time, while they reside in the front-end, you'll still get some
moderately good performance from most of them, from what I understand, since
Access tries to make your queries as efficient as possible. (Like I said, I
have no experience with ODBC and SQL Server.)

> Does it also mean network traffic is minimized since an entire data set
> does not have to be passed back to the front end app for processing on the
> user's local PC?

Yes. At least assuming you're writing your queries that way..."SELECT *
FROM MyTable Where MyField = 1" will execute entirely on the SQL Server
side. The following air-code, however, may or may not:

rs.Open "SELECT * FROM MyTable"
rs.Filter = "MyField = 1"

You can tell ADO to use either a client-side cursor, or a server-side
cursor. I *believe* this would affect where the filter is processed as well
(can someone confirm that I'm right about that).

> I use DAO in a lot of VB code throughout the application where I create a
> DAO recordset and process the data record by record. I could replace this
> DAO code with ADO code.

Yes, you could. Most of it will probably be very easy to replace, but there
are a few differences you'll need to get used to. For example, you don't
have to create a new recordset to capture a filter; filter is simply a
property of a recordset, and you can add it, remove it, or change it at any
time, and the recordset will immediately be filtered. ADO also issues an
implicit Update command if you move off of a record, where as DAO would
simply drop your changes silently. There are other changes, but there are a
bazillion websites out there that'll give you more info on that particular
subject, so I won't even try to cover them all here.

> I have a lot of queries created in Access also used throughout the
> application. Some of these are executed in VB code, and I believe I could
> execute them with ADO code, again avoiding ODBC. Other queries are used
> behind forms and reports, so I assume these will have to continue to use
> ODBC.

If they're the data source for the report or for combo/list boxes, etc.,
then yes, they will continue to use ODBC until you either convert the
project to an ADP, or choose to re-write them using your existing ADO
connection.

Another gotcha may be caching/timing issues. Because of things like
caching, and the fact that you'll be using two separate connections, you may
find that changes that you do on one connection may take a while to show up
on the other connection.

Those are my thoughts...there are others with even more experience than what
I have, though, and I'm sure they'll chime in as well. :)

Rob

[Bill Murphy]

Rob,

I really appreciate your thoughts. This should be very helpful in our
migration.

Bill

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:eW6ubhrh...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

I'm not sure I agree with 'DAO is faster than ADO'

Jet is not an option; from performance, reliability-- stability standpoint

so NO it is not faster

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:eW6ubhrh...@TK2MSFTNGP03.phx.gbl...

[Mattias Jonsson]

Bill,
You got the idea. Whether you upsize to SQL Server, MySQL or Oracle,
you can get big performance benefits if you make sure as much as
possible of the processing is done on the server. You can do that using
ADO and setting the form recordset property, and such, but if you want
to avoid making big changes to your Access application, I would vote for
simply linking tables.

There is some low hanging fruit available once you're using a database
server. You can get big performance gains with the linked table
approach by taking queries and recreating those as views on the server.
Then delete the original query and link the view as a linked table
with the same name as the query had. Your forms/reports will benefit
from this optimization even though they are unaware you swapped the
query out from under them, and you can of course make use of these views
in VBA code as well.

Another case where you can get big gains is to change action queries to
passthrough, or using a direct ADO connection for that purpose. But
this involves a bit more work on your part. It doesn't matter if you
use ODBC: If you pass the SQL expression directly to the server without
having Access parse it, then it's going to be fast.

If (like you describe), you have processes where you get a lot of data
using loops, then you will probably not find any improvement by just
switching from DAO to ADO. If you can change that whole loop structure
to some sort of update query or server-side procedure and execute it on
the server, then it will be fast. Not extremely low hanging, but
probably worth it. You can see amazing speedups by doing those loops on
the server.

Unless I'm mistaken, ADO doesn't actually avoid ODBC either. In your
situation, it's simply another wrapper for the ODBC interface, as an
alternative to DAO. Like the other poster said, it provides some added
functionality like offline recordsets and such. And you can make a
separate (2nd) connection to the server, although you can do that with a
DAO object too.

Bottom line is that ODBC and DAO aren't your enemies, but rather you
just need to move the data manipulation to the server as much as
possible. This can be acheived fairly easily with linked tables and
some carefully considered investment in new server side views and
procedures.

My advice is to stay away from ADPs because I'm suspecting Microsoft
will drop this feature first chance they get. I tried it for a project
about 10 years ago, and remember thinking it was a neat way to do things
but really not worth the trouble. But these are just my opinions.

Good luck,
Mattias

[Robert Morley]

DAO with Jet is much faster than ADO with Jet. DAO with linked tables is a
whole other ballgame, and unlikely to be much different than ADO when you
take into account the delays going to the server. Poor choice of words on
my part.

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message
news:u0zbkbth...@TK2MSFTNGP04.phx.gbl...

[Robert Morley]

> Unless I'm mistaken, ADO doesn't actually avoid ODBC either. In your
> situation, it's simply another wrapper for the ODBC interface, as an
> alternative to DAO.

Actually, ADO is a wrapper around OLE DB, not ODBC.

Rob

[Tony Toews [MVP]]

Mattias Jonsson <ne...@ett-remove-idesystem.se> wrote:

>There is some low hanging fruit available once you're using a database
>server. You can get big performance gains with the linked table
>approach by taking queries and recreating those as views on the server.

I forgot the exact code I used but I was able to recreate many of my
queries by programmatically copying them to SQL Server. SQL Server
would give you a return code so you could tell if SQL Server actually
accepted it. Then you could programmatically delete the query and
link to the query on SQL Server.

Of course you'd have to thoroughly test things.

>My advice is to stay away from ADPs because I'm suspecting Microsoft
>will drop this feature first chance they get.

MS certainly hasn't made any improvements lately to ADPs.

> I tried it for a project
>about 10 years ago, and remember thinking it was a neat way to do things
>but really not worth the trouble.

My understanding is that it has it's own set of quirks too.

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

[Aaron Kempf]

DAO 'with jet'

with JET is not an option; and it hasn't been for 10 years

have you ever taken a class on logic?

if it's not an option; then it sure ain't faster!

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:eNNkS9uh...@TK2MSFTNGP04.phx.gbl...

[Aaron Kempf]

MS hasn't updated ADP?

what about SQL 2005 updates?

MS hasn't updated MDB in 10 years
and DAO hasn't been included with Office, MDAC or Windows in the past 10
years

"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:8s3v2354kuepnlfdd...@4ax.com...

[Aaron Kempf]

seriously Tony

please explain to me how 'MS hasn't update ADP' when we just got a huge
update

MS hasn't updated MDB in 10 years.. but all of a sudden they're going
'retro'?

it makes me sick

I DEMAND A COMMITMENT TO ADP

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:u1a1KS2h...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

DAO hasn't been updated or included in Windows, Office or MDAC for the past
decade.

it's ridiculous to think that MS is going back to Jet.

they're just giving us all the run around.

MS is -LYING- to you guys about the future of DAO.
it isn't happening. it isn't gonig anywhere.

and they're doing this to CON us all into moving to .NOT

I DEMAND DATA ACCESS LIBRARY THAT IS NOT A MEMORY LEAK
I DEMAND DATA ACCESS LIBRARY THAT DOESN'T TRY TO HELP ME BY AUTOMAGICALLY
CLOSING CONNECTIONS

I DEMAND A DATA ACCESS LIBRARY THAT CLEANS UP AFTER ITSELF WHEN MY VARIABLE
GOES OUT OF SCOPE

"Bill Murphy" <wmdm...@houston.rr.com> wrote in message
news:462e6dd8$0$24698$4c36...@roadrunner.com...

[Tony Toews [MVP]]

"Aaron Kempf" <ake...@dol.wa.gov> wrote:

>MS hasn't updated MDB in 10 years

Wrong.

[Aaron Kempf]

how is it wrong?

they gave you pivot tables?
ROFL

they did the same thing for ADP!

stop spreading lies, dork

ADP has a much brighter future than MDB

"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message

news:fsdv23paiminv1a1o...@4ax.com...

[Aaron Kempf]

please explain to me.. how MDB has gotten new FEATURES in the past decade

I'm waiting..

"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message

news:fsdv23paiminv1a1o...@4ax.com...

[Aaron Kempf]

and I dare you to say 'SharePoint'

because if you want to see the ultimate SharePoint client-- then look into
Access Data Projects, kid

I mean.. it lives in SQL Server!
Of course it's better to use ADP directly against SQL Sharepoint backend
then this frilly DAO _CRAP_

I had to push 10k records into sharepoint from an access 2003 mdb.. do you
know how logn it took?

"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message

news:fsdv23paiminv1a1o...@4ax.com...

[Robert Morley]

Not sure what you're getting at here, Aaron. DAO still opens Access/Jet
databases, last I looked...in fact, I believe that's it's primary purpose.
What do you mean, it hasn't been an option for 10 years?

My larger point was DAO will work immensely faster with Access databases
than ADO will. Try any test you want, and DAO will almost always be faster.

In a case where you're dealing with linked ODBC tables, however, it's a
completely different scenario, and I suspect ADO's performance and abilities
will be much more desirable.

(Oh, and you'll notice that I'm not responding to your taunt...you seem
unable to refrain from using them, but please, do try once in a while...they
really reduce the effectiveness of your arguments.)

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:ORNodQ2h...@TK2MSFTNGP03.phx.gbl...

[Robert Morley]

Use your polite words, Aaron, then maybe you can have a conversation with
the grown-ups.

Rob

[Aaron Kempf]

I mean DAO hasn't been included in Office, Windows or MDAC for 10 years

do the math kid DAO is still DED and it always will be

_BECAUSE_ of the fact that MDB is not a reliable database-- ADO and Access
Data Projects are completely superior in every fashion

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OnkCtx5h...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

what. I can't say 'crap'

I'm not a friggin mormon, screw you

I WORKED FOR THREE MONTHS ON A CONVERSION AFTER 9/11.
BECAUSE OF A BUG THAT MICROSOFT DID NOT FIX-- I DIDN'T GET PAID FOR THREE
MONTHS WORTH OF WORK

And you wonder why I'm pissed off?

I don't accept the _FACT_ that Microsoft thiinks that it has 'more important
things to do than fix bugs in Access'

robotics studio and xbox are not more important than Access

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:epSNtx5h...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

DAO works faster than ADO

ROFL

MDB DOES NOT WORK

what part of that do you not understad?

how many times have you been pulled out of bed at 3am in order to go and fix
a FE / BE crap that doesn't print a report and a manufacturing company can't
keep the assembly line working?

how many times?

until you've got some real world experience, stfu kid

MDB is not reliable
and NO DAO is not faster for anything

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OnkCtx5h...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

I use ONE LIBRARY
and I use ONE DATABASE

and it's neither DAO nor MDB

how friggin DARE you say that DAO is faster?

MDB IS NOT AN OPTION, IT IS NOT FASTER DAO DOES NOT WORK BECAUSE MDB IS NOT
AN OPTION

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OnkCtx5h...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

seriously BOOB MORLEY

please explain to me-- where it's stated that I'm not allowed to say 'crap'

and please explain to me where you got your egocentric, ethnocentric
attitude problem

haven't you ever heard of ebonics, yo?

swearing it LEGAL now kid

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:epSNtx5h...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

why don't you go and take your DAO crap and build a time machine and go back
and play in the 90s

ADO is faster -BECAUSE- MDB is unacceptable

ADO isn't strong enough for a single user nor a single record

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OnkCtx5h...@TK2MSFTNGP03.phx.gbl...

[Robert Morley]

Actually, it was included in MDAC up to 2.6 (which is not 10 years old),
Office 2000 (which is not 10 years old), Office XP (which is not 10 years
old), Windows 2000 (which is not 10 years old), and I believe even Windows
XP (which is not 10 years old). So I did my math, how are you doing yours?

But regardless of that, the OP was talking about existing DAO code which he
is currently using, so it's relevant to this conversation no matter how you
look at it.

Your posts in this thread are strictly inflammatory, and not meant to be
helpful (as they almost always have been since I first saw your name), so
you'll find that I probably won't respond to many--if any--of them in the
future.

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:uCzgrC6h...@TK2MSFTNGP05.phx.gbl...

[Robert Morley]

Bwahahaha...I don't need to say anything further; your post makes it plain
to everybody just what kind of person you are.

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:OR7HtF6h...@TK2MSFTNGP04.phx.gbl...

[Bill Murphy]

To Aaron Kempf,

My posting on 4/24 asking for help with ADO started this thread. The
postings by Robert, Tony and Mattias have been extremely helpful to me and
probably to others. They understand the purpose of the forums, helping
others. You apparently do not understand this. Your rantings and ravings
and personal attacks have been extremely unhelpful. They have no place in
these forums. A new user would certainly not be encouraged to return here
for help based on your postings.

Bill

[Immanuel Sibero]

I'm still not convinced... tell us how you really feel, Aaron...
You're comical.. thanks for the laugh.

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:unyacU6...@TK2MSFTNGP06.phx.gbl...

[Aaron Kempf]

I disagree, kid

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:eDjjCB7h...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

he's not talking about 'DAO' he is talking about 'ADO'

if you want to be a DAO / MDB fanboy you can go and play in the corner.

this is an ADP newsgroup; if you say ANYTHING anti-ADP you will pay the
price

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:eDjjCB7h...@TK2MSFTNGP02.phx.gbl...

[molsonexpert]

you have issues, don't you?

steve.

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:%23Pghl3P...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

http://msdn2.microsoft.com/en-us/library/ms810810.aspx

Deprecated MDAC Components
These components are still supported in the current release of MDAC, but
might be removed in future releases. Microsoft recommends that when you
develop new applications, you avoid using these components. Additionally,
when you upgrade or modify existing applications, remove any dependency on
these components.

Jet: Starting with version 2.6, MDAC no longer contains Jet components. In
other words, MDAC 2.6, 2.7, 2.8, and all future MDAC releases do not contain
Microsoft Jet, Microsoft Jet OLE DB Provider, or the ODBC Desktop Database
Drivers.
JRO: The Microsoft Jet OLE DB Provider and other related components have
been removed from the MDAC stack since MDAC 2.6. Jet Replication Objects
(JRO) is used only with Jet (Access) databases, basically to create or
compact the Jet Database and Jet Replication Management. JRO has been
deprecated and MDAC 2.7 will be its last release. It will not be available
on the 64-bit Windows operating system.

MDAC Releases
Here is a list of the supportability scenarios of past, present, and future
MDAC releases, starting with the earliest.

MDAC 1.5, MDAC 2.0, and MDAC 2.1: These versions of MDAC were independent
releases that were released through the Microsoft Windows NT® Option Pack,
the Microsoft Windows Platform SDK, or the MDAC Web site. These versions of
MDAC are no longer supported.
MDAC 2.5: This version of MDAC was included with the Windows 2000 operating
system. Future services packs of MDAC 2.5 will be included with
corresponding Windows 2000 service packs. Additionally, these MDAC services
packs will be released to the MDAC Web site in accordance with the Windows
2000 service pack release schedule. You can only install this version of
MDAC on Windows NT, Windows 95, and Windows 98 platforms. You can only
install this version on Windows 2000 and Windows Millennium Edition
platforms through the operating systems or their services packs. This
version is currently supported.
MDAC 2.6: MDAC 2.6 RTM, SP1, and SP2 were included with Microsoft SQL Server
2000 RTM, SP1, and SP2, respectively. Additionally, these MDAC service packs
were released to the MDAC Web site in accordance with the Microsoft SQL
Server 2000 service pack release schedule. You can install this version of
MDAC and its service packs on Windows 2000, Windows Millennium Edition,
Windows NT, Windows 95, and Windows 98 platforms. This version is no longer
supported.
MDAC 2.7: This version of MDAC is included with the Microsoft Windows XP RTM
and SP1 operating systems. You can install this version of MDAC and its
service packs on Windows 2000, Windows Millennium, Windows NT, and Windows
98 platforms. You can only install this version on the Windows XP platform
through the operating system or its services packs.
The 32-bit version of MDAC 2.7 has been released to the MDAC Web site.
The 64-bit version of MDAC 2.7 will release with the 64-bit version of
Windows XP only.
MDAC 2.8: This version of MDAC is included with Windows Server 2003 and
Windows XP SP2 and later.
The 32-bit version of MDAC 2.8 will also be released to the MDAC Web site at
the same time Windows Server 2003 is released to the customer.
The 64-bit version of MDAC 2.8 will release with the 64-bit version of
Windows Server 2003 only.

2.5
Maybe the original 2.5 has it.. Maybe not-- I don't know how we can tell.
of course I've updated to Windows 2000 SP4; so I've got MDAC 2.7-- which
'doesn't include JET right'?

2.6
This release does not include Microsoft Jet, the Microsoft Jet OLE DB
Provider or ODBC driver, the Desktop Database ODBC Drivers, or the Visual
FoxPro ODBC Driver.

2.7
This release does not include Microsoft Jet, the Microsoft Jet OLE DB
Provider, the Desktop Database Drivers ODBC Driver, or the Visual FoxPro
ODBC Driver.
2.8

This release does not include Microsoft Jet, the Microsoft Jet OLE DB
Provider or ODBC driver, the Desktop Database ODBC Drivers, or the Visual
FoxPro ODBC Driver.

**Microsoft Data Access Components (MDAC) 2.60.6526.3

This release does not include Microsoft Jet, the Microsoft Jet OLE DB
Provider, the Desktop Database Drivers ODBC Driver, or the Visual FoxPro
ODBC Driver.

**Office XP
Jet is not the default data access library, stick a fork in it

**Office 2003
Microsoft doesn't have MY PERMISSION to waffle on something like DAO vs ADO
vs DAO.. so I'll stick with their previous story that 'DAO/JET is going
away'.. So yeah-- I'm in militant denial about this crap

**Office 2007
Numerous improvements to ADO and ADP
NO NEW VERSION OF 'DAO' IT HAS BEEN RENAMED TO SOMETHING ELSE RIGHT?

**Windows 2000
of course I've updated to Windows 2000 SP4; so I've got MDAC 2.7-- which
'doesn't include JET right'?

**Windows XP
Who gives a crap about this artsy-fartsy crap? I'm a Windows 2000 User, XP
is for trendy dorks

**Windows Vista
THIS IS SUPPOSED TO HAVE WINFS; WHICH MEANS SQL SERVER-- AS A FILESYSTEM--
ON EVERY DESKTOP. DO YOU REALLY THINK THAT YOU'LL HOUSE MDB FILES _INSIDE_
OF SQL SERVER FOR DATABASE WORK? ROFL

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:eDjjCB7h...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

yeah I have issues with MDB dorks hanging out in my newsgroup

"molsonexpert" <imd...@work.ca> wrote in message
news:eLru08Pi...@TK2MSFTNGP04.phx.gbl...

[Aaron Kempf]

what 'kind of person' am I?

are you accusing me of being black?

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:ewAroG7h...@TK2MSFTNGP04.phx.gbl...

[Aaron Kempf]

screw you kid

'oh i want to use DAO'

DAO HAS BEEN DED FOR A DECADE KID
STFU and start talking to me with some respect

"Bill Murphy" <wmdm...@houston.rr.com> wrote in message

news:4630b882$0$18850$4c36...@roadrunner.com...

[Aaron Kempf]

and I'm SOOOOOOO sorry that you're a little script kid that's scared of ADP

you should lose the training wheels, kid

"Bill Murphy" <wmdm...@houston.rr.com> wrote in message

news:4630b882$0$18850$4c36...@roadrunner.com...

[Bill Murphy]

Respect has to be earned. You've earned absolutely nothing with your
misbehavior on this forum. Don't expect any futher response from me, since
it's clear this would be a waste of my time or anyone's time.

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:OmEDjZQi...@TK2MSFTNGP06.phx.gbl...

[Aaron Kempf]

it's not mis-behaivor

I'm the only one speaking the truth

JET and DAO haven't been included with Office, MDAC or WINDOWS for 10 years

STFU and read the facts, MDB WIMP

"Bill Murphy" <wmdm...@houston.rr.com> wrote in message

news:46326376$0$1355$4c36...@roadrunner.com...

[david@epsomdotcomdotau]

ADO is a wrapper around OLEDB.
OLEDB may be a wrapper around ODBC.

for example, if you use ADO on Access queries,
stored in an Access database, connected to linked
tables, you are using an OLEDB connection to JET,
which is using an ODBC connection to SQL Server.

or if you just use the ODBC provider to connect
to a database, then you are just using OLEDB as
a wrapper around ODBC.

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:%23REIDxv...@TK2MSFTNGP06.phx.gbl...
> > Unless I'm mistaken, ADO doesn't actually avoid ODBC either. In your
> > situation, it's simply another wrapper for the ODBC interface, as an
> > alternative to DAO.
>
> Actually, ADO is a wrapper around OLE DB, not ODBC.
>
>
>
> Rob
>
>

[david@epsomdotcomdotau]

1) Just to be clear, there is no performance advantage
to using ADO for queries that are stored in the mdb.
You have to move the stored queries/views/procedures
to the SQL Server, or use inline sql.

2) DAO transactions against ODBC are broken. (This is
undocumented) Using ADO does not fix this. If you have
DAO transactions, moving to stored procedures in the server
database would be a good idea.

(david)

[Robert Morley]

True enough. Hadn't considered that permutation, since the OP was speaking
specifically of migrating to SQL Server, but you're right of course.

Rob

<david@epsomdotcomdotau> wrote in message
news:e2qdfBTi...@TK2MSFTNGP02.phx.gbl...

[Robert Morley]

<Aaron's entire post snipped because it was idiotically huge.>

Wow, all that just to prove my statements correct. You sure do take a long
time to get your point across. (PS, my wording on the MDAC thing was a
little misleading; it would have been better stated as "included in MDAC
until 2.6, at which point they removed it".)

As for the rest of it, I said specifically, that it was "included", not that
it was the "default" or what-have-you.

As I said previously, the OP started with the statement that he was
converting from DAO to ADO, so DAO became relevant to the discussion at that
point.

Oh and one last thing Aaron, dear, you might want to get your facts straight
before you accuse me of spreading my DAO ways all over this group: I
haven't used DAO for anything in 3 years. That doesn't negate the fact that
it's still MUCH faster on native Jet databases.

Rob

[Robert Morley]

Where on EARTH did you get that idea?!? I'm sorry to burst your bubble, but
Canada didn't go through the various race issues that you did in the US. I
don't give a rat's asinine posterior what colour your skin is.

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:e4v55YQi...@TK2MSFTNGP05.phx.gbl...

[Robert Morley]

> DAO HAS BEEN DED FOR A DECADE KID
> STFU and start talking to me with some respect

Why should ANYBODY talk to you with respect when you actively denigrate
them?

Rob

[Robert Morley]

> JET and DAO haven't been included with Office, MDAC or WINDOWS for 10
> years

You just proved your own incorrectness a few messages ago. Why do you
insist on your lies? They have been *included* in all of them up until
about 3 years ago, give or take, and depending on which one you're talking
about.

[Larry Linson]

"Aaron Kempf" <ake...@dol.wa.gov> wrote

> this is an ADP newsgroup; if you say ANYTHING
> anti-ADP you will pay the price

Mr. Kempf, that surely seems a strange statement to appear in a post in
newsgroup microsoft.public.access.modulesDAOvba. (emphasis mine) Perhaps you
should take a look at the newsgroups list to see what newsgroups have been
crossposted before making such rash statements.

Larry Linson
Microsoft Access MVP

[Graham R Seach]

Just curious Aaron,

What was the bug that led to you having to work for free for 3 months?

Regards,
Graham R Seach
MSysDev, MCAD.Net, MCSD.Net, MCP
Microsoft Access MVP
Sydney, Australia

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:%23y77tD6...@TK2MSFTNGP04.phx.gbl...
> what. I can't say 'crap'
>
> I'm not a friggin mormon, screw you
>
>
>
> I WORKED FOR THREE MONTHS ON A CONVERSION AFTER 9/11.
> BECAUSE OF A BUG THAT MICROSOFT DID NOT FIX-- I DIDN'T GET PAID FOR THREE
> MONTHS WORTH OF WORK
>
> And you wonder why I'm pissed off?
>
> I don't accept the _FACT_ that Microsoft thiinks that it has 'more
> important
> things to do than fix bugs in Access'
>
> robotics studio and xbox are not more important than Access

[Aaron Kempf]

some crash in a UDF of a ADP

I put a UDF in a WHERE CLAUSE of an ADP and it caused an intermittent crash

it was Access 2000 :)
against SQL 2000 ;)

"Graham R Seach" <gse...@REMOVEaccessmvp.com> wrote in message
news:25A6D178-E54A-4496...@microsoft.com...

[Aaron Kempf]

DAO has been depecreated.

it hasn't been included with Office, Windows or MDAC for a decade.
ADP just got a huge facelift in Access 2007

"Larry Linson" <bou...@localhost.not> wrote in message
news:%23yYQJkU...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

Canada? Are they still around?

I thought that we annexed them; or someone did

you know-- I got kicked out of 11th grade english class for writing a paper
about why we should invade canada

I got kicked out of the english class, because my teacher found it offensive
that I was talking about how we should invade canada

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:ugNc6mTi...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

because I've been the only one out here; speaking the truth for the past 5
years

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:%23min6mT...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

it isn't faster.. because there is no such thing as an 'Access Database'

you'd say the same thing if you had as much of a hard time with corruptiond
and poor performance as I have

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OB04ShTi...@TK2MSFTNGP05.phx.gbl...

[Aaron Kempf]

10 years?

sorry-- since the fall of '99... I meant to say EIGHT YEARS not 10 sorry

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:%23Tn66mT...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

and for the record, there IS a new version of ADO in vista.

there is NOT a new version of DAO in vista

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:%23Tn66mT...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

David

you are wrong, there is no such thing as MDB

<david@epsomdotcomdotau> wrote in message
news:eQ0DgBTi...@TK2MSFTNGP02.phx.gbl...

[Robert Morley]

Okay, let me try another tactic, then: the sky is blue.

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:OdpSxD2i...@TK2MSFTNGP03.phx.gbl...

[Robert Morley]

Well, you've been speaking SOMETHING, but it's most definitely not the
truth, as earlier posts have proven. But let's say for the moment that you
had been...that STILL doesn't justify your attitude towards others,
denigrating them and then turning around and expecting THEM to give you
respect!

You gotta show some to get some.

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:OLuihE2i...@TK2MSFTNGP03.phx.gbl...

[Graham R Seach]

Hmm, Tough break. Did you ever work out what the exact problem was?

Regards,
Graham R Seach

Microsoft Access MVP
Sydney, Australia

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:uzjJ2B2i...@TK2MSFTNGP05.phx.gbl...

[Aaron Kempf]

what are you talking about kid

In the past 10 years, I've never heard Access referred to as a DATABASE

Access is a FRONTEND

STFU and lose the training wheels, kids

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:u0We8U5i...@TK2MSFTNGP06.phx.gbl...

[Aaron Kempf]

stfu retards

you don't have 'less layers' then we do

single layer = OLEDB

stop spreading misinformation, kids

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OyfMThTi...@TK2MSFTNGP05.phx.gbl...

[Aaron Kempf]

I speak the truth

I do it proudly and I refuse to let you guys convince newbies to become
Access MDB dorks

the world needs more SQL Server Developers

this whole Access MDB thing has been obsolete for a decade

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:eaRt8U5i...@TK2MSFTNGP06.phx.gbl...

[Aaron Kempf]

yeah.

Access Crashed Inconsistently

The client was PISSED and I didn't eat for months

of course, everyone at MS was sitting around driving their big fat BMW so
they dont' give a flying crap

next time I get someone at MSFT telling me that THEY WILL NOT FIX A BUG?
I don't know what I'll do

I'm just sick and friggin tired of Microsoft focusing on the XBox and Excel
Server and InfoPath and SharePoint-- and ignoring their core competencies

BUG FREE SOFTWARE IS MY DEMAND

"Graham R Seach" <gse...@REMOVEaccessmvp.com> wrote in message

news:06D6C17E-A740-4A28...@microsoft.com...

[Robert Morley]

Whatever...I'm tired of hearing your lies, rants, revisionist history, and
denigration. You're now on my Ignore list. Bye bye.

Rob

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:OJqSmyCj...@TK2MSFTNGP05.phx.gbl...

[Aaron Kempf]

MY LIES?

wtf are you talking about?

I'm not the one spreading mis-information

I'm not the one that is WAFFLING on the future of MS Access.
and I'm not the one that is convincing newbies to go iinto a dead-end
database

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OOE7XWDj...@TK2MSFTNGP04.phx.gbl...

[Aaron Kempf]

I've never lied once in my life

revisionist history?

Just because MS killed MDB 10 years ago; you think that you can say that I'm
revisionist history?

JET AND DAO HAVE NOT BEEN INCLUDED WITH WINDOWS, MDAC OR OFFICE FOR A DECADE

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:OOE7XWDj...@TK2MSFTNGP04.phx.gbl...

[molsonexpert]

well, at least you're admitting it is a database...

steve.

"Aaron Kempf" <ake...@dol.wa.gov> wrote in message

news:%23gTzvPO...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

good stuff

Access isn't a database; and it hasn't been for a decade

Access is a front end to SQL Server

File, New, Project (existing data)

anything else is depecrated

"molsonexpert" <imd...@work.ca> wrote in message
news:uWQ3TYOj...@TK2MSFTNGP04.phx.gbl...

[Klatuu]

Robert,
Ignore this moron. I have seen him out here lately making statements about
Access and Jet that are completely incorrect. He seems to have some perverse
bias against Access and Jet.
His statements show his lack of knowledge on the subject and he is giving
very bad input on every post to which he responds.
--
Dave Hargis, Microsoft Access MVP

"Immanuel Sibero" wrote:

>
>
> I'm still not convinced... tell us how you really feel, Aaron...
> You're comical.. thanks for the laugh.

>
>
>
> "Aaron Kempf" <ake...@dol.wa.gov> wrote in message

> news:unyacU6...@TK2MSFTNGP06.phx.gbl...
> > why don't you go and take your DAO crap and build a time machine and go
> back
> > and play in the 90s
> >
> > ADO is faster -BECAUSE- MDB is unacceptable
> >
> > ADO isn't strong enough for a single user nor a single record

> >
> > "Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

> > news:OnkCtx5h...@TK2MSFTNGP03.phx.gbl...
> > > Not sure what you're getting at here, Aaron. DAO still opens Access/Jet
> > > databases, last I looked...in fact, I believe that's it's primary
> purpose.
> > > What do you mean, it hasn't been an option for 10 years?
> > >
> > > My larger point was DAO will work immensely faster with Access databases
> > > than ADO will. Try any test you want, and DAO will almost always be
> > faster.
> > >
> > > In a case where you're dealing with linked ODBC tables, however, it's a
> > > completely different scenario, and I suspect ADO's performance and
> > abilities
> > > will be much more desirable.
> > >
> > > (Oh, and you'll notice that I'm not responding to your taunt...you seem
> > > unable to refrain from using them, but please, do try once in a
> > while...they
> > > really reduce the effectiveness of your arguments.)

> > >
> > >
> > >
> > > Rob
> > >
> > > "Aaron Kempf" <ake...@dol.wa.gov> wrote in message

> > > news:ORNodQ2h...@TK2MSFTNGP03.phx.gbl...
> > > > DAO 'with jet'
> > > >
> > > > with JET is not an option; and it hasn't been for 10 years
> > > >
> > > > have you ever taken a class on logic?
> > > >
> > > > if it's not an option; then it sure ain't faster!
> > >
> > >
> >
> >
>
>
>

[Aaron Kempf]

completely incorrect?

bs kid

I'm sorry-- but I stand by the FACT that Windows, Office and MDAC haven't
included JET or DAO for a decade

try arguing with the FACTS kid

"Klatuu" <Kla...@discussions.microsoft.com> wrote in message
news:1E525704-5049-4EB4...@microsoft.com...

[sero...@hushmail.com]

On Apr 26, 1:43 am, "Robert Morley"

<rmor...@magma.ca.N0.Freak1n.sparn> wrote:
> My larger point was DAO will work immensely faster with Access databases
> than ADO will. Try any test you want, and DAO will almost always be faster.

Perhaps I do not understand myself what the word means 'immensely'?

Here any test I want:

' ### Code start here ###
Option Explicit

Private Declare Function GetTickCount _
Lib "Kernel32" () As Long

Private Const CONN_STRING = _
"C:\db1.mdb"

Private Const SQL = _
"select ID from MillionRecords order by ID"

Sub main()
Dim i As Long
For i = 1 To 6
Debug.Print TestADO
Debug.Print TestDAO
Next
End Sub

Function TestADO() As String
Dim lTick As Long
Dim rs As ADOR.Recordset
Dim lValue As Long

Set rs = New ADOR.Recordset
With rs
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=" & CONN_STRING
.Source = SQL
.CursorType = adOpenStatic
.LockType = adLockReadOnly
lTick = GetTickCount
.Open
.MoveLast
lValue = .Fields(0).Value
TestADO = "AD0=" & GetTickCount - lTick
.Close
End With
End Function

Function TestDAO() As String
Dim lTick As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lValue As Long

Set db = DBEngine.Workspaces(0).OpenDatabase( _
CONN_STRING, , True)
lTick = GetTickCount
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
With rs
.MoveLast
lValue = .Fields(0).Value
TestDAO = "DAO=" & GetTickCount - lTick
.Close
End With
Set rs = Nothing
db.Close
Set db = Nothing
End Function

' ### Code end here ###

For me, the results:

AD0=6719
DAO=6579
AD0=6734
DAO=6485
AD0=6750
DAO=6156
AD0=7063
DAO=7125
AD0=6578
DAO=7062
AD0=6985
DAO=6062

For me, the results are not to be extremely different. And you?

[Mattias Jonsson]

"Please do not feed the Troll"

[sero...@hushmail.com]

On May 4, 11:29 am, Mattias Jonsson <n...@ett-remove-idesystem.se>
wrote:

> > On Apr 26, 1:43 am, "Robert Morley"
> > <rmor...@magma.ca.N0.Freak1n.sparn> wrote:
> >> My larger point was DAO will work immensely faster with Access databases
> >> than ADO will. Try any test you want, and DAO will almost always be faster.
>

> "Please do not feed the Troll"
>

This 'immensely' was spoken by Robert Morely. He is surely not the
troll?

[Mattias Jonsson]

No, but you get the idea

[Robert Morley]

In this case, you're using "MoveLast", which jumps directly to the end of
the recordset. You're essentially measuring a single operation. Try
scrolling through every last record in the recordset (a more normal
operation in most circumstances), or performing an update to every record in
the recordset, and you will likely see substantially greater differences.

Rob

<sero...@hushmail.com> wrote in message
news:1178269273.9...@y80g2000hsf.googlegroups.com...

[Aaron Kempf]

stfu kid im not a troll I speak the truth

"Mattias Jonsson" <ne...@ett-remove-idesystem.se> wrote in message
news:eov6Ncjj...@TK2MSFTNGP03.phx.gbl...

[Aaron Kempf]

did you consider that SQL Server does a better job of indexing than MS
Access?

that is a key part of this argument

please give more info; I'll gladly share some of my results
All I know is that i've got 200 reports right now that each take 5 minutes

and in a week I rewrote them all to ADP and they're all sub-minute

<sero...@hushmail.com> wrote in message
news:1178269273.9...@y80g2000hsf.googlegroups.com...

[Aaron Kempf]

additionally

with ADO you can write your layer to work against SQL relationally-- and
then you can rewrite queries to use analysis services

this is the trump card-- DAO has no place in any business in the year 2007
not when every vendor has a freeware solution

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:%23FJ6dAl...@TK2MSFTNGP02.phx.gbl...

[Aaron Kempf]

and also-- you've got to compare DAO w/ MDB against ADO and SQL Server

ADO against MDB is just plain stupid because ANY use of MDB is just plain
stupid

<sero...@hushmail.com> wrote in message
news:1178269273.9...@y80g2000hsf.googlegroups.com...

[Aaron Kempf]

Robert Morley _IS_ the trolll
him and all the other MVP dorks around here?

they're all a bunch of worthless MDB kids

"Mattias Jonsson" <ne...@ett-remove-idesystem.se> wrote in message

news:emNQ66jj...@TK2MSFTNGP02.phx.gbl...

[david@epsomdotcomdotau]

> In this case, you're using "MoveLast", which jumps directly to the end of

That is not really true. Movelast fully populates the recordset, and
he has a snapshot, so each record in the recordset is fully populated.
There is no 'jumping' involved

However, you are correct that the example in not useful. Loading a
sorted recordset of 1 million records, then just discarding it, is
ridiculous.
Regardless of ADO or DAO, this is the absolute worst way to select
that record that I can think of. This example is really bad code. This
is a bad way to find a record. It is possible to imagine that there might
be a reason for not using a sensible select statement, or sensible ADO
and DAO methods in the ADO and DAO objects, but taken all
together it is just ridiculous.

So I agree with your conclusion: that example is the wrong recordset
with the wrong code, and no relation to anything an experienced person
would put into either an ADO application or a DAO application.

(david)

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:%23FJ6dAl...@TK2MSFTNGP02.phx.gbl...

[Robert Morley]

> That is not really true. Movelast fully populates the recordset, and
> he has a snapshot, so each record in the recordset is fully populated.
> There is no 'jumping' involved

Even in a snapshot situation, most of the data is stored internally to the
provider, and does not need to be set up in the client (ADO/DAO), so the
results are unlikely to be significantly different. When you access each
record, it marshals it from the provider to the client (unless it's a
client-side cursor, of course). THAT's where you'll begin to see
significant differences. As I remember it, there's a significant difference
in other operations such as client-side filtering and sorting, etc., as
well.

No doubt, ADO is more powerful though, and is therefore probably preferable
in most situations unless speed is absolutely essential.

Rob

[david@epsomdotcomdotau]

> record, it marshals it from the provider to the client (unless it's a
> client-side cursor, of course). THAT's where you'll begin to see

ADODB defaults to server-side cursor for SQL Server, but I
think that ADOR only supports client-side cursor, and in any
case the Jet provider only provides a client-side cursor for the
SQL statement shown.

Since the Jet provider only provides a client-side cursor for the
SQL statement shown, the DAO example is a client-side cursor
also.

(david)

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:%23EYQ%23WrjH...@TK2MSFTNGP05.phx.gbl...

[Robert Morley]

Regardless of that, probably the best quick test is to simply use something
like GetRows/GetString to ensure that all rows are being hit. I prefer
something like this to blindly using MoveLast, and having to figure out
whether that's a sufficient test or not.

Rob

<david@epsomdotcomdotau> wrote in message
news:%23RC2pr6...@TK2MSFTNGP04.phx.gbl...

[Aaron Kempf]

and more importantly; you can rewrite ADO commands to work against ADOMD (or
just leaving it in ADO and changing the ole provider)

try doing THAT with DAO

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:%23EYQ%23WrjH...@TK2MSFTNGP05.phx.gbl...

[Aaron Kempf]

crosstab queries dont exist in SQL Server?

are you friggin kidding me?

a) PIVOT keyword
b) Analysis Services

stop spreading mis-information, kid

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:eW6ubhrh...@TK2MSFTNGP03.phx.gbl...
> I can't respond to all of your points, since I don't have experience with
> SQL Server via ODBC. I *can* respond to some of them, though, so here's
> what I can tell you, having just gone through a similar migration a couple
> of years ago:
>
> > Because of the complexity I do not want to rewrite the front end as an
adp
> > application. Instead, it would continue as an mdb with ODBC linked
tables
> > to SQL Server.
>
> That's definitely the easiest way to start out. I chose to go directly to
> an ADP app, but I knew what was involved and had sufficient time to
convert
> most of the project before-hand. Starting with this will avoid a number
of
> ADP/SQL issues, like migrating crosstab queries, for example (which don't
> exist on SQL Server 2000 and below, though with a lot of effort, they can
be
> faked).
>
> > I'm considering changing from DAO to ADO where possible.
>
> Not a bad idea. ADO is actually slower than DAO when it comes to Jet
> databases, but probably won't make much difference in a SQL Server
> environment (others would have to comment on that), and will offer you
some
> advantages like asynchronous processing and disconnected recordsets.
>
> Do be aware that there are things that SQL Server doesn't support, such as
> DAO's Index/Seek methods. ADO is capable of it as well, but since SQL
> Server itself isn't, you'll need to write those statements as Find
> statements, or whatever other method is appropriate.
>
> > My understanding of ADO is that it provides a direct connection to a SQL
> > Server, avoiding the use of ODBC and therefore improves performance.
Does
> > this mean the queries actually are passed to SQL Server and are executed
> > there instead of on the client PC?
>
> Yes. You may also want to consider migrating some (eventually all,
> probably) of your existing queries to get even better performance. But in
> the mean-time, while they reside in the front-end, you'll still get some
> moderately good performance from most of them, from what I understand,
since
> Access tries to make your queries as efficient as possible. (Like I said,
I
> have no experience with ODBC and SQL Server.)
>
> > Does it also mean network traffic is minimized since an entire data set
> > does not have to be passed back to the front end app for processing on
the
> > user's local PC?
>
> Yes. At least assuming you're writing your queries that way..."SELECT *
> FROM MyTable Where MyField = 1" will execute entirely on the SQL Server
> side. The following air-code, however, may or may not:
>
> rs.Open "SELECT * FROM MyTable"
> rs.Filter = "MyField = 1"
>
> You can tell ADO to use either a client-side cursor, or a server-side
> cursor. I *believe* this would affect where the filter is processed as
well
> (can someone confirm that I'm right about that).
>
> > I use DAO in a lot of VB code throughout the application where I create
a
> > DAO recordset and process the data record by record. I could replace
this
> > DAO code with ADO code.
>
> Yes, you could. Most of it will probably be very easy to replace, but
there
> are a few differences you'll need to get used to. For example, you don't
> have to create a new recordset to capture a filter; filter is simply a
> property of a recordset, and you can add it, remove it, or change it at
any
> time, and the recordset will immediately be filtered. ADO also issues an
> implicit Update command if you move off of a record, where as DAO would
> simply drop your changes silently. There are other changes, but there are
a
> bazillion websites out there that'll give you more info on that particular
> subject, so I won't even try to cover them all here.
>
> > I have a lot of queries created in Access also used throughout the
> > application. Some of these are executed in VB code, and I believe I
could
> > execute them with ADO code, again avoiding ODBC. Other queries are used
> > behind forms and reports, so I assume these will have to continue to
use
> > ODBC.
>
> If they're the data source for the report or for combo/list boxes, etc.,
> then yes, they will continue to use ODBC until you either convert the
> project to an ADP, or choose to re-write them using your existing ADO
> connection.
>
> Another gotcha may be caching/timing issues. Because of things like
> caching, and the fact that you'll be using two separate connections, you
may
> find that changes that you do on one connection may take a while to show
up
> on the other connection.
>
> Those are my thoughts...there are others with even more experience than
what
> I have, though, and I'm sure they'll chime in as well. :)
>
>
>
> Rob
>
>
>

[Aaron Kempf]

CORRECTION!

ADO is not slower against MDB because MDB is not a database!

[Susie Johnson]

just to be clear

a) MDB is not a database, so of course ADO is faster
b) ADO is _SIMPLER_.. because I can do everything from one language, instead
of mixing DAO and VBA
c) ADO is the default
d) ADO is included with all versions of Windows
e) ADO is compatible with other languages (Analysis Services for example)
f) ADO is not a memory leak; you don't need to .CLOSE and SET rst = NOTHING
every 10 seconds
g) ADO supports CONNECTION SHARING- DAO does not

<david@epsomdotcomdotau> wrote in message
news:eQ0DgBTi...@TK2MSFTNGP02.phx.gbl...
> 1) Just to be clear, there is no performance advantage
> to using ADO for queries that are stored in the mdb.
> You have to move the stored queries/views/procedures
> to the SQL Server, or use inline sql.
>
> 2) DAO transactions against ODBC are broken. (This is
> undocumented) Using ADO does not fix this. If you have
> DAO transactions, moving to stored procedures in the server
> database would be a good idea.
>
> (david)
>
> "Bill Murphy" <wmdm...@houston.rr.com> wrote in message
> news:462e6dd8$0$24698$4c36...@roadrunner.com...
> > I have a large and complex Access 2000 application where the front end
and
> > back end mdb files are split. Both mdb's currently reside on a Citrix
> > server, and the performance is very good. However, I need to move the
> > backend mdb data to SQL Server, and I'm concerned about what this will
do
> to
> > performance. Because of the complexity I do not want to rewrite the

front
> > end as an adp application. Instead, it would continue as an mdb with
ODBC
> > linked tables to SQL Server.
> >

> > I would like to make some modifications to the front end app to improve
> > performance in SQL Server. I'm considering changing from DAO to ADO
where
> > possible. My understanding of ADO is that it provides a direct

connection
> > to a SQL Server, avoiding the use of ODBC and therefore improves
> > performance. Does this mean the queries actually are passed to SQL
Server

> > and are executed there instead of on the client PC? Does it also mean

> > network traffic is minimized since an entire data set does not have to
be
> > passed back to the front end app for processing on the user's local PC?
> >

> > I use DAO in a lot of VB code throughout the application where I create
a
> > DAO recordset and process the data record by record. I could replace
this
> > DAO code with ADO code.
> >

> > I have a lot of queries created in Access also used throughout the
> > application. Some of these are executed in VB code, and I believe I
could
> > execute them with ADO code, again avoiding ODBC. Other queries are used
> > behind forms and reports, so I assume these will have to continue to
use
> > ODBC.
> >

> > I would appreciate any thoughts.
> >
> > Bill
> >
> >
>
>

[Susie Johnson]

please give some examples of DAO that do not include mixing SQL and VBA

I can do everything from a single library and you can't

add a field?

ALTER TABLE ADD COLUMN

create a query?
CREATE VIEW vME as Select * FROM SYSOBJECTS
CREATE PROCEDURE spME as Select * FROM SYSOBJECTS

"Susie Johnson" <ake...@mailcity.com> wrote in message
news:%23mVX5tQ...@TK2MSFTNGP04.phx.gbl...

[Robert Morley]

"Susie Johnson", huh? Nice try Aaron. Bye bye.

Rob

"Susie Johnson" <ake...@mailcity.com> wrote in message
news:%23mVX5tQ...@TK2MSFTNGP04.phx.gbl...

[sero...@hushmail.com]

On May 4, 2:22 pm, "Robert Morley" <rmor...@magma.ca.N0.Freak1n.sparn>
wrote:

> In this case, you're using "MoveLast", which jumps directly to the end of
> the recordset. You're essentially measuring a single operation. Try
> scrolling through every last record in the recordset (a more normal
> operation in most circumstances), or performing an update to every record in
> the recordset, and you will likely see substantially greater differences.

You said the «try any test you want». I said, by my test, that you
should obtain the SQL engine to carry out work. To make a loop in
recordsets is incorrect for SQL, incorrect tool. It is my test. Why
movelast? To seek all the records. DAO seems sometimes fast because it
did not seek all records and you cannot obtain the recordcount. ADO
always this.

[Robert Morley]

DAO will always give you a correct recordcount after a MoveLast, but it
won't necessarily seek all the records (see the post where the person
monitored the back-end operations to discover that only indexes and viewed
records were retrieved, not the entire recordset).

As I've stated in several posts now, if you're using SQL Server or any other
external data source, that's a completely different scenario. When I said
that DAO was invariably faster than ADO, I meant it strictly for local Jet
databases, not for anything else. Poor wording on my part in the original
message.

Rob

<sero...@hushmail.com> wrote in message
news:1178696056.6...@h2g2000hsg.googlegroups.com...

[Fifth Amendment]

JET is not a database and it hasn't been for a decade

it is obsolete; I've quoted articles where MS states that JET has been
obsolete for a decade

move to SQL Server and Screw DAO

"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message

news:uUV5dGlk...@TK2MSFTNGP03.phx.gbl...

[sero...@hushmail.com]

On May 9, 4:49 pm, "Robert Morley" <rmor...@magma.ca.N0.Freak1n.sparn>
wrote:

> DAO will always give you a correct recordcount after a MoveLast

You know my test then!

> As I've stated in several posts now, if you're using SQL Server or any other
> external data source, that's a completely different scenario.

I never mentioned the "SQL Sever", I speak of JET. SQL means the
language, not the product "SQL Sever".

[Robert Morley]

I misunderstood the message.

If you're speaking strictly of Jet data, then I suspect that the data
retrieval in DAO is even more optimized than it would be for remote data.
MoveLast may not be sufficient to retrieve all recordset data in many
situations, even if it's a snapshot.

That said, my original wording was perhaps not the greatest...there will
always be tests in which ADO and DAO will perform similarly or ADO will even
outdo DAO, but if you look at average speed comparing common operations in
both, overall, DAO will always come out a clear winner at the end of the
day, provided that the data is strictly local Jet data and not being
marshalled across processes, as discussed elsewhere.

At a million records, I had similar results to yours, with ADO actually
finishing a bit faster than DAO, indicating that perhaps with very large
recordsets, ADO manages its memory better or something of that nature. But
I would submit that 1,000,000 records in a single table is an unusual
situation for a Jet database. For smaller recordsets, my results differ
significantly. (Oh and off-topic, what on EARTH are you testing on that you
got speeds 10 times faster than mine? My computer is admittedly about 2
years old now, but it was a pretty decent machine at the time, and I
wouldn't have expected THAT much difference.)

In my own tests, I used QueryPerformanceCounter instead of GetTickCount,
simply to get more granularity for the smaller tests...GetTickCount reported
times of 0 for the 1-record tests. Not sure what you used for a table, but
my table consists of three fields, ID is an AutoNumber and is the Primary
Key, as well as two junk fields, Text1 and Text2, which are completely
ignored, both filled with 36 characters for each record (all lower-case
letters plus each digit 0-9). Before running each set of tests, I also
compacted the database, to ensure that indexing was optimal, etc. The code
itself was simply run straight out of the IDE. In my tests, I came out with
the following, averaged over 10 tests for each:

MoveLast in Snapshot (as per original test):

1 record: ADO = 222.8 ms; DAO = 81.5 ms
1000 records: ADO = 3600 ms; DAO = 2037 ms
1,000,000 records: ADO = 60 s; DAO = 65 s

Looping through all records in Snapshot and assigning rs!ID.Value to a long
variable:

1 record: ADO = 199.5 ms; DAO = 86.5 ms
1000 records: ADO = 12739 ms; DAO = 4350 ms
1,000,000 records: ADO = 196 s; DAO = 98 s

As you can see, the more real-world tests came out with the significant
speed differences I mentioned, as did most of the not-very-realistic tests.
A Jet database with a million records in a single table is unusual in
itself; opening that table as a snapshot just to move to the end of it and
grab a single field value is not particularly logical as a test. Why would
you EVER do that in a real-world scenario? Perhaps in my original
statement, I should have said something like "run whatever real-world tests
you want"?

As an aside, I tried moving the database from a slow IDE drive to a
high-speed striped SATA array, and there was virtually no difference in
these figures, often with changes of only one or two percent, both for DAO
and ADO. Apparently drive speed is not the major factor I would have
thought for Jet databases.

Rob

<sero...@hushmail.com> wrote in message
news:1178783759.7...@h2g2000hsg.googlegroups.com...

[sero...@hushmail.com]

On May 10, 6:48 pm, "Robert Morley"

<rmor...@magma.ca.N0.Freak1n.sparn> wrote:
> my original wording was perhaps not the greatest...there will
> always be tests in which ADO and DAO will perform similarly or ADO will even
> outdo DAO, but if you look at average speed comparing common operations in

> both, overall,DAO will always come out a clear winner at the end of the

> day, provided that the data is strictly local Jet data and not being
> marshalled across processes, as discussed elsewhere.

Thank you for your complete answer. Many provisory clauses - it is not
simple this one or the other, is it :) I say, select that which you
like. The troll like the other, good for her, eh?

[Robert Morley]

> Thank you for your complete answer. Many provisory clauses - it is not
> simple this one or the other, is it :) I say, select that which you
> like.

Yes, there's a lot of provisory clauses in any discussion as to what's
better for what purposes. As I've said, ADO is my choice, simply because of
its universality and that I find it somewhat easier to use, but for what
it's good for, DAO will beat ADO hands down...but only for that, and not
much else. :-)

> The troll like the other, good for her, eh?

Not sure what you mean by that.

Rob

[aaron...@gmail.com]

DAO is not relevent anywhere

jsut because we got a bunch of DAO dorks around here; that doesn't
make it a scientifically valid survey

go and ask if you should be using DAO in a real vb newsgroup! lol

On Apr 25, 8:08 pm, "Robert Morley"
<rmor...@magma.ca.N0.Freak1n.sparn> wrote:
> Actually, it was included in MDAC up to 2.6 (which is not 10 years old),
> Office 2000 (which is not 10 years old), Office XP (which is not 10 years
> old), Windows 2000 (which is not 10 years old), and I believe even Windows
> XP (which is not 10 years old). So I did my math, how are you doing yours?
>
> But regardless of that, the OP was talking about existing DAO code which he
> is currently using, so it's relevant to this conversation no matter how you
> look at it.
>
> Your posts in this thread are strictly inflammatory, and not meant to be
> helpful (as they almost always have been since I first saw your name), so
> you'll find that I probably won't respond to many--if any--of them in the
> future.
>
> Rob
>
> "Aaron Kempf" <ake...@dol.wa.gov> wrote in message
>
> news:uCzgrC6h...@TK2MSFTNGP05.phx.gbl...
>
>
>
> >I mean DAO hasn't been included in Office, Windows or MDAC for 10 years
>
> > do the math kid DAO is still DED and it always will be
>
> > _BECAUSE_ of the fact that MDB is not a reliable database-- ADO and Access
> > Data Projects are completely superior in every fashion
>
> > "Robert Morley" <rmor...@magma.ca.N0.Freak1n.sparn> wrote in message
> >news:OnkCtx5h...@TK2MSFTNGP03.phx.gbl...
> >> Not sure what you're getting at here, Aaron. DAO still opens Access/Jet
> >> databases, last I looked...in fact, I believe that's it's primary
> >> purpose.
> >> What do you mean, it hasn't been an option for 10 years?

>
> >> My larger point was DAO will work immensely faster with Access databases
> >> than ADO will. Try any test you want, and DAO will almost always be
> > faster.
>

> >> In a case where you're dealing with linked ODBC tables, however, it's a
> >> completely different scenario, and I suspect ADO's performance and
> > abilities
> >> will be much more desirable.
>
> >> (Oh, and you'll notice that I'm not responding to your taunt...you seem
> >> unable to refrain from using them, but please, do try once in a
> > while...they
> >> really reduce the effectiveness of your arguments.)
>
> >> Rob
>
> >> "Aaron Kempf" <ake...@dol.wa.gov> wrote in message
> >>news:ORNodQ2h...@TK2MSFTNGP03.phx.gbl...
> >> > DAO 'with jet'
>
> >> > with JET is not an option; and it hasn't been for 10 years
>
> >> > have you ever taken a class on logic?
>
> >> > if it's not an option; then it sure ain't faster!- Hide quoted text -
>
> - Show quoted text -

[aaron...@gmail.com]

I disagree
OLEDB is not a layer on top of ODBC

On Apr 27, 5:50 pm, <david@epsomdotcomdotau> wrote:
> ADO is a wrapper around OLEDB.
> OLEDB may be a wrapper around ODBC.
>
> for example, if you use ADO on Access queries,
> stored in an Access database, connected to linked
> tables, you are using an OLEDB connection to JET,
> which is using an ODBC connection to SQL Server.
>
> or if you just use the ODBC provider to connect
> to a database, then you are just using OLEDB as
> a wrapper around ODBC.

>
> "Robert Morley" <rmor...@magma.ca.N0.Freak1n.sparn> wrote in message
>

> news:%23REIDxv...@TK2MSFTNGP06.phx.gbl...
>
>
>
> > > Unless I'm mistaken, ADO doesn't actually avoid ODBC either. In your
> > > situation, it's simply another wrapper for the ODBC interface, as an
> > > alternative to DAO.
>
> > Actually, ADO is a wrapper around OLE DB, not ODBC.
>
> > Rob- Hide quoted text -

[aaron...@gmail.com]

Facts Straight?

You used DAO against a MDB and yu think that it's faster?
Big FUCKING deal bro

compare DAO against MDB to ADO against SQL Server
and then when I get serious; I'll rewrite my connection string to
Analysis Services and you'll be stuck in the stone ages

On Apr 27, 6:52 pm, "Robert Morley"
<rmor...@magma.ca.N0.Freak1n.sparn> wrote:
> <Aaron's entire post snipped because it was idiotically huge.>
>
> Wow, all that just to prove my statements correct. You sure do take a long
> time to get your point across. (PS, my wording on the MDAC thing was a
> little misleading; it would have been better stated as "included in MDAC
> until 2.6, at which point they removed it".)
>
> As for the rest of it, I said specifically, that it was "included", not that
> it was the "default" or what-have-you.
>
> As I said previously, the OP started with the statement that he was
> converting from DAO to ADO, so DAO became relevant to the discussion at that
> point.
>
> Oh and one last thing Aaron, dear, you might want to get your facts straight
> before you accuse me of spreading my DAO ways all over this group: I
> haven't used DAO for anything in 3 years. That doesn't negate the fact that
> it's still MUCH faster on native Jet databases.
>
> Rob

[aaron...@gmail.com]

and again; this is from the company that made Access 2000 and SQL 2000
NOT COMPATIBLE and then they sold us on a new version 6 months later

what a piece of shit company

On Apr 30, 12:48 pm, "Aaron Kempf" <ake...@dol.wa.gov> wrote:
> some crash in a UDF of a ADP
>
> I put a UDF in a WHERE CLAUSE of an ADP and it caused an intermittent crash
>
> it was Access 2000 :)
> against SQL 2000 ;)
>
> "Graham R Seach" <gse...@REMOVEaccessmvp.com> wrote in messagenews:25A6D178-E54A-4496...@microsoft.com...
>
>
>
> > Just curious Aaron,
>
> > What was the bug that led to you having to work for free for 3 months?
>
> > Regards,
> > Graham R Seach
> > MSysDev, MCAD.Net, MCSD.Net, MCP
> > Microsoft Access MVP
> > Sydney, Australia

>
> > "Aaron Kempf" <ake...@dol.wa.gov> wrote in message

> >news:%23y77tD6...@TK2MSFTNGP04.phx.gbl...
> > > what. I can't say 'crap'
>
> > > I'm not a friggin mormon, screw you
>
> > > I WORKED FOR THREE MONTHS ON A CONVERSION AFTER 9/11.
> > > BECAUSE OF A BUG THAT MICROSOFT DID NOT FIX-- I DIDN'T GET PAID FOR
> THREE
> > > MONTHS WORTH OF WORK
>
> > > And you wonder why I'm pissed off?
>
> > > I don't accept the _FACT_ that Microsoft thiinks that it has 'more
> > > important
> > > things to do than fix bugs in Access'
>
> > > robotics studio and xbox are not more important than Access

>
> > > "Robert Morley" <rmor...@magma.ca.N0.Freak1n.sparn> wrote in message

> > >news:epSNtx5h...@TK2MSFTNGP03.phx.gbl...
> > >> Use your polite words, Aaron, then maybe you can have a conversation
> with
> > >> the grown-ups.