Update QuickBooks from Microsoft Access

Did you know you can update QuickBooks from Microsoft Access? Even if you don't know SQL? I've been doing it since 2006 and wrote a book about it so you can do it too. Database Magic! with QODBC takes you step-by-step with copy & paste code and screen shots to make 11 Microsoft Access database forms.

You don't have to know VBA, SQL, QODBC or much at all about Microsoft Access. I take you right from the beginning with how to create a blank database and exactly how to do everything you need to create 11 forms, 8 of which update QuickBooks right from Access.

You can pick up the book at Amazon or Barnes & Noble but if you get it today at My e-Store, you'll get $10 off with coupon code VJUETR2L. I only ask that you promise to leave an honest review on Amazon or Barnes and Noble.

You don't need to read all 500+ pages because most of the book is code, screen shots and code explanations. The book includes a password protected link to a pdf file so you can simply copy and paste the code as you go.

You're going to love Database Magic! with QODBC. I created these databases and forms and use them every day but you don't have to pay a programmer. Simply follow the instructions and screen shots.

If you're a programmer, you'll still you'll love Database Magic! with QODBC because all the code is written, tested and organized and includes simple solutions to some aggravating problems when using QODBC.

So what's the book all about?

First, you create a form that imports QuickBooks tables and their schemas. You learn what schemas are and why you need them in the book. For a youtube video of the QuickBooks Tables form, click here.

Second, you create a switchboard that automatically lists forms, reports, queries, tables and modules and lets you open them with a click. Here's a youtube video example of the Automated Switchboard.

Here's another video of the automated switchboard that demonstrates even more if its functions: https://youtu.be/wjxahllO5BU.

Third, you create a customer form that imports and updates QuickBooks data directly from Access. 

Here's a video showing some of the things you can do with the customers form: https://youtu.be/nXw8cOIeTwc.

Here's a video showing how to add fields to the customer form and let the database automatically create drop-down boxes pre-filled with the appropriate lists from QuickBooks: https://youtu.be/TuyHxIoT0t8.

You create a save button and simply change information, click the button and the information is changed in QuickBooks. If you want, you can watch while the information changes in QuickBooks right before your eyes.

The customer form stores a folder path for each customer and lists each customer's files. You can then open those files with a mouse click. Even movie files.

Add the word thumbnail to any image in the customer's folder and the form displays that image for that customer. Separate thumbnails for each customer.

When the form opens for the first time, you're prompted to select a temporary folder and the database stores that path for you. You can change this temporary folder any time you like.

When you create, save or download files, you'll save them in the temporary folder. When ready, you'll move selected files to the customer folder of your choice with a mouse click. No more navigating the computer for folders or remembering where you keep a customer's folder. The database stores the customer folders for you.

After the customer form is complete, you'll paste some code, copy the customers form and turn the copy into 8 additional forms in 2 minutes or less each. Now that's automation!

  1. Vendor
  2. Employee
  3. Other Name
  4. Entity
  5. Item Fixed Asset
  6. Item Non Inventory
  7. Item Other Charge
  8. Item Service

If you're a programmer, you can create these databases and forms for customers.

QuickBooks to PDF Payroll Tax Forms

If you landed here, you are probably frustrated when all of a sudden QuickBooks stopped printing payroll tax forms to PDF.

Until either QuickBooks or Adobe provides a fix, here are two work arounds beginning with the simplest.

Print to the Micorosft XPS Document Writer and then print to Adobe.

1. Select Print for Your Records from the tax form window.
2. Select Printer Setup from the Printing window.
3. Select the Microsoft XPS Document Writer from the list of printers.
           (Save the XPS document in a temporary folder (see The Link Principle))
4. Open the XPS document and select File then Print
5. Select Adobe PDF from the list of printers
6. Print the tax form

QuickBooks Payroll Tax Form


Print to Snagit then to PDF.

If you choose Snagit as the printer, you will only get the first page, even if you navigate to each page individually. For example, if you navigate to page three of the tax form and select Snagit as the printer, you will still only get page one.
Follow these steps to print all pages to Snagit individually and then combine in PDF.
1. Set Snagit as the default printer.

2. Right-click in the left or right margin of the page in the tax form that you want to print and select Print This Page. Repeat for each page.

3. Navigate to the Snagit Editor. Hold down the CTRL key and click on each page you want to print to select it.
4. Select Print from the File menu.
5. Select Adobe as the printer and your highlighted pages will be combined into one PDF document.

Hope this helps!


Linking Spreadsheets in Microsoft Access

This is part of a tutorial on viewing QuickBooks transactions while QuickBooks is closed. The tutorial begins here.

Tutorial: Part 2

The method in Part 1 used spreadsheets to store and access QuickBooks data. QuickBooks makes exporting spreadsheets fast and painless; however, accessing, filtering and searching spreadsheets leaves room for improvement. We can improve on this method by using a database.

A database lets you combine tables to search all data at once. Remember our three tables?

A database can combine these tables through VBA or queries into one big table for fast, easy searches.

Since spreadsheet data changes, we need a quick way to update it in the database and that solution is Linking. Linking lets you export spreadsheets from QuickBooks when it's open and refresh the spreadsheets when it's closed.

Any database will do but this tutorial uses Microsoft Access in examples.

These steps were recorded using Microsoft Access 2002. Your version of Microsoft Access may differ in appearance but the basic steps remains the same.

Only refresh the linked tables when you need the data. In other words, when you change transactions in QuickBooks, export the memorized spreadsheet but do not open the database and refresh the link at that time. Close QuickBooks and only refresh the links when you open the database to view current information.

For example, perhaps on Monday you needed to change a memo or make a note in a 2011 transaction. You open the 2011 QuickBooks file and make the change. Before closing QuickBooks, export the memorized spreadsheet and do nothing else.

Then on Friday, you need to see something in the 2011 QuickBooks transaction while you are working in the 2012 QuickBooks file. You open the database, refresh the linked table and view the most current transaction data.

Maybe you are an accountant or bookkeeper working in the ABC QuickBooks file. Before closing QuickBooks, export the spreadsheet and do nothing else. Later in the week, you are working in the XYZ file when the ABC client calls with a question. Open the database, refresh the linked table and view the current data without closing XYZ QuickBooks. Hang up and continue working without losing where you were.

We can improve on the manual linking method by writing a bit of code and creating a button on a form to refresh the linked tables and this is explained in the next post.

Continue to the next post or keep checking back if it is not written yet.