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?