Solved

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

Posted on 2004-09-07
13
208 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now