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 use 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 use either “RecordCount” Property or “MoveNext” method of ADODB Recordset class object.

Solutions 1

     The solution to resolve this error is to set “CursorLocation” property of the ADODB RecordSet class object as “adUse Server ”.

Problem Description 2

     BUT with this solution, we are now facing speed issue. The program runs very slow. We have experienced the speed issue when we use “MoveNext” method of ADODB Recordset class object. We are not only facing speed issue with this sample company “sample.qbw” but also with those databases which never produced such error i.e. “Data provider or other service returned an E_FAIL status”. 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 really appreciate if 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 “CursorLocation” property of the ADODB RecordSet class objects as “adUse Server ” and also do not experience the speed issue.

Solutions 2

As far as cursors are concerned, the correct approach when using QODBC is to only 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 speed for ADO at the same time. Please try the latest version of QODBC for testing.