|
August, 2005
Issue 4
Contents
QODBC
Release 5.0.0.104 (Build 104) is now available for download and
contains the following enhancements and fixes :-
Changes since 5.0.0.96 (Build 96 was our previous
download build available for download from www.QODBC.com.au):
Allow INNER JOIN in a simple two table join without the {oj}
syntax as used by default by Crystal Reports XI Professional and
Developer Editions. The following SQL statement from a Crystal
Report example built using QuickBooks Employee and EmployeeEarning
tables, now works correctly:-
SELECT "Employee"."Name", "Employee"."EmployeeAddressAddr1",
"Employee"."EmployeeAddressAddr2", "Employee"."EmployeeAddressCity",
"Employee"."EmployeeAddressCounty", "Employee"."EmployeeAddressPostalCode",
"EmployeeEarning"."PayrollInfoPayPeriod", "EmployeeEarning"."PayrollInfoClassRefFullName",
"EmployeeEarning"."PayrollInfoEarningsClearEarnings", "EmployeeEarning"."PayrollInfoEarningsPayrollItemWageRefFullName"
FROM "Employee" "Employee" INNER JOIN
"EmployeeEarning" "EmployeeEarning" ON "Employee"."Name"="EmployeeEarning"."Name"
ORDER BY "Employee"."Name"
QODBC also supports Crystal Reports LEFT OUTER JOINs using the {oj}
syntax like this:-
SELECT "Employee"."Name", "Employee"."EmployeeAddressAddr1",
"Employee"."EmployeeAddressAddr2", "Employee"."EmployeeAddressCity",
"Employee"."EmployeeAddressCounty", "Employee"."EmployeeAddressPostalCode",
"EmployeeEarning"."PayrollInfoPayPeriod", "EmployeeEarning"."PayrollInfoClassRefFullName",
"EmployeeEarning"."PayrollInfoEarningsClearEarnings", "EmployeeEarning"."PayrollInfoEarningsPayrollItemWageRefFullName"
FROM {oj "Employee" "Employee"
LEFT OUTER JOIN "EmployeeEarning"
"EmployeeEarning" ON "Employee"."Name"="EmployeeEarning"."Name"}
ORDER BY "Employee"."Name"
Update batches have been added. They allow you to start a batch
for given table and all insert/updates will be queued until the
sp_batchupdate command is issued. This allows for fewer round
trips to QuickBooks which increases performance. Sp_lastinsertid
has been modified to return the ListID/TxnID plus an error message
column. Sp_lastinsertid returns a row for every row added to the
batch. The batches are limited to a maximum of 500 transactions
per batch.
- sp_batchclear tablename
clears the current batch started with sp_batchstart.
- sp_batchstart tablename
starts a new batch. All inserts/update issued on this table will
be batched until sp_batchupdate is issued.
- sp_batchupdate tablename
sends the batched transactions to QuickBooks. ListID/TxnIDs and
error messages are available through sp_lastinsertid tablename.
A bug was fixed that prevented SalesReceiptLine table from
accepting an insert to a Group Item.
IN statement fixed. If you selected ListIDs or TxnID in an IN
statement if any item was not found it would not return any items.
This has been corrected so that the available items will return.
QuickBooks Online Edition schema (USA only) was updated to
include jumpins on most of the transaction tables.
Added an optimization to request only the max rows requested from
a SELECT TOP x command that does not have a WHERE, ORDER BY or
GROUP BY
Allow INSERT INTO tablename SELECT
while still maintaining
old syntax of INSERT INTO tablename VALUES (SELECT
)
In QuickBooks Online Edition (USA Only) added Vendor to the Item
list. It was incorrectly missing.
Corrected Custom fields in Purchase Orders. They were not
always available.
Corrected a fault error that occurs when an invalid name was
passed on the SQL statement.
Corrected PurchaseOrder, PurchaseOrderLine and
PurchaseOrderLinkedTxn to correctly show all CustomFields and the
special Other1 and Other2 in the header.
998 Data Execution Prevention errors we have been corrected.
Prior to Build Release 5.0.0.103, there was an open issue with
QODBC and Data Execution Prevention on newer processors like the
AMD.
Corrected issues with updating CustomFields in the SalesOrder
table.
To update your copy of QODBC,
click on the download button, download QODBC.exe to a temporary
folder on your computer and install QODBC v5 Build 104 over the top of your existing
installation. There's no need to uninstall QODBC first!
Back
to Top
From the Support Desk
This month's support incident article comes from a QODBC Pro Read
Only Edition
user that asked,
"I am trying to do a
mail merge between QuickBooks and a letter I have in
Microsoft Word using QODBC. I have Microsoft Office 2003 and I dont
seem to be able to connect to the "QuickBooks Data"
database.
Could someone please send me a step by step procedure to
accomplish this? I must be missing a step somewhere, but I
cant locate the mistake. I have no problems connecting to
Excel but Word is a big problem!"
Step 1 - Start Your Mail
Merge
Start Microsoft Word and open the letter you wish to mail
merge QuickBooks information into. From the Tools
pulldown within the "Letters and Mailings" option,
click on "Show Mail Merge Toolbar" to add the toolbar
option to Word. Once that's done, click on "Mail
Merge" to start the merge wizard.
Click on "Letters" and select Step 1 of 6 - "Next:
Starting document".
Click on "Use the current document"
and select Step 2 of 6 - "Next: Select
recipients".
Click on "Use an existing list"
and select "Browse...".
Normally you should be able to use the "New Source..."
button ....
to select your ODBC DSN using the new "Data Connection
Wizard" added to Office 2002 and 2003....
and then select the "QuickBooks Data" ODBC Data Source.....
but the following message gets displayed and Microsoft's
"Data Connection Wizard" will fail!
Well this isn't useful, and what's the point of this
article? Office 95, 97 and 2000 ran MS Query straight out of
Word (where the "New Source" button is in the Select Data
Source pane in Word 2002 and 2003).
Step 2 - Using Microsoft Query
(MSQRY32.EXE) to build your database query
What you might of not noticed is that Word 2002 and 2003
stills allows you to run mail merges against database query
files created using MS Query. MS Query is used in Excel to
get external data and providing the Microsoft add-in is
already installed on your computer, you can also use it with
Word!
To locate MS Query, open Windows Explorer and locate the
C:\Program Files\Microsoft Office\OFFICE11 folder (Word 2002
users need to locate the C:\Program Files\Microsoft
Office\OFFICE10 folder instead). Create a "MSQRY32.EXE"
shortcut on your desktop for future use and open
"MSQRY32.EXE".
Select File and "New..." to start a new database query.
Select "QuickBooks Data" to establish a connection to the
current company file open in QuickBooks. Yes, this means
that QuickBooks must already be running on your computer and
your company file open before hitting the "OK" button. Click
on "OK" to continue.
Select the "Customer" table to merge customer data into your
letter. (I've used a letter about overdue payments, so I
need customer addresses and balances). You can also select
or de-select the columns to be used in your query here too!
Click on "Next>" to continue.
As I only want to send letters to customers with an overdue
balance, I used a "Filter" here that will only include
customers where the "TotalBalance" outstanding is greater
than zero.
You can also specify the order you want the letters printed.
I've chosen "FullName" here but "BillAddressPostalCode"
could be more useful for mail sorting. Click on "Finish" to
generate the query.
Customers with a current TotalBalance greater than zero will
then be displayed.
Save your database query as "Customer Query from QuickBooks
Data.dqy", click on "Save" and close MS Query. You're
now ready to use this query within your letter!
Step 3 - Using your Microsoft Query
database query to do Mail Merges
Back in
Word, re-select the Mail Merge "Use an existing list"
option and select "Browse..."
if you still have Word open from doing the steps above or
simply click on the "Open Data Source" icon on the Mail
Merge toolbar..
Your database query "Customer Query from QuickBooks Data.dqy"
is now available to mail merge in your "My Data Sources"
folder. Select
"Customer Query from QuickBooks Data.dqy" and click on "Open".
Say "Yes" to allow Word to reopen your customer query
every time Word needs to open your data source so that when
you re-open your letter next month it will have next months
outstanding customers (and not today's outstanding
customers). Database queries are dynamic, so whenever they
are refreshed they will contain up to date information.
Database queries should not be thought of as being the same
as the static exports that you are able to do from
QuickBooks!
Word will now display a list of Mail Merge Recipients, if
you don't want to send a letter to particular customers,
simply de-select them along with any job payments if
applicable. Click "OK" when done.
Move your cursor to where you would like the address
information to appear in your letter and on the Mail Merge
Toolbar select the "Insert Address Block" icon. Choose you
desired format and click on "OK".
Now the special <<Address Block>> mail merge tag has been
added to your document click on the "Match Fields" icon on
the Mail Merge Toolbar to match the Word <<AddressBlock>>
special tags to the QuickBooks Customer Bill Address as
shown here. Click "OK" when done.
Using the "Insert Merge Fields" icon on the Mail Merge
Toolbar you can quickly add additional columns such as <<FirstName>>
and <<TotalBalance>> as shown here.
Using the "<<<ABC>>>" icon on the Mail Merge Toolbar you can
quickly see real live data from your QuickBooks company file
in your letter. Use the |<, < and >, >| buttons to move
through the rows in your query.
You're now ready to merge the letter to your printer, mapi
email system, fax card or network machine.
Note: The technique and example shown here can be used with
the FREE QODBC Basic Edition. You don't need to purchase
QODBC Pro or Web Edition to query customer data.
Back
to Top
Website Product Links
 |
QODBC is an ODBC
driver for reading and writing QuickBooks 2005/06, 2004 & 2003
Pro, Premier, Accountant and Enterprise
accounting data files by using standard SQL
queries. The Basic edition is FREE! |
 |
QDashboard automates
the transfer of data to and from QuickBooks with very
little user intervention, with no programming required!
QDashboard eliminates the need to re-enter data manually,
saving time and money and eliminating data transfer errors
caused by manual re-entry. |
 |
QTableGrabber
is an Excel Add-In that gives you access to
QuickBooks data tables with virtually no limitation on
transaction accessibility other than certain payroll
information that Intuit does not make available outside
its QuickBooks product line. |
 |
QReportGrabber allows
you to create a live data link between QuickBooks report
data and an Excel spreadsheet all from within Excel.
This innovative Excel add-in installs new menus directly
in Excel, allowing you easy access to all available
QuickBooks reports you are familiar with. |
 |
Last Price & Cost Grabber
is a utility that displays a listing of both QuickBooks
vendor costs and customer selling prices for a particular
item or range of items in one central location.
There's no need to run multiple reports, take extra time
to select item filters, or re-arrange multiple windows to
see both what you pay for an item, and what price you
charge your customer. Each tab in Last Price & Cost
Grabber can be used independently or in conjunction with
each other. |
 |
QDataViewer is a
multi-purpose reporting and data formatting tool designed
specifically to use QuickBooks 2005/06, 2004 & 2003 Accounting
data. The expandable platform of QDataViewer allows the
customer to add an unlimited number of report variations
to the included "canned" templates based on a basic
knowledge of Crystal Reports 9. If they are not using
Crystal Reports, Data Access Worldwide can also custom
design reports based on customer needs. |
|
Try our
QuickBooks Tools & Utilities for 30 Days FREE! |
Do
you know someone who would benefit from receiving
QODBC News Asia Pacific?
If so, please forward this issue to them and ask them to subscribe
by visiting the link below. We genuinely appreciate your help
in spreading the good news about QODBC!
To subscribe to this newsletter, click
here.
Send
comments to
CustomerService@DataAccess.com.au.
Data Access Worldwide is located at Shop 9, 246 Dorset Road,
Boronia VIC 3155, Australia. Tel: +61 3 9761 3644.
Back
to Top
|
