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

Aging Post Note:

See Edit below 1/9

I’ve received some well-deserved snarky comments due to my failure to post parts 2 & 3. Okay okay okay! I have heard you loud and clear! Some of you were really funny too. Today is, 11/28/2016. I will have what amounts to parts 2 & 3 (though could be a single post), with sample code, by next Wednesday (12/7). I was working on it this morning. If you want an email when I publish it, submit your email using this form. I will NOT add you to any list and will delete your email upon sending the update…. I promise.. 😉

Added 1/9/2017: I completed the tutorial code and will upload it shortly but also discovered a bug in the QBSDK. I’ll notate what that is and hopefully have a workaround/solution this week.

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.

Follow Matthew Moran:

President/Founder of Pulse Infomatics. Father, author, and occasional rock star!

11 Responses

  1. Excellent article (Connecting Microsoft Access and Quickbooks using the Quickbooks SDK part 1 of 3) !!
    Will there be a part 2 and 3 eventually?

    • Matthew Moran

      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. Dave Perrault

    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…

    • Matthew Moran

      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.

  3. I’m liking the start of this trilogy… but the wait is killing me!

    • Matthew Moran

      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!

    • Matthew Moran

      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. Jeanne Dixon

    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?

    • Matthew Moran

      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.

      • Jeanne Dixon

        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.

Leave a Reply