OLEDB.4.0 vs OLEDB.3.51

[GaryFrost]

I used to have code that looks like the following

cnnConnection.Provider = "Microsoft.Jet.OLEDB.3.51"
cnnConnection.Open gsDatabaseName

lsSQL = "SELECT * FROM BLAH WHERE [Keyfield] = '" & lsSelectedKey & "' "

rstRecSet.Open lsSQL, cnnConnection, adOpenDynamic, adLockReadOnly, adCmdText

After packaging the application up and installing on another machine I got a "3706 ADO could not find the specified provider" message.

The solution to this (according to microsoft knowledge base article Q197902) is to change the provider string to use version 4.0 of the Jet OLE DB provider.

So the only line I have changed is...
cnnConnection.Provider = "Microsoft.Jet.OLEDB.3.51"
to
cnnConnection.Provider = "Microsoft.Jet.OLEDB.4.0"

But now the application seems to be a lot, lot slower in returning the records.

Is there anything else I can tweak or is it a symptom of the new Jet OLEDB

 

[jasek78]

I had that error message, also, except I still get it, after updating my references and connection strings to Jet 4.0. Have you updated your References in the program? That may cause performance to suffer some.

jason

 

[cardyin]

Although the package and deployment wizard is a good tool, it does not work 100% of the time. In your case, you need to make sure that the Microsoft Data Access Objects are installed on your client machine. You can download this from

http://www.microsoft.com/data

Just download the latest MDAC as well as the DAO 4.0 installation. Install both on any client machine that has this problem. This should fix your bug.

Cardyin --------------------------------------
Cardyin Kim
C/S & Web Development Analyst
Information Services
San Antonio Community Hospital
ckim@sach.org (909)920-6237
--------------------------------------

 

[GaryFrost]

I did have the reference of
"Microsoft ActiveX Data Object 2.0 Library"

After changing the connection string to OLEDB.4.0 I changed the reference to
"Microsoft ActiveX Data Object 2.5 Library"

and I also tried 2.1 and 2.6.

All work but the performance is nowhere as fast as when the connection string was OLEDB.3.51