Solved

VB6 reading MS Access 97/2000 db using ADO vs. DAO

Posted on 2004-09-07
13
216 Views
Last Modified: 2013-12-25
I have a scheduling application written in VB 6.  The database is in MS Access 97 (or in 2000, makes no difference) and is sitting on a shared network drive.  The application is only used by 4 or 5 people at one time.  I have created a "Data Access Layer" component to do all of the database reading and writing.

When I use ADO for the database access it is VERY slow.  When I use DAO for the database access it is a LOT faster.  The exact same query reading 12,000+ "people names" from the database using ADO takes over 2.5 seconds and using DAO it takes less than 0.5 seconds!!  There is a similar difference when reading lists of City names, product names, etc.

ADO seems to be more stable, causes fewer database corruptions and provides the "disconnected recordset" option which is not available in DAO.  But it is unacceptably slow - or it seems to be.

Should I expect to see this kind of degradation in response from ADO or should it be as fast as DAO?

Thanks much,

Paul W.
0
Comment
Question by:PAXSoftware
13 Comments
 
LVL 18

Accepted Solution

by:
Sethi earned 125 total points
ID: 12002030
DAO is definately faster than ADO as it was made exclusively for Access. So you may see some speed difference between both the technologies and this difference widens on network and when data is huge.
0
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 125 total points
ID: 12003443
DAO is old technology, and microsoft stop supporting it, and it's working fine with access or desktop database

  ADO is new technology from microsoft, and it's working faster and have more feathers than DAO, and when using it with sql server and oracle it be faster than DAO

if you would like to convert from DAO to ADO look at this page from microsoft to help you

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp



Here's some good links for more info.

http://www.databasejournal.com/features/mssql/article.php/1490571

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic2.asp

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q225048&;
0
 
LVL 2

Author Comment

by:PAXSoftware
ID: 12003493
Thanks for the responses.  I know that DAO is old, and no longer supported.  I started out using ADO in this version of the application, but was horrified by the slow response over a network.  My customers are comparing this to an older version of the application that did use DAO, and it had a pretty snappy response.  The response with ADO is slow.

If I want to move the database to SQL Server (or Oracle, or DB2, etc) I am sure I will use ADO, but many of my customers do NOT want to shell out the money for that (small businesses).

I guess I am asking if I should expect to see such a slow response in ADO?   I have tried playing with the parameters in the ADO call and it does not seem to make a difference.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Expert Comment

by:x50Fade
ID: 12007698
Have you tried playing around with the curser location...

AdUseClient
AdUseServer
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12009204
>>I guess I am asking if I should expect to see such a slow response in ADO?  

This may also depend on your query structure.  Although DAO is faster, there should not be huge differences.  Lets take a look at a sample query and see if it can be improved.

Leon
0
 
LVL 2

Author Comment

by:PAXSoftware
ID: 12013490
The query I use in ADO or DAO is identical.  I actually have the query defined in the MS Access database and then make reference to that query in the application code:  As an example, retrieving the list of people (used to build a drop-down combo-box) is quite simple:

SELECT CM_PPL_Person.*
FROM CM_PPL_Person
WHERE (((CM_PPL_Person.SYS_Active)=True) AND ((CM_PPL_Person.SYS_Deleted)=False))
ORDER BY CM_PPL_Person.CM_LastName, CM_PPL_Person.CM_FirstName, CM_PPL_Person.CM_BirthDate, CM_PPL_Person.CM_PersonID;

The structure of the "CM_PPL_Person" table is quite simple - just stuff to identify a person:

CM_PersonID                         Long Integer                Primary Key
CM_FirstName                        String                        
CM_MiddleName                     String
CM_LastName                         String
CM_PreferredName                 String
CM_SalutationID                     Long Integer                Alternate Index - not unique
CM_Suffix                              String
CM_BirthDate                         Date/Time                   Alternate Index - not unique
CM_GenderID                         Byte                            Alternate Index - not unique
CM_MaritalStatusID                 Long Integer               Alternate Index - not unique
CM_Deceased                         Yes/No
SYS_Status                            Long Integer
SYS_Valid                              Yes/No
SYS_Active                             Yes/No
SYS_Deleted                           Yes/No

 and then I have a "Create/Update stamp" identifying when/where/who created and last updated each record.  And there is a non-unique compound index built on:
CM_LastName
CM_FirstName
CM_MiddleName
CM_BirthDate


As for the Cursor Location - I specifically set it to use the client side cursor.  I thought that if you wanted to use disconnected recordsets in ADO then you had to use client side cursors.

Paul W.

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12016917
First of all try this for the query:

SELECT *
FROM CM_PPL_Person
WHERE SYS_Active = True AND SYS_Deleted = False
ORDER BY CM_LastName, CM_FirstName, CM_BirthDate, CM_PersonID;

You may also try listing the columns you want as opposed to selecting all (*).  You should index SYS_Active and SYS_Deleted fields since these are the fields you are searching by.

Make sure your cursor type is static:
objRS.CursorType = adOpenStatic

Leon
0
 
LVL 1

Expert Comment

by:martin_watts
ID: 12063721
If your customers do not want to shell out for SQL Server (understandably), maybe MSDE is an alternative?
0
 
LVL 2

Author Comment

by:PAXSoftware
ID: 12814920
Thanks for your help, however, this time I did not learn anything new.  I am going to ask the moderator to delete this question and refund my points.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12816544
This question should in no way be deleted or refunded.  Recommend

Split: Sethi, Dhaest


Leon
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to create a duplicate finder Application 9 122
using web browser with BING 40 132
Access Object Property from VBA Module in Excel 2010 2 37
Spell Check in VB6 13 127
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question