Dear Support,

We are using QuickBooks Enterprise Canadian Edition 2004 and QODBC Version 7.0.0.200. We are getting problem for using “NOSYNC” keyword on Inventory tables as well as some transaction tables like Invoice of sample company database.

We did check and found that the same queries works with old version of the QODBC like 6.0.0.150 that we were using recently.

For instance, consider query…  “SELECT TxnID, TxnNumber, CustomerRefListID FROM Invoice NOSYNC where TxnID = '22-1033078139'”

When we execute this query using VB Demo 32, it returns accurate results. The query above returns following data when we execute it using VB Demo 32…with Query Results as 1 i.e. Number of records returned as 1.

TxnID                     TxnNumber     CustomerRefListID

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

22-1033078139        2                  50000-1033057458

But when we tried to execute it through a VB project using a connection string, it returns exact number of records but with null or blank values for each field in that records.

Below is snippet of the code…

<Snip>

‘ rsCustom is a Recordset

‘ strSQL is the query “SELECT TxnID, TxnNumber, CustomerRefListID FROM Invoice NOSYNC where TxnID = '22-1033078139'”

‘ oQBConnection is the connection string – “Provider=MSDASQL.1;Extended Properties="DSN=GUMU;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer"

rsCustom.Open strSQL, oQBConnection, adOpenDynamic, adLockOptimistic

<Snip>

Executing the query through VB program returns same number of records i.e. 1 but it returns Blank or Null value for each field in the query.

After executing above code, “rsCustom.Fields(0).Value” returns “Null” value.

Also, if we remove “NOSYNC” keyword, the program runs very slow as each time while executing it optimizes the data.

Do we missing anything? Is there any solution to resolve the problem?

Thanks in advance for your help and support.

Clifford.

I assume you are saying that this works:

‘ strSQL is the query “SELECT TxnID, TxnNumber, CustomerRefListID FROM Invoice where TxnID = '22-1033078139'”

but is slow?

Try changing these lines:

‘ oQBConnection is the connection string – “Provider=MSDASQL.1;Extended Properties="DSN=GUMU;SERVER=QODBC"

‘ strSQL is the query “SELECT * FROM Invoice NOSYNC where TxnID = '22-1033078139'”

A work around is to use ForwardOnly Cursors.

With rs
    .CursorType = CursorTypeEnum.adOpenForwardOnly
    .LockType = LockTypeEnum.adLockOptimistic
    .CursorLocation = CursorLocationEnum.adUseServer
End With

(I know, not the best answer but one that works none the less). We think its related with refinding Bookmarks with PassThrough Optimizer queries. Those without Passthrough on should be okay.

Yes Tom, you are correct in your understanding.

Now when we use the above solution it gives me a strange result. We set cursor location of Record Set object as “ForwardOnly” as suggested by you.

Before we execute query to get records in the Record set object, first we have optimized the Table. With this, Record set object does not contain any records. But if we do not optimize table then Record set object does contains the records and each record contains valid value. But this results into slow execution of program.

As we mentioned above, before upgrading the QODBC latest version 7.0.0.200 (though QODBC setup screen say OQDBC version 6.0.0.199) we were using QODBC version 6.0.0.150. With QODBC version 6.0.0.150 we did not received above error. However with QODBC 6.0.0.150, we had recently reported speed issue problem when we resolved “Data provider or other service returned an E_FAIL status” error which is posted on forum. Its link is http://www.qdeveloper.com.au/forum.php?homeinclude=topicdetails&forum_id=1&category_id=19&post_id=969

As per recommendation from QODBC support team, we have upgraded the latest version of driver but now it result in above error. Can you please confirm whether this is an issue with latest version of QODBC 7.0.0.200 as it is working with QODBC 6.0.0.150 without any issue? If yes then when the issue will be solved?

Thanks in advance for your help and support.
Clifford