?
Solved

Reading QB Customers

Posted on 2010-01-11
9
Medium Priority
?
1,333 Views
Last Modified: 2013-11-27
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
DC
0
Comment
Question by:Deadcats
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 57

Expert Comment

by:HainKurt
ID: 26285795
export it to a csv file and use import feature of access...
0
 
LVL 85
ID: 26285889
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.
0
 

Author Comment

by:Deadcats
ID: 26285964
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?

Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85
ID: 26291905
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:

http://www.infotrakker.com/ProgSamples/QBInterface.zip

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:
http://www.experts-exchange.com/Software/Industry_Specific/Financial/QuickBooks/Q_23074711.html
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 26296514
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
FROM Customer NOSYNC
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.
0
 
LVL 85
ID: 26296567
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 ...
0
 

Author Comment

by:Deadcats
ID: 26299716
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.

DC

Function GetCustomerAccounts(gQBSession As clsQBSession) As Boolean
'/Purpose:
'/Parameters:
'/    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

  gQBSession.ClearMessageRequests
      
  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
    Else
      '/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



Exit_GetAccounts:

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

Err_GetAccounts:

  Select Case Err
    'case
    'case
    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

0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 26301929
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
0
 
LVL 11

Expert Comment

by:stlinae
ID: 26311203
Just a side note. ODBC is only a paid product in Pro?Premier. It comes included with Enterprise Solutions.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question