VBA addin - ODBC via ADO compared to DAO

[popfan]

I have a vba addin that was originally created in DAO format and it is really quick, problem is that this addin needs to be shared to a lot of ppl and we need to a lot of setup to get DAO to function correctly within Excel 2007, so I am trying to switch the addin to ADO. The speed in ADO is terrible compared to the DAO. The DAO does 4800 records in about 36 seconds and ADO is doing the same amount of records in 30 minutes.

DAO setup:

Please Login or Register  to view this content.

Current ADO setup:

Please Login or Register  to view this content.

I could like to get the if statement working if I can

Function call for DAO:

Please Login or Register  to view this content.

ADO Function call:

Please Login or Register  to view this content.

Any help will be greatly appreciated!

[popfan]

I found the answer in PRO VBA 2007 book. I had to change:

rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic
to
rs.Open sSQL, cnn, adOpenDynamic

[OnErrorGoto0]

Why does DAO take more work to set up than ADO?
Incidentally, if you are simply reading values from the recordset, a firehose cursor is usually the fastest

Please Login or Register  to view this content.