Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 29th, 2004, 10:59 AM
Registered User
Join Date: Apr 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO and DAO speed

I am converting my database access module from DAO to ADO in my C++ program. The program is just for writing about one million records to a Access database. I got two problems in my development.

1. I found that the writing speed with ADO module is much slower than with DAO module. But people told me that ADO can work faster. Anybody has done the writing speed comparison between ADO and DAO module? There is any way I can increase the ADO writing speed?

2. There are two ways in ADO module for writing new records to a database:
    1) Using recordset->Fields->Item["field_name"].value to assign the value to each field directly.
    2) Use the data binding function to first bind a class to recordset.
    Anybody know which way can make the writing speed faster?

Any answer is appreciated.


Old April 29th, 2004, 11:18 PM
Authorized User
Join Date: Feb 2004
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts

DAO is the fastest method for working with an Access database. ADO adds a layer of abstraction that sacrifices speed and functionality in order to provide a generic interface to a broader variety of data sources. I would not expect data binding to add much overhead but it does add some.

If the records are in a format that can be linked, you would probably do best with an append query from the linked data. No matter what method you use, keep the connection object (ADO) or database object (DAO) open betweem writes. The next question is whether to open a recordset and keep it open, with .AddNew, .Update in a loop or running a series of connection or database executes in the loop. If you are using an mdb file of type A2k or newer, it is a good idea to turn off single record locking as single record locks force each record to use a 2k file page which may cause signficant file bloat (recoverable with a compact).

If there are complex indexes, it is more time consuming to add records while the indexes exist. It is much more efficient to add the records into a table without indexes and then add the indexes afterwards.

Jürgen Welz
Edmonton AB Canada

Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO or DAO knowledge76 Access VBA 3 October 12th, 2015 04:26 PM
DAO vs. ADO SerranoG Access VBA 11 December 5th, 2006 01:19 PM
Dao to Ado vrtviral Access VBA 5 February 19th, 2005 11:13 AM
How to speed up looping ADO code? llowwelll Pro VB Databases 7 October 24th, 2004 11:12 PM
DAO / ADO? merguvan Access VBA 8 January 18th, 2004 07:39 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.