About ODBC & QuickFill

QuickFill includes an ODBC (Open Database Connectivity) driver which allows you to retrieve data from your database and create reports other than those provided by QuickFill itself.  Common desktop applications such as Microsoft Access, Microsoft Excel and Crystal Reports all can be used to create reports using the ODBC driver.  For a complete walkthrough on how to create such a report using Microsoft Excel ask for a copy of the QuickFill Application Note entitled Sales Analysis Using QuickFill and PivotTables.

We also provide examples of a variety of Excel spreadsheets that retrieve data from the QuickFill database. You can find these examples in the ODBCExamples folder within the QuickFill program folder. Please note that recent versions of Microsoft Excel implement a variety of security protections against malicious macros embedded within Excel spreadsheets. We have digitally signed the macros in our samples so that you can be assured that the macros contained within them do indeed come from CWC Software and are not Trojan horses. Even so, you may find that a scary looking warning pops up and that you will be unable to use the spreadsheets unless you take action to enable the macros embedded within the spreadsheets. For more information on enabling the macros in the sample Excel spreadsheets, please click here.

Many desktop applications provide a "drag and drop" style of constructing queries that does not require any knowledge of the SQL language.  In the event that the application you are using requires you to enter SQL queries manually, you will need to know the details of the SQL syntax supported by the ODBC driver.  You can find this in the documentation provided by OpenAccess Software, the publisher of the tool kit used to build the ODBC driver, which can be found here.  Be aware that QuickFill's ODBC driver is read-only and does not support any SQL commands other than the SELECT command.

If you have used ODBC before you are no doubt aware that you must create a "data source" using the ODBC control panel in Windows before you can access your data.  Rest assured that this is not necessary with QuickFill.  QuickFill automatically creates data sources for you whenever you open a database in QuickFill.  The data sources are assigned names of the form "QuickFill XXXX Database" where the XXXX is the name of the QuickFill database.

QuickFill ODBC tables

Once you have specified a QuickFill database as your data source, you'll be given access to all of the QuickFill ODBC tables listed below. You can then use your ODBC application to create queries, forms, reports, etc. that retrieve data from these tables.

• Note: currency fields

QuickFill's ODBC driver returns the value of currency fields in pennies, not dollars. When using an ODBC application, you can change the display from pennies to dollars, by defining your query so that currency fields are divided by 100. For example, using Microsoft Access you can design a query that displays the earned income in dollars for each order, by using the expression "Amount:[ORD_EIC]/100." You can do this by right clicking in an empty field and selecting "Build…" from the menu that appears. Or, using Microsoft Excel, change the column's number format property to "Currency."

In addition, currency fields use QuickFill's internal accounting convention. That is, debit balances have positive values and credit balances have negative values. (Click here for more information about accounting in QuickFill.)

Linking ODBC tables

You can link (or join) ODBC tables based on fields that they have in common. This allows you to retrieve subscriptions purchased by a particular customer, or orders for a particular subscription.  In theory you can link any two fields that have common data in them but in practice you will find that the speediest joins are made using the fields with "DBA" in their name.  The DBA fields are database address fields which uniquely identify every record in a QuickFill database.  Every record has a database address contained in a field that matches the record name.  For example, the subscription record (or SUB record) has a database address field that is named SUB_DBASUB.  Most records also contain fields which link them to their associated records.  For example, the subscription record contains a link to its associated customer record in the SUB_DBACUS field.  When joining two tables it is best to use fields that have matching names after the underscore.  The best joins for the most commonly used tables are these:

(To see a list of the fields in a specific table, click on that table in the list below.)