Microsoft Access Pros QuickBooks Link Developer's Guide

This is the Developer's Guide for QuickBooks Link. QuicksBook Link is a Microsoft Access executable (qblink.accde) library that allows communication directly with QuickBooks versions from QuickBooks 2002 up to QuickBooks 2015 (and likely future years). This library allows any developer who is familiar with VBA to push objects from Microsoft Access into QuickBooks or pull objects from QuickBooks into Microsoft Access tables.

The library will allow the following objects to be pushed to or pulled from QuickBooks.

Customers (push, pull, modify)
Vendors (push, pull, modify)
Invoices (push, pull, modify, delete)
Invoice Lines (push, pull, modify, delete with Invoices)
Purchase Orders (push, pull, modify, delete)
Purchase Order Lines (push, pull, modify, delete with Purchase Orders)
Sales Receipts (push, pull, modify, delete)
Sales Receipt Lines (push, pull, modify, delete with Sales Receipts)
Vendor Bills (push, pull, modify, delete)
Vendor Bill Lines (push, pull, modify, delete with Vendor Bills)
Payments (push only)
Classes (push only)
Payment Methods (push only)
Sales Tax Codes (push only)

Registration

Open the qblink.accde by double-clicking on it.
You will see the version and a button to Register.
Press the Register button and follow the Register Wizard.
You will need to create a user name and login on first use, so choose that option. The wizard should be self-explanatory beyond that.
When prompted, choose Standalone versus the Network license.
If you move the qblink.accde to a different folder, you will need to copy the qblink.lic file along with it, or else you will need to re-register.
Once you have registered, you will see your Product

Application

The application referencing qblink.accde must attach all the tables in QBData.accdb
The library must be referenced under Tools, References in the Access Code window.

Interface

All functions return a Boolean value of True if successful, or False if not successful. If False, any error messages are displayed to the user and recorded in the table tblQBErrors.
The library needs to know the complete path to the QuickBooks file. The table tblQBSetup contains that path, and other setup information.
- strUser is an optional field in case you want to offer a different setup per user.
- strQuickBooksPath is a required field containing the complete path to the QuickBooks file, e.g. C:\Users\Brad\Documents\Brad\NorthwindTraders.qbw. This field can be a Memo field or a Text field if the path is less than 255 characters.
- datCustomerImport is the date of the last Customer Import. This is set by the library, and should initially be null.
- datVendorImport is the date of the last Vendor Import. This is set by the library, and should initially be null.
- strInvoiceTerms must match the Invoice Terms to be used when creating an Invoice, e.g. Net 30.
- binCurrent must be set to true for one and only one record. This record is the current QuickBooks record to use for all transactions. This field allows an application to work with multiple QuickBooks files, one at a time.
All functions create an entry into tblQBActivityLog, if successful, to store the date/time and significant details of each.
The connection to QuickBooks is automatically established with each transaction if not previously connected, and automatically closed at the end of each transaction if opened within the transaction. The recommended method is to establish a connection before all transactions and close after all transactions unless the user action requires only one transaction at a time, for example if pushing a Customer as soon as a Customer is created.

Included Functions and Classes

See the included Functions and Classes for a detailed description of all the Functions available to developers.

Sample Code

The code snippet below shows an example of adding a customer to QuickBooks.

Dim qbCustomerAdd As Object
Dim strCustomerListId As String
Dim binSuccess As Boolean

Set qbCustomerAdd = cwGetQBCustomerAdd()
logRaiseErrorQb qbCustomerAdd.SetCompanyId(rsOrders!lngOrderId)
logRaiseErrorQb qbCustomerAdd.SetCompanyName(rsOrders!strCompanyName)
logRaiseErrorQb qbCustomerAdd.SetFullName(CStr(rsOrders!lngOrderId) & " " & rsOrders! strCompanyName)
logRaiseErrorQb qbCustomerAdd.SetNotes(Nz(rsOrders!memNotes))
logRaiseErrorQb qbCustomerAdd.SetBillToAddress1(rsOrders!strOrderName)
logRaiseErrorQb qbCustomerAdd.SetBillToAddress2(Nz(rsOrders!strAddress1))
logRaiseErrorQb qbCustomerAdd.SetBillToAddress3(Nz(rsOrders!strAddress2))
logRaiseErrorQb qbCustomerAdd.SetBillToCity(Nz(rsOrders!strCity))
logRaiseErrorQb qbCustomerAdd.SetBillToState(Nz(rsOrders!strRegion))
logRaiseErrorQb qbCustomerAdd.SetBillToPostalCode(Nz(rsOrders!strPostalCode))
logRaiseErrorQb qbCustomerAdd.SetShipToAddress1(Nz(rsOrders!strAddress1))
logRaiseErrorQb qbCustomerAdd.SetShipToAddress2(Nz(rsOrders!strAddress2))
logRaiseErrorQb qbCustomerAdd.SetShipToCity(Nz(rsOrders!strCity))
logRaiseErrorQb qbCustomerAdd.SetShipToState(Nz(rsOrders!strRegion))
logRaiseErrorQb qbCustomerAdd.SetShipToPostalCode(Nz(rsOrders!strPostalCode))

binSuccess = qbCustomerPushIntoQuickBooks(qbCustomerAdd, strCustomerListId) 

Set qbCustomerAdd = Nothing

This code snippet shows an example of adding an Invoice to QuickBooks.

Dim qbInvoice As Object
Dim strInvoiceListId As String
Dim strInvoiceTxnId As String
    
Set qbInvoice = cwGetQBInvoice()

logRaiseErrorQb invInvoice.SetCompanyId(rsInvoices!lngCompanyId)
logRaiseErrorQb invInvoice.SetCustomerId(rsInvoices!strQuickBooksId)
logRaiseErrorQb invInvoice.SetCustomerName(rsInvoices!strCompanyName)
logRaiseErrorQb invInvoice.SetRefNumber(rsInvoices!strReferenceNumber)
logRaiseErrorQb invInvoice.SetTemplate(c_strQBTemplateName)
logRaiseErrorQb invInvoice.SetInvoiceDate(Nz(rsInvoices!datInvoice))
logRaiseErrorQb invInvoice.SetInvoiceDateDue(Nz(rsInvoices!datInvoiceDue))
logRaiseErrorQb invInvoice.SetTerms(Nz(DLookup("strInvoiceTerms", "tblSetup"), "Net 30"))
logRaiseErrorQb invInvoice.SetARAccountRefFullName(c_strARAccountRefFullName)
logRaiseErrorQb invInvoice.SetMemo(strMemo)
logRaiseErrorQb invInvoice.SetTotal(curAmount)

logRaiseErrorQb invInvoice.SetBillToAddress1(Nz(rsInvoices!strCompanyName))
logRaiseErrorQb invInvoice.SetBillToAddress2(Nz(rsInvoices!strAddress1))
logRaiseErrorQb invInvoice.SetBillToAddress3(Nz(rsInvoices!strAddress2))
logRaiseErrorQb invInvoice.SetBillToCity(Nz(rsInvoices!strCity))
logRaiseErrorQb invInvoice.SetBillToState(Nz(rsInvoices!strRegion))
logRaiseErrorQb invInvoice.SetBillToPostalCode(Nz(rsInvoices!strPostalCode))

'Get the invoice lines, and fill them
Dim invlinInvoiceLine As Object

'Create a new Invoice Line object and add it to the Invoice
Set invlinInvoiceLine = cwGetQBInvoiceLine()
     
logRaiseErrorQb invlinInvoiceLine.SetAmount(curAmount)
logRaiseErrorQb invlinInvoiceLine.SetPrice(curAmount)
logRaiseErrorQb invlinInvoiceLine.SetItem(c_strItem)
logRaiseErrorQb invlinInvoiceLine.SetDescription(Nz(rsInvoices!strInvoiceDesc))
logRaiseErrorQb invlinInvoiceLine.SetIsTaxable(False)
logRaiseErrorQb invlinInvoiceLine.SetQuantity(1)

binSuccess = invInvoice.AddInvoiceLine(invlinInvoiceLine)
If (Not binSuccess) Then
    Exit Function
End If

binSuccess = qbInvoicePushIntoQuickBooks(qbInvoice, strInvoiceListId, strInvoiceTxnId) 

Set qbInvoice = Nothing

Demo Application

The Northwind Demo Application demonstrates many of the above functions, specifically:

Opening a Connection to QuickBooks
Pushing Customers
Pushing Invoices
Pushing Payments
Pushing Sales Receipts
Pushing Vendors
Pushing Purchase Orders
Pushing Bills

This demo uses Microsoft’s Northwind Trader’s database for the data. The goal here is to show that the QuickBooks interface can link with any data, and to demonstrate how to use QuickBooks Link to push objects into QuickBooks.

QuickBooks Open

The library requires the associated QuickBooks .qbw is open, either by opening manually, or by opening before any transactions using the qbConnectionOpen function.

First Run

The first time the application interfaces with QuickBooks, the user will be prompted by QuickBooks to allow access. This is a security measure provided by QuickBooks to prevent unauthorized access. Choose "yes, always; allow access even if QuickBooks is not running" or "Yes, whenever the Quickbooks company file is open" if you prefer.

QuickBooks Security form

Error Handling

Errors will be logged to the table tblQbErrors and displayed to the user via a MsgBox. Each record will contain either the QuickBooks or Microsoft Access error number, the error message, the date and time of the error, the function where the error occurred, the windows login id of the user, and the library version. Additional information will be recorded where appropriate, such as the Invoice List Id if retrieving an Invoice.