QODBC version 7 is a revolutionary version which is 10 times faster to use than previous editions in certain conditions due to the use of Optimizer technology. The first time you access a given QuickBooks table, QODBC by default optimizes access to that data, so the next time you need data from that table, it will be much faster.

QODBC works with all versions of QuickBooks, but QuickBooks 2006 and 2007 (USA Edition only) provides the best performance due to changes in the way our tools can access the data inside QuickBooks using the Intuit qbXML v5 & v6 SDK released currently for USA only.

QODBC has also been chosen to be included as a feature in the QuickBooks Enterprise Edition 2006 and 2007 products (USA Edition only) by Intuit, on the main file menu under Utilities. If you need a read-only edition of QODBC and have this United States version of QuickBooks 2006 Enterprise, you are ready to go, no purchase or CD Key required!

My recommended settings for using QODBC are shown here, read below for an explaination of all the options available to you.

• Use Optimizer brings some data to a local cache to increase retrieval performance for queries. Check to activate the Optimizer.
• Optimizer Database Folder: specifies where the local data store will be saved on disk. The default location %userprofile%\QODBC Driver for QuickBooks\Optimizer is under your Windows login account name under your Documents and Settings folder. The data will be stored in a file in this folder with a name similar to the company file name with a .opt extension. If you opt to store that data somewhere other than the default click the browse button and select the destination folder. With very large company files we suggest running synchronization after hours.
• Keep my optimized data up to date as of: when you choose the optimizer to synchronize itself with your Company data. Depending on how static your data need is and the size of the file, you can opt for various time frames of synchronization. The farther down the list you go the less synchronization will occur and your queries will run much faster, but will be using less current data than the company file itself.
  • The start of every query is the default setting using the most current data. If your query needs require the latest up to the minute data and speed is not the primary focus use this default setting. Please keep in mind that the QODBC Optimizer is local to your computer only, so when other users change things in QuickBooks how does QODBC know? Well, by default the optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the local optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have.
  • The start of every new connection (with "Load Updated Data" first) is used if you don't need to update the opitmized data while you are for example running queires or importing or exporting data. The optimized data is updated upon a new connection and left unupdated until the next new connection occurs again.
  • The start of every new connection (with "Reload All Data" first) is used if you need to rebuild opitmized data from scratch before running important queires (like Sales Commisions) or importing or exporting data. The optimized data is rebuilt upon a new connection and left unupdated until the next new connection occurs again.
  • The end of yesterday is used for a better balance between speed and data updating and would be the option that works best for most users. It only has to synchronize each table once per day and the data will be current as of the end of the previous day.
  • The end of last month option is a great speed boost for running last month or last year’s queries. It does not need to synchronize again to get the data.
  • The last time I pressed one of the load data buttons option allows you to control the synchronize timing. Synchronizing only occurs when you manually run it by pressing the Load Update Data in the QODBC setup screen.
  • For balances and stock quantities: the option "Nulls" - will return NULLs for all fields that cannot be kept in sync, "Dirty Reads" - means return the most current value that was loaded into the optimizer cache, and "Real Time" uses the slower method to get the data directly out of QuickBooks making sure you get the most current value for the requested fields. (Real Time is the previous method used).
  • Optimize data after an Insert or Update: causes QODBC to do "Load Updated Data" after an insert or update is executed on a table. Useful for programmers who want to test that their data has succesfully been written to QuickBooks. 
  • Load Updated Data: This button is used to Synchronize your optimized data with your company file. This will only load the data that has changed since the last automatic or manual synchronization.
  • Multi-Table Sync: This option is unchecked by default for all non-USA enterprise users. If checked, the QODBC optimizer will use other tables to re-sync tables, however, this will slow down performance. When off all Multi-Table Sync fields become Real-Time fields. (Similar to version 7.0.x) Note: This feature is basically ignored and set to unchecked if the version is QBOE or SDK is less than v3 (used by QuickBooks 2004 USA and all QuickBooks versions outside USA). Prior to SDK v3 the other multi-sync tables did not exists so using real data for those fields is safer.
  • Reload All Data: This button is used to completely rebuild the optimized data from scratch. It is useful when you start with a new company if you do not want to synchronize the tables as you first encounter them and make sure that your data is 100% current.

See Also: How do I switch OFF or RESET the QODBC optimizer?

Note: You can also schedule full or update optimization overnight using either: QODBCFUL.EXE or QODBCUPD.EXE. Both set the errorlevel variable if errors are detected, and error messages are written into the QODBCLOG.TXT log file. Passing a DSN on the command line to these programs will choose the QuickBooks company file you wish to use.

Note: QODBC "Auto Login" unattended mode needs to be setup for this work, see: How do I setup QODBC to auto-login into QuickBooks and start it if it's not running? 

To temporarily bypass your optimizer settings and extract the latest information you can add the unoptimized tag after the table name like this to your queries:

select * from InvoiceLine unoptimized where TxnDate >= {d'2006-03-06'}

To get this feature to work, simply install QODBC on both the machine you have hosting the data (the one running QuickBooks), and the machine you want to use the data from (the remote system or client). Once this is done, a few simply settings in the driver setup screen are all that is required to use this data remotely.

Setup Remote Access - Host Side - PC Running QuickBooks

The iBiz Remote Connector is a new module that ships with ALL versions of QODBC including the evaluation. It can be started on any console that has a version of QuickBooks running.

Click on "Start" and "All Programs" and locate the "QODBC Driver for QuickBooks" program group on your desktop and select the "QODBC Remote Connector". This will launch the IBiz Integrator Setup. The "Connection" tab allows the setting of the HTTP port the Remote Connector will use. The default is :2080. Write this down and use the same port number when setting up QODBC to communicate with this Remote Connector. Click "Start Server on Application Start" to make sure this connector is always running.

Click on "Start" and "All Programs" and locate the "QODBC Driver for QuickBooks" program group on your desktop and select the "QODBC Setup Screen". The “Remote Connector” tab lets you configure a remote connection. Checking the "Connect to /n Software’s iBiz Integrator for QuickBooks Remote Connector" checkbox activates the fields required to setup connecting to a Remote Connector running on another computer or even on the same computer. The connection string attribute for this item is IBizRemoteConnect. It can be set to Yes or No. The default is No.

Hosts Behind Routers
Many sites use a router box to connect multiple PC's to a single broadband connection. When setting up a host to allow a remote PC to access the data on a machine, you will need to setup "Port Forwarding". In the case of a router, you are using only one network address (IP Number) but your router is allowing multiple computers to share this number. If you setup port forwarding, the router will be able to determine which PC in your shared network should get the incoming call from the remote user. Check your router configuration for further details.

Hosts Changing IP Numbers
Customers who do not have a persistent IP number (network address) such as those using dial-up internet, cable or DSL internet services which can change the IP number at any time, will need a small amount of additional setup to allow communication with their system even as the IP number changes. Try a free service like no-ip.com or zoneedit.com - these services will give you a physical name on the internet that will stay the same no matter what your IP number changes to, allowing you to set it up once and then forget it.

Setting Miscellaneous Properties That Affect Remote Connector Sessions
Reconnect Delay can be used to configure how many seconds QODBC will wait after it disconnects. This allows QuickBooks time to unload properly before the calling application returns to reopen the connection. The connection string attribute for this item is IBizReconnectionDelay. The default value is 0 seconds.

Checking the Connect To QuickBooks Online Edition checkbox activates the fields required to connect to the QuickBooks Online Edition. The connection string attribute for this item is IBizOEConnection. The valid values are Yes and No. The default is No.

The Connection Key is a unique key that can only be created by your Online Edition Administrator. If you have administrator rights to your Online Company file then you can run the following URL: https://login.quickbooks.com/j/qbn/sdkapp/confirm?serviceid=2004&appid=71271511 to obtain a connection key. Follow the wizard. The “…” after Connection Key: will launch a browser with the above URL. The connection string attribute for this item is IBizOETicketGUID. The default is an empty string.

Connection Key Wizard

A short summary

Login to your company file

Start of Interview

For Connection Name, use "QODBC Driver"

Select the access you want QODBC to have to your Online Data

Select who can access. NOTE: If you choose anything other than "No, Anyone can run QODBC Driver on this computer can use the connection" you will have to enter your login in every time a QODBC session or a QODBC connection is made. For a user to have access to your data they must have a connection ticket which can only be created by a company file administrator and must be running a copy of QODBC which passes it own private password along to connect.

If you choose No you will get this warning also. See the note above on who could access the data and if you want to login every time you connect to QODBC.

The Login Key comes from Intuit by running the following URL: https://login.quickbooks.com/j/qbn/sdkapp/sessionauth2?serviceid=2004&appid=71271511 and logging in. The “…” after Login Key: will launch a browser with the above URL. The connection string attribute for this item is IBizOELoginGUID. The default is an empty string. This value is only required if the QuickBooks Online Edition company file has user security activated. If you chose No security then leave this blank. This Key will expire after a period of non-use. If your application makes several connections each time you access QODBC you might want to return to this screen and press the "..." and login once, saving the new Login Key. Then you will not be prompted again until the non-use timeout expires.

Ask to login if invalid can be checked to allow the user to be sent to the Intuit WEB site to login if the Login Key is not valid. After the user has successfully logged in the user must copy the Login Key to the clipboard, close the browser and paste the Login Key into the Popup prompting for a Login Key. If not checked, and the Login Key is invalid, then an error message is returned to the calling program. The connection string attribute for this item is IBizOEPromptForLogin. The valid values are Yes and No. The default value is Yes. This is only useful if the QuickBooks Online Edition company file has user security activated.

The selected defaults in the Advanced settings should apply to most users and normally don't need not be changed.

• Reconnect Delay: Is an amount of time to wait after a disconnect when using the remote connector. This allows QuickBooks to fully unload it if it is being loaded in the background on the Host Remote Connector, before you connect again.
• Maximum Field Name Length: is set to the QuickBooks limitation but certain applications like Delphi can not handle this limit and must be set accordingly.
• Use Case Sensitive RefNumber Searches when checked means a refnumber of 'ABC' and 'abc' will be different, but means QuickBooks can use an index internally for a search instead of a complete table scan. (Note: this feature applies to versions of QuickBooks 2005 and higher)
• Include Raw Files if checked will display both filtered and raw data formats. QODBC formats data into SQL field types more easily used in programming languages for math and comparison operations. The Raw tables return the data as it is returned to QODBC without modification.
• Run 2.1 ODBC Compatibility is checked for certain applications that require ODBC 2.1 Compatibility to use QODBC. Anything that uses the Java ODBC library, like ColdFusion, requires this option turned on or recordsets will be returned with no rowsets or only 1 rowset.
• Simulate Transaction Support for SQL Server will turn transaction support on so that SQL Server will allow updates when using QODBC as a linked server. QODBC still does not support transactions, but simulates the turning on and off of transactions.
• The Advanced tab in QODBC Setup v6 Build 186 and later has new defaults for Use Iterators and Iterator or Chunk Size fields. This allows you to turn off iterators. This may be needed on systems like MS-SQL Server and Web Servers that use multiple threads. Using Iterators is faster but is only available to USA QuickBooks 2006 and USA QuickBooks 2007 users.
• Iterator or Chunk Size determines the size of the rowsets that are retrieved from QuickBooks. Set this value high if you are exporting records from QuickBooks. The maximum Iterator or Chunk size that can be set is 100000 (higher than that and it resets itself back to the default of 500).
• Integrated Application Defaults Authorization popup settings. These settings are only available to USA QuickBooks 2005, USA QuickBooks 2006 and USA QuickBooks 2007 users, otherwise they are greyed out.

• Text Field Handling has been added to QODBC v7.00.00.204 and later.  Convert Fields > 255 to LONG VARCHAR and Limit length to 4096 characters (default): These fields help some development environments get data from long fields that would not be able to otherwise. Connection string options include: ConvertToLongVARCHAR=Yes|No and MaxLONGVARCHAR=4096.