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

 

[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

 

[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

From: Bill Murphy, on 4/24/2007 4:48 PM:

 

[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

 

[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]]

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!

 

[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

 

[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]

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

 

[Tony Toews [MVP]]

MS hasn't updated MDB in 10 years

Wrong.

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]

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

 

[Aaron Kempf]

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

I'm waiting..

 

[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?

 

[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

 

[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

 

[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