|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| SQL query in VBA returns 0 results from Transactions |
| Author |
Message |
|
|
| Posted : 2007-11-04 05:14:03 |
Hi there,
I am able to connect to the QODBC driver, and retrieve * from any table classed as a 'List', but when I attempt any SQL queries with a Transaction table, I get 0 results returned. I have tried every permutation of select statement I can think of, and it just won't happen. My current attempt looks like this:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
sConnectString = "DSN=TestQuickbooks;OLE DB Services=-2;"
sSQL = "SELECT * FROM Estimate"
cn.Open sConnectString
rs.Open sSQL, cn, adOpenStatic, adLockOptimistic
Debug.Print rs.RecordCount
I am using QuickBooks 2007 Canadian Premium Contractor edition, ADO v2.8, and QODBC Server Edition 2008 All Regions, v8.00.00.234.
Thanks!
Darren |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-06 09:20:43 |
| Sorry the transaction table can only be seen using USA editions of QuickBooks 2006, 2007 or 2008. The Estimate and Invoice tables are header tables only with the transaction lines actually shown in the EstimateLine and InvoiceLine tables. |
|
|
|
|
|
| Posted : 2007-11-07 02:46:44 |
Thank you for your reply.
Just to clarify, it is not at all possible for me to connect using QODBC and retrieve estimate or invoice information?
Thanks again,
Darren |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-07 16:55:25 |
Try using VB Demo to explore the "transaction class" tables to see what you really have. If you don't see any transactions in your company file, try opening one of the sample company files instead to see if it's a QODBC setup or company file problem.
See: How do I use the VB Demo for testing? for more.
If the "transaction class" tables are seen correctly in VB Demo, try changing your ADO recordset cursor type. Depending on your version MDAC, some developers have had to use :
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
oRecordset.CursorType = 2
oRecordset.LockType = 3
oRecordset.CursorLocation = adUseClient |
|
|
|
|