Connecting Microsoft Access and Quickbooks using the Quickbooks SDK part 1 of 3

Posted on by

Aging Post Note:

I considered taking this down. At this point, I'm not writing the series. I am busy with a myriad of projects - both programming and in other areas (see my music/writing blog).

However, I will publish some VBA SDK code as more or less stand-alone blog entries. I'll categorize them under the Quickbooks SDK category.

They won't have the step-by-step tutorial feel - per se. But it will be code that I've had working on my system or at a client.

 

Summary:

These blog entries are meant to jumpstart your ability to connect Quickbooks (desktop editions) to Microsoft Access. I’m not going to go into a lot of detail. Instead, I’ll provide links to other resources with additional information.

I’ll cover items I found lacking or confusing in the SDK documentation and in what I found online. We've used the Quickbooks SDK for a number of client projects. Some of those include:

  • Create payroll records from a web-based asp.net, SQL Server application we wrote;
  • Re-allocate accounting line items across multiple bills and vendors to appropriate jobs and GL accounts;
  • Create job and sub-jobs from web-based and desktop applications;
  • Provide automated reporting directly to Excel from multiple data sources including SQL Server, Quickbooks, and web services.

Microsoft Access and the Quickbooks SDK

The Quickbooks SDK, found here, provides a series of objects, properties, and methods to let you build applications that interact directly with Quickbooks data.

In the next few blog entries, I’m going to demonstrate how to integrated Microsoft Access and Quickbooks. We use this same technology to connect SQL Server, asp.net, VB.NEt and other technologies to Quickbooks.

The Quickbooks SDK documentation is long and remarkably confusing and unclear.. but it does provide a powerful way to integrate your other systems with Quickbooks. I hope this helps you get started on using it.

What we are going to do today

  1. Sign-up for the Intuit Developer’s Network
  2. Download & Install the Quickbooks SDK
    When installed, create a shortcut in Firefox to the SDK on your desktop (Chrome and IE do not work properly)
  3. Create an Access database with a basic form and Quickbooks connectivity code
    I called mine: TestQBSDK.accdb
  4. Authorize our Access application to access the Quickbooks Company File
  5. Retrieve Quickbooks Company data
  6. Save it as a text file

Later, we’ll parse the Quickbooks data and update an Access table. Sounds exciting, yes?

Create an Acess DB

As stated above, I called mine, TestQBSDK.accdb but you may call yours anything you like.

  • Add a blank form to your database. I called mine: frmQBTest
  • Add a button named, btnQBAuth caption: “Authorize DB”
  • Add a button named, btnQBGetCust caption: “Get Customers”
  • Add a text box (with scrollbars) named, txtXML

Code References

View the code of your form. Set the following references:

  • qbFC13 1.0 Type Library (unless a later SDK exist)
  • Microsoft XML, v6.0

Authorize Access to access our customer file

The first time you access your customer file, you’ll be prompted to allow your application to access it. The most basic connection session must be made to do this.

  • Make sure your Quickbooks company file is open.

On the btnQBAuth, add the following code:


Dim qbSessmgr As QBSessionManager
Set qbSessmgr = New QBSessionManager
qbSessmgr .OpenConnection "", "Our Test QB App"
qbSessmgr .BeginSession "", omDontCare   ‘App with authorize right here.
qbSessmgr .EndSession
qbSessmgr .CloseConnection




Run this code by pressing the command button. In quickbooks, on the .BeginSession line, you’ll be presented with the following Application Certificate screen. Later, you’ll want to make sure you are using the same application name used in the .OpenConnection line. In my case, “Our Test QB App”.


I’m giving the app full permission, even when the application is not open.
 quickbooks-application-certificate 


Confirm that you are allowing access:
 quickbooks-access-confirmation
 


Retrieving the customer list


For the sake of this demo, I am going to retrieve the customer list. Quickbooks uses XML to send and receive data. I’m not going to parse the customer list until the next blog entry. Instead, I’m going to retrieve it and save it as an XML file - which you can download below.


I use a simple Session with a CustomerQuery request to Quickbooks. I then save the resulting XML into a text file with an XML extension. Here is the code to accomplish this.


QBCustQuery function


This function connects to the open Quickbooks company file, creates a Customer query, and returns the XML with customer information.



Function QBCustQuery()

Dim smgr As QBSessionManager
Set smgr = New QBSessionManager

smgr.OpenConnection "", "Our Test QB App"
smgr.BeginSession "", omDontCare

Dim rMsg As IMsgSetRequest
Dim rMsgr As IMsgSetResponse
Dim custlist As ICustomerQuery

Set rMsg = smgr.CreateMsgSetRequest("US", 13, 0)
Set custlist = rMsg.AppendCustomerQueryRq
Set rMsgr = smgr.DoRequests(rMsg)

strResponse = rMsgr.ToXMLString
smgr.EndSession
smgr.CloseConnection
QBCustQuery = strResponse

End Function



MakeFile function


Simple function, takes two parameters. A string of text and a file location and name.


I place the MakeFile function in a separate module available to my entire Access DB.


Function MakeFile(inFText, inFileName)

 Set oFS = CreateObject("Scripting.FileSystemObject")
 Set nRF = oFS.CreateTextFile(inFileName, True)
 nRF.Write inFText
 Set nRF = Nothing
 Set oFS = Nothing
 MakeFile = True

End Function



Running the code


On the btnQBGetCust button, run the following:


strCustXML = QBCustQuery()
Me.txtXML.Value = strCustXML  ‘places XML into the form’s text box
blnMakeFile = MakeFile(strCustXML, "C:\data\CustXML.xml")



My Quickbooks customer list


quickbooks-customer-list


My XML File


Below is an image of the resulting XML but you can download and view the actual XML by clicking the link below. Save it as an XML file to your desktop and open with Internet Explorer or other XML viewer.
 


DOWNLOAD CUSTOMER XML


quickbooks-customer-xml


Next time:


We’ll parse the XML and I’ll explain why I don’t use the Quickbooks ICustomerRetList.
Posted in Consulting, Programming, Quickbooks SDK, SQL Server, Tips and Tools and tagged , , , .

25 Comments

    • Hey Jim.. Thanks! I apologize that parts 2-3 aren’t published yet.

      Yes there will be parts 2 & 3 but we were hired onto several projects and got backlogged. I just need to sit down and get it done.

  2. Really good article. I am looking forward to parts 2 and 3. When I run the code I get an error: “The version of QBXML that was requested is not supported or is unknown.” Any suggestions on where I went wrong. This is happening when we get to the DoRequests line. Here is the pertinent section of the code:

    Set rMsg = smgr.CreateMsgSetRequest(“US”, 13, 0)
    Set custlist = rMsg.AppendCustomerQueryRq
    Set rMsgr = smgr.DoRequests(rMsg)

    If it makes a difference, we are running on QB Pro 2012…

    • Try referencing 11 instead of 13. I believe the version of QBXML is a version behind the version of Quickbooks you are running. Of course, I haven’t looked that up but I thought that was the case.

    • Scott.. you and several others – including us. Our recent development schedule took us out of blogging anything new.. it keeps coming up and we keep pushing it back. My apologies.

      We do have it on the calendar for Sunday and hope to do an abbreviated, mostly code, blog entry.

  4. Wow! Just what I was looking for. I need to write some Access invoices to a client’s QB data. Are you going to cover something like that? I’ve used Access since Version 1 but I’m an XML novice. I know there are several import options for sale and a QODBC driver which I gave up on. I just want to write a simple invoice record using my own hands. Importing is too complicated for my client!

    • Hi Ed.. I’ll look into something to show how this is done. I’ve been remissing in updating this blog, so hopefully we can get on it.

  5. Maybe you can help me. I have some legacy code that passes transactions from Access to QuickBooks using the SDK (version 7). After we upgraded our computers to Windows 10 we get a “Cannot start QuickBooks” error on the BeginSession statement. What is weird is that the same exact code works in Excel, but not Access. All this works fine on computers that are still running Windows 7. Have you seen anything like this?

    • Jeanne, I haven’t seen this. I’d have to do more research. Either that or consider upgrading to the newer SDK – most of the rest of your code should work fine. You’d have to change the library version.

      • I did try upgrading to version 13 of the SDK with no difference. I have a feeling it is a COM dll or something along those lines, but I have no idea for sure what is different between the two applications.

  6. To everyone who has commented here… You may have guessed, I’m not updating this tutorial. but I will be providing Quickbooks SDK code – primarily VBA at this point. It will be mostly unadorned – a starting point. But it will have all worked either on my computer or at a client.

    I will categorize them under my Quickbooks SDK category:
    https://www.pulseinfomatics.com/category/quickbooks-sdk/

  8. qbSessmgr .OpenConnection “”, “Our Test QB App”

    Right after this code runs how can I determine if the connection is actually open?

    Thanks

    • Hey TD,

      Were you and I supposed to talk a couple weeks back? I apologize if I missed that. Or am I mistaking you for someone else?

      I’ve never had the command run successfully when the connection was not successful. However, you might run a query for some basic company data from a small data set (I’d have to look at querying company file data) and trap for any errors.

      Are you running into a specific problem that suggest the connection is not open properly?

  9. Thanks for getting back to me. The very next line of code after the code I posted is “smgr.BeginSession “”, omDontCare”. This is where the error is triggered if QB is not open.

    Is there no way to tell if the connection was successful or not? I have not had any success in trapping for the error but I’ll try again.

    Is there any way to contact QB tech support to see if they have nay suggestions?

    If you can think of anything else to try I would greatly appreciate it !!!

    • There are forums but I would post it there and there may be a community on stackoverflow – risk the scorn 😉

      I’d have to play with that and see what to do when QBooks isn’t open. Also, I thought there was a way to force open it. Hmmm..

      I’m on some other projects and don’t have QBooks on this system right now. But let me know if you find a solution. If I can get to it and a VM I’ll see what I can come up with.

  13. Hi, Matthew. The idiot is back again :). I really appreciate what you’ve put out there, and have used this “base” to access several different data points. What I’m having a hard time figuring out is how to filter some lists. I can successfully filter PO and Invoice, for example, but Customers I cannot make a FromModifiedDate and ToModifiedDate filter work. I’m sure you’ve worked that out already. I’d appreciate any guidance you could provide. Below is what I’ve used for other queries (modified a little for Customers) that you can laugh at. It errors on the SetValue, saying argument not optional. But what’s weird is that a similar statement does not when querying PO’s or Invoices. Thanks! Stephen

    ——-
    Sub QueryCustomer(ByRef custlist As ICustomerQuery)
    custist.IncludeRetElementList.setValue True
    custlist.ORCustomerListQuery.CustomerListFilter.FromModifiedDate.setValue “1980-01-01”
    custlist.ORCustomerListQuery.CustomerListFilter.ToModifiedDate.setValue “2017-12-31”

    End Sub

    • I’m not sure what you’re asking. Where are you having a specific problem or attempting to use those symbols? I may not be able to respond for some time as I’m on a data dashboard project for the next several weeks/months.

Leave a Reply

Your email address will not be published. Required fields are marked *