troubleshooting Question

Quickbooks to Access Intergration using the SDK

Avatar of jadaiber
jadaiber asked on
Microsoft AccessQuickBooks
13 Comments1 Solution2318 ViewsLast Modified:
I've included the code I'm using to query salesorders and salesorder lines from Quickbooks into two MS Access database tables.  The code runs without error, but only the salesorder header information is returned without any line detail.   Can anyone see what I'm missing.  I'm using Acess 2003 / Quickbooks Enterprise 7.0/ Quickbooks SDK 6.  Thanks for the help



Private Sub Command3_Click()
    Dim accessDB As Database
    Set accessDB = CurrentDb
    If (accessDB Is Nothing) Then
        Exit Sub
    End If
   
   
    Dim sessionManager As New QBSessionManager
   
    Dim requestMsgSet As IMsgSetRequest
    Set requestMsgSet = sessionManager.CreateMsgSetRequest("US", 6, 0)
    requestMsgSet.Attributes.OnError = roeContinue
   
    If (requestMsgSet Is Nothing) Then
        Exit Sub
    End If
   
    Dim salesOrderQuery As ISalesOrderQuery
    Set salesOrderQuery = requestMsgSet.AppendSalesOrderQueryRq

   
    salesOrderQuery.ORTxnNoAccountQuery.TxnFilterNoAccount.ORDateRangeFilter.ModifiedDateRangeFilter.FromModifiedDate.SetValue ModDate, False
   
    salesOrderQuery.IncludeLineItems.SetValue True
   
    salesOrderQuery.IncludeRetElementList.Add ("TxnID")
    salesOrderQuery.IncludeRetElementList.Add ("TimeModified")
    salesOrderQuery.IncludeRetElementList.Add ("CustomerRef")
    salesOrderQuery.IncludeRetElementList.Add ("RefNumber")
    salesOrderQuery.IncludeRetElementList.Add ("PONumber")
 
    salesOrderQuery.IncludeRetElementList.Add ("SalesOrderLineRet.TxnLineID")

   
   
    sessionManager.OpenConnection "", "TT"
    sessionManager.BeginSession "", omDontCare
   
    Dim responseMsgSet As IMsgSetResponse
    Set responseMsgSet = sessionManager.DoRequests(requestMsgSet)
   
    sessionManager.EndSession
    sessionManager.CloseConnection
   
    If (responseMsgSet Is Nothing) Then
        Exit Sub
    End If
   
    Dim responseList As IResponseList
    Set responseList = responseMsgSet.responseList
    If (responseList Is Nothing) Then
        Exit Sub
    End If
   
   
    Dim i As Integer
    For i = 0 To responseList.Count - 1
        Dim response As IResponse
        Set response = responseList.GetAt(i)
       
        If (response.StatusCode = 0) Then
            If (Not response.Detail Is Nothing) Then
                Dim responsetype As Integer
                responsetype = response.Type.GetValue
                Dim j As Integer
                If (responsetype = rtSalesOrderQueryRs) Then
                    Dim salesOrderRetList As ISalesOrderRetList
                    Set salesOrderRetList = response.Detail
                    For j = 0 To salesOrderRetList.Count - 1
                        Dim salesOrderRet As ISalesOrderRet
                        Set salesOrderRet = salesOrderRetList.GetAt(j)
                        If (salesOrderRet Is Nothing) Then
                            Exit Sub
                        End If
                        Dim insSQL As String
                       
                        insSQL = "INSERT INTO SalesOrder" _
                            & "(SalesOrderID,DateModified,CustomerListID,CustomerFullName, ReferenceNumber,PurchaseOrderNumber)" _
                            & "VALUES" _
                            & "("
                   
                                                               
                            insSQL = insSQL & "'" & salesOrderRet.TxnID.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.TimeModified.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.CustomerRef.ListID.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.CustomerRef.FullName.GetValue & "',"
                            insSQL = insSQL & "'" & salesOrderRet.RefNumber.GetValue & "',"
                            If (Not salesOrderRet.PONumber Is Nothing) Then
                                insSQL = insSQL & "'" & salesOrderRet.PONumber.GetValue & "');"
                            Else
                                insSQL = insSQL & "'');"
                            End If
                            accessDB.Execute insSQL
                   
                                If (Not salesOrderRet.ORSalesOrderLineRetList Is Nothing) Then
                                Dim m As Integer
                                    For m = 0 To salesOrderRet.ORSalesOrderLineRetList.Count - 1
                                        Dim orSalesOrderLineRet As IORSalesOrderLineRet
                                        Set orSalesOrderLineRet = salesOrderRet.ORSalesOrderLineRetList.GetAt(m)
                               
                                        If (Not orSalesOrderLineRet.SalesOrderLineRet Is Nothing) Then
                                            Dim insSQL2 As String
                                            insSQL2 = "INSERT INTO SalesOrderLine" _
                                                & "(SalesOrderLineID)" _
                                                & "VALUES" _
                                                & "("
                                           
                                                insSQL2 = insSQL2 & "'" & orSalesOrderLineRet.SalesOrderLineRet.TxnLineID.GetValue & "',"
                                           
                                                insSQL2 = insSQL2 & "'');"
                                         
                                           
                                            accessDB.Execute insSQL2
                                   
                                        End If
                                Next m
                                 
                            End If
                   
                    Next j
                                   
                End If
                           
            End If
                   
        End If
         
    Next i
       
End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros