a97 to a03 and DAO to ADO

[Mario G.]

I am preparing to convert an Access 97 mdb to Access 2003. I've similated
the upgrade using Access 2000 with no problems. However, I also need to
upgrade the code from utilizing DAO 3.6 to ADO.

Does anyone have any good references for this (books, web sites, etc....)
Has anyone done this? What problems did you encounter and had to resolve.

Thanks.

 

[Allen Browne]

Why do you need to change the code to ADO?

DAO is the native Access library - the one that Access itself uses.
If you are using JET tables (the tables in Access), then DAO is still the
most appropriate library to use.

 

[Mario G.]

Hi Allen,

As a VB Developer, I know the performance benefits of utilizing ADO over DAO.

The Access DB I am asked to upgrade utilizes a combination of Access tables
and SQL Server tables through ODBC. I was under the assumption that the
performance gains realized in ADO in VB would translate into similar gains in
Access.

Would this not be the case?

 

[Alex]

Hi,

I think that the move to ADO should be in conjuction with turning the
project into an ADP, I would only recomend this for 2 reasons, how much
data have you got? how many users have you got?

The time spent converting your code over to ADO would not be that much
of a benefit in a small database or small amount of users.

In quite a few cases the DAO model will outperform ADO when accessing
access MDB's, what ADO brings to the party is a common model that you
can use for almost every data need.

Mario if your DAO experence is good then the move to ADO is not that
painfull, just a few new ideas to learn. Personally I use the
msdn.microsoft.com web site for most of my learning.

I only use ADO in access MDB's when I know that the project will
eventually end up in SQL.

Hope that helps

Regards

Alex
I think that if the project was just accessing access mdb data then the
DAO library is still faster than ADO, but once you are in the mixed
mode mdb/SQL the benefits of using ADO one library that has all the
features of SQL and at its disposal

 

[david epsom dot com dot au]

Most Access application use bound forms: it is a
rich and rapid development environment, and if you
aren't using bound forms why use Access at all?

It is technically pretty demanding to convert bound
forms from Access/DAO to semi-bound forms using ADO:
because DAO is the native Access method, nothing else
works as well or with as little effort. In fact no
one does convert bound forms to ADO: it is just too
painful, for too little benefit. Instead, either
convert the mdb to adp, or convert the bound forms
to unbound forms, or just leave it as it is.


BTW, for most Access applications, even on SQL Server,
ADO objects don't have any particular performance gains
over DAO/ODBC. ADO/OLEDB and adp's generate less network
traffic, but Access is not generally used in network-
limited applications.

(david)

 

[Brendan Reynolds]

You can use both ADO and DAO in the same MDB, Mario, as long as you're
careful to disambiguate when using objects that have the same name in both
libraries (Dim rst As ADODB.Recordset and Dim rst As DAO.Recordset, rather
than just Dim rst As Recordset). So rather than trying to convert all the
code at once, you could leave most of the code alone and try to identify the
specific procedures that you believe may benefit from using ADO.

 

[Mario G.]

Alex,

I believe there are approximately 60 users on the system at this point. The
DB is relatively small (9 MB compacted).

I am actually more familiar with ADO than DAO as the majority of project's
I've developed were all done utilizing ADO. Unfortunately, the previous
developer (whom is no longer with us) left the project in a mixed state in
which some tables are in Access (to populate combo boxes, etc...) where the
main client data is stored in SQL Server.

At this point I have 3 options:

1) Convert the DB to Access 2003 and leave the DAO code in tact.
2) Convert the DB to Access 2003, Convert the non bound forms (input forms,
display forms, etc...) to utilize ADO and leave the project as a MDB.
3) Convert the DB to Access 2003, upgrade the MDB to a ADP and convert all
the code to ADO vs DAO.

 

[Mario G.]

Thanks Brendan,

Since I am doing this for a client, only a partial conversion is not an
option (mainly due to time and cost factor). I will most likely have to
convert the entire application to use ADO and determine whether there was a
significant performance gain due to the conversion. Thanks for the note
about disambiguating the libraries. I have trained myself not to take short
cuts and to always quantify which library/name space, etc.. is being
referenced.

 

[Mario G.]

Hi David,

What benefits are realized from converting the Access application from an
MDB to a ADP without converting the underlying data access technology.
Unless you are implying to use the CDM (Client Data Manager) as the
middleware component between Access and SQL server.

The F/E of the application resides on the client's desktop while the SQL
Server backend is on a central server (along with Word Document Templates,
Excel Templates, Exchange Server public folder access, etc...). In this
scenario, there is quite a bit of network traffice for both data and
templates.

 

[Brendan Reynolds]

If you stick with an MDB and ODBC-linked tables, Mario, time and cost are
exactly the reasons why you should *not* attempt to convert all the code. In
that scenario, much of the conversion will be time wasted, and will result
in no gain to the customer whatsoever.

 

[david epsom dot com dot au]

I've developed were all done utilizing ADO. Unfortunately, the previous

developer (whom is no longer with us) left the project in a mixed state
which some tables are in Access (to populate combo boxes, etc...) where

!!!! That is not 'unfortunate' !!!!!

That is the preferred design paradigm for an Access Application !!!!!

It may be that this application should not be touched at all. Is there
some compelling reason why it needs to be modified? I appreciate that
you want to port the application to a form you feel more comfortable
with, and now may be the appropriate time to do that, but if so, don't
try to do it by 'fixing' this.

Make a case for building a new application that you will find easier
to support, and choose the tools you feel comfortable with.

(david)

 

[david epsom dot com dot au]

What benefits are realized from converting the Access application from

MDB to a ADP without converting the underlying data access technology.
Unless you are implying to use the CDM (Client Data Manager) as the

An ADP does use a different underlying data access technology.
The underlying data access technology of an MDB is DAO. The
underlying data access technology of an ADP is ADO.

ADO can use an OLEDB connection. DAO can only use ODBC connections.
A limitation of ODBC is that the specification only permits one
Left or Right join per query. Using MDB/DAO/ODBC, any query with
multiple Left and Right joins must be implemented as a View on the
server, or else the join must be evaluated at the client. This means
that Access/DAO/ODBC must download the indexes or do a scan to do
the join. OLEDB does not have this limitation, which means that
an ADP can evaluate the same query with less network traffic.

(david)

 

[Mario G.]

Hi David,

The only incentive reasons for the upgrade is to get off of Access '97 and
to move it to Access 2003. The client is peforming an OS and Office upgrade.
In lieu of this change, I had considered to upgrade the MDB to Access
project instead and perform the data access upgrade.

Considering some of the feed back, it may worth another look at keeping DAO
3.6 instead of ADO.

 

[Albert D. Kallal]

Hi Allen,

As a VB Developer, I know the performance benefits of utilizing ADO over
DAO.

Well, actually...most of the time there is no benefits.

I was under the assumption that the
performance gains realized in ADO in VB would translate into similar gains
in
Access.

Hum, no, this was never really the case. For sure ADO was a newer object
model then was DAO. And, DAO was built around JET (however, ODBC JET direct
did in fact by pass JET 100%.). So, ADO was really a extra "layer" of
abstractions that (supposedly) allowed you to write code without regards for
what data engine you use. So, if you code using ADO to JET (mdb) in
ms-access, then in theory you could change the data source to sql server,
but not have to change your ADO code.

And, of course ADO has better support for working with stored procedures
etc.with sql server.

However, it was never any faster then DAO...and certainly not when going
ADO->JET.

There is absolute no performance gains to be had with ADO and JET. And,
there is absolute NO reason to re-write good existing DAO code that works
with the JET/mdb data. You get zero benefits here.

Having said the above, I would certanly recomemnd using ADO for connecitong
to sql server.

And, you might find it interesting that for access 2000, 2002, MS had
removed the DAO reference, and you have to put it back in (tools->references
when in the code editor). However, for access 2003, the DAO reference is
actually now back by default!!

So, while one can argue that DAO is old, and many people jumped to ADO...now
ADO is surpassed and now we got ado.net. So, we could argue that no you
should not be using ADO anymore either!!!

The fact of the matter is that using DAO is just fine when working with JET
databases. I would not waste ANY time re-writing the existing DAO code in
that application. The ONLY exceptions would be code that works with tables
on sql server...

 

[drquake]

Do you mean we can create Access2003 mdb using DAO? Could you show me how to
do that?

 

[Allen Browne]

This example creates a new database, and turns off the AutoCorrupt
properties:

Sub CreateDatabaseDAO()
Dim dbNew As DAO.Database
Dim prp As DAO.Property
Dim strFile As String

'Create the new database.
strFile = "C:\SampleDAO.mdb"
Set dbNew = DBEngine(0).CreateDatabase(strFile, dbLangGeneral)

'Create example properties in new database.
With dbNew
Set prp = .CreateProperty("Perform Name AutoCorrect", dbLong, 0)
.Properties.Append prp
Set prp = .CreateProperty("Track Name AutoCorrect Info", _
dbLong, 0)
.Properties.Append prp
End With

'Clean up.
dbNew.Close
Set prp = Nothing
Set dbNew = Nothing
Debug.Print "Created " & strFile
End Sub