Access and Quickbooks ODBC

[jimatqsi]

I'm working with the Quickbooks ODBC for the first time. This is in the RightSource remote desktop environment, using Quickbooks Premier Manufacturing and Wholesale edition. The ODBC is the read only version from Flexquarters.

The response of queries with joins is very very very slow. Query the Bill_line table and the recordset comes back in 5 seconds. Join the ItemInventory table with the LineItemRefListID=ListID and the recordset comes back in 80 seconds.

Have you seen the same behavior? Do you have a way around it?

Thanks,
Jim

[NeoPa]

Hi Jim.

I have no idea why an Expert would need their posts moderated. Hopefully these are just teething issues with changes to the site.

As for your issue it seems to me that any simple query that can be wholly understood by the driver can be sent up the line and therefore executed efficiently by the far end. You get back a pre-filtered dataset.

On the other hand, when you give it a query that has to work relative to some other table known at the Access end but not at the far end - as the driver is not able to recognise and optimise separate tables even if they do ultimately refer to the same remote database - then it has to say something on the lines of :
I can't make this work together so the best I can do is to request the full set of data from the whole table and let Access handle the rest.

This would happen for each of the remote tables and would thus cause considerable traffic & delay. Incidentally it ain't great for bloat either.

I don't know what you have available in QB but certainly, when falling over similar issues in SQL Server, you would be well advised to design a View on the server to do the grunt work and only return the limited data that's needed.

[jimatqsi]

Hi Ade, and thanks for the reply.

In this case all tables are remote and users of the QB ODBC have no access on server side. I can send a pass-through query, which saves surprisingly little time, but that's about as close as I can get to server side processes. Saying that, I should tell you that I have only a few weeks of working with the Quickbooks ODBC, only a few hours at a time, so I know next to nothing.

The ODBC is not a true ODBC, according to the developer of that code. Apparently QB doesn't allow direct access over ODBC. So there is an intermediate step where the ODBC request is turned into a request that QB understands. So there's plenty of room for bottlenecks, but it is really extreme. A user tried to print 21 purchase orders to .PDF files and waited more than 30 minutes. I'm pretty sure results are supposed to be better than that.

Thanks again,

Jim

[NeoPa]

To be fair Jim, I'd expect a PTQ to give similar benefits to a remote View, but that obviously depends on what's in it. Also, the QB ODBC and its disconnected interface may well be why that isn't showing the benefits expected.

Certainly I'd expect fewer problems with that approach. Sorry I couldn't be more help :-(

Cheers -Ade.