Troubleshooting: Speed issue when resolved Data provider or other service returned an E_FAIL status error

Problem Description 1

     We are using QuickBooks Enterprise Canadian Edition 2004 and QODBC Version 6.0.0.150. When we used Sample Company provided along with QuickBooks Canadian Edition, i.e., "sample.qbw," we faced an error "Data provider or other service returned an E_FAIL status" with invoice Table. We found that the error occurred when we used either the "RecordCount" Property or the  "MoveNext" method of the ADODB Recordset class object.

Solutions 1

     The solution to this error is to set the "CursorLocation" property of the ADODB RecordSet class object as "adUse Serve.".

Problem Description 2

     But with this solution, we are now facing a speed issue. We have experienced the speed issue when we use the "MoveNext" method of the ADODB Recordset class object. The program runs very slowly. We are facing speed issues with this sample company,y "sample.qbw," and those databases which never produced a such error, r, i.e., "Data provider or other service returned an E_FAIL statu.". Those databases are used with QuickBooks as well as AUS Edition.

     We would like to know whether there is any way to get over this speed issue. Also, we would appreciate it if you could please let us know when this "Data provider or other service returned an E_FAIL status" error will resolve so that we do not need to set the "CursorLocation" property of the ADODB RecordSet class objects as "adUse Server" and also do not experience the speed issue.

Solutions 2

Regarding cursors, the correct approach when using QODBC is only to use static or forward-only type cursors. For example:

----------------

Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

'connect to the QuickBooks database, using connection defined on this machine
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"

sSQL = "SELECT ListID,FullName FROM Item"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
Do While (not oRecordset.EOF)
sMsg2 = oRecordSet.Fields("ListID") & " --- " & oRecordSet.Fields("FullName")
objTextFile.WriteLine(sMsg2)
oRecordset.MoveNext
Loop

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
Set objFSO = Nothing

-----------------------

Note: We have now corrected the issues with Pass-Through SQL and ADO keyset cursors and increased ADO's speed simultaneously. Please try the latest version of QODBC for testing.