Hello Tom,
Let me start off by apologizing for the length of my request. I will beg for your forgiveness and any advice you might be able to give me would be greatly appreciated.
I will start off with the background info. I set up a Whiteboard at work that queries QB every hour to get updated sales orders. This query takes about 10 seconds to run. Last week, we sent our company file to Data Services to have them fix some corrupted data. When we received the file back, QODBC optimized as expected but after that, it could no longer query without connection issues. Since the file name was changed, I reconfigured the ODBC to point to the new file but that still failed. After a few more attempts, I updated the QODBC from v7 to v8. We are currently running Enterprise Solutions 7.0.
The update to v8 made it possible to connect again without error. I set up the ODBC as before and ran the query. I received an error that the column SalesOrderLineOther1 could not be found. However, that column shows up in MSQuery. I removed it from my SQL and the query ran. After about 20 minutes, no data was returned but it did run to completion. It should have returned at least 20 records. Each additional runs also take 20 and return no data. This is also the case using MSQuery. If I try to reconfigure QODBC (and remove optimizer just for testing purposes), I do not see my settings but when I save using DSN QODBC N, it asks to overwrite so I assume my settings are saved but are just not apparent.
What I am asking of you is to help me rule out possibilities so that I can figure it out once and for all. I find it difficult to believe QODBC could be the issue because it ran fine before we sent our file and failed after it. However, just to be sure, I will include my script and ODBC settings. Thanks in advance for your help and having to read this book.
VBA script:
'set up connection string
sConnectString = "DSN=QODBC N"
'set up SQL
sSQL = "SELECT SalesOrderLineDesc, DueDate, SalesOrderLineItemRefFullName, SalesOrderLineQuantity, PONumber, SalesOrderLineAmount, SalesOrderLineClassRefFullName, RefNumber, CustomerRefFullName, TxnDate, SalesOrderLineOther1 FROM SalesOrderLine WHERE SalesOrderLine.TimeModified >= {ts '" & Format(Date, "yyyy-mm-dd") & " 00:00:00'}"
'set connections
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
'open qb
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection
'place in the data into whiteboard temp sheet
Do While (Not oRecordset.EOF)
this is lengthy and irrelevant
Loop
’close out of the string
oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
QODBC settings:
General Tab
Locate the company file and have browsed to location.
DSN = QODBC N
Opens same as company file
Optimizer
Default optimizer database folder
The start of every query is selected
Real Time balances
Multi-table Sync
All Other tabs
Everything else is in default state
Thanks so much! Please let me know what else you might need.
Robin Peckham | 
