Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1341
  • Last Modified:

Reading QB Customers

Hello all,
This is probably pretty basic abut I have had this dumped in my lap and need to do this as quickly as possible.

How can I using the SDK for QB loop through the customer list and return all fileds for all customers so I can read them into an Access table.

Thnks in Advance
1 Solution
HainKurtSr. System AnalystCommented:
export it to a csv file and use import feature of access...
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The QB SDK can be somewhat confusing, and not all data can be returned using the SDK (QB maintains strict control over how data is moved into the database, thus the need for the SDK).

If you need a quick-quick solution, you can always purchase and use one of the various QB "ODBC" connectors, like this one: http://www.qodbc.com/

Otherwise, you'll need to work with the SDK and get the information through there. The SDK includes quite a few code samples which are VB-centric, but will work fine in Access.
DeadcatsAuthor Commented:
Thanks I discussed thaat with management but this may become a two way thing. does anyone have a code sample to red the data out of QB's?


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'll need the SDK: http://developer.intuit.com/

Next, you'll need to build a query to the QB database. The SDK includes an "OnScreen Reference" which shows you the various items you can work with in QB, and it has VB code samples, and there are plenty of Sample projects included with the SDK but they require you to have VB 6.0 or .NET. This is very, very complicated, and not all things are supported but you can certainly get the data from QB that you need.

QB must be running in order for the attached sample to work; you can make this work without QB running, but it takes a little tweaking.

Note that the first time you "run" your application against QB,  you must accept it. QB will present you with an "Application Certificate" which you must accept in order for your code to work (this helps to make sure that errant/malicious code can just automatically interact with your QB data). Generally, if you're going to use this regularly, you'd want to select the last option (Always, even if QB not running).

I've included a sample db that shows how to do this; the sample includes a class I use to connect to QB, so it's a little different from the samples you might see with QB. Here's the link:


Open the db, then click the Load Listbox button.

The "heavy lifting" is done in the LoadAccounts routine in basRoutines. Review that to see how to build a query for QB. Also, when you download the SDK read over the Programmer's Guide to understand how this all works. As I stated earlier, QB has an odd way of interfacing with the data, but when you understand how and why they do it, it makes a bit more sense (not much, mind you, but more <g>). You interface with QB through Requests and Responses. Basically, you build a Request and submit it ... QB provides you a Response telling you what happened, whether it succeeded or failed. If it fails, the Response usually gives some indication of why so you can fix the problem.

Note also that the sample I've uploaded used the 6.0 SDK. If you download the 7.0 version, you might need to change the Reference in the Access database to point to the correct one.

Note: I copied this from one of my postings on this question:
Bill BachPresidentCommented:
By far, the easiest way to do this is with QODBC.  It is a paid product, but it vastly simplifies what you are trying to do.  There are multiple versions, so be sure to get the one with Write capability if you think you will need to make changes to your QB database from MSAccess.

Once you have QODBC installed and configured (the 30-day trial can also be used to get started, too), then you can access the data directly.  The core table you want is called "Customer" for obvious reasons.  You can bring it up directly in MSAccess by creating a query called "Customers" and entering this statement:  "SELECT * FROM Customer".  Then, change the query type to SQL Pass Through, and set the ODBC DSN to the "QuickBooks Data" DSN, created automatically by QODBC.

You can also right-click on the Tables screen and select "Link Data...", then provide the ODBC data source and select the Customer table.  Note that this gives you a LIVE link to the QB data, and any changes that you make will be made (by QODBC) to the QB database.

If you just want a private COPY of the data in MSAccess, then start a new Access database, and instead of Linking to the data, right-click the tables screen and select "Import Data...".  Supply the QODBC data source and then select the Customer table, and it should import right into Access.  This will be a private copy that you can manage as you see fit.

You can also create other queries as needed.  For example, we have a customer status of CLOSED that we use for out-of-business customers, and we like to get things in alphabetical order.  Further, I only need basic adderss info, so I use a more direct query like this:

SELECT Customer.FullName, Customer.Contact, Customer.Phone, Customer.EMail, Customer.BillAddressCity, Customer.BillAddressState, Customer.BillAddressAddr1, Customer.BillAddressAddr2, Customer.BillAddressAddr3, Customer.BillAddressAddr4, Customer.BillAddressPostalCode, Customer.BillAddressCountry
WHERE (Customer.CustomerTypeRefFullName<>'CLOSED')
ORDER BY Customer.CompanyName

I use a tool like SQLExec to export this data to a text file, and then import it right into my other database for better searching capabilities.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I agree with Bill - if you don't have the time or skillset necessary to really understand the SDK, then QODBC is the way to go ...
DeadcatsAuthor Commented:
Thanks Bill and LMS consulting for your help.. I have to go with the SDK and not a third party product just the rules that we have to adhere to.

With that said, i have modified the code and can get information returned from QB but I am confused because when I try to get some fields such as the Account Number from QB I get an errror 91. I have included my modified code and the changes are bracketed with comment lines. If you would be kind enought to take a look at it and see what I'm doing wrong would be a great help.


Function GetCustomerAccounts(gQBSession As clsQBSession) As Boolean
'/    ListboxToFill
'/Returns: Boolean
'/Created: 1/11/2008
'/Created By: Scott

On Error GoTo Err_GetAccounts
  Dim qryAccts         As ICustomerQuery    'IAccountQuery
  Dim lstResponse      As IResponseList
  Dim rsp              As IResponse
  Dim acct             As ICustomerRet     'IAccountRet
  Dim lstAccts         As ICustomerRetList 'IAccountRetList
  Dim reqAcct          As IMsgSetResponse
  Dim rspAcct          As IResponse
  Dim i                As Integer
  Dim j                As Integer

  CurrentProject.Connection.Execute "DELETE * FROM tQBAccounts"
  'Set qryAccts = gQBSession.QBMessageRequest.AppendAccountQueryRq
  Set qryAccts = gQBSession.QBMessageRequest.AppendCustomerQueryRq
  'qryAccts.ORAccountListQuery.AccountListFilter.ActiveStatus.SetValue asActiveOnly
  qryAccts.ORCustomerListQuery.CustomerListFilter.ActiveStatus.SetValue asAll
  Set reqAcct = gQBSession.QBSession.DoRequests(gQBSession.QBMessageRequest)
  Set rspAcct = reqAcct.ResponseList.GetAt(reqAcct.ResponseList.Count - 1)

    If rspAcct.StatusCode <> 0 Then
      GetCustomerAccounts = False
      '/got something back
      Set lstResponse = reqAcct.ResponseList

        '**Code_Err:: Replaced If lstResponse Is Nothing Then Exit Function
        If lstResponse Is Nothing Then GoTo Exit_GetAccounts

        For i = 0 To lstResponse.Count - 1
          Set rsp = lstResponse.GetAt(i)

            If rsp.StatusCode = 0 Then
                If rsp.Type.GetValue = rtCustomerQueryRs Then
                  Set lstAccts = rsp.Detail

                    For j = 0 To lstAccts.Count - 1
                      Set acct = lstAccts.GetAt(j)
                        With acct
                          CurrentProject.Connection.Execute "INSERT INTO tQBACcounts(sListID, sName, iType) VALUES('" _
                            & .ListID.GetValue & "','" & .FullName.GetValue & "','" & .AccountNumber.GetValue & "' )"
                        End With
                    Next j
                End If 'If rsp.Type.GetValue = rtAccountQueryRs
            End If
          Next i
        End If 'If rsp.StatusCode = 0


  On Error Resume Next
  Set qryAccts = Nothing
  Set lstResponse = Nothing
  Set rsp = Nothing
  Set lstAccts = Nothing
  Set reqAcct = Nothing
  Set rspAcct = Nothing
  Exit Function


  Select Case Err
    Case Else
     MsgBox "An error occurred in this application." & vbCrLf & vbCrLf & Err & ":" & Error$ & vbCrLf & vbCrLf & "Technical Information: Occurred in [Module1].[GetAccounts]", vbCritical, "Application Error"
  End Select
  Resume Exit_GetAccounts
End Function

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There's really nothing wrong with the code, but you'd probably want to make this more robust.

You should always check the returned values to see if they are set to anything:

Dim sAcctNum As String
Dim sListID As String
Dim sFullName As String

If Not acct.ListId Is Nothing Then  sListId = acct.ListId.GetValue
If Not acct.AccountNumber Is Nothing Then sAcctNum = acct.AccountNumber.GetValue
If Not acct.FullName Is Nothing Then sFullName = acct.FullName.GetValue

NOW insert into the table.

BTW, unless you've changed the DataType for the field named "iType", you'll get an error when trying to insert into tQBAccounts
Just a side note. ODBC is only a paid product in Pro?Premier. It comes included with Enterprise Solutions.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now