Import QuickBooks Tables: IIF vs. QODBC

Importing Tables: IIF vs. QODBC

The Three Part Tutorial: QuickBooks Tables in Access w/ QODBC Form (www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html) scratches the surface of QODBC's table function. Why not simply export tables using QuickBooks® export feature?

You can, but let's examine each method.

To export the list of accounts from QuickBooks®:
  1. Select Utilities from File menu
  2. Select Export from the Utilities fly-out
  3. Select Lists to IIF files from the Export fly-out




We'll use the Chart of Accounts list, so check that one and hit OK.





Name the file and save it.




  1. Navigate to the folder where you saved the QuickBooks® IIF file.
  2. Right-click the file
  3. Select Open With
  4. Select Excel


With the file open in Excel, Select Save As from the File Menu.



  1. Select Microsoft Excel Workbook as the file type.
  2. Name the file.
  3. Hit Save



Now your folder contains two files named Account. One is the original IIF file and the other the Excel file you just saved.




Next open the database where you wish to use the Account List.
  1. Select Get External Data from the File menu.
  2. Select Link Tables from the Get External Data menu




Select the Accounts.xls file you saved.




Name the new linked table: Accounts.




 Check the box to tell Access the first row contains column headings.



Save the table. It is now linked to the spreadsheet named Accounts.



Open the table in Design View.



Note all the fields in the table. The linked table contains 14 fields.

Note the field names. How definitive are they?


Now open the VB Demo that comes with QODBC. We're going to look at the Accounts table from there.

Select Add New Connection from the Connections menu of the VB Demo.



Select QuickBooks® Data from the Machine Data Source tab and click OK.




Type: sp_columns account and hit the Query button.




You should see something like the screenshot below.

First, notice you now see 29 fields versus the 14 fields from an IIF file. 

Second, notice how much more descriptive the field names are.

Third and perhaps most imporant, notice the first field named ListID. ListID's are not available to you in the QuickBooks® interface or in IIF files.

ListID fields are important for three reasons:
  1. QuickBooks® assigns them
  2. They are unique identifiers
  3. You cannot change them
These three reasons guarantee perfect Microsoft Access primary keys that are unique identifiers AND link to corresponding QuickBooks® records. But that's not all.



I moved the columns in VB Demo to show you another benefit of using QODBC to import tables into Access.

Note the three columns to the right named:
  1. queryable
  2. updateable
  3. insertable
This information is invaluable when creating a database that writes to QuickBooks®.

Take advantage of this information in Access. Programmatically create queries that do not attempt to insert or update field records that are not insertable or updateable.

Do not underestimate the value of programmatically including the updateable and insertable information.

QuickBooks® has in the past changed whether certain fields are insertable or updateable. If you use QODBC and Visual Basic to their fullest potential, your database capabilities will change as QuickBooks® changes its table properties.

Why depend on announcements or luck to find out that a field you previously could not insert or update is now insertable and updateable?

A simple if/then function can omit non-insertable and non-updateable fields from your queries.

Access forms used to collect user input to insert or update QuickBooks® data can be programmed to tell users what fields are insertable/updateable. When QuickBooks® changes, the form can update without any programming or code changes.

Comments

Popular Posts