Why Import QuickBooks Tables w/QODBC Article

This post explains why to use QODBC to see fields not available to you in QuickBooks®.

A database table looks like a spreadsheet with columns and rows. Each field is a column. In a customer table, for example, the column heading contains field names like: Name, Phone Number, Email, etc.

Each record is a row. In a customer table, for example, each row is a customer.

QODBC makes more fields (or columns) available to you than either QuickBooks® reports or QuickBooks® export feature.

To read more about the difference between IIF files and QODBC imported tables, see this article:

            Import QuickBooks Tables: IIF vs. QODBC
            www.vbquick.com/2011/10/import-quickbooks-table-iif-or-qodbc.html


The article also contains screenshots of IIF tables and QODBC imported tables for comparison. There you will see how many more fields QODBC makes available to you.

For a Visual Basic function to import tables and their properties using QODBC, see this post:

            QuickBooks Tables in Access w/ QODBC Form
            www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html


When using Microsoft® Access to read and write QuickBooks® data, you need the field names and their properties.

For example, to write a QuickBooks® check from your Access database, you need the field names for Vendor, Check Number, Account, etc.

You also need to know what you can do with each field:
  1. read (property name: queryable)
  2. insert (property name: insertable)
  3. edit (property name: updateable)
  4. null (propery name: nullable)
  5. length (property name: length
  6. type (property name: type, typename or datatype)
These properties have changed in the past with newer versions of QuickBooks®. That is one reason you need to access them; to be aware of changes. The details below may not apply to the version of QODBC and QuickBooks® that you use; however, the principle applies. For example, a field previously not insertable may become insertable in the future, so the ability to access this changing information is advantageous.

Let us examine these properties. Remember, the details may change but these examples should give you an idea of the necessity of accessing these properties.

Queryable

I have not checked the queryable property of every field in every table with QODBC but I have yet to see a field whose Queryable property is false. To be safe, I still access and use them.


Editable

You may wish to edit information using QODBC. For example, you may use The Link Principle (available at Amazon.com) to create memos that link transactions in QuickBooks® to receipts on your computer. You may then create a form that lets you select a QuickBooks® transaction from a list and open its receipt with a mouse click.

You need to know what fields are Editable. For example, you may create a new deposit and insert the date using QODBC; however, you may not edit the date of an existing deposit. You may only edit the dates of existing deposits in QuickBooks®.


Insertable

To create a new check in QuickBooks®, you need to know which fields are Insertable. For example, if you try to insert Amount into a check, you will produce an error. You may only insert Amounts into checks through expense lines or item lines. QuickBooks® adds up the expense and item line Amounts to calculate the check Amount.


Nullable

To create a new check in QuickBooks®, you may ask for user input through a form. If your form allows null values for fields, you may produce errors. Not all fields are nullable. For example, to make sure you select an Account when writing a check, QuickBooks® does not allow a null value for AccountRefListID or AccountRefFullName. The good news is that you need only provide one and the other is automatically written.


Length

To edit or insert data to any field, you must limit the data length to the length QuickBooks® allows. For example, if you try to insert or edit a Reference Number with twelve characters for a check, you will produce an error. The field length of RefNumber in the Check table is eleven. You must set the lengths of fields in user input forms to the QuickBooks® allowed length to avoid errors.


Type

To edit or insert data to any field, you must insert the right type of data. For example, you cannot insert text into a number or date field. You may use any of these properties to determine the right type:
  1. type
  2. typename
  3. datatype
Use one of the above type properties to limit user input to the proper type of data for the field in your database.



Summary

When programming a database to insert and edit QuickBooks® transactions with QODBC, your queries must not allow insertion and editing of information whose:
  1. corresponding QuickBooks® field properties are set to False (0)
  2. length exceeds the length allowed by QuickBooks®
  3. data type is not the same as the QuickBooks® required data type

Accessing and using these properties in your programming is a must. Make sure your user input forms allow the proper input to avoid errors.

Microsoft Access References

Do you spend hours finding, copying and pasting code into your Visual Basic Editor only to find the code does not work because of missing references? Many sites leave you guessing when it comes to needed references.
My sites include references with codes so you do not waste time copying and pasting for nothing.

You do not need to know anything about references to use them, except which ones you need! You only select them once for each database and you are done with them.

To use my codes, you mostly need VBA, Access and DAO. I like code that uses only these references because the more references you use, the slower the database and the higher the risk for problems. One problem is ambiguity.

If you use both DAO and ADO in your code, you must disambiguate. This means each time you declare a recordset, you have to precede it with DAO or ADO like this:

DIM rs as DAO.Recordset

or

DIM rs as ADO.Recordset
If you do not disambiguate, you risk data corruption and other problems.

You may notice that I do disambiguate because I do not know if you are already using ADO in your database.

Microsoft® Access includes VBA and Access but you will probably have to select DAO. I use Microsoft® Access 2002 and that version uses the Microsoft DAO 3.6 Object Library. Your version may use a different DAO library but you will recognize it by the DAO in the name.



Selecting References

Microsoft DAO 3.6 Object Library is not included by default in Microsoft Access 2000 or Microsoft Access 2002. Therefore, you must select it from the References window.

To find References, go to the Visual Basic Editor in Microsoft® Access:






Your Visual Basic Editor may not look exactly like the window below but no matter.

Notice the highlighted x in the code in the module window. Microsoft® Access has displayed an error message. I purposely did not define the variable to create this error and stop the code for you.

See the bottom window titled: Immediate. There I typed the name of the procedure and hit Enter to make it run. This is one way to test codes.





After clicking OK on the error message, Visual Basic highlights the error line in yellow.




You cannot access References from the Tools menu while Visual Basic is stalled like this. References will be grayed-out and inaccessible as illustrated below.






You must use the Reset button as illustrated below.





Then you can access the References menu:





Now you can check off any references from the list of references needed to run the code:




The References I use may not look exactly like the references available to you. For example, look at all the Microsoft ActiveX Data Objects Libraries below. You may only choose one or Visual Basic will display an error message. Choose the Reference that most closely matches the one you are looking for.





Try to choose codes that use the same references rather than adding new ones. This assumes that any developers working on complicated databases are certainly not reading a post on how to select references.

If you are at the point in your learning where you are reading about references, then keeping it simple is also where you want to be.

Windows Search 4.0 interferes with application.filesearch

Application.filesearch Issue
You have a problem if you:
  • use Microsoft® Access 2002
  • installed Windows® Search 4.0
  • use application.filesearch in your code
You will soon find that Windows® Search 4.0 prevents application.filesearch. This is probably of no consequence to Microsoft since they released Microsoft® Access 2007 without the application.filesearch function.

The consequence may be huge to businesses and programmers still using Microsoft® Access 2002.

You may resolve this problem two ways.
  1. Rewrite your databases using vbDirectory or filesystemobject. Some resources for doing this:
    1. Replacing Application.Filesearch with vbDirectory for List Box Rowsource of Files (http://www.thegenericdatabase.com/2011/11/file-list-vbdirectory-vba-visual-basic.html)
    2. Downloadable sample database that uses vbDirectory to create file lists and also moves files. Available on my Google Docs page:  docs.google.com/file/d/0B5TehEdDbkHNNTRhMWMwODUtZmVlMC00NmM2LWFmNTgtZTM1NTE2Y2ExZjU3/edit?pli=1
    3. Instructions for downloading and opening my sample databases from my Googledocs page : docs.google.com/file/d/0B5TehEdDbkHNNTRhMWMwODUtZmVlMC00NmM2LWFmNTgtZTM1NTE2Y2ExZjU3/edit?pli=1
  2. create a shortcut on the Desktop to stop and start the Indexing Service whenever you wish.
Continue reading below to create a shortcut to stop and start the Indexing Service.



Creating a Shortcut to the Indexing Service

To create a shortcut on the Desktop to control the Indexing Service:

Click on the Start button on the left bottom of the screen.
Click on Run from the Start Menu
In the text box that appears, type:    mmc
Select File from the menu bar of the Console Window that opens.
Select Add/Remove Snap-in from the drop-down menu
Select the Standalone Tab on the Add/Remove Snap-in Window
Click the Add button on the Add/Remove Snap-in Window
Select Computer Management from the Add Standalone Snap-in Window
Click the Add button at the bottom of the Add Standalone Snap-in Window
Select Local Computer using the radio button
Click the Finish button
Close the Add Standalone Snap-in Window
Close the Add/Remove Snap-in Window
On the Console Window, click the - sign  (minus sign) next to Computer Management to expand the tree
Click the - (minus sign inside a box) next to Services and Applications to expand the tree
Highlight (select) Indexing Service
With Indexing Service highlighted (selected), select File from the menu bar
Select Save As from the drop-down menu
Save the file to your Desktop so you can click on it when needed

Now you can keep the Indexing Service running but stop it when you need to work with your databases that use application.filesearch. When you are finished working, you can start it again.

Continue reading below for instructions on using the shortcut.


Using the Shortcut to the Indexing Service

Four simple steps to using the shortcut:

  1. Click or double-click the shortcut as per your computer setup.
  2. Select Action from the menu bar of Console Window.
  3. Select either Start or Stop as desired.
  4. Close the Console Window.
Be sure to let me know how this works for you.

Function QODBCConnect

Places your connection string into a one-word function. You will see this function throughout this site.



Details


Why try to remember that QODBC connection string? Place it in a function and forget about it.



Code


Function QODBCConnect() As String
'make the QODBCConnect string equal to whatever your connection string is
QODBCConnect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
End Function



How to Use


Instead of typing something like this:

        qDef.connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"

Type This:

        qDef.connect = QODBCConnect