Knowledgebase : Tutorials > Database & Company File
SYSTEM ENVIRONMENT
Windows XP System
QuickBooks Enterprise Solution 9.0 (USA)
QODBC v10
Microsoft SQL Server 2000
CONFIGURATION OF QODBC
When using QODBC with MS SQL Server, the USE DCOM SERVER option
should be checked in QODBC Setup Scree...
NOTE: DCOM COMPONENT IS DEPRECATED. AS AN ALTERNATIVE TO DCOM, YOU
CAN USE QREMOTE.
Please refer articles for more information:
How to configure QRemote
[http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2483]
H...
SYSTEM ENVIRONMENT
Window XP Service Pack 2
QuickBooks Enterprise Solutions 9.0 (USA Version)
Microsoft SQL Server 2000 Personal Edition
QODBC 9.00.00.261 Version
We recommend using QRemote
For SQL Server 64-bit Click Here "How to create Li...
INTRODUCTION
The following documentation was written by Andrew Rachmiel, at
BrightStar Partners, Inc. in order to provide guidance in setting up a
Microsoft SQL server environment to store the QuickBooks database
tables.
The purpose of writing this d...
HOW TO CREATE LINK SERVER WITH MS SQL 2005/2008/2012/2016 64-BIT USING
QODBC/QREMOTE
For 32-bit Refer: How to create Link Server with MS SQL
2005/2008/2012/2016 32-bit Using QODBC/QRemote
[http://support.flexquarters.com/esupport/index.php?/Default/Know...
There are multiple ways to execute SP_OPTIMIZEUPDATESYNC or
SP_OPTIMIZEFULLSYNC for selected/multiple tables.
For a Single Execution follow below steps
Click on the VB DEMO icon to launch the Visual Basic Demo program
supplied with QODBC.
From the...
HOW CAN I USE SAME EXCEL FILES IN 64 & 32-BIT SYSTEMS
PROBLEM DESCRIPTION:
How Can I use same Excel files in 64 & 32-bit Systems?
Is it possible to have a connection setup in an Excel file and be
able to refresh on either a 64 or 32-bit system?
I...
EXAMPLES OF CREATING INVOICE USING QODBC
First of all please create new MS Access database & link ALL table.
Please refer below mentioned article for How to Use QODBC with
Microsoft Access:
How to Use QODBC with Microsoft Access
[http://support.flexqua...
PCI COMPLIANCE IN QODBC
PROBLEM DESCRIPTION:
We are working with a client that processes a lot of credit cards and
must be PCI compliant. Do you have any insight into what needs to be
done on the QODBC side to maintain PCI compliance?
SOLUTIONS:
QODB...
HOW CAN I FIND QUICKBOOKS DELETED RECORD INFORMATION FROM QODBC?
PROBLEM DESCRIPTION:
Is there a stored procedure that provides all deleted transactions?
I.e. Voided/Deleted Transactions Detail?
For Example:
If an Invoice gets deleted from QuickB...
TROUBLESHOOTING: HOW TO ACCESS ACCOUNT NAME DETAILS FROM JOURNAL
REPORT USING SQL SERVER
PROBLEM DESCRIPTION:
I need some guidance on extracting data from the general ledger. In
particular, I need to see the FullName of the GL account. Running
sp_repor...
TROUBLESHOOTING: UPDATE QUERY SCANNING ALL RECORDS IN LINKED SERVER
PROBLEM DESCRIPTION 1:
I am using MS SQL linked server with QRemote.
I am updating a single EstimateItemLine, passing Ref Number and
ItemFullName (also tried itemtxnlineid). Every time...
TROUBLESHOOTING - CANNOT CREATE NEW TABLE USING QODBC / CANNOT ADD A
NEW FIELD TO QODBC TABLE
PROBLEM DESCRIPTION:
I'm trying to create a new table with VB Demo.
Can you send me a sample query to do this?
SOLUTIONS:
QODBC is an ODBC driver. Please...
TROUBLESHOOTING - ERROR [42S00] [QODBC] INSERT VALUE MUST BE A SIMPLE
VALUE
PROBLEM DESCRIPTION 1:
We have a customer trying to import a credit memo from our database
into QuickBooks using a vb.net program. They are getting the following
error: ERROR [4...
TROUBLESHOOTING: MS ACCESS AS MIDDLEWARE IS NOT WORKING WITH MS SQL
SERVER
PROBLEM DESCRIPTION:
I am trying to use MS Access as a middleware with MS SQL Server. I
have created MS Access database & linked all QODBC tables through
QRemote DSN, After that...
TROUBLESHOOTING - FREEZING QUICKBOOKS, SLOW QUERY RUNNING.
PROBLEM DESCRIPTION:
When I run report query, query works very slow & take about 1-5 mins
per simple report like:
"sp_report ARAgingSummary show Current parameters
DateFrom={d'2014-11-12'}, Dat...
TROUBLESHOOTING - HOW TO GET ALT.EMAIL1,2 & CC EMAIL FIELDS FROM
CUSTOMER TABLE
PROBLEM DESCRIPTION:
How do I access the alternate email address 1 and 2 and the cc email
fields? I don't see them at the customer table.
SOLUTIONS:
You can get CC Email ...
TROUBLESHOOTING - HOW TO USE QODBC FOR DEVELOPMENT WITH VISUAL STUDIO
PROBLEM DESCRIPTION:
I am trying to use a demo file for development, but QODBC is not
allowing me to do this now that the evaluation period on my
development machine has expired. Are ...
TROUBLESHOOTING - MS ACCESS CRASHES HARD ON DATE QUERY
PROBLEM DESCRIPTION:
I've linked all the QODBC tables into an Access 2010 DB. I have
created table subset queries on the original tables and would like to
do selects based on date ranges - other thi...
TROUBLESHOOTING - CAN QODBC / QREMOTE RUN AS A SERVICE
PROBLEM DESCRIPTION:
How do I run QODBC as windows services in the background? I notice it
required QuickBooks to be logged and windows server 2008 to be logged
on.
SOLUTIONS:
QODBC and QuickBoo...
TROUBLESHOOTING - CANNOT USE ALIAS IN MS QUERY
PROBLEM DESCRIPTION:
I am getting the below error message when trying to SELECT data
fields AS Alias, the statement runs fine otherwise.
[sql syntax error] Expected lexical element not found:=
Please se...
TROUBLESHOOTING - HOW TO ADJUST VALUE OF A STOCK ITEM WITHOUT
ADJUSTING STOCK QUANTITY
PROBLEM DESCRIPTION:
Can I adjust the value of a stock item without adjusting the stock
quantity?
SOLUTIONS:
Yes, you can adjust the value of a stock item withou...
TROUBLESHOOTING - INVOICE REFNUMBER FAQ
PROBLEM DESCRIPTION:
I have invoice number (RefNumber) from my own table outside of
QuickBooks. But if QuickBooks user creates invoices directly in
QuickBooks then QuickBooks keeps new incrementing invoices
autom...
QODBC POS COULD NOT ACCESS TO QUICKBOOKS POS COMPANY FILE
PROBLEM DESCRIPTION:
When set QODBC POS DSN to access the company file that is running in
QuickBooks POS (Version 10), it will fail to connect to the company
file. QuickBooks POS will search for ...
TROUBLESHOOTING - "ERROR PARSING COMPLETE XML RETURN STRING" ERROR
WHEN TRYING TO UPDATE TABLE PRICELEVELPERITEM IN MS ACCESS
PROBLEM DESCRIPTION:
I have constructed an Access database file (in 32bit Access 2003) with
tables linked to QuickBooks. I can ...
TROUBLESHOOTING - HOW TO VERIFY IF OPTIMIZER FILE CORRUPTED OR NOT
PROBLEM DESCRIPTION:
Is there any way to figure out when the Optimizer cache is corrupted
or if there's a way we can avoid it altogether in the future?
SOLUTIONS:
Please refer below...
TROUBLESHOOTING - HOW TO USE DATE() AND DATEADD() FUNCTION IN QODBC
PROBLEM DESCRIPTION:
I want to write some select statements on both InvoiceLine and
SalesReceiptLine that returns all records dated WITHIN the past 30
days, relative to whatever TODAY ...
TROUBLESHOOTING - HOW TO SELECT RECORD WHEN VALUE IS NULL
PROBLEM DESCRIPTION:
I am trying to run a report which returns ItemInventory where
LastReceived is NULL I have tried in vain to accomplish this. My
question is what syntax do you use to select a...
HOW TO USE QXL
STEP 1 - DOWNLOAD QXL
If you haven't already downloaded QXL, do it now by going to the
download page [http://qxl1.com/qxl-for-quickbooks-desktop-downloads/].
Don't forget to read the "Readme" file during installation for the
latest infor...
TROUBLESHOOTING - GETTING MULTIPLE-STEP OLE DB OPERATION GENERATED
ERRORS IN SQL SERVER
PROBLEM DESCRIPTION:
Trying to insert into SalesOrderLine and getting a 'Multiple-step'
error.
INSERT INTO QBREMOTE...SalesOrderLine (CustomerRefListID,
TemplateR...
DIFFERENCE BETWEEN QODBC FOR QUICKBOOKS DESKTOP AND ONLINE.
TABLE DIFFERENCES:
Number of Tables available in
QODBC DESKTOP
QODBC ONLINE
150
83
List of Tables available in QODBC Desktop
[http://doc.qodbc.com/qodbc/usa/TableList.php?catego...
EXAMPLES OF CREATING INVOICE USING QREMOTE FROM SQL SERVER
NOTE: For multi-line invoices, some may find it easier to NOT include
the header information with each line item, but rather to have
separate smaller commands for the Invoice Lines and a final ...
TROUBLESHOOTING - ERROR WHILE INSERTING BILL
PROBLEM DESCRIPTION 1:
I am following the below steps and getting the error.
1.First, we have inserted the records in Bill & BillExpenseLine table
and records got inserted successfully.
2.Second, we are in...
TROUBLESHOOTING - APPLICATION CONNECTIVITY DELAY
PROBLEM DESCRIPTION:
I have an inventory control system (c#, SQL Server) that needs Read
Only access to four tables in the accounting system. I have deployed
your multiuser version and can get to the loca...
WRITING TO QUICKBOOKS SAFELY
PROBLEM DESCRIPTION:
I'm a little concerned about the write feature and changing any
current data in QuickBooks from the Excel files I want to experiment
in. Are my concerns valid or what is the best way for me to prevent
th...
CONFIGURE QODBC FOR USE WITH JITTERBIT COMMUNITY EDITION 3.0.0
(QREMOTE)
To use QODBC with Jitterbit community edition 3.0.0 you should follow
the steps below to configure your QODBC driver and Jitterbit
connection. These steps work for QODBC with Jitte...
TROUBLESHOOTING - HOW TO LOCATE DATA FOUND IN QUICKBOOKS BY TABLE AND
FIELD NAME INSIDE QODBC
PROBLEM DESCRIPTION:
I have a question about How to locate data found in QuickBooks by
table and field name inside the QODBC system.
SOLUTIONS:
The fastest ...
TROUBLESHOOTING - HOW TO RE-LINK QUICKBOOKS DATA IN MS ACCESS USING
QODBC
PROBLEM DESCRIPTION 1:
We are adding invoice records to QuickBooks using an Access front end.
When we create the records and try to insert into QB the line items
seem to go but wh...
TROUBLESHOOTING - HOW TO USE QODBC WITH MULTIPLE QUICKBOOKS COMPANY
FILE
PROBLEM DESCRIPTION:
I have a question about setting ODBC to auto login. We have two
different company files for accounting purpose. I am trying to
automate some of the tasks and I...
HOW TO USE QODBC WITH WINDOWS SCHEDULER / HOW TO USE Z-CRON WITH
QODBC / HOW TO RUN QODBC APPLICATIONS ON A REPEATING/NIGHTLY BASIS
PROBLEM DESCRIPTION 1:
My application needs read data from QuickBooks and update data in SQL
server, up-to this I can ab...
HOW TO CONNECT TO MULTIPLE COMPANY FILES AT THE SAME
TIME/SIMULTANEOUSLY
PROBLEM:
There are multiple company files in QuickBooks. At a time, I can
access only one company file. Is QODBC allow me to access multiple
company files same time?
SOLUTION:
T...
PROBLEM DESCRIPTION:
VB DemoTest Connection is crashing. When I tried to connect to
QuickBooks using QODBC.
I have tried to reinstall QODBC & QuickBooks but still, QODBC Test
connection (also via VB Demo) is crashing. Also, I tried to run QODBC
as Admi...
PROBLEM DESCRIPTION:
How to insert employee time information from MS Excel time spreadsheet
into QuickBooks using QODBC
SOLUTION:
You cannot perform insert/update operation directly in MS Excel
because Excel is just exporting table in the spreadshee...
TROUBLESHOOTING - HOW CAN I GET SINGLE USER ACCESS USING QODBC
CONNECTION STRING
PROBLEM DESCRIPTION:
I am in access and need to update the Inventory I have made a table
that contains the adjustments. (Thousands of them) In your notes about
InventoryA...
TROUBLESHOOTING - QODBC/QREMOTE TRIES TO CONNECT FOR 2 MINUTES OVER
AND OVER
PROBLEM DESCRIPTION:
I am using QRemote DSN to connect to QuickBooks. QODBC/QRemote tries
to connect for 2 minutes over and over, says it has a connection but
does nothing. I...
TROUBLESHOOTING - QUICKBOOKS ADVANCED INVENTORY MODULE IN QODBC
PROBLEM DESCRIPTION:
Does your enterprise driver support the advanced inventory module for
QuickBooks enterprise? Would we be able to query stock per item, per
location?
SOLUTION:
You...
TROUBLESHOOTING - QUICKBOOKS UI GETTING LAUNCHED AFTER UPDATING QODBC
VERSION.
PROBLEM DESCRIPTION:
We recently upgraded from QODBC 14 to 16.00.00.313. It is functioning
normally except now the QuickBooks window is opening up when it didn't
use to. In...
TROUBLESHOOTING - CANNOT CONNECT TO MY QUICKBOOKS ONLINE SANDBOX
PROBLEM DESCRIPTION:
I am trying to do some testing and now have access to a QBO Sandbox
company. I have tried to connect to it two different ways.
1. If I "Connect to QuickBooks" on th...
TROUBLESHOOTING - HOW DO I LIST THE SUBTOTAL OF INVOICES WITHIN DATE
RANGE
PROBLEM DESCRIPTION:
I would like to do is list find the subtotal of all Invoices by a
customer for not only that particular customer but all jobs assigned
to that customer, wi...
FOREIGN AND UNICODE CHARACTERS NOT SUPPORTED BY QODBC
INFORMATION:
Evidently, QuickBooks allows users to put foreign (i.e. Chinese,
Japanese, etc...) characters into item descriptions, etc...
Unfortunately, these characters are not returned properly i...
HOW TO CREATE OR CONFIGURE A QODBC ONLINE DSN
PROBLEM DESCRIPTION:
I have one QuickBooks Online login, which provides access to multiple
QuickBooks Online companies.
When I initially configured the connection, I selected one of the
clients that I hav...
TROUBLESHOOTING - WHERE IS THE BIN LOCATION FOR ITEMS STORED?
PROBLEM DESCRIPTION 1:
I am using QuickBooks Enterprise with Advanced Inventory enabled.
I am trying to find where the bin location for items is stored in
QuickBooks through QODBC.
PROBL...
TROUBLESHOOTING - BILLABLE PAYROLL - NEED TO DISPLAY MORE FIELDS ON
INVOICE
PROBLEM DESCRIPTION:
We need to bill all payroll costs to various customers. Payroll costs
are defined as all of the following:
1. Employee's gross salary
2. Company share ...
TROUBLESHOOTING - HOW CAN I GET LAST ITEMS SOLD TO CUSTOMER DETAILS
PROBLEM DESCRIPTION:
I was able to create a report using a customer set with the customer
as the parent and InvoiceLine as the child giving me items ordered in
the past.
My problem ...
TROUBLESHOOTING - HOW CAN I GET LINKS BETWEEN ESTIMATE AND INVOICE
USING QODBC
PROBLEM DESCRIPTION:
I've explored the related-to fields, but cannot determine how a line
on the invoice is linked back to the estimate.
I'm designing a report that needs...
TROUBLESHOOTING - HOW CAN I USE WILDCARD (*) WITH CRYSTAL REPORT AND
SP_REPORT
PROBLEM DESCRIPTION:
I am using the following sql statement:
sp_report JobProfitabilityDetail show AmountActualCost_Title,
AmountActualRevenue_Title, AmountDifferenceActu...
TROUBLESHOOTING - HOW TO USE BETWEEN IN QODBC
PROBLEM DESCRIPTION:
I need to use Between, in range of dates to return new customer in one
period, I try this:
SELECT FullName FROM Customer Where TimeCreated Between
{d'2007-12-18'} AND {d'2007-12-19'...
HOW TO REMOVE QODBC RELATED CERTIFICATES FROM INTEGRATED APPLICATION
UNDER QUICKBOOKS POS
Please follow the steps to remove QODBC Related Certificates from
Integrated Application Under QuickBooks POS:
Try removing all the integrated application entries...
HOW TO USE QODBC WITH MICROSOFT EXCEL 2016 (64-BIT)
FOR MS ACCESS 64-BIT, PLEASE REFER: HOW TO CONFIGURE QODBC TO WORK
WITH 64-BIT MS ACCESS
[HTTP://SUPPORT.FLEXQUARTERS.COM/ESUPPORT/INDEX.PHP?/DEFAULT/KNOWLEDGEBASE/ARTICLE/VIEW/2771]
SETUP QODBC TO US...
HOW TO SHARE/INVITE FLEXQUARTERS SUPPORT TEAM TO QUICKBOOKS ONLINE
COMPANY FILE
PROBLEM DESCRIPTION:
How do I share/invite FLEXquarters Support team to access my QBO
company file?
SOLUTION:
Please follow below steps to share/invite FLEXquarters Sup...
TROUBLESHOOTING - TRYING TO UPDATE ITEMINVENTORYASSEMBLYLINE TABLE
PROBLEM DESCRIPTION:
I am using MS Access connected to QuickBooks via QODBC. I am using
queries in MS Access to load/synchronize Inventory data from my master
inventory list, which is ...
TROUBLESHOOTING - HOW DO I SEE BACKORDER ITEMS THAT HAVE BEEN
INVOICED BUT NOT SHIPPED
PROBLEM DESCRIPTION:
Could someone kindly provide me with a sample, or is there a stored
procedure that would allow me to only pull out back ordered invoices
from t...
TROUBLESHOOTING - QODBC LOGGING IN WITH WRONG USER ACCOUNT
PROBLEM DESCRIPTION:
I am using ms access. I have the integrated application certificate
set up correctly (see screenshot). The username for this certificate
is "odbc".
QuickBooks is closed.
...
TROUBLESHOOTING - DSN CONNECTION NOT CONSISTENT ACROSS MULTIPLE
COMPUTERS
PROBLEM DESCRIPTION:
I have a pass-through query for access:
Select * from Item
The connection string is as follows:
ODBC;DRIVER={QODBC Driver for
QuickBooks};SERVER=QODBC;DF...
TROUBLESHOOTING - QB BEGIN SESSION FAILED. ERROR - UNAUTHORIZED-401
PROBLEM DESCRIPTION:
I was able to use QODBC Online. Suddenly, I am getting below error
when I tried to connect QBO using QODBC Online. It was working
properly. No known changes to Wi...
TROUBLESHOOTING - CHANGES TO THE INTEGRATED APPLICATION
AUTHENTICATION FOR QUICKBOOKS
Intuit is reinforcing existing security features that help protect
QuickBooks customers' sensitive information.
Refer: Intuit's News: Changes to the Integrated Applic...
TROUBLESHOOTING - QXL 80040414 MODAL POPUP ERROR
PROBLEM DESCRIPTION:
I am trying export data from QXL. But when I click on QXL export
button I got below error message.
Unable to connect to QuickBooks. Popup window(s) in the QuickBooks
application M...
TROUBLESHOOTING - CONNECTION SERIAL, LIMITATION, QUEUE AND UPPER
LIMIT
PROBLEM DESCRIPTION:
How many concurrent connections does the Server edition support?
Is the product QB version specific, requiring additional purchase with
each version. AKA. Up...
TROUBLESHOOTING - CODE DIFFERENCES BETWEEN QODBC DESKTOP AND QODBC
ONLINE
PROBLEM DESCRIPTION:
I have an MS Access application using QODBC to connect to desktop
QuickBooks. Can I use the QODBC Online driver to connect to QuickBooks
Online without chan...
TROUBLESHOOTING - HOW TO CLOSE SQL LINKED SERVER CONNECTION
PROBLEM DESCRIPTION:
We are using QRemote with SQL Linked Servers with several different
QuickBooks files. How can we close the connection to a QuickBooks file
after the query has run, then o...
TROUBLESHOOTING - QODBC RECORD SET ISSUE
PROBLEM DESCRIPTION:
I am using below code it is returning records & giving me the correct
result. But If I change query from "SELECT Name FROM customer" to
"SELECT * FROM customer" it does not return any recor...
TROUBLESHOOTING - GETTING ERROR SOCKET CLOSED BECAUSE OF ABNORMAL
PACKAGE
PROBLEM DESCRIPTION:
I am getting Error SOCKET Closed because of abnormal package in
QRemote client. I am accessing QuickBooks Data from the remote machine
over the internet (WA...
CONNECTING MULTIPLE QUICKBOOKS COMPANIES VIA EXCEL
Download Sample
[http://support.flexquarters.com/esupport/newimages/MultipleCompany/MultipleQBCompanyConnection.zip]
There is a limitation with the QuickBooks SDK that it can connect only
1 company fil...
HOW TO INSERT INVOICE USING EXCEL - VBA
Download Sample
[http://support.flexquarters.com/esupport/newimages/InsertInvoiceVBA/Insert_Invoice.zip]
In this example, We will show you that How to insert Invoice in
QuickBooks using the data available in Exce...
EXCEL AUTOMATION - HOW TO OPEN, REFRESH AND CLOSE A COMPANY FILE
Download Sample
[http://support.flexquarters.com/esupport/newimages/ExcelAutomation/ExcelAutomation.zip]
Before using MS Excel application, please make sure you have installed
MS Office C...
ACCESSING QUICKBOOKS DATA REMOTELY VIA SQL SERVER
First, you need to create Linked Server in SQL Server.
For 32-bit SQL Server. Please refer How to create Link Server with MS
SQL 2005/2008/2012 32-bit Using QODBC/QRemote
[http://support.flexquarters.co...
HOW TO USE POWERSHELL WITH QODBC
Download Sample
[http://support.flexquarters.com/esupport/newimages/PowerShell/DisplaySQL.zip]
SAMPLE CODE FOR USING QODBC DSN:
$connectstring = "DSN=QuickBooks Data;"
$sql = "Select TOP 10 Name from Customer"
$conn ...
QODBC DESKTOP - HOW TO LINK INVOICE TO DEPOSIT USING QODBC?
PROBLEM DESCRIPTION 1:
I want to apply the Payment to a particular Invoice using Deposits.
How can I do so using the QODBC?
SOLUTIONS:
Refer to the screenshot of QuickBooks UI Invoice cre...
[QODBC DESKTOP] HOW DO I DETERMINE WHICH DATABASE IT IS USING TO SET
UP THE CONNECTION WITH QUICKBOOKS?
PROBLEM DESCRIPTION:
How do I determine which database it is using to set up the connection
with QuickBooks?
SOLUTIONS:
To determine which databa...
[QXL DESKTOP] HOW TO EXPORT QUICKBOOKS DATA TO GOOGLE SHEETS USING QXL
PROBLEM DESCRIPTION:
How to export QuickBooks Data to Google Sheets using QXL
SOLUTIONS:
Install the Google Drive desktop application and authorize it to
connect to your Google D...
Help Desk Software by Kayako