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.
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
- Sign-up for the Intuit Developer’s Network
- 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)
- Create an Access database with a basic form and Quickbooks connectivity code
I called mine: TestQBSDK.accdb
- Authorize our Access application to access the Quickbooks Company File
- Retrieve Quickbooks Company data
- 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
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”.
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.
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
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
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.
We’ll parse the XML and I’ll explain why I don’t use the Quickbooks ICustomerRetList.