|
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
|
|
| Recordset Returns Null Values after an Insert |
| Author |
Message |
|
|
| Posted : 2008-09-20 04:30:14 |
We insert jobs into Quickbooks' Customer table using VBA in MS Access. Once the job is successfully inserted into QuickBooks using QODBC we run an SQL statement against the Customer table to get the ID of the new job so we can store it in a Job table in Access using the following code that immediatly follows the insert:
strSQL = "SELECT ListID, ParentRefListID, FullName FROM Customer WHERE FullName Like '*:" & JobNumber & "'"
Set rs = dbs.OpenRecordset(strSQL)
If Not rs.EOF Then
Me!CustQuickBooksID = rs("ParentRefListID")
Me!JobQuickBooksID = rs("ListID")
Else
MsgBox ("Something is messed up!")
End If
rs.Close
It works 95% of the time which is really frustrating.
Is there a better way to look up the ListID of the newly inserted record that works 100% of the time assuming the record successfully inserts?
I've been having to manually cut and paste the ListID when it fails.
SP_lastinsertid seems to work spotty as well.
By the way we're running QODBC in a terminal server environment using the iBiz remote connector and a Quickbooks session running on the server's console.
Why does this happen? Is the insert not complete when control is passed back to my code? Do I need to program a wait period? |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-22 09:58:00 |
This is most likely a optimizer sync issue. Try this instead:
strSQL = "SELECT ListID, ParentRefListID, FullName FROM Customer UNOPTIMIZED WHERE FullName Like '*:" & JobNumber & "'" |
|
|
|
|
|
| Posted : 2008-09-23 00:33:20 |
Should I be using UNOPTIMIZED for all INSERT, UPDATE, and DELETE queries as well?
Also, on some occasions it will insert an invoice twice or a line on an invoice twice. Will the UNOPTIMIZED keyword help there too?
Jerry |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-23 09:23:14 |
QODBC doesn't INSERT a INSERT twice, most likely your routine has run twice, or the data you're importing is doubled up. The UNOPTIMIZED tag is used to extract rows directly out of QuickBooks. This is useful when adding new data.
See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more on how the QODBC optimizer works. |
|
|
|
|