VB 6, MS Access 97 and ADO... what is best connection?

[PJ2000]

I am using Visual Basic 6, MS Access 97 and my data connections and etc are ADO.
I currently am using a direct connection to the database via: strConnection = "Driver={Microsoft Access Driver (*.mdb)};Dbq=Z:\database.MDB"

1. What is/are the best connection method(s) for a database when implementing MS Access 97 in a VB6 ADO application. (eg, Jet 4.0, OLE ODBC, etc)?

 

[Karl Blessing]

That depends greatly on how you are going to use the data.
Can you give us some more specifics

if you are just reading data, and not making changes etc, ForwardOnly Cursor is the fastest to use. with a ReadOnly Lock

 

[PJ2000]

I am aware of cursors and their relative efficiency - thank you.

Let me restate the question: What is/are the best connection provider(s) for a database when implementing MS Access 97 in a VB6 ADO application. Here is what I already know -- further enlightenment would be appreciated.

ODBC = This is the default provider for ADO, and all provider-dependent ADO properties and methods are supported.
(Probably not the most efficient as it adds another layer).

Microsoft Indexing Service = Only a Static cursor type is available. Allows data search for exact words or phrases, or use wildcards to search for patterns. Returns a NON updatable recordset. (Only useful for quick read-only queries - returns are relatively fast)

Microsoft Jet 3.5 OLE DB, Microsoft Jet 4.0 OLE DB = Command text uses Microsoft Jet SQL dialect. You can specify row-returning queries, action queries, and table names in the command text. It does NOT support dynamic cursors! (Appears to be the provider of choice, however the documents differ somewhat on version functionality between versions - why would I choose 3.5 or 4.0)

All of these provide cursors, just some have different functions available to them in the recordset.

 

[Dragon55]

With all due respect to kb244, I must take exception to his answer. Even though MS claims that ForwardOnly is the fastest cursor type, I have found that this is NOT always the case. Unfortunately, the only way to find out which is really the fastest access method in any particular situation is to run tests, and time them.

I recently ran some tests on an application that I've been building. I needed to extract some records from an Access97 DB, then simply read through them all, looking for a particular value in each field. ForwardOnly would have worked for this, but after trying it, it seemed awfully slow. So I tried several other ways. I tried every cursor type there is, and I also tried sorting and not sorting the records.

I had assumed that ForwardOnly would be the fastest, and that it would also be faster to leave the records unsorted. It turns out I was wrong on both counts. It was actually very much faster to sort the records and use the Dynaset cursor type. In fact, it was 40 times faster!!.

There are obviously many factors that determine the fastest access method in any particular situation. Unfortunately, MS only gives us very general information on the subject, which leads us to believe that certain methods are always faster. But this is only a generalization.

As I said, the only way to be sure is to run your own tests.

Bob Dragon

 

[Karl Blessing]

Well like Bob said, run your own tests, I have only worked in raw ODBC, and ADO , and I also know that DAO is pretty fast (it runs through ODBC but its faster than ADO if you want single connection, single threaded access) so cant help you much there.