• Access form: mainImport - Copy QuickBooks data to an .MDB database file
• Access form: mainQuery - Run or Edit SQL Queries
• Access form: mainUtility - Utility Functions

(note: We recommend printing this page to use as a reference while running the program)

QACC_CODEC.MDB is an Access 2000 program that runs in the Access 2000 (or later) environment. The code and utility forms are contained in file QACC_CODEC.MDB - however the data is accessed through attached (also known as linked) tables. This allows a common set of queries, forms, reports, macros, etc. to be reused with different sets of data - each set of data representing a single QuickBooks file.

Each set of data is contained in a separate .MDB file that can be attached. When you click the Tables tab in MS Access, attached tables have an arrow to the left of the table icon. If you click the Design button with a table selected, then view the table properties, the full path name of the attached .MDB file is displayed.

This is a powerful feature - but it can also be very confusing. The important point is that the actual data you see when you view a table depends on what that table is attached to. Access always wants tables attached to real database files.  There are approximately 30 tables and each needs to be attached to the the corresponding table in the same .MDB database file.

The first time QACC_CODEC.MDB is run (or whenever it is moved to another directory), ...form 'mainImport' starts automatically.... The first thing it does is reattach all data tables to .MDB file QACC_DATAC.MDB - which initially is attached to nothing.

 

To run the Microsoft Access sample program, select the Access Data Importer and Reporter menu item from the DataBlox OfficeQ submenu of your Windows start menu.

When the program is run, a worksheet with buttons, named Utility Menu is shown. It contains 5 buttons. Three of these open Access forms and two open the browser with DataBlox's support web pages. Each section below explains one of the forms.

---

Access form:  mainImport - Copy QuickBooks data to .MDB Database

This form is used to fill an MDB database file with data. A single MDB database typically holds all the data from a single QBW QuickBooks data file. The data can come from either the currently open QuickBooks file or from a previously saved .QQT file.

There are 2 sections of fields that need to be filled before the QuickBooks data can be copied. The first section controls where the data comes from. The second controls what .MDB file the data will fill.  If the .MDB file to be filled is not empty, it's contents will be automatically emptied first.

Where Data Comes From

Above the blue line controls where the data comes from. If the tab From currently open QuickBooks File is selected, data will be transferred from the QuickBooks file that is currently open to the .MDB file destination below.

The following options can be set. Note that some options increase the amount of time required to extract the data and should only be used if you know that particular data is needed.

• Include Budgets - Check if budgets should be included.
• Include TimeLines - Check if time lines should be included. (these do not add substantially to time required).
• Include Custom Fields - Check if custom fields should be included. These are typically only used for special cases.

Transaction Detail Level - This is very important and should be set properly - especially with larger files. Lengthy processing is required to get complete data, and this option allows for tailoring the extraction time to data importance. The following options are available:

• Exclude All Transactions (fastest) - This will get all list type data - but no transaction data. The tables TranDoc, TranLine, and TranLink will be empty. This is very fast - even with large files and is often a good choice when first importing from a large file to verify that the process works.
• Exclude Payroll and Inventory - This will import transactions, but just the 'core fields' of TranLine will be filled - such as accounts and dollar amounts. Other fields - such as inventory items, counts, payroll items, etc. are skipped.
• Exclude Payroll - This will get all detail, except payroll related detail. Choose this if the file contains no payroll or if payroll data is not needed. Extracting payroll data is very time consuming.
• Complete (slowest) - This option provides the most complete data - but also takes the most time. Very large files can take multiple hours and are best done as a background task or at night.

From Previously Saved .QQT file - Use this option if the data has previously been saved to a .QQT file. In this case the options above would have been specified when the .QQT file was created. The correct .QQT file name needs to be selected using the Select .QQT File button.

Where Data Copied To

Existing .MDB databases in folder officeq_user - ...add this to form... Displays a list of all .MDB databases that currently exist in the 'officeq_user' folder (see file locations). You can either select one of these databases - in which case all the data it contains will be replaced, or you can create a new .MDB database which will be added to the list. You should click on (select) the .MDB file you want the data to be transferred to. Any data in the .MDB database will be lost and replaced by the new data.

(button) Create new empty .MDB database... - Click this button to create a new .MDB database file.  A standard file dialog form will be shown where you enter the name of the new file to create.

(button) Copy The Data - Transfers the data from the source selected in the top of this form to the .MDB file selected.  Note that depending on the size of the file and the transaction detail options set above - this could take a long time.  We recommend picking the 'Exclude All Transactions (fastest)' option the first time any file is first used to verify proper operation.

---

Access form:  mainQuery  - Run or Edit SQL Queries

This form is used to run and/or edit SQL queries. When the form starts, all queries are listed on the left. To run a query, you need to pick the .MDB database file containing data to be queried using the top left box. You also need to pick the query from the list on the left. After you run the query, the data resulting from the query ...goes where ... ...what user needs to add...

MDB Database File to Query

The top left of the form is used to select the .MDB database file that contains the data to query. This is data that originated in a QuickBooks .QBW file - and was copied earlier. You can either use the Get .MDB File ... button, which opens a file dialog so a file can be selected - or manually type in the full file path of an .MDB file.

Select Query to Display / Run

This box lists all the queries that are currently available. You need to select one of the queries by clicking it with the mouse. You can click on a few different queries to see what they do, but which ever query remains selected is the one used to fill the worksheet.

When a query is clicked, the SQL text that will run is displayed in the top box on the right. A description or general notes about the query appear in the bottom box at the right.

Edit Queries

Press this button to bring up the query editor. This allows queries to be run / edited / and permanently saved for future use. This is different than editing queries ... ...link to query editor page...

---

Access form:  mainUtility  - Utility Functions

This form contains buttons used for ...what.... Typical functions are:

• first thing
• Check attached tables ...
• third thing