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 , , , .

43 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.

  15. why i can connect to quickbooks

    im having an error of this

    runtime error ‘- 2147221164 80040154 )’: class not registered

    please help!!.

  16. I got this error, too. It pops up on “Set qbSessmgr = New QBFC13Lib.QBSessionManager”

    Note that I did put in the reference for QBFC13Lib and the VBA editor (for Excel) is auto-completing based on the class.

    • Hi Dan,

      Thanks for stopping by. It has been awhile since I worked with the Quickbooks SDK. There used to be an online community of people using the SDK. You may have better luck looking there.

      Or I would need to log into your system to take a look with you at what is going on.

      Thanks,
      MM

  17. Dan,

    What bit-version are you running? Even today, MS still recommends running the 32-bit version of Office in lieu of the (more bug-filled) 64-bit version.

    First thing to try, run a “Repair” on your MS Office. If that fails to resolve the issue, reinstall. If that fails to resolve the issue and you’re running MS Office 64-bit, reinstall the 32-bit version.

    Note: If you NEED the 64-bit version of other apps, you can selectively uninstall/re-install individual components, such as Access or Excel.

    This error can also appear if QuickBooks (desktop) wasn’t installed “correctly”, most commonly the app was installed using a local user account. Most users wont see an issue, but as a developer, when you try to do “certain things” strange errors occur. Uninstall QB and re-install using a full-rights Administrator account.

    Good hunting…

  18. After a lot of googling, my best guess is that the problem is that the SDK is only 32-bit, and I am running 64-bit Excel. The solution would be to uninstall ALL off Office, and re-install it as 32-bit. Individual components can’t be selectively reinstalled.

    I’ll probably give that a try later.

    This leaves me disappointed in both Intuit and Microsoft.

    • Hi Dan,

      That might be it… Intuit has been particularly frustrating in this regard. Both in their documentation, which was pretty awful, and their support of developers using their tools.

      Sorry I couldn’t be more help.

  19. hello, I’m stuck right off the bat here.
    I get syntax error on this line(qbSessmgr .OpenConnection “”, , “Our Test QB App”)
    I’m thinking that it doesn’t like to double quotes?

    • Hi Aaron. It’s been several years since I worked on this project. I don’t know what changes, if any, the Quickbooks SDK has implemented. It has always been temperamental. 😉

      Sorry I cannot offer a lot more guidance. Intuit used to have an online forum. You might see if there is additional information there.

    • Aaron. Hopefully you saw Jeanne’s information below. I don’t know if it will be helpful but I do wish you luck on getting it working.

      If you do, perhaps add a comment here so others might find it.

      Thanks.

  20. Aaron –

    I have code that has been running for years. Here is what works for me:

    Set QB = CreateObject(“QBFC7.QBSessionManager”)
    ‘get the qb file information
    QB.OpenConnection “”, “Upload Payroll”
    QB.BeginSession strDatabase, omMultiUser

    Where strDatabase is the name of the database. One other way I have tested it is to hard-code the database to connect to, like:

    QB.BeginSession “\\OurServer\QuickBooks_Data\QBDatabase.qbw”, omMultiUser

    Please keep in mind this is working with Quickbooks 7 (which is really old). Newer SDKs may have different syntax.

    • Thanks Jeanne. Aaron, I hope this is helpful.

      I’ve moved away from most of my VBA development into automation using Google Apps Scripts and using Javascript with Node and Express.

      I apologize that I never went into the XML parsing routines.

  21. i was able to make it work after a bit, now i am looking for a way to put the data on a MS Accses table or excel file rather then xml file, is there a way?

    • Excellent Aaron. You need to add the Microsoft XML library to your project. There are many articles that describe how to parse XML – grabbing data as needed. I believe I even parse XML on this project – the Google Maps API blog entry. You can download the code and see how I am parsing it. The structure for the XML you receive is different.
      Google Maps API

      My advice: Save the XML into a file and then write a routine that opens the file and puts the contents into a XML object. That way, you are not running the Quickbooks code repeatedly.. it is just easier/faster to write the functions totally separate. Whether you pass the Quickbooks response to an XML object or the contents of a file, it is the same XML.

    • When you make a request, you get back a response object. There’s no need to save that as an .xml file then open and parse it, just process the response, e.g. if you make a CustomerTypeQuery, you get back an object which can be simply parsed and whatever fields you need added to your database using a standard SQL INSERT. See https://developer.intuit.com/app/developer/qbdesktop/docs/api-reference/qbdesktop/customertypequery for example, and clcik the response tab on that page, or run your code in debug mode and add a watch to the declared response object.

  22. I have ever only sent data to Quickbooks, not read it back. Can’t help with that. I tried to find the documentation I used years ago to do this but I can’t find it after several new computers. Good luck.

Leave a Reply

Your email address will not be published.