Problem Description:

Users need to access additional contact information for vendors - specifically Work Phone, Mobile, and Website fields - via QODBC. These fields are not directly visible in the Vendor table by default until the appropriate hotfix is applied.

Affected products: QODBC Desktop
Expected outcome: Additional contact fields on the Vendor table are accessible through AdditionalContactRef columns, and can also be exported in QXL after applying the DLL-swap workaround for beta version access.

Symptoms:

• WorkPhone1, MobilePhone1, and Website1 columns do not appear in the Vendor table schema.
• The Vendor Address Info Work Phone, Mobile, Website, and Other 1 fields are not found in either Vendor or VendorContacts tables.
• QXL does not export newly added vendor contact fields even after installing a beta hotfix.

Cause:

QuickBooks Desktop stores additional contact information for vendors in an AdditionalContactRef repeating structure, similar to how customer contact fields are stored. Prior to the hotfix (B01745 / B01752), QODBC did not expose these fields in the Vendor table. The QODBC team added them starting with those beta builds.

For QXL access, newly added fields from a beta QODBC build require a DLL swap because QXL loads qxlfqqb32.dll (the standard runtime) rather than fqqb32.dll (the beta runtime). Swapping the DLLs makes QXL load the beta version instead.

Environment:

Resolution:

Step 1: Install the Required Hotfix

1. Download hotfix B01752-beta-2024-10-30 (or later):
   Download B01752-beta-2024-10-30
2. Close all applications and QODBC connections (QuickBooks, MS Access, MS Excel, QRemoteServer, and any other programs using QODBC).
3. Double-click the installer and click "Extract".
4. Click "Yes" when prompted to overwrite.
5. Delete the existing optimizer file (located in %appdata%\QODBC Driver for QuickBooks) and rebuild it using:

Sp_optimizefullsync All

Use the QODBC Support Wizard to run this command. See How to use QODBC Support Wizard for details.

Step 2: Use AdditionalContactRef Fields on the Vendor Table

The hotfix adds AdditionalContactRef1 and AdditionalContactRef2 columns to the Vendor table. Each contact entry has two parts:

• AdditionalContactRefContactName - the label (e.g., "Work Phone", "Mobile", "Website")
• AdditionalContactRefContactValue - the actual value (phone number, URL, etc.)

Read vendor additional contact data:

SELECT Name, AdditionalContactRef1ContactName, AdditionalContactRef1ContactValue, AdditionalContactRef2ContactName, AdditionalContactRef2ContactValue FROM Vendor

Update a vendor's Work Phone:

UPDATE Vendor SET AdditionalContactRef1ContactName = 'Work Phone', AdditionalContactRef1ContactValue = '555-0100' WHERE Name = 'Vendor Name'

Update Work Phone and Website in one statement:

UPDATE Vendor SET AdditionalContactRef1ContactName = 'Mobile', AdditionalContactRef1ContactValue = '555-0101', AdditionalContactRef2ContactName = 'Website', AdditionalContactRef2ContactValue = 'https://example.com' WHERE Name = 'Vendor Name'

Step 3: Access These Fields in QXL (DLL Swap)

QXL by default loads the standard runtime (qxlfqqb32.dll) which does not include the newly added beta fields. To access them in QXL:

1. Navigate to C:\Windows\SysWOW64\.
2. Rename qxlfqqb32.dll to qxlfqqb32-backup.dll.
3. Copy fqqb32.dll (the beta runtime) to qxlfqqb32.dll in the same directory.
4. Restart QXL - it will now load the beta runtime and expose the additional vendor contact fields.

To restore: Delete qxlfqqb32.dll, rename qxlfqqb32-backup.dll back to qxlfqqb32.dll.

Additional Notes:

• Beta version usage: The QODBC team confirmed that using the beta version (B01752) in production is acceptable, with a stable build expected within 30–45 days.
• sp_optimizefullsync returns no records: This is the expected result - it indicates successful sync. The optimizer file is created in %appdata%\QODBC Driver for QuickBooks, not returned as a result set.
• Multiple contact entries: Use AdditionalContactRef1, AdditionalContactRef2, etc. for additional contact entries on the same vendor record.

Related Articles:

QODBC Customer Contact Field (Mobile, Fax) Update via QODBC QODBC Optimizer - How to Enable, Rebuild, and Troubleshoot Performance Understanding QODBC Data Access Limitations

Keywords:

Problem Description:

You are writing SQL INSERT statements against QODBC tables (such as Customer, Invoice, SalesOrder, etc.) and receiving errors about missing required fields or invalid column names. You need a reliable way to discover which columns are available for any given table and which of them are required.

Affected products: QODBC Desktop, QODBC Online, QuickBooks Desktop, QuickBooks Online
Expected outcome: You can identify all available columns for any QODBC table, know which are required for INSERT operations, and construct valid SQL statements on the first attempt.

Symptoms:

• INSERT into a QODBC table fails with an error about a missing or invalid column.
• The record is created but some fields appear empty because they were not included in the INSERT.
• Attempting to INSERT without a required field (e.g., omitting the Name column for Customer) - the insert silently fails or returns an error.
• Uncertainty about which columns are mandatory vs. optional for a given table.

Cause:

QODBC tables mirror QuickBooks data structures, and some fields (like Name in the Customer table) are always required by QuickBooks itself. When an INSERT omits a required field, the SDK call to QuickBooks fails - often with a generic parsing or validation error. QODBC does not supply default values for missing columns; whatever you omit is left null, and QuickBooks rejects the request.

Unlike relational databases that provide information_schema views, QODBC uses stored procedures to expose table schemas. You must query the schema explicitly before writing INSERT statements.

Resolution:

1. Use SP_Columns in the QODBC Test Tool to discover all available columns for any table. This is the most reliable way to see exactly which columns a QODBC table supports and their data types.
   
   Open the QODBC Test Tool and run:

   SP_Columns TableName

   
   For example, to see all columns available on the Customer table:

   SP_Columns Customer

   
   This returns a result set listing every column name, data type, nullability, and whether it is required.
2. Check the online table documentation as a supplementary reference. QODBC maintains documentation pages for each table:
   
   https://doc.qodbc.com/qodbc/usa/TableDetails.php?tableName=<TableName>
   
   Replace <TableName> with the actual table name (e.g., Invoice, SalesOrder, Item). The page lists all columns, descriptions, and any required-field notes.
3. For a full list of available tables in your edition:
   
   https://doc.qodbc.com/qodbc/usa/TableList.php?categoryName=
4. Apply the findings to your INSERT statement. Ensure all required columns are included with valid values.
   
   For example, the Customer table always requires the Name column. An insert like this will fail:

   INSERT INTO Customer (FirstName, LastName, CompanyName) VALUES ('John', 'Doe', 'Acme Corp')

   
   The corrected version includes Name:

   INSERT INTO Customer (Name, FirstName, LastName, CompanyName) VALUES ('John Doe', 'John', 'Doe', 'Acme Corp')

SQL Examples:

Finding columns for any QODBC table:

-- Available columns for Customer table
SP_Columns Customer

-- Available columns for Invoice table
SP_Columns Invoice

-- Available columns for SalesOrder table
SP_Columns SalesOrder

-- Available columns for InvoiceLine (child table)
SP_Columns InvoiceLine

Correct INSERT with all required fields (Customer example):

INSERT INTO Customer
(Name, FirstName, LastName, BillAddressAddr1, BillAddressCity,
 BillAddressState, BillAddressPostalCode, Phone, Email)
VALUES
('John Doe', 'John', 'Doe', '123 Main St', 'Anytown',
 'CA', '90210', '555-0100', 'john@example.com')

Example Scenarios:

Scenario 1 - Missing required Name field on Customer insert:

A customer wrote an INSERT into the Customer table including FirstName, LastName, and CompanyName but omitted the required Name column. The QODBC log showed "error when parsing the provided XML." William identified that Name is mandatory for every Customer record in QuickBooks. Adding Name to the INSERT resolved the issue. The customer confirmed: "Thanks for getting back so quickly, we'll have them try supplying the company name and try again."

Scenario 2 - Building an ETL integration from scratch:

A developer setting up a new data pipeline uses SP_Columns on each target table before writing any INSERT statements. This avoids trial-and-error with column names and ensures all required fields are included from the start.

Additional Notes:

• SP_Columns works for both QODBC Desktop and QODBC Online editions - just make sure your DSN is connected before running it.
• A column marked as "non-nullable" in SP_Columns output means QuickBooks always requires it. Omitting such a column will cause the SDK call to fail every time.
• Different QODBC editions may expose different tables. QODBC Online has a different set of available tables than QODBC Desktop (e.g., no SalesOrder in online, but Invoice is available in both).
• Always include Name for Customer-type tables. Whether Customer, Vendor, or Employee, the Name field is universally required across all QuickBooks entity types.

Related Articles:

QODBC Support Wizard and Unoptimized Tables QODBC SDK Data Access Limitations

Keywords:

Problem Description:

When using the QODBC Support Wizard, some tables may appear in red text with a label indicating they are "unoptimized." Users may wonder why these tables cannot be optimized - even after running the Fix or Optimize Selected Tables buttons.

Affected products: QODBC Desktop
Expected outcome: Red (unoptimized) tables are typically empty - they contain no data and therefore do not need optimization. This is expected behavior, not an error.

Symptoms:

• The QODBC Support Wizard displays a list of tables with some highlighted in red, labeled as "unoptimized."
• Running the Fix button completes successfully, but the same tables remain red.
• Running Optimize Selected Tables also leaves the red tables unchanged.
• The Fix operation may take an extended time (12+ hours for large company files) to complete.

Cause:

Tables that contain no data are not optimized by QODBC. When a table has zero records, the QODBC optimizer skips it entirely - there is nothing to index or compress. In the Support Wizard UI, these skipped tables are displayed in red to indicate their unoptimized status.

This behavior is intentional and correct. It is not a bug or a sign of a corrupted optimizer. Empty tables do not benefit from optimization because there is no data to optimize.

Resolution:

1. Review the list of red (unoptimized) tables. Identify which tables are shown in red in the QODBC Support Wizard.
2. Determine whether each table is in use. Check your QuickBooks company file to see if you have ever recorded data in these tables (e.g., PayrollItem, SalesTaxItem, etc.).
3. If the table is not in use: You can safely ignore red tables. They contain no data and do not impact query performance.
4. If the table is in use but still shows red: Verify that you have actually recorded records in that table within QuickBooks. If records exist and the table remains red after running Fix/Optimize, contact QODBC Support for further analysis.

Example Scenarios:

Scenario 1 - Ignore unused tables:

A user runs the QODBC Support Wizard and sees five tables in red: PayrollItemDetail, SalesTaxRate, ServiceTerritory, TaxCode, and TaxRate. After checking their QuickBooks file, they confirm these tables have never been used (no payroll items, no sales tax codes set up). These red tables can be safely ignored.

Scenario 2 - Verify table usage:

A user notices the Employee table is unoptimized (red) and suspects something is wrong. After checking QuickBooks, they confirm they do have employees recorded. They clear their trace logs, run the Fix button again, and re-examine the trace log. If the Employee table still appears red in the log with a "no data to optimize" message, this may indicate a deeper issue - they should contact QODBC Support with the trace log.

Additional Notes:

• The Fix button performs a broad optimization of all tables that have data. For large company files, this can take an extended time (several hours or more depending on data volume).
• Optimize Selected Tables lets you choose specific tables to optimize rather than running the full Fix operation.
• Reset Optimizer Files clears all optimizer data and forces a complete rebuild the next time a query runs. This is useful if the optimizer is behaving incorrectly, but it also requires re-optimizing all tables afterward.
• For more information, see the article How to Use QODBC Support Wizard.

Related Articles:

QODBC Optimizer Stuck During Payroll Data Access Understanding 32-Bit vs 64-Bit ODBC DSN Configuration

Keywords:

Problem Description:

QRemote Server automatically starts every time Windows boots up. You want to disable this behavior - either because it conflicts with another user's session on a multi-user server, causes startup errors, or you prefer to launch QRemote manually when needed.

Affected products: QRemote Server, Windows (all versions)
Expected outcome: QRemote Server no longer launches at Windows bootup; it can be started manually from its own application menu when needed.

Symptoms:

• QRemote Server.exe starts automatically on Windows boot, even if you did not want it running.
• Multiple users on the same machine experience QRemote conflicts because one user's instance blocks another.
• An error appears when QRemote tries to auto-start without a logged-in QuickBooks session.
• You want full manual control over when QRemote starts and stops.

Cause:

QRemote Server includes an option called "Start QRemote Server on system startup" that is enabled by default during installation. When this setting is active, QRemote runs as a background process every time the machine boots - regardless of whether a QuickBooks session is available.

Resolution:

1. Open QRemote Server from your Start menu or system tray icon.
2. Navigate to File → Settings.
3. Switch to the Advanced tab.
4. Uncheck "Start QRemote Server on system startup."
5. Click OK to save the change.
6. Optionally, stop the currently running QRemote instance: File → Quit or close from the system tray.

QRemote Server will now only run when you manually start it. You can re-enable auto-start at any time by reversing the steps above.

Additional Notes:

• On multi-user servers or terminal sessions, QRemote running under a specific user's context may block other users from connecting. Disabling auto-start prevents this conflict.
• If QuickBooks is not logged in at boot time, QRemote Server cannot initialize properly and may log errors. Disabling auto-start eliminates these unnecessary error messages.
• For production use where QRemote should always be available, keeping the auto-start option enabled is recommended - ensure only one user has QuickBooks running on the machine.
• The official documentation for this setting: How to remove QRemote from Startup.

Example Scenarios:

Scenario 1 - IT admin manages a multi-user QuickBooks server:

An IT admin noticed QRemote was launching on boot under a service account, preventing actual users from connecting. Disabling auto-start allowed each user to launch QRemote in their own session after logging in, resolving the conflict.

Keywords:

Problem Description:

When using QODBC over QRemote (remote access to QuickBooks from another machine), connections may unexpectedly close after a period of inactivity or during long-running queries. This is controlled by two settings: the PingTotalCount limit (maximum pings before forced disconnect) and the Auto Close broken connections interval (how quickly a dead connection is detected and closed).

Affected products: QODBC Desktop, QRemote Server, QRemote Client
Expected outcome: Administrators can configure ping count and auto-close timeout to match their application's expected query duration and network stability.

Symptoms:

• QODBC connections close unexpectedly after a fixed period (e.g., 50 minutes) without any error from QuickBooks or the application.
• Long-running SQL queries via QRemote fail with a connection timeout, even though QuickBooks is still processing the query.
• Connections hang indefinitely after an application crashes - no automatic cleanup occurs.
• "Ping from client socket object" messages flooding the QRemote Server event log (resolved in v24 build 352+).

How Ping and Auto-Close Work Together:

The ping mechanism and the auto-close timeout work in tandem to manage stale connections:

1. Ping interval depends on the "Auto Close broken connections" setting:

2. PingTotalCount is the maximum number of pings before forced disconnect: The total connection lifetime = Ping Interval × PingTotalCount. For example, with Auto Close set to 6 minutes (5-minute ping interval) and PingTotalCount=10: 5 min × 10 = 50 minutes total before forced disconnect.
3. Auto Close broken connections detects dead links: If the client stops responding for the configured interval (e.g., 6 minutes), QRemote Server considers the connection lost and closes it immediately. The effective close time in this scenario is roughly double the interval: 1 minute to detect + 5 minutes of ping timeout = ~11 minutes total.

Configuration via ODBCINST.INI:

PingTotalCount is configured manually in the ODBCINST.INI file on the machine where QRemote Client is installed:

[QRemote Driver for QuickBooks (32 bit)]
PingTotalCount=10

File location: C:\ProgramData\QODBC Driver for QuickBooks\ODBCINST.INI

Recommended values: PingTotalCount should be between 3 and 50. Values above 90,000 are the maximum possible. The default value after installation may be 90,000 (effectively unlimited), which means connections will only close due to network issues or manual intervention.

Example Scenarios:

Scenario 1 - Hung SQL Statement:

A SQL query is executing on the QuickBooks side, but the application crashes before closing the connection. With PingTotalCount=10 and Auto Close=6 minutes, QRemote Server keeps the connection open for 50 minutes (5-minute ping × 10 counts), then aborts the statement and closes the connection automatically.

Scenario 2 - Network Disconnect During Query:

A network issue interrupts communication between QRemote Client and Server during an active query. With Auto Close=6 minutes, the server detects the lost connection after 6 minutes. Combined with ping timeout, the total close time is approximately 11 minutes. The connection is automatically terminated.

Scenario 3 - Long-Running Report Query:

A report takes longer than 50 minutes to execute (with default PingTotalCount=10 and Auto Close=6). The connection will be forcefully closed at the 50-minute mark. To prevent this, increase PingTotalCount in ODBCINST.INI (e.g., set it to 100 for an approximate 8-hour window).

Additional Notes:

• Ping flood fix (v24+): Starting with QODBC v24 build 352, the "Ping from client socket object" log spam issue was resolved. A ping count limit was added to prevent excessive logging.
• Default PingTotalCount after fresh install: May be set to 90,000 (effectively unlimited). Modify as needed for your environment's needs.
• Auto Close broken connections setting: This is configured in the QODBC DSN settings under the General tab. It controls how quickly stale connections are detected and cleaned up.
• Configuration applies to the QRemote Client machine: Only modify ODBCINST.INI on machines that run the QRemote Client, not the QuickBooks server machine.

Keywords:

Problem Description:

Users connecting to QODBC via Python's pyodbc module (particularly through QRemote) may experience inconsistent crashes or connection failures. The issue is often intermittent - some queries succeed while others fail, and repeated attempts may work.

Affected products: QODBC Desktop (all versions), QRemote Server/Client, Python with pyodbc module, Windows Server
Expected outcome: Stable QODBC connections through pyodbc without crashes or inconsistent query results.

Symptoms:

• Python program using pyodbc exits with an error code (e.g., 3221226356) when querying QODBC data through QRemote.
• The issue is highly inconsistent - some runs succeed while others fail on the same query.
• The crash occurs at different points in the program, making it difficult to reproduce reliably.
• Restarting or re-running the script sometimes works, suggesting a timing or state issue.

Cause:

Multiple factors can contribute to pyodbc crashes when used with QODBC via QRemote:

• Beta/bug in older QODBC versions: Certain builds of QODBC had bugs that could cause intermittent crashes when used with third-party ODBC drivers like pyodbc, especially under multi-threaded conditions or remote connections.
• QRemote state synchronization: The QRemote optimizer may need to be rebuilt to ensure all table data is synchronized. Stale optimizer files can cause inconsistent query results or crashes.
• Connection pool management: pyodbc's connection pooling may conflict with QODBC's persistent connections to QuickBooks, particularly when using QRemote across different processes.

Solution:

1. Install the latest beta hotfix. Check for available hotfix builds from the QODBC downloads page. The latest fixes often address crash issues seen with third-party ODBC integrations. The issue has already been fixed and released in QODBC version 355.
   1. Download the latest beta fix (e.g., B01721_beta_2024_04_11.exe) from https://qodbc.com/download/beta/.
   2. Close all applications and QODBC connections (QuickBooks, MS Access, MS Excel, QRemoteServer, and any ODBC-consuming tools).
   3. Run the installer and click Extract, then Yes when prompted to overwrite files.
2. Delete the optimizer file and rebuild it. After installing the hotfix, delete the existing optimizer file from the QODBC optimizer folder, then open the folder to verify deletion.
3. Run the full sync command. Open the QODBC Support Wizard and run:

   Sp_optimizefullsync All

   This rebuilds all table data in QuickBooks and ensures the optimizer has a fresh, consistent state.

4. Test your pyodbc connection. Run your Python script again with pyodbc. The intermittent crashes should be resolved by the hotfix and optimizer rebuild.
5. Optional workaround - Use an intermediary process: If issues persist, consider using an intermediary C# or other application to extract QODBC data and feed it to your Python program via a file or API. This can help isolate the issue to either the pyodbc connection or the underlying QODBC/QRemote data.

Additional Notes:

• QODBC license requirement for QRemote: A paid QODBC license is required when using QRemote, whether connecting from Excel, MS Access, or Python/pyodbc. The free read-only edition included with QuickBooks Enterprise does not include QRemote functionality.
• Persistent connections: QODBC and QRemote maintain persistent connections to QuickBooks. Ensure all ODBC-consuming applications are properly closed when making configuration changes.
• Connection pooling in pyodbc: If you experience connection issues, try disabling pyodbc's connection pooling by omitting the POOLING parameter from your DSN configuration, or setting Pooling=False.

Related Articles:

• QODBC 64-Bit Driver Activation Through 32-Bit ODBC Administrator
• Understanding 32-Bit vs 64-Bit ODBC Drivers on Windows
• How to Use QODBC Support Wizard

Keywords:

Problem Description:

When using Microsoft Power BI with the On-Premises Data Gateway to access QuickBooks Desktop data, users may encounter connection errors (such as Error = 80040408, Could not start QuickBooks) if the licensing or driver configuration is incorrect. Specifically, the setup is highly dependent on whether the Power BI Gateway and the QuickBooks application are installed on the same machine or different machines within the network.

Affected products: Power BI Desktop, Power BI Service (via Gateway), QODBC Desktop, QODBC Server, QuickBooks Desktop
Expected outcome: After configuring the correct QODBC licenses and driver compatibility (specifically the 3.8 compatibility mode), Power BI can successfully query QuickBooks data, whether the gateway is local or remote.

Symptoms:

• The Power BI service or Desktop application returns an ODBC error: Error = 80040408, Could not start QuickBooks.
• The connection works perfectly when running Power BI locally on the QuickBooks machine, but fails when refreshing data via the On-Prem-Gateway.
• The error explicitly mentions that QuickBooks could not be started or the session failed on the target machine (e.g., Computer Name: OLEMSQBP01).
• The connection works only when the QuickBooks application is manually opened and logged in on the host machine.

Cause:

The connection requirement depends on the network architecture of your Power BI deployment:

1. Single-Machine Architecture: If the Power BI On-Premises Data Gateway and QuickBooks Desktop are both installed on the same machine, only one QODBC license is required (the QODBC Server edition).
2. Multi-Machine Architecture (Remote Access): If the Power BI Gateway is on a separate server from the QuickBooks machine, you need two licenses:
   • On the Gateway Machine: QRemote License, with QRemote Client and Power BI Gateway.
   • On the QuickBooks Machine: QODBC Server Edition license, with QuickBooks, QODBC, QRemote Server, and the QuickBooks company file.
3. Compatibility Mismatch: Recent versions of the QODBC driver/QRemote client may default to a higher compatibility mode than what Power BI's Power Query engine (via the Gateway) expects. For many legacy/standard queries, the 3.8 compatibility mode is required for stability.

Environment:

Solution:

1. Determine your architecture. Identify if your Power BI Gateway service is running on the same Windows machine as your QuickBooks Desktop application.
2. Deploy required licenses.
   • If Single-Machine: Ensure QODEX/QODBC is properly activated on that single machine.
   • If Multi-Machine: Install the QODBC Server edition on the QuickBooks machine and the QODBC Desktop (QRemote) on the Gateway machine.
3. Configure ODBC Compatibility to 3.8 (Crucial Step). To prevent "Begin Session Failed" or "Could not start QuickBooks" errors, manually set the driver to use the 3.8 compatibility mode on both machines:
   1. Navigate to Control Panel → Administrative Tools → Data Sources (ODBC) (3 32-bit) (or 64-bit, as applicable).
   2. Select your System DSN (e.g., 'QuickBooks Data') and click Configure.
   3. Navigate to the Advanced tab.
   4. Locate the ODBC Compatibility dropdown and select '3.8'.
   5. Click Apply and OK.
4. Ensure QRemote Server is running. If using the multi-machine setup, ensure the QRemote Server service is started and accessible on the QuickBooks machine.
5. Verify the connection. Test the connection using the QODBC Test Tool or by performing a manual refresh in Power BI Desktop.

Example Scenarios:

Scenario 1 — Same Machine (Simple): A user has QuickBooks and the Power BI Gateway on their local workstation. They use QODBC Desktop. The connection is straightforward and works as long as QuickBooks is open or Auto-Login is configured.

Scenario 2 — Enterprise Remote Access (Complex): A company has a dedicated Windows Server running the Power BI On-Premises Data Gateway. Their QuickBooks data lives on a separate, highly-secured Accounting Server. To allow the Gateway to pull data, they installed the QODBC Server edition on the Gateway Server and the QODBC Desktop/QRemote driver on the Accounting Server, both configured to 3.8 compatibility.

Additional Notes:

• Auto-Login: For both architectures, it is highly recommended to configure Auto-Login in the QODBC DSN settings. This prevents "Access Denied" errors caused by the user not being logged into QuickBooks.
• Service Account: If the Power BI Gateway is running under a specific Windows Service Account, ensure that the QODBC driver and QuickBooks-related permissions are accessible to that account.
• QS_Error 80040408: This error is almost always caused by the lack of a running QuickBooks session or a failure in the QRemote/ODBC handshake between the two machines.

Related Articles:

Accessing Multiple QuickBooks Company Files Simultaneously with QODBC and QRemote QODBC License Machine Transfer and Deactivation How to Use QODBC with MS Excel Power Query

Keywords:

Problem Description:

Users may encounter slow query performance when retrieving large datasets (e.g., Invoices, Transactions) from QuickBooks using QODBC. Specifically, queries that should be fast are taking an unusually long time to complete, or the "end of transmission" (closing the connection/results) appears to hang.

Affected products: QODBC Desktop, QODBC Online, QuickBooks Desktop
Expected outcome: Using the QODBC Optimizer feature to significantly reduce query response times and ensure smooth data retrieval.

Symptoms:

• Queries on large tables (e.g., Invoice, Customer) are extremely slow.
• The connection appears to hang or time out during the closing phase of a large data transmission.
• Executing a query with the UNOPTIMIZED keyword returns results much faster than a standard query.
• The application (e.g., MS Access, Excel, Power BI) becomes unresponsive during data refreshes.

Cause:

The QODBC Optimizer is a feature designed to cache and index QuickBooks data locally to speed up subsequent queries. Over time, if the QuickBooks company file changes significantly (e.g., large transactions, deletions, or restructuring), the local optimizer cache may become out of sync or corrupted.

When the optimizer cache is out of sync, the driver may struggle to reconcile the cached data with the live QuickBooks data, leading to extreme performance degradation or hangs during the "end of transaction" phase.

Environment:

Solution:

If you suspect the optimizer is causing performance issues, you should first verify if it is enabled and then attempt to rebuild it.

Step 1: Verify that 'Use_Optimizer' is Enabled

The Optimizer must be actively enabled in the driver configuration to provide any benefit.

1. Navigate to the 32-bit QODBC Setup Screen:
   Start → All Programs → QODBC Driver for QuickBooks → QODBC 32-bit Setup Screen
2. Navigate to the Optimizer tab.
3. Ensure the 'Use_Optimizer' checkbox is checked.

Step 2: Rebuild the Optimizer (Reload All Data)

If the optimizer is enabled but queries are still slow, the cache may be corrupted. Rebuilding the cache is the most effective fix.

1. In the same Optimizer tab mentioned above, locate the 'Reload All Data' button.
2. Click 'Reload All Data'.
3. QODBC will begin scanning your QuickBooks company file to rebuild the local index and cache. Note: For very large company files, this may take several minutes.
4. Once the process completes, attempt your queries again using the standard SELECT statement.

How to Test if the Optimizer is the Problem

You can perform a quick diagnostic test using any ODBC-compliant tool (e.g., QODBC Test Tool, Excel, MS Access) by comparing a standard query against an unoptimized one.

Test 1 (Standard Query):

SELECT * FROM Invoice

Observe the time taken to return results.

Test 2 (Unoptimized Query):

SELECT * FROM Invoice UNOPTIMIZED

Observe the time taken to return results.

Analysis: If the UNOPTIMIZED query is significantly faster than the standard query, it is a definitive sign that the Optimizer cache is corrupted and must be rebuilt using the 'Reload All Data' method described in Step 2.

Additional Notes:

• Performance Trade-off: While the optimizer speeds up reads, the initial "Reload All Data" process consumes CPU and disk I/O. Schedule this during off-peak hours for very large company files.
• Automation: The optimizer works automatically once enabled, but manual rebuilds are necessary after significant database changes.
• ⚠ Critical Risk - Optimization Can Delete ALL Records: Running Sp_optimizefullsync or "Reload All Data" on a table can cause it to lose all rows. This happens when the optimizer rebuild process fails mid-way (e.g., QuickBooks closes during sync, connection drops, or the company file is opened by another session). Always back up the QODBC database before running a full optimizer rebuild.

Related Articles:

• How to Setup QODBC Optimizer
• QODBC Error: Connection Timeout
• Troubleshooting Slow QODBC Queries

Keywords:

Problem Description:

When using QODBC Online to consolidate data from multiple QuickBooks Online (QBO) company files simultaneously through Power BI, queries may fail with Error 653 ("There was an error while communicating with the Ids Server - The Response Stream was null or empty") and/or port 8383 binding issues. Consolidated P&L queries across 6+ company files fail, while pulling one file at a time succeeds.

Affected products: QODBC Online, QuickBooks Online, Power BI
Expected outcome: By limiting consolidated queries to 2–3 company files at a time and managing connection cycling, errors can be avoided. QODBC Online itself does not limit simultaneous connections - the limit comes from Intuit's API side.

Symptoms:

• A consolidated Power BI query that pulls P&L data from multiple QBO company files works when pulling one file at a time, but fails when consolidating 4+ files simultaneously.
• Error message: "OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [00000] [QODBC ONLINE] QuickBooks message: 653 - There was an error while communicating with the Ids Server. - The Response Stream was null or empty."
• Port 8383 becomes unavailable without any configuration change from the user.
• The issue is specific to QuickBooks Online (not QuickBooks Desktop) and occurs when multiple company files are queried concurrently.

Cause:

While QODBC Online does not impose any limit on the number of simultaneous company file connections, Intuit's API does have rate limits and concurrent connection limits. When Power BI queries multiple QBO files simultaneously (especially through a consolidated query), the Intuit API may reject requests due to:

1. Concurrent API connection limits: Intuit restricts how many simultaneous API connections a single application/user can maintain.
2. Response stream timeout: When too many files are queried at once, the response stream may time out or return empty, resulting in Error 653.
3. Port 8383 overload: The QODBC Online communication port can become overloaded by simultaneous large data extracts from Power BI.

Environment:

Solution:

1. Limit consolidated queries to 2–3 company files at a time. This is the recommended workaround to avoid Intuit API rate-limit and timeout issues. Instead of pulling data from all 7 companies in one query, split into batches of 2–3 files per refresh cycle.
2. Cycle through incremental consolidations. Pull P&L for 2–3 files, refresh Power BI, then pull the next 2–3 files, and so on. While more time-consuming, this is the most reliable approach.
3. If port 8383 becomes unavailable: Try restarting your machine to clear any stale port bindings, then test again.
4. Avoid querying too many files simultaneously. While QODBC Online does not limit connections, Intuit's API does. Keep concurrent queries well below the number of available company files.

SQL Examples:

Example - Splitting a 7-file consolidation into batches of 3:

-- Batch 1: Companies A, B, C
SELECT * FROM ProfitAndLoss WHERE Company IN ('A', 'B', 'C')

-- Refresh Power BI after batch 1 completes

-- Batch 2: Companies D, E, F
SELECT * FROM ProfitAndLoss WHERE Company IN ('D', 'E', 'F')

-- Refresh Power BI after batch 2 completes

-- Batch 3: Company G (remaining)
SELECT * FROM ProfitAndLoss WHERE Company = 'G'

Example Scenarios:

Scenario 1 - Consolidating P&L from 7 QBO companies:

A company uses Power BI to consolidate P&L data from 7 QuickBooks Online company files. A single consolidated query across all 7 files fails with Error 653 and port 8383 issues. The fix was to split the consolidation into incremental batches of 2–3 companies per refresh cycle. Each batch completes successfully before moving to the next.

Scenario 2 - Power BI data refresh timeout:

A scheduled Power BI data refresh across 6 QBO files fails intermittently with "Response Stream was null or empty." The issue is Intuit API rate limiting when too many concurrent requests are sent. Limiting the refresh to 2–3 files per cycle eliminates the error.

Additional Notes:

• This limit comes from Intuit, not QODBC. QODBC Online acts as a pass-through to the Intuit API - it does not impose its own connection limits. The rate-limiting behavior is controlled by Intuit's infrastructure.
• Error 653 specifically means the response from the Intuit IDs Server was empty or timed out. It is NOT a QODBC bug - it is an API-level timeout caused by overloading the connection pool.
• Port 8383 is used by QODBC Online for communication with the IntIds server. If it becomes unavailable after a Power BI query completes, restarting the machine typically resolves the binding issue.

Related Articles:

Using QuickBooks Online Data with MS SQL Server via QODBC Online Troubleshooting: Slow Performance/Cannot Get Full Records From QuickBooks

Keywords:

Problem Description:

In certain versions of QODBC Online (including the QXL tool), running a General Ledger report query causes the Quantity values to be exported as integers instead of floating-point numbers, leading to data inaccuracies in external reports and spreadsheets.

Affected products: QODBC Online, QXL Online, QuickBooks Online
Expected outcome: Quantity values in General Ledger report results are returned as floating-point numbers, preserving the decimal precision from the source data.

Symptoms:

• General Ledger report quantity columns show whole numbers (e.g., 5) instead of the actual value (e.g., 5.25 or 0.75).
• The values appear correct in QuickBooks Online but are truncated when exported via QODBC Online.
• The issue only affects quantity-type fields - monetary amounts (Amount, etc.) are unaffected and retain proper decimal precision.

Cause:

A bug in QODBC Online's report processing (tracked as B01712 (The issue has been fixed and released in QODBC Online version 358.)) caused the Quantity column to be cast to an integer type during the export pipeline, discarding any fractional portion of the value. This is a data type mapping issue in the QOL reporting engine, not a QuickBooks Online or customer-side problem.

Environment:

Resolution:

1. Upgrade to a version that contains the fix for B01712.
   
   The issue was resolved in beta build B01711-13-beta_2024-06-28. Upgrade by downloading and installing the fixed version.
2. Install the updated QXL Online (if applicable).
   
   
   1. Close all applications that use QXL or QuickBooks.
   2. Uninstall the current version of QXL Online.
   3. Install the latest version from the QODBC download page.
   4. Restart your computer.
   5. Open QXL Online → Settings/Options → About tab to verify the version is updated.
3. Re-run your General Ledger report. Quantity values should now appear with full decimal precision.

SQL Examples:

Affected query (returns truncated quantities in prior versions):

EXEC sp_report GeneralLedger
  SHOW RowData, Date, TransactionType, Name, Memo, Split, Amount, Quantity
  PARAMETERS DateFrom = '2024-01-01', DateTo = '2024-03-31'

In affected versions, the Quantity column returns integers. After upgrading to the fixed version, Quantity values are returned as floating-point numbers with full precision.

Example Scenarios:

Scenario 1 - Inventory quantity tracking:

A customer runs General Ledger reports for inventory accounts and notices quantities like 25.5 appearing as 25. This causes inventory reconciliation mismatches because the fractional portion (partial shipments, adjustments) is lost in QODBC's export.

Additional Notes:

• This bug only affects QODBC Online / QXL reports. Direct queries against transaction tables (e.g., SELECT Quantity FROM TransactionLine) are not affected - the issue is specific to the sp_report GeneralLedger report pipeline.
• Bug B01712 status: The fix was first released in beta build B01711-13-beta_2024-06-28. Check the QODBC release notes to confirm whether the fix is included in your current version.
• CDKey preservation: When upgrading from a beta build, do not deactivate your existing CDKEY - the installation preserves it.

Related Articles:

QODBC Online General Ledger Reports Return Blank Results - Bug B01667 Troubleshooting: Reports Return No Data in QODBC Online

Keywords:

Problem Description:

When using QODBC Online to insert records into QuickBooks Online tables, queries may fail silently - or appear to succeed on some rows but fail on others - when the FQSaveToCache field is used incorrectly. This commonly occurs after migrating from QuickBooks Desktop (where FQSaveToCache behavior differs) to QuickBooks Online via QODBC Online.

Affected products: QODBC Online, QuickBooks Online
Expected outcome: Insert queries succeed for all intended records by using FQSaveToCache only on child/line-item tables, not on parent tables.

Symptoms:

• Insert queries into parent tables (e.g., Bill, Invoice, VendorCredit) fail or produce no visible result in QuickBooks Online.
• A batch of mixed insert queries appears to succeed for some rows but not others - the child table inserts work while the parent table insert silently fails.
• The QODBC trace log shows the INSERT statement was sent, but no corresponding record appears in QuickBooks Online.

Cause:

In QODBC Online, the FQSaveToCache field is only valid on child (line-item) tables, not on parent tables.

QuickBooks Online uses a parent-child data model for transactions. Parent tables (e.g., Bill, Invoice, SalesReceipt, PurchaseOrder) hold the header information, while child tables (e.g., BillExpenseLine, InvoiceLine, SalesReceiptLine) hold the line-item detail. The FQSaveToCache field tells QuickBooks to queue the record for later processing and should only be used on child tables when building a multi-line transaction.

Using FQSaveToCache on a parent table is invalid in QODBC Online and causes the insert to fail silently - the query does not generate an error message but no record is created.

Resolution:

1. Identify the table(s) where FQSaveToCache is being used on parent tables. Common parent tables include: Bill, Invoice, SalesReceipt, PurchaseOrder, JournalEntry, Estimate, ReceivePayment, etc.
2. Remove FQSaveToCache from INSERT statements targeting parent tables. Only keep it on child/line-item table inserts within a multi-line transaction build.
3. Restructure your queries in the correct order:

Correct Query Pattern (Multi-Line Transaction Build):

When inserting a multi-line transaction and using FQSaveToCache to queue the lines before committing the parent:

-- Child/line-item rows with FQSaveToCache
INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache") VALUES ('57', 21488.80, 'Current Purchases', 1)

-- Parent row WITHOUT FQSaveToCache (this commits the transaction)
INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber") VALUES ('2909', '37', {d'2024-12-06'}, '120624-3')

Single-Line Transaction (Simple Insert):

If you are inserting a single line-item transaction, FQSaveToCache is not needed at all on either table:

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber") VALUES ('2909', '37', {d'2024-12-06'}, '120624-3')

Common Parent Tables:

Do not use FQSaveToCache on these tables:

Example Scenarios:

Scenario 1 - Desktop code migrated to Online:

A company migrated from QuickBooks Desktop to QuickBooks Online and used existing QODBC code that included FQSaveToCache on all INSERT statements. Bill inserts silently failed in the Online environment because FQSaveToCache is not valid on parent tables in QODBC Online. Removing it from the parent table insert resolved the issue.

Scenario 2 - Multi-line build:

A user builds a multi-line bill by inserting expense lines first (with FQSaveToCache=1), then inserts the Bill header. The expense line insert works correctly because FQSaveToCache is valid on child tables. The Bill insert fails if FQSaveToCache is included - remove it from the Bill statement only.

Additional Notes:

• FQSaveToCache on child tables: When using FQSaveToCache=1 on a child table, all subsequent line-item inserts are queued until the parent record is inserted (which commits the entire transaction).
• Differences from Desktop: In QuickBooks Desktop via QODBC Desktop, FQSaveToCache behavior may differ. Always test queries against the target environment.
• Debugging tips: Enable the QODBC trace log to see which INSERT statements are being sent. If the parent table insert appears in the trace but no record shows up in QuickBooks Online, check for FQSaveToCache on the parent statement.

Related Articles:

Understanding QODBC Online Data Layouts QuickBooks Online Table Hierarchy Reference

Keywords:

Problem Description:

In earlier versions of QODBC Online, attempting to update or clear the ExpenseLineCustomerRefListID (also known as ExpenseLineProjectRefListID) field on the BillExpenseLine table would fail - even though the SDK API permits QuickBooks Online to accept such updates. This was tracked as internal bug B01772.

Affected products: QODBC Online (prior to B01772 fix), QuickBooks Online
Expected outcome: You can reset or clear the Project reference on any BillExpenseLine record via an UPDATE query, after installing the beta build that resolves Bug B01772.

Symptoms:

• An UPDATE on the BillExpenseLine table fails silently or returns no error when attempting to change the ExpenseLineCustomerRefListID / ExpenseLineProjectRefListID field.
• The existing Project reference persists in QuickBooks Online despite the UPDATE query reporting success.
• The issue is specific to QODBC Online - the same query works correctly against QODBC Desktop (QuickBooks Desktop).

Cause:

Bug B01772 in QODBC Online prevented the driver from properly resetting or updating the Project/Customer reference on BillExpenseLine child records. This was a driver-level limitation where the ODBC layer did not pass the project reference update to the QuickBooks Online SDK correctly. A fix was developed and released as part of beta build B01772-beta-2025-02-11. The issue has been fixed and released in QODBC Online version 360.

Resolution:

1. Install the QODBC Online beta build that fixes Bug B01772.
   Download from: https://qodbc.com/download/beta/Fixes/B01772-beta-2025-02-11/B01772-beta-2025-02-11.exe
   Before installing, back up SocketReceiver.exe from C:\Program Files (x86)\QODBC Driver for QuickBooks Online\QBO\. Close all applications using QODBC Online and kill SocketReceiver.exe from Task Manager before overwriting.
2. Use an UPDATE query to reset or change the Project reference on BillExpenseLine. After installing the fix, use the following SQL pattern:

   UPDATE BillExpenseLine
   SET ExpenseLineCustomerRefListID = '', ExpenseLineProjectRefListID = ''
   WHERE TxnID = '<transaction-id>' AND ExpenseLineTxnLineID = '<line-id>' AND ExpenseLineSeqNo = '<seq-no>'

   
   To set a new Project reference instead of clearing it, replace the empty string with the target ListID:

   UPDATE BillExpenseLine
   SET ExpenseLineCustomerRefListID = '<new-list-id>', ExpenseLineProjectRefListID = '<new-list-id>'
   WHERE TxnID = '<transaction-id>' AND ExpenseLineTxnLineID = '<line-id>' AND ExpenseLineSeqNo = '<seq-no>'

Example Scenarios:

Scenario 1 - Clearing a project from a bill expense line:

A customer needed to remove the project assignment from an existing BillExpenseLine record. After installing the B01772 fix, they ran:

UPDATE BillExpenseLine
SET ExpenseLineProjectRefListID = ''
WHERE TxnID = '343' AND ExpenseLineTxnLineID = '1' AND ExpenseLineSeqNo = '1'

The project reference was successfully cleared from the QuickBooks Online bill.

Scenario 2 - Reassigning a project on an expense line:

A company needed to move an expense line from one project to another. After installing the B01772 fix:

UPDATE BillExpenseLine
SET ExpenseLineCustomerRefListID = '8A', ExpenseLineProjectRefListID = '8A'
WHERE TxnID = '343' AND ExpenseLineTxnLineID = '1' AND ExpenseLineSeqNo = '1'

The expense line was reassigned to the new project in QuickBooks Online.

Additional Notes:

• This bug only affected QODBC Online - QODBC Desktop had no such limitation since the QuickBooks Desktop SDK natively supports updating the Project reference on expense lines.
• Bug B01772 is resolved in QODBC Online build 24.0.0.358+ (and later). If you are running a newer release, this issue should not occur.
• The Project reference uses the same ListID field as the Customer reference on BillExpenseLine - projects in QuickBooks Online use the Customer table with <isProject> = true. This is why both ExpenseLineCustomerRefListID and ExpenseLineProjectRefListID may appear in queries.
• Always test beta builds on sandbox data first. The B01772 fix involves replacing a core binary (SocketReceiver.exe) - ensure you have backups and verify functionality before deploying to production.

Related Articles:

QODBC Online: FQSaveToCache Field Restrictions for Parent and Child Tables

Keywords:

Problem Description:

When inserting records into QODBC tables (such as InvoiceLine with FQSaveToCache followed by an Invoice header) from MS Access via VBA, the inserts may silently lose their cached lines. The header INSERT then fails because no line records exist to commit.

This is often caused by two issues: (1) MS Access's native date literal syntax (#2025-06-20#) is incompatible with QODBC's ODBC canonical format requirement, and (2) using DoCmd.RunSQL instead of a direct connection method can close the ODBC session between queries.

Affected products: QODBC Desktop, MS Access (all versions), QuickBooks Desktop
Expected outcome: All cached line records persist until the header INSERT, which commits everything to QuickBooks successfully.

Symptoms:

• INSERT statements into InvoiceLine with FQSaveToCache = 1 appear to succeed, but no lines are committed when the Invoice header INSERT runs.
• The Invoice header fails, complaining there are "no line records" - as if the cached InsertLines were never sent.
• The issue occurs intermittently: it works sometimes (especially after restarting QuickBooks/Access) but not consistently.
• Reliably fails when using MS Access's native date literal syntax (#YYYY-MM-DD#) in SQL queries executed via DoCmd.RunSQL or direct connections.

Cause:

Two independent issues can combine to cause cache loss:

1. Date literal format mismatch. MS Access uses #YYYY-MM-DD# for date literals. QODBC requires ODBC canonical format: {d'YYYY-MM-DD'}. When a date literal in the wrong format is sent via an ADO/OLEDB connection, QODBC may silently fail to parse it - and depending on the connection method, may close the session, which clears all pending cache.
2. FQSaveToCache = 1 Cache is cleared on any subsequent error. When the header INSERT fails (often because of the date format mismatch), QODBC clears all previously cached InvoiceLine records. Re-running only the header INSERT will succeed (no lines to commit) but creates an orphan invoice - the lines are gone forever in that session.

Resolution:

1. Use ODBC canonical date format for ALL date literals in QODBC queries.
   
   Replace MS Access #2025-06-20# with ODBC format {d'2025-06-20'}:
   
   Wrong (MS Access native):

   VALUES (..., #2025-06-20#, ...)

   
   Correct (ODBC canonical):

   VALUES (..., {d'2025-06-20'}, ...)

2. Use oConnection.Execute instead of DoCmd.RunSQL.
   
   DoCmd.RunSQL uses an action query path in MS Access that may close and reopen the connection between statements, which clears FQSaveToCache. Use an explicit ADO Connection object instead:

   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   cnn.Open "Provider=MSDASQL;DSN=QuickBooks Data;"
   
   ' Execute line inserts with FQSaveToCache = 1
   cnn.Execute "INSERT INTO InvoiceLine (...) VALUES (...)"
   
   ' Execute header insert (commits all cached lines)
   cnn.Execute "INSERT INTO Invoice (...) VALUES (...)"
   
   cnn.Close

3. If any error occurs during a batch, re-execute the entire sequence from the beginning. Once the cache is cleared by an error, there is no way to recover the cached lines - they must all be re-sent.

SQL Examples:

Correct multi-step Invoice creation (all in one ADO session):

' Step 1: Insert cached line 1
cnn.Execute "INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, " & _
  "InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) " & _
  "VALUES ('80000FBD-1705490688', '80000220-1566593614', 'Service', " & _
  "100.00, 100.00, 1)"

' Step 2: Insert cached line 2
cnn.Execute "INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, " & _
  "InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) " & _
  "VALUES ('80000FBD-1705490688', '80000221-1566593615', 'Materials', " & _
  "50.00, 50.00, 1)"

' Step 3: Insert header (commits all cached lines)
cnn.Execute "INSERT INTO Invoice (CustomerRefListID, TxnDate, RefNumber, " & _
  "Memo, FQSaveToCache) VALUES ('80000FBD-1705490688', {d'2025-06-20'}, " & _
  "'INV-001', 'Auto-invoiced', 0)"

Example Scenarios:

Scenario 1 - Batch invoice export from Access:

A company exports invoices from an MS Access-based management system to QuickBooks via QODBC. Their code previously used #date# notation with DoCmd.RunSQL. After switching to ADO oConnection.Execute and ODBC {d'2025-06-20'} date format, batch exports work reliably - including the FQSaveToCache mechanism that allows multi-line invoice creation before committing.

Scenario 2 - Recovering from a cache-clear error:

After an InvoiceLine insert with FQSaveToCache = 1 fails (e.g., due to an invalid item ref), the customer re-runs only the header INSERT. It succeeds (no error) but creates an empty invoice because the cached lines were already cleared. Fix: wrap the entire multi-INSERT sequence in a VBA error handler that re-sends all statements from scratch if any step fails.

Additional Notes:

• FQSaveToCache is session-scoped. If the ODBC connection closes (even unexpectedly due to a parse error), all cached data is lost. Always use a single persistent connection object for multi-step inserts.
• Date format matters even when the value looks correct. The date string "2025-06-20" is identical, but the surrounding syntax (# vs {d'...'} ) determines whether QODBC can parse it. This is one of the most common causes of intermittent cache loss.
• After upgrading QODBC, always relink Access tables. New builds may reset linked table connections, causing silent date format regressions.

Related Articles:

QODBC Online: FQSaveToCache Restrictions

Keywords:

Problem Description:

You have linked a QuickBooks table in MS Access (or SQL Server) via QODBC and want to understand the capabilities and limitations - can you edit data directly in QuickBooks, does validation apply, are changes immediate, and can updates be rolled back?

Affected products: QODBC Desktop, MS Access, SQL Server, QuickBooks Desktop
Expected outcome: You understand the capabilities and limitations of editing QuickBooks data via QODBC linked tables in MS Access or SQL Server.

Symptoms:

Common gotcha: If updates silently fail or report data-has-changed, check that Read-Only Access is unchecked in QODBC Setup → Advanced tab. When this option is enabled, all linked tables become write-protected.

• You want to edit QuickBooks data directly from an MS Access or SQL Server linked table and need to know what behavior to expect.

Capabilities:

1. Yes, you can edit data in QuickBooks directly from MS Access or SQL Server via QODBC linked tables.** Updates flow through the ODBC connection into QuickBooks and appear as if they were entered within the QuickBooks UI.
2. Updates are immediate.** When you save a record in the linked table in MS Access or SQL Server, the change is written to QuickBooks in real time. There is no batch or queue delay - the data appears in QuickBooks as soon as the record is saved.
3. You cannot roll back or undo a query once it has executed. ODBC does not provide an automatic rollback mechanism for write queries. If a change was made in error, you must execute a new UPDATE or INSERT query to correct the data.

Limitations:

• QuickBooks validation still applies. All business rules enforced by QuickBooks (e.g., required fields, valid types, closed periods) are applied when external edits reach QuickBooks. Invalid data will be rejected with an error message.
• No transactional rollback for bulk operations. Each record is written independently - a failure in one record does not roll back changes to other records in the same batch.

Best Practices:

• Use transaction control where possible. For critical bulk operations, wrap your UPDATE/INSERT logic in application-level transactions (in VBA or your scripting language) to catch errors before committing changes.
• Maintain a local log or audit trail. Since there is no undo, consider keeping a local copy of original records before running bulk updates via linked tables.

Example Scenarios:

Scenario 1 - Memo field update with typo:

A customer linked the Invoice table in MS Access and updated the memo field on several records. The changes appeared in QuickBooks immediately. After saving, they noticed one memo had a typo. Because QODBC does not support rollback, they manually corrected the affected record by running an UPDATE query on the linked table.

Scenario 2 - Bulk update with validation failures:

A company ran a bulk UPDATE of Customer records from Access via a linked table. QuickBooks validated each change and rejected a few that violated business rules (e.g., invalid customer type). Only the valid changes were written; the invalid ones returned errors. The customer used this feedback to correct the data on a second pass.

Related Articles:

MS Access + QODBC: Date Literals, Connection Methods, and FQSaveToCache Caching QODBC: Finding Required Fields for Table INSERTs

Keywords:

Problem Description:

QODBC can only access data through the fields, features, and commands that are exposed by the QuickBooks SDK (an embedded feature of the QuickBooks application). When a particular operation - such as appending or updating records in the JournalEntry table - is not available through the SDK, it will also be unavailable through QODBC.

Affected products: QODBC Desktop, QuickBooks Desktop (all editions)
Expected outcome: Understand that QODBC write/append operations are entirely constrained by what the QuickBooks SDK exposes. Unsupported SDK features cannot be implemented in QODBC.

Symptoms:

• Attempting to append or update a JournalEntry record via QODBC returns an error such as "feature not available" or the operation silently fails.
• QODBC can read JournalEntry data (via SELECT), but cannot create new records (INSERT) or modify existing ones via write operations.
• The customer expects to use QODBC's append feature (WriteList / SQL INSERT/UPDATE) on the JournalEntry table, but the operation is not supported.

Cause:

QODBC is a pass-through layer for the QuickBooks SDK. It does not have independent data access capabilities. Every read and write operation in QODBC maps directly to an equivalent QuickBooks SDK call. When Intuit has not implemented (or disabled) an SDK feature - such as writing JournalEntry records via WriteList - QODBC cannot provide that capability.

This is a QuickBooks Desktop / Intuit SDK limitation, not a QODBC bug or configuration issue. QODBC will support the feature when and if Intiot adds it to the SDK in a future release.

Environment:

Workaround:

1. Create JournalEntry records directly in QuickBooks Desktop. Navigate to Company → Make Journal Entries to manually enter debits and credits.
2. Use the QuickBooks Import wizard (if applicable). For bulk data entry, import from CSV using QuickBooks' built-in import feature for journal entries.
3. Request the feature from Intuit. If your business depends on programmatic JournalEntry writes through QODBC, submit a feature request to Intuit via their developer support page:
   Intuit SDK Feature Request
4. Consider alternative integration approaches. For complex data migration needs, explore QuickBooks Web Connect (.QBO files) or third-party tools that support JournalEntry imports.

Additional Notes:

• SDK dependency: QODBC's capabilities are entirely determined by the QuickBooks SDK. If a feature exists in the SDK, QODBC supports it; if it does not exist, QODBC cannot implement it.
• Read operations are broader than write operations: While some tables support read-only access (SELECT), they may not support write operations (INSERT/UPDATE/DELETE). This is per the SDK's exposure - not a QODBC design decision.
• Feature availability may change: Intuit periodically adds new features and fields to the SDK. Check the QODBC data layouts page for the latest field-by-field availability.

Related Articles:

Understanding QODBC Data Access via the Intuit SDK QuickBooks Closing Entries - Not Available via QODBC (SDK Limitation) QODBC Data Layouts Reference

Keywords:

Problem Description:

You are unable to update certain CustomFieldInvoiceLineOtherX fields on the InvoiceLine table via QODBC. The UPDATE statement fails silently or throws an error, while other custom fields (such as Other1 and Other2) work correctly.

Affected products: QODBC Desktop (builds after v357), QuickBooks Desktop
Expected outcome: You understand which InvoiceLine custom fields are writable via QODBC, the known bug number, and a workaround to restore full UPDATE capability.

Symptoms:

• An UPDATE InvoiceLine SET CustomFieldInvoiceLineOther3 = 'abc' statement fails or silently does nothing.
• CustomFieldInvoiceLineOther1 and CustomFieldInvoiceLineOther2 work fine, but Other3 through Other6 do not.
• The issue began after upgrading to a newer QODBC build - it worked in an older version (v357 or earlier).
• SELECT queries on these fields may still return data, but UPDATE does not write to them.

Cause:

A bug introduced in a QODBC Desktop release prevents UPDATE operations on the CustomFieldInvoiceLineOther3 through Other6 fields of the InvoiceLine table. These fields are no longer writable, while Other1 and Other2 remain supported.

Resolution:

1. Workaround: Downgrade to QODBC v357 (build 357). This version still supports UPDATE on CustomFieldInvoiceLineOther3–Other6.
1. Download QODBC v357: https://qodbc.com/download/builds/357/qodbc.exe
2. Close all applications that use QODBC or QuickBooks.
3. Uninstall the current (broken) version of QODBC.
4. Install the downloaded v357 build.
5. Reboot Windows.
6. Activate your CDKEY from the QODBC Setup Screen (About tab).
7. Test your UPDATE statement again. The custom field updates should work as before.
2. Alternative: Use Other1 or Other2 instead. If downgrading is not feasible, consider mapping your data to CustomFieldInvoiceLineOther1 or CustomFieldInvoiceLineOther2, which continue to work on all builds.

Additional Notes:

• This is a known regression from QODBC build v357 and later. If you are relying on UPDATE of CustomFieldInvoiceLineOther3–Other6, stay on v357 or earlier until a fixed version is released.
• Select queries are not affected. You can still read CustomFieldInvoiceLineOther3–Other6 via SELECT; only UPDATE is broken.
• Your existing license carries over to v357 - there is no need to reactivate or purchase a new CDKEY.

Example Scenarios:

Scenario 1 - Mapping external data to InvoiceLine custom fields:

A company maps external ERP custom codes to QuickBooks InvoiceLine lines using CustomFieldInvoiceLineOther3. After upgrading QODBC, their integration stopped writing. The workaround was to downgrade to v357 and restore the UPDATE statements.

Related Articles:

QODBC: Known Limitations on QuickBooks Table Field Support How to Download and Install a Specific QODBC Build

Keywords:

Problem Description:

QODBC is distributed in multiple build versions. Installing the latest or a different build than what third-party client applications expect can cause compatibility issues - including connection failures, empty datasets, and application crashes. It is important to ensure that the QODBC build version matches the requirements of any external tools, scripts, or client applications connecting through it.

Affected products: QODBC Desktop (all builds), QRemote Server/Client, third-party ODBC clients
Expected outcome: QODBC is installed at a build version compatible with all dependent applications; connections work as expected.

Symptoms:

• After updating or reinstalling QODBC to the latest build, existing client applications can no longer connect to QuickBooks via QODBC.
• The QRemote Test Tool connects successfully, but external scripts return empty datasets or connection errors.
• QuickBooks launches unexpectedly when testing a QODBC connection (indicating a mismatch in how the driver communicates with QuickBooks).
• A third-party application that previously worked with QODBC starts failing after a QODBC update.

Cause:

QODBC is updated periodically with new builds. Each build may change internal APIs, SDK bindings, or connection behavior. Third-party client applications built against a specific QODBC build (e.g., build 351) may stop working when the driver is updated to a newer build that has different internal interfaces or communication protocols.

This is not a bug in either QODBC or the third-party application - it is a version compatibility issue. The client expects a specific driver behavior that only exists in the build it was tested against.

Environment:

Solution:

Identify the Required Build Version

1. Contact your third-party application vendor or IT team. Ask which QODBC build version their software was tested and certified against (e.g., "What QODBC build does this tool require?").
2. Check documentation. Some applications explicitly document the required QODBC version in their setup guide or release notes.

Install the Correct Build

1. Uninstall the current QODBC version. Go to Control Panel → Programs and Features → uninstall QODBC Driver for QuickBooks. Restart the machine.
2. Download the required build. Contact QODBC Support or check the builds archive at https://qodbc.com/download/builds/ for the correct installer.
3. Install and configure. Run the installer, then configure your DSN through the 32-Bit ODBC Administrator (Control Panel → Administrative Tools → Data Sources (ODBC) (32-bit)).
4. Test the connection. Use the QODBC Test Tool to verify connectivity before running your third-party application.

Workaround: Multiple Builds on Separate Machines

If you need different QODBC builds for different purposes, consider:

• VMs or separate machines: Install the latest QODBC on one machine for general use, and a specific older build on a dedicated VM for third-party applications that require it.
• QRemote routing: Use QRemote to route connections from your application to a QODBC instance with the correct build. This way, only the server-side QODBC needs the specific build version; the client application connects via QRemote.

Example Scenarios:

Scenario 1 - Latest update breaks existing client:

A customer had a third-party reporting tool built against QODBC build 351. After reinstalling the latest QODBC, their connection failed and returned empty datasets. The vendor confirmed the tool requires build 351. Reverting to that build restored functionality.

Scenario 2 - Multi-VM environment:

A company runs four QuickBooks instances on separate VMs. Three VMs work fine with the latest QODBC, but the fourth had a client tool that broke after updating. They resolved this by keeping build 351 on the affected VM and using the latest build on the others.

Additional Notes:

• Build numbers matter: QODBC version (e.g., v24) is not the same as the build number (e.g., 351). Both must match your application's requirements.
• Always backup before updating: Before installing a new QODBC build, document your current DSN configurations and CDKEY for reference. If you need to downgrade, you will have these details available.
• Contact support if unsure: If you cannot determine the required build version, contact QODBC Support with the name of your third-party application. They can help identify compatibility requirements.
• QODBC v24 vs v23: QODBC v24 introduced a subscription licensing model that is not compatible with v23 CDKeys. See the Forward and Backward Compatibility FAQ for details.

Related Articles:

QODBC Licensing: Forward and Backward Compatibility QODBC Subscription License Broken After QODBC 24 Upgrade How to Install and Activate QODBC

Keywords:

Problem Description

The RefNumber field in QODBC tables (e.g., SalesReceiptLine, SalesOrderLine, InvoiceLine) is a varchar type. Standard SQL aggregation functions like MAX(RefNumber) return the highest lexicographic (alphabetical) value, not the highest numeric value. For example, '9999' sorts higher than '40107' as a string.

Symptoms

• SELECT MAX(RefNumber) FROM SalesReceiptLine returns '9999' when the actual next sequential number should be 40107.
• Attempting to cast RefNumber directly with standard SQL CAST - e.g., SELECT MAX(CAST(RefNumber AS numeric)) FROM SalesReceiptLine - produces a QODBC syntax error: [SQL syntax error] Expected lexical element not found: = <identifier>
• The QODBC driver does not support standard SQL CAST for this purpose.

Cause

1. The RefNumber field is defined as a varchar, so MAX() performs string comparison rather than numeric comparison.
2. QODBC uses ODBC scalar functions (not standard SQL CAST) for type conversion in SELECT statements.

Environment

Resolution

Use the ODBC CONVERT scalar function to cast RefNumber to an integer before applying MAX():

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesReceiptLine;

This converts the varchar RefNumber to an integer type, allowing MAX() to return the correct highest numeric value.

SQL Examples

Get max RefNumber from SalesReceiptLine

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesReceiptLine;

Get max RefNumber from SalesOrderLine

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesOrderLine;

Use with WHERE clause for date filtering

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesReceiptLine WHERE TxnDate >= {d '2024-01-01'};

Additional Notes

• The ODBC {fn CONVERT(field, SQL_Integer)} function works with any varchar field that stores numeric values and needs integer comparison.
• If some RefNumber values contain non-numeric data (e.g., free-text references), convert those to a standard format first - otherwise the conversion may silently exclude or misinterpret those records.
• This approach works in both QODBC Desktop and QODBC Online drivers.

Related Articles

• QODBC - SQL Language Support (Syntax)

Support Tickets

• MQR-566-94291

RefNumber MAX CONVERT SQL_Integer varchar SalesReceiptLine type conversion

Problem Description:

Developers connecting to QuickBooks data through QODBC via the ODBC API sometimes need to programmatically determine which QuickBooks company file is currently open and connected. This is useful for logging, audit trails, or dynamic report generation where the active company must be identified.

Affected products: QODBC Desktop, QRemote Server/Client, any ODBC client application (Excel VBA, MS Access, C#, Python, etc.)
Expected outcome: Retrieve the current QuickBooks company file path from the active QODBC connection using the DFQ parameter in the connection string.

Solution: Using SQLDriverConnect (ODBC API)

When connecting to QODBC via the ODBC API function SQLDriverConnect(), the driver returns the active connection string through the OutConnectionString output parameter. This connection string contains several parameters, including DFQ, which holds the full path of the current QuickBooks company file.

Example: VBA / MS Access

Dim conn As ADODB.Connection
Dim connString As String
Dim dfqValue As String

Set conn = New ADODB.Connection
conn.ConnectionString = "DSN=QuickBooks Data;DRIVER=QODBC;"
conn.Open

' Extract the DFQ parameter from the connection string'
dfqValue = conn.Properties(86)  ' Property index 86 returns DFQ'
MsgBox "Connected to: " & dfqValue

conn.Close

Example: C# / .NET

// Using ODBC API via P/Invoke
StringBuilder outConnStr = new StringBuilder(256);
// After calling SQLDriverConnect, scan outConnStr for DFQ=
string connStr = outConnStr.ToString();
// Parse the DFQ parameter from the connection string

Example: Python (pyodbc)

import pyodbc

conn = pyodbc.connect("DSN=QuickBooks Data")
# conn.cursor().description or connection.properties
# Access the DFQ parameter from the connection info

conn.close()

Understanding the QODBC Connection String Parameters

A QODBC connection string includes several key-value pairs. The most relevant ones are:

Note: QRemote Environments

In a QRemote setup, the DFQ parameter reflects the company file on the QODBC Server machine (where QuickBooks is running), not the client machine. If your application connects via QRemote, the DFQ value returned by SQLDriverConnect will still be valid - it tells you which QuickBooks company the remote server is currently connected to.

Example Scenarios:

Scenario 1 - Multi-company VBA application:

A financial analyst built an MS Access front-end that connects to QODBC. Before generating reports, the code checks the DFQ parameter from the connection string to log which company file was queried. This provides an audit trail of which data source was used for each report.

Scenario 2 - Debugging QRemote connections:

A developer troubleshooting a multi-VM QRemote setup needed to confirm which QuickBooks company file each server was serving. By reading the DFQ parameter from each connection, they verified that all VMs were pointing to the correct company files.

Related Resources:

• SQLDriverConnect Function - Microsoft Docs
• Connection String with QODBC Parameters
• How to setup QODBC for Auto-login into QuickBooks

Keywords:

Problem Description:

QODBC users may encounter Error 80040154 when trying to connect to QuickBooks data via QODBC. The error message states: "Create QuickBooks COM Instance Error on Machine <hostname>, Errorcode = 80040154, Class not registered." This indicates that the Windows COM registration for QuickBooks is missing or invalid on the machine running QODBC.

Affected products: QODBC Desktop (all versions), QRemote Server/Client, QuickBooks Desktop (Enterprise, Pro, Premier)
Expected outcome: QuickBooks COM registration is restored and QODBC can successfully connect to the open company file.

Symptoms:

• "QuickBooks Open Connection Failed. Create QuickBooks COM Instance Error on Machine <hostname> Errorcode = 80040154, Class not registered"
• QODBC Test Tool fails to connect with a "Class not registered" or "Class not found" error.
• Errors only start appearing after installing/upgrading QuickBooks, another version of Office, or running a system cleanup tool that may have undone COM registrations.
• QuickBooks opens fine normally - the issue is specifically QODBC's ability to communicate with it via COM.

Cause:

1. QuickBooks COM registration was undone. Installing a previous version of QuickBooks, reinstalling Office, or running certain system cleanup tools can unregister the QuickBooks COM components that QODBC depends on to communicate with the QuickBooks application.
2. QRemote is launching in admin mode. If QRemote runs with elevated (Administrator) privileges while the calling application runs as a standard user, the two cannot communicate via COM - resulting in Error 80040154 or related connection failures.
3. QuickBooks is not installed on the same machine as QODBC. The QuickBooks COM object must be present on the machine where QODBC is installed. Remote-only access requires QRemote architecture (not direct DSN connections).

Environment:

Solution:

1. Ensure QRemote is NOT running in admin mode. This is a common cause. Right-click the QRemote tray icon → "Exit." Then launch QuickBooks normally (not as Administrator), followed by launching QODBC from a standard user session. Verify in Task Manager that QRemote.exe is running without elevated privileges.
2. Verify QuickBooks is installed and updated on the same machine. QODBC requires QuickBooks to be installed locally. The machine must have the matching or newer version of QuickBooks (e.g., QODBC for QuickBooks 2024 requires QB Desktop 2024+). Update QuickBooks to the latest release via Help → Update QuickBooks.
3. Restart the machine. After confirming QuickBooks is installed and updated, restart to allow COM registrations to take effect.
4. Re-register QuickBooks COM components (if the error persists). Open an elevated Command Prompt and run:

   regsvr32.exe "C:\Program Files (x86)\Common Files\Intuit\QuickBooks\QuickBooks.exe"

   If this does not work, reinstall the latest version of QuickBooks - it will re-register all COM components automatically.
5. Test the connection. Open the QODBC Support Wizard (http://qodbc.com/links/2981) or the QODBC Test Tool and try to connect. The connection should now succeed without Error 80040154.

Additional Notes:

• QODBC 64-Bit requires QuickBooks 2022 or above. For QuickBooks 2021 and older, you must use the QRemote 64-Bit driver or DSN instead of the native 64-Bit QODBC driver.
• QuickBooks must be open before querying. QODBC connects to whatever company file is currently open in QuickBooks. If QuickBooks is not running, the COM object will not be available.
• Avoid running QRemote and applications as different privilege levels. Both QRemote and any consuming application (Excel, Access, custom scripts) must run under the same user context - either both as Administrator or both as standard users. Mixing privilege levels causes COM communication to fail.

Related Articles:

QODBC QRemote Multi-Company File Setup

Keywords:

Problem Description:

Applications connecting to QODBC via a System DSN may report an "architecture mismatch" error — the application and the ODBC driver must be the same bitness (both 32-bit or both 64-bit). A 32-bit application cannot connect to a 64-bit DSN, and a 64-bit application cannot connect to a 32-bit DSN.

Affected products: QODBC Desktop (any version), any ODBC-aware application (Excel, MS Access, C#, Python, SQL Server, etc.)
Expected outcome: Align the bitness of your application with the correct ODBC Administrator tool to create or select a matching DSN.

Symptoms:

• Error message: "The specified DSN contains an architecture mismatch between driver and application."
• Connection succeeds in the QODBC Test Tool but fails in the customer's application.
• The QODBC About screen shows "read-only, License error" even though the license is active.
• Switching between a newly installed 64-bit driver and an older 32-bit DSN (or vice versa) triggers the mismatch.

Cause:

1. ODBC Administrator tools are bitness-specific. Windows provides two separate ODBC Administrators:
   • 32-bit (SysWOW64): C:\Windows\SysWOW64\odbcad32.exe — manages 32-bit System DSNs.
   • 64-bit (System32): C:\Windows\System32\odbcad32.exe — manages 64-bit System DSNs.
2. A DSN created in one tool is invisible to the other. If your application is 32-bit but you opened the 64-bit ODBC Administrator (or vice versa), the DSN shown may not match your application's architecture.
3. Upgrading QODBC can reset or change DSN bitness. Installing a newer QODBC version (e.g., v24) may install both 32-bit and 64-bit drivers, but existing DSNs may remain on the old bitness until rebuilt.

Environment:

Resolution:

1. Determine your application's bitness. Check whether the application connecting to QODBC is 32-bit or 64-bit. For Excel, open Task Manager — if excel.exe runs under Processes (not Details) as "Microsoft Excel" it may be either; check the installation path or use HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\... registry keys to confirm. For MS Access, check the Office installation bitness.
2. Open the matching ODBC Administrator.
   • For 32-bit applications: Run C:\Windows\SysWOW64\odbcad32.exe (this is mandatory — the Start Menu shortcut may open the wrong bitness).
   • For 64-bit applications: Run C:\Windows\System32\odbcad32.exe.
3. Verify or create the correct DSN. In the matched ODBC Administrator, go to the System DSN tab and confirm that a QODBC DSN exists matching your application's bitness. If it does not exist, click Add and select the appropriate QODBC driver.
4. Test the connection. Use the Test Connection button in the DSN configuration dialog or run your application to verify the mismatch is resolved.
5. Rebuild the optimizer (if applicable). After installing a new QODBC version, the default DSN may be reset. Rebuild the optimizer file:
   1. Delete existing optimizer files per How to remove the Optimizer file.
   2. Rebuild the optimizer per How to rebuild the optimizer file.

Example Scenarios:

Scenario 1 — Upgrading QODBC breaks an existing application connection:

A customer upgraded from QODBC v23 to v24. Their 32-bit MS Access application suddenly reported a DSN architecture mismatch because the new installer created a 64-bit default DSN. Opening C:\Windows\SysWOW64\odbcad32.exe, reconfiguring the existing DSN to use the 32-bit QODBC driver, and restarting Access resolved the issue.

Scenario 2 — QODBC Test Tool works but Excel fails:

A customer confirmed connectivity via the QODBC Test Tool but Excel 64-bit reported a mismatch. The DSN was created on the 32-bit ODBC Administrator. The customer opened the 64-bit ODBC Administrator (System32\odbcad32.exe), verified the 64-bit DSN, and Excel connected successfully.

Additional Notes:

• Always use the correct ODBC Administrator for your application's bitness. This is the most common cause of architecture mismatch errors.
• After a QODBC version upgrade: The installer may reset default DSNs. Always verify bitness alignment after upgrading, and rebuild the optimizer file per the steps above.
• QuickBooks Desktop is 32-bit only. If you are connecting to QuickBooks Desktop directly (not via QRemote), your QuickBooks data source DSN should typically be 32-bit (SysWOW64\odbcad32.exe).
• For additional information, see the QODBC Architecture Mismatch troubleshooting article.

Related Articles:

Understanding 32-Bit vs 64-Bit ODBC Drivers on Windows QODBC Optimizer Rebuilding and Troubleshooting QODBC 64-Bit Driver and Excel Read/Write Connectivity Issues

Keywords:

Problem Description:

The Customer table in QODBC exposes only a single Note column per customer record - typically the first or pinned note. Customers often need to retrieve all notes stored on a QuickBooks customer record (which can number into the dozens), but querying Customer.Note returns at most one line.

Affected products: QODBC Desktop, QuickBooks Desktop
Expected outcome: By querying the CustomerAddtionalNote table instead of Customer, all notes for a customer are returned as separate rows via the AdditionalNotesRetNote field.

Symptoms:

• Querying SELECT Note FROM Customer WHERE … returns only one note, even when the customer has many notes in QuickBooks.
• The note returned by Customer.Note appears to be the first or pinned note - not all notes are accessible through this column.
• A customer record shows dozens of notes in the QuickBooks UI, but an ODBC query returns zero or one row from CustomerAddtionalNote.

Cause:

QuickBooks Desktop stores a single short note on the main customer record (exposed by QODBC as Customer.Note). Additional notes are stored separately in the QuickBooks SDK's internal note collection. QODBC exposes this secondary storage through the virtual table CustomerAddtionalNote (note: QODBC uses the original spelling from the SDK, not "Additional").

The key detail that causes most queries to return zero rows: CustomerAddtionalNote does not use the customer's full name. Instead, it requires FirstName or LastName as the filter - because QuickBooks SDK stores names internally in the format "LASTNAME, FIRSTNAME", and queries using Name = 'LASTNAME, FIRSTNAME' or other variations may not match.

Environment:

Solution:

1. Query the CustomerAddtionalNote table, not Customer.Note, to retrieve all notes.

-- Retrieve all additional notes for a customer by last name
SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE LastName = 'DELALUZ'

-- Retrieve all additional notes for a customer by first name
SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE FirstName = 'MICHAEL'

-- Retrieve both the note and the raw additional note
SELECT Notes, AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE LastName = 'DELALUZ'

2. If you only need the count of notes, use:

SELECT COUNT(*) FROM CustomerAddtionalNote WHERE LastName = 'DELALUZ'

3. To discover all available columns, run:

sp_columns CustomerAddtionalNote

Using the UNOPTIMIZED Hint

In some cases, adding the UNOPTIMIZED hint ensures all rows are returned without being filtered by the optimizer:

SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote UNOPTIMIZED WHERE LastName = 'DELALUZ'

Discovering Available Columns

To list all available columns on CustomerAddtionalNote, use the QODBC Test Tool or any ODBC client:

SP_Columns CustomerAddtionalNote

You can also consult the online table documentation at:
https://doc.qodbc.com/qodbc/usa/TableDetails.php?tableName=CustomerAddtionalNote

SQL Examples:

Example - Retrieving and displaying all customer notes in a report:

-- Get ListID first from Customer table
SELECT ListID, FullName FROM Customer WHERE LastName = 'DELALUZ'

-- Then fetch all notes using the ListID
SELECT Notes, AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE ListID = '164900-1234567890'

Example - Filtering notes by date range (via the Notes timestamp):

-- Notes field typically contains a timestamp prefix like "7/24/2023 9:08:50 AM: note text"
SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote
WHERE LastName = 'DELALUZ'
  AND Notes LIKE '%2024%'

Example Scenarios:

Scenario 1 - Importing all customer notes into an external system:

A company needs to migrate customer interaction history from QuickBooks into a CRM or ticketing system. Instead of only getting the single Customer.Note field, they query CustomerAddtionalNote by LastName to retrieve every note row as a separate record, preserving timestamps embedded in the Notes column.

Scenario 2 - Debugging zero-row results:

A developer queries CustomerAddtionalNote WHERE Name = 'MICHAEL DELALUZ' and gets zero rows. The QuickBooks SDK stores the name internally as "DELALUZ, MICHAEL", so the Name column does not match. The fix is to use WHERE LastName = 'DELALUZ' or WHERE FirstName = 'MICHAEL' instead.

Additional Notes:

• CustomerAddtionalNote is a virtual/projection table. It does not exist in QuickBooks or any SQL database. QODBC creates it dynamically to expose the SDK's internal note collection for customer records.
• The Name column may not work as expected. Because QuickBooks stores names internally as "LASTNAME, FIRSTNAME" (e.g., "DELALUZ, MICHAEL"), queries using Name = 'MICHAEL DELALUZ' will return zero rows. Use FirstName or LastName instead.
• The Notes column often contains a timestamp prefix (e.g., "7/24/2023 9:08:50 AM: note text"). Parse the timestamp from the left portion of this field if you need to sort or filter by date.
• Pin/star notes in QuickBooks affect which note appears on Customer.Note. Pinned or highlighted notes in the QuickBooks UI change which single note is returned by Customer.Note - but this has no effect on CustomerAddtionalNote, which returns all rows regardless of pin status.
• The UNOPTIMIZED hint can help in some cases. Adding UNOPTIMIZED to the query bypasses QODBC's optimizer filtering, ensuring all matching rows are returned.

Related Articles:

QODBC Table Reference - Customer Table Columns and Required Fields How to Use sp_columns to Discover QODBC Table Schema

Keywords:

Problem Description:

When using sp_report stored procedures in QODBC (e.g., ProfitAndLossBudgetOverview, SalesByItemSummary), you may need to filter the report output by a specific class or group of classes. Some users ask whether they can use SQL's LIKE '%Restricted%' syntax or other wildcard patterns in sp_report parameters to match partial class names.

Affected products: QODBC Desktop, QuickBooks Desktop
Expected outcome: You can filter sp_report results by exact class full names using the ClassFilterFullNames parameter. Wildcard filtering is not supported - you must specify the full class name(s) explicitly.

Symptoms:

• You know the class names contain certain keywords (e.g., "Restricted"), but you don't know the exact full names stored in QuickBooks.
• You tried using wildcard filtering (e.g., ClassFilterFullNames = '%Restricted%') and it did not work.
• You are unsure whether QODBC or QuickBooks supports partial-class matching in sp_report queries.

Solution:

Step 1: Discover the Exact Class Full Names

First, query the Class table to find the exact full names of the classes you want. The Class table does support wildcard filtering:

SELECT ListID, FullName FROM Class WHERE FullName LIKE '%Restricted%'

This returns the exact FullName values (e.g., "Restricted Grants", "Restricted Donations") that you will use in your sp_report query.

Step 2: Use ClassFilterFullNames in Your sp_report Query

Pass the exact full names from Step 1 to the ClassFilterFullNames parameter. Multiple classes are comma-separated:

EXEC sp_report ProfitAndLossBudgetOverview
  SHOW Amount_Title, Text, Label, Amount
  PARAMETERS
    FiscalYear = 2026,
    BudgetCriterion = 'AccountsAndClasses',
    SummarizeBudgetRowsBy = 'Account',
    SummarizeBudgetColumnsBy = 'Date',
    DateFrom = {d '2026-02-01'},
    DateTo = {d '2027-01-31'},
    ClassFilterFullNames = 'Restricted Grants', 'Restricted Donations'

SDK Limitations:

• No wildcard filtering in sp_report parameters. The QuickBooks SDK does not support wildcard patterns (e.g., LIKE, %) in sp_report parameter values. You must provide exact class full names.
• Report layout is controlled by the SDK, not QODBC. When using BudgetCriterion = 'AccountsAndClasses', QuickBooks may display Classes as columns instead of dates regardless of what you set for SummarizeBudgetColumnsBy. This behavior is determined by the QuickBooks SDK and cannot be overridden through QODBC.
• QODBC's role: QODBC sends your query parameters to the QuickBooks SDK, which processes the report. QODBC formats the result set but does not control how the SDK structures the output columns or rows.

Workaround - Pivot in Your Application:

If you need dates as columns and the SDK returns classes instead, consider filtering the report data in your application layer (Excel Power Query, Python, etc.) after retrieving it from QODBC. You can pivot or reshape the results programmatically.

Related Articles:

How sp_report Parameters Work in QODBC QuickBooks SDK Limitations on Wildcard Filtering Understanding Report Layout Control in QuickBooks

Keywords:

Problem Description:

When inserting a Bill record via QODBC (using OPENQUERY from MS SQL Server or ODBC), the DueDate field is ignored and QuickBooks automatically sets the due date equal to the invoice/TxnDate.

Affected products: QODBC Desktop, QRemote Server, MS SQL Server, QuickBooks Desktop
Expected outcome: The DueDate specified in the INSERT query is correctly applied in QuickBooks.

Symptoms:

• A Bill is created via QODBC OPENQUERY INSERT with an explicit DueDate value.
• The Bill appears in QuickBooks, but the due date equals the TxnDate (invoice date) regardless of the DueDate value sent.

Cause:

1. TermsRefListID set to "Due on receipt." When the TermsRefListID field in the Bill INSERT query references a payment term of "Due on receipt," QuickBooks overrides any manually specified due date and automatically sets it equal to the TxnDate.
2. Conflict between manual date and terms. If you explicitly provide a DueDate, there is no need to include TermsRefListID — QODBC will apply the explicit date. Including both with conflicting values causes QuickBooks to honour the terms ("Due on receipt") over the explicit date.

Environment:

Resolution:

1. Remove TermsRefListID from the INSERT query. If you are explicitly providing a DueDate value, do not include the TermsRefListID field in your Bill INSERT statement. QODBC will use your explicit date instead of trying to apply payment terms.
2. Verify the updated query works. The DueDate should now be correctly applied in QuickBooks.

Example — Without TermsRefListID (fixed):

-- Bill header INSERT without TermsRefListID
INSERT OPENQUERY ([DSN_NAME], 'SELECT "APAccountRefListID", "Memo", "DueDate", "TxnDate", "RefNumber", "VendorRefListID" FROM "Bill" where "TxnID" = ''0'' ')
VALUES ('60001-XXXX', 'Memo text', {d'2025-02-18'}, {d'2025-02-17'}, 'REF001', '80000489-XXXX')

-- Expense line items can still reference the vendor separately
INSERT OPENQUERY ([DSN_NAME], 'SELECT "ExpenseLineMemo", "ExpenseLineAccountRefListID", "ExpenseLineAmount", "FQSaveToCache" FROM "BillExpenseLine" where "VendorRefListID" = ''80000489-XXXX'' ')
VALUES ('Memo', 'AccountID', 150.00, 1)

-- Note: Bill header line no longer includes TermsRefListID or DueDate will be honored from the explicit value above.

Example Scenarios:

Scenario 1 — Vendor with Net-30 terms, but customer wants custom due date:

A company normally pays a vendor on Net-30 terms. However, for a particular batch of bills, they want to set custom due dates. The original query included TermsRefListID (Net-30) plus explicit DueDate. Removing TermsRefListID allows the explicit DueDate to be applied correctly.

Scenario 2 — Vendor with "Due on receipt" terms:

If a vendor's default term in QuickBooks is "Due on receipt," including TermsRefListID will always force the due date to match TxnDate. Removing it lets QODBC use the explicit DueDate value instead.

Additional Notes:

• When to keep TermsRefListID: If you want QuickBooks to automatically calculate the due date based on the vendor's payment terms, include TermsRefListID and omit DueDate. QODBC will apply the term-based calculation.
• BillExpenseLine inserts are separate from the Bill header. Expense line items are inserted via a separate OPENQUERY INSERT into BillExpenseLine before the Bill header. They do not affect due date logic.

Related Articles:

QODBC: Inserting Bills via MS SQL Server and QRemote QODBC: Understanding Payment Terms in QuickBooks Desktop

Keywords:

Problem Description:

You receive a "Beta Expired", "Trial Expired", or similar license error when attempting to connect from QODBC to QuickBooks. The issue typically occurs after the QODBC beta/trial build reaches its expiration date, even though the customer may have previously been using the driver successfully.

Affected products: QODBC Desktop (beta/trial builds), QuickBooks Desktop
Expected outcome: The latest full version of QODBC is installed and activated, and connections to QuickBooks work normally again.

Symptoms:

• A "Beta Expired" or "Trial Expired" error appears when connecting to QuickBooks through QODBC.
• The connection worked previously but stopped after a date passes.
• The QODBC version is an older beta or evaluation build that has since expired.

Cause:

Beta or trial builds of QODBC include an expiration date. Once that date passes, the driver stops accepting connections and displays a "Beta Expired" or "Trial Expired" error - even if the CDKEY is valid.

This is not a licensing issue; it is an intentional build-level timeout on evaluation versions. The fix is to install the current full (released) version of QODBC.

Resolution:

1. Download the latest full version of QODBC. Go to the official QODBC download page: https://qodbc.com/download/
2. Run the installer and bypass the CDKEY prompt during installation. When prompted for a CDKey, skip/pause/bypass that step - do NOT enter your CDKEY yet. Complete the installation as-is.
3. Reboot the machine.
4. After reboot, activate from the QODBC Setup Screen. Open QODBC Setup Screen (Start → All Programs → QODBC Driver for QuickBooks → Configure 32-Bit QODBC Driver) → About tab → enter your CDKEY to activate.

Additional Notes:

• Bypassing CDKEY during install prevents conflicts. Entering a valid CDKEY during the installer will overwrite the trial with the expired beta build still attached. Skipping it and activating from the Setup Screen after reboot ensures the full version activates cleanly.
• If you renewed your license but are still seeing this error, it likely means you are still running an old beta/trial build rather than the full released version. Installing the latest build resolves this even if the CDKEY was renewed.
• Your CDKEY carries forward. If your existing QODBC Desktop subscription is active, the same CDKEY works for the full version - there is no need to purchase a new license just because the beta expired.
• Using sample company files during evaluation: The QODBC 30-day trial works with QuickBooks sample company files (shipped with QuickBooks by Intuit). If your trial expires but you need more time to evaluate, switching to a different sample company file will reset the trial period. Mark Kuznar confirmed this as a workaround for customers who want extended evaluation without purchasing.

Related Articles:

QODBC Machine Transfer and Deactivation Guide

Keywords:

Problem Description:

When using QODBC batch procedures (sp_batchstart, sp_batchclear, sp_batchupdate) in a .NET/C# application, executing any SELECT query before calling sp_batchstart causes the subsequent sp_batchupdate to throw a System.AccessViolationException:

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Affected products: QODBC Desktop, .NET applications using ODBC connection strings
Expected outcome: A SELECT query before batch procedures should not interfere with batch operations. Batch updates should work regardless of prior SELECT statements on the same connection.

Symptoms:

• A .NET/C# application using a QODBC ODBC connection executes a normal SELECT query.
• Subsequently calling sp_batchstart, inserting/updating records, and then sp_batchupdate throws System.AccessViolationException.
• Removing the preceding SELECT statement makes the batch operation work correctly.
• The issue only occurs when a SELECT result set is left open or not fully consumed before beginning a batch sequence on the same connection.

Cause:

This is a known bug in QODBC Desktop (tracked as B01770). The QODBC driver's ODBC cursor state is not fully reset after a SELECT result set, which corrupts the connection state before batch operations begin.

Environment:

Workaround:

Do not execute any SELECT queries on the same ODBC connection before batch operations. If your application needs data from a SELECT query before running batch commands, use a separate ODBC connection for the SELECT operation.

1. Create two ODBC connections: Use one connection exclusively for data reads (SELECT queries) and another for batch write operations.
2. Close any result sets fully before starting a batch sequence on the same connection.
3. Reopen the connection before running sp_batchstart if a SELECT was used on it previously.

Keywords:

Problem Description:

QODBC can connect to a QuickBooks company file without manually opening or logging into the application. This "auto-login" feature allows external applications (Excel, MS Access, SQL Server, etc.) to query and update data through QODBC even when no user is logged into QuickBooks.

Affected products: QODBC Desktop, QuickBooks Desktop, Reckon Accounts
Expected outcome: External applications can connect to QuickBooks/Reckon via QODBC without a user being actively logged in, provided auto-login is properly configured.

Symptoms:

• QODBC connections work normally when QuickBooks is open and a user is logged in.
• Connections fail when QuickBooks is closed or the user is logged out, even with auto-login enabled.
• The QODBC Test Tool returns an error saying it cannot find the company file or cannot connect to QuickBooks.

Cause:

Auto-login functionality relies on the QuickBooks SDK background service. In QuickBooks Desktop versions prior to 2023, auto-login works reliably: when QODBC attempts a connection, QuickBooks silently loads the company file in the background without user interaction.

Important change with Reckon 2023: Auto-login functionality no longer works in Reckon Accounts Enterprise 2023 and newer versions. This is a known issue reported to Reckon (CAS-295937-X0D2R5) and remains unresolved.

Environment:

Solution:

Step 1: Verify Company File Path Matches

The QuickBooks company file path configured in the QODBC DSN must match the path shown in QuickBooks (press F2 in QuickBooks to view it).

1. Open the QODBC 32-bit Setup Screen: Start → All Programs → QODBC Driver for QuickBooks → Configure QODBC Data Source.
2. Select System DSN, highlight "QuickBooks Data", and click Configure.
3. On the General tab, locate the company file path. Ensure it matches exactly what QuickBooks shows.

Step 2: Test Auto-Login

1. Log in to QuickBooks as the administrator and open your company file.
2. Open the QODBC 32-bit Test Tool and connect to your data source.
3. Run a simple query: SELECT * FROM Customer
4. Log off QuickBooks - but do not close the application.
5. Close the Test Tool and reopen it. Attempt to connect again.
6. If successful, QuickBooks loads silently in the background and the connection succeeds without user interaction.

Step 3: Terminal Services / Remote Desktop Workaround (Reckon 2023+)

If you are using Reckon 2023 or newer on a terminal server or remote desktop:

1. Keep the Reckon/QuickBooks application running (do not close it entirely).
2. Log in to your company file in Reckon/QuickBooks.
3. Ensure QODBC connections are made while Reckon is logged in and the company file is open.

Additional Notes:

• ⚠ OneDrive caveat: Auto-login may fail if the QuickBooks company file is stored in a OneDrive-synced folder. Customer confirmed: files must be on a local C: drive or network drive path, not accessed via OneDrive sync. If auto-login works from one location but fails from another (e.g., after switching to/from OneDrive), check the file's actual storage location.
• Multiple company files: When pulling data from multiple QB companies in one application, create a separate System DSN for each company file. Both users can refresh the same Excel workbook using those shared System DSNs.
• Terminal Services: On terminal servers/remote desktop, keep QuickBooks running (no need to log in) as an additional workaround if auto-login fails.

Related Articles:

How to Setup QODBC Optimizer Understanding QODBC DSN Configuration How to setup QODBC for Auto-login into QuickBooks How to set up the QODBC Driver to auto-connect to QuickBooks. (Video)

Keywords:

Problem Description:

When QODBC attempts to connect to a QuickBooks company file, the QuickBooks SDK displays an "Application Permission" pop-up dialog asking the user to authorize the connection. The input box where you are supposed to type "Yes" is grayed out and inactive - you cannot click into it or type anything.

Affected products: QODBC Desktop, QuickBooks Desktop (all editions)
Expected outcome: The Application Permission dialog allows entry of "Yes" to authorize the connection, and subsequent connections proceed without repeated prompts.

Symptoms:

• A pop-up dialog titled "Application Permission" appears when QODBC connects to QuickBooks.
• The dialog contains an input box asking you to type "Yes" to confirm the authorization, but it is grayed out and unresponsive.
• Clicking on the input box does nothing - no cursor appears, and no text can be entered.
• The connection attempt times out or fails because the SDK cannot proceed without confirmation.

Cause:

This is a QuickBooks SDK behavior, not a QODBC bug.

1. The input box on the Application Permission dialog requires you to first click the hyperlinked text titled "Learn more about the permissions of applications that integrate with QuickBooks."
2. Once that link is clicked, the SDK activates the input field, and it becomes responsive for typing "Yes".
3. If you do not click this link first, the input box remains disabled by design - this is a known QuickBooks SDK quirk.
4. After authorization, QuickBooks caches the permission for that application. You should not see this prompt again for the same QODBC driver and company file, unless you connect a new/different application to QuickBooks.

Environment:

Resolution:

1. When the Application Permission pop-up appears, look for the blue hyperlinked text within the dialog that says: "Learn more about the permissions of applications that integrate with QuickBooks."
2. Click that link first. This triggers the SDK to enable the input field.
3. Type "Yes" into the now-active input box and click OK or Confirm to authorize the connection.
4. The QODBC driver should now connect to the company file successfully, and you will not see this prompt again for subsequent connections from the same application.

Additional Notes:

• This prompt only appears once per application. If it reappears repeatedly after initial authorization, check whether a different QODBC driver instance or another third-party application is also trying to connect to QuickBooks - each new application triggers the permission prompt again.
• If you cleared browser cache or QuickBooks credentials, the SDK may forget cached permissions and re-prompt. Re-authorize as above.
• A known connection-failure scenario: Some customers have reported that after setting up permissions correctly, QODBC still fails to connect (connection spins indefinitely). This can be caused by a bug in certain QODBC builds that was resolved in later versions. If the permission dialog shows the prompt repeatedly and connections fail even after authorization, try installing the latest version of QODBC. A crash report from this scenario was resolved in QODBC build v359 (released 2025-06); the workaround was to install build v357 or later.

Example Scenarios:

Scenario 1 - First-time QODBC setup with QuickBooks Enterprise:

A new QODBC installation attempts to connect to a QuickBooks company file for the first time. The Application Permission dialog appears with an inactive "Yes" input box. The user follows the resolution steps above: click "Learn more," type "Yes," and the connection succeeds. Future connections work automatically.

Scenario 2 - Repeated prompt after permissions set:

A company that had previously authorized QODBC starts seeing the permission dialog again after an IT team member ran a different third-party application against QuickBooks. Each new application triggers a fresh prompt. The user authorized each one, and connections resumed normally.

Related Articles:

QODBC Machine Transfer and Deactivation Guide QODBC Licensing Information

Keywords:

Problem Description:

When connecting a front-end application (such as MS Excel, MS Access, or a custom VBA application) to QODBC via ODBC DSN, the bitness of the application must match the bitness of the DSN. Mismatching them - for example, using a 64-bit Excel workbook that references a 32-bit DSN - results in an 80004005 ODBC error when attempting to read records.

Additionally, some users run both 32-bit and 64-bit versions of the same application on different machines connecting to QuickBooks and wonder whether each version needs a separate integration entry or DSN.

Affected products: QODBC Desktop (both 32-bit and 64-bit drivers), QuickBooks Desktop, MS Excel (32-bit and 64-bit), any ODBC-compatible application
Expected outcome: Each application must use a DSN of the same bitness. You create separate 32-bit and 64-bit DSNs with the same name (e.g., "QuickBooks Data") for their respective architectures.

Root Cause:

ODBC drivers are architecture-specific. A 64-bit application can only access 64-bit ODBC drivers, and a 32-bit application can only access 32-bit ODBC drivers. There is no cross-bitness bridging possible - the operating system prevents a 64-bit process from loading a 32-bit DLL and vice versa.

DSN Configuration for Mixed Environments:

In a mixed 32/64-bit environment, you create separate DSNs for each architecture. They can share the same name but are registered in different system registries.

Creating a 32-bit DSN:

1. Open QODBC Setup (32-bit): Start → All Programs → QODBC Driver for QuickBooks → Configure QODBC Data Source (32-bit).
2. Go to the System DSN tab.
3. Click "Add" and select "QODBC Driver for QuickBooks."
4. Name the DSN (e.g., "QuickBooks Data") and configure it as needed.
5. Test the connection in the QODBC Test Tool (32-bit).

Creating a 64-bit DSN:

1. Open QODBC Setup (64-bit): Start → All Programs → QODBC Driver for QuickBooks → Configure 64-bit QODBC Driver.
2. Go to the System DSN tab.
3. Click "Add" and select "QODBC Driver for QuickBooks."
4. Name the DSN with the same name as the 32-bit version (e.g., "QuickBooks Data"). Both names can coexist.
5. Test the connection in the QODBC Test Tool (64-bit).

Verifying Which DSN Your Application Uses:

Your application automatically picks up the DSN based on its own bitness - no additional configuration is needed. A 64-bit Excel will use the 64-bit "QuickBooks Data" DSN, and a 32-bit Excel will use the 32-bit "QuickBooks Data" DSN.

To verify which version of ODBC your application sees:

• 32-bit ODBC Manager: Navigate to C:\Windows\SysWOW64\odbcad32.exe
• 64-bit ODBC Manager: Navigate to C:\Windows\System32\odbcad32.exe

QuickBooks Integration Links - One Per Application, Not Per Bitness:

QuickBooks manages integration links per application name, not per bitness. You will see a single entry for "Excel" in the QuickBooks Integrated Applications list regardless of how many bitness versions are installed or connecting.

• A single Excel entry is expected - whether you have 32-bit, 64-bit, or both versions installed on the same machine.
• QuickBooks automatically manages this list. Do not manually delete integration entries - let QuickBooks update them as applications connect and disconnect.

Troubleshooting Error 80004005:

If you receive an 80004005 ODBC error when reading data through QODBC from a front-end application, the most common cause is a bitness mismatch:

1. Determine your application's bitness. In Excel: File → Account → About Excel (or check the installer used). A 64-bit Excel will show "Microsoft Excel 20xx (64-bit)" in the title bar or about dialog.
2. Verify the DSN matches. If your application is 64-bit, ensure a 64-bit QODBC DSN exists. Use the 64-bit ODBC Manager (C:\Windows\System32\odbcad32.exe) to check.
3. Create a matching DSN if needed. Follow the steps above for your application's bitness.
4. Re-test your connection.

Related Articles:

How to Activate QODBC How to Reconnect QODBC Online with QuickBooks Online Edition QODBC Licensing Information How to Activate QODBC 64-Bit on a Machine That Already Has 32-Bit Installed QODBC Licensing Forward and Backward Compatibility

Keywords:

Problem Description:

I have installed QODBC Online build 358.

I can connect to the QuickBooks Online company file using QODBC Online 32-bit DSN.

I have created a new QODBC Online DSN 64-Bit.

When I am trying to connect to the QuickBooks Online company file using QODBC Online 64-Bit DSN, on the browser, it says connected, but the DSN still shows "Connect to QuickBooks," and it does not connect.

Sometimes the DSN window disappears.

Solution:

Updates (2025-05-30):

We are currently working on fixing the issue, and we will soon release a new build.

The issue is limited to when using the new QODBC Online 64-bit DSN.

If you are using a single QuickBooks Online company file, you can use "QuickBooks Data Online" from a 32-bit system.

Once you connect to your QuickBooks Online company file using a 32-bit "QuickBooks Online Data" DSN, this will automatically update your "QuickBooks Online Data" 64-bit.

You should be able to use "QuickBooks Online Data" 64-bit.

If you wish to create your own QODBC Online DNS, you can use one of the following workarounds.

Workaround 1:

Use QODBC Online 32-bit DSN, connect to the QuickBooks Online company file.

Once connected, export the connection key from the 32-bit.

Problem Description

I am using Microsoft Excel.

I am using the following SQL statement.

SELECT TransferInventoryLine.TxnDate,
TransferInventoryLine.RefNumber,
TransferInventoryLine.FromInventorySiteFullName,
TransferInventoryLine.ToInventorySiteFullName,
InventorySite.SiteAddressAddr1,
InventorySite.SiteAddressAddr2,
InventorySite.SiteAddressAddr3,
InventorySite.SiteAddressAddr4,
InventorySite.SiteAddressCity,
InventorySite.SiteAddressState,
InventorySite.SiteAddressPostalCode,
InventorySite.SiteAddressCountry,
TransferInventoryLine.Memo,
TransferInventoryLine.TransferInventoryLineQuantityTransferred,
TransferInventoryLine.TransferInventoryLineItemRefFullName,
Item.Description
FROM (TransferInventoryLine unoptimized
INNER JOIN InventorySite unoptimized
ON TransferInventoryLine.ToInventorySiteListID = InventorySite.listen)
INNER JOIN Item
ON TransferInventoryLine.TransferInventoryLineItemRefListID = Item.ListID
WHERE (TransferInventoryLine.TxnDate={d'2020-09-15'})

Solutions

We can reproduce the issue. The issue can be produced in QODBC build 336 and above.

We are working on fixing the issue.

As a workaround, please change the SQL statement.

SELECT TransferInventoryLine.TxnDate,
TransferInventoryLine.RefNumber,
TransferInventoryLine.FromInventorySiteFullName,
TransferInventoryLine.ToInventorySiteFullName,
InventorySite.SiteAddressAddr1,
InventorySite.SiteAddressAddr2,
InventorySite.SiteAddressAddr3,
InventorySite.SiteAddressAddr4,
InventorySite.SiteAddressCity,
InventorySite.SiteAddressState,
InventorySite.SiteAddressPostalCode,
InventorySite.SiteAddressCountry,
TransferInventoryLine.Memo,
TransferInventoryLine.TransferInventoryLineQuantityTransferred,
TransferInventoryLine.TransferInventoryLineItemRefFullName,
Item.Description
FROM TransferInventoryLine, InventorySite, Item
where TransferInventoryLine.ToInventorySiteListID = InventorySite.ListID
and TransferInventoryLine.TransferInventoryLineItemRefListID = Item.ListID
and TransferInventoryLine.TxnDate={d'2020-09-14'}

Click here to raise a support ticket and request if you still face the issue.

Problem Description

I have purchased the QODBC Server edition license 2021.

I have activated the QODBC 2021 Server edition license on the machine on which I have my service-based application (SQL Server).

When creating a linked server, I get the following error message.

"QODBC Server Edition License Required. Please visit qodbc.com/links/needserverlicense."

Solutions

We can reproduce the issue.

The issue is limited to the QODBC 2021 Server edition license.

The reported issue is fixed in QODBC v21.0.0.340. It is recommended to use the latest version of QODBC. To know about the latest version of QODBC, please visit http://qodbc.com/download.htm

Please download the latest version of QODBC. http://qodbc.com/download.htm

If you are still facing the issue, please raise a support ticket.

Click here to raise a support ticket.

Problem Description:

Unable to use ARAgingSummary and APAgingSummary

Error - Column not found: Amount_Title, Column not found: Amount_1, Column not found: Amount

Solutions:

QODBC Online can access data from QuickBooks Online using the QuickBooks Online API.

QuickBooks Online API has made some changes to the ARAgingSummary and APAgingSummary report output. 

We have reported this issue to Intuit. Intuit may fix the problem, and your issue in the QODBC Online report should get resolved automatically.

If you are on a time-critical project or urgently need the ARAgingSummary and APAgingSummary report output, please download the latest version of QODBC Online.

Install QODBC Online version 20.0.0.337 or later

Close all the connections to QODBC Online.

Uninstall the current version of QODBC Online. Do not deactivate the QODBC Online license before uninstalling QODBC Online.

Download the latest version of QODBC Online. Click here to download 

Install the QODBC Driver for QuickBooks Online.

Skip the CDKEY step during the installation.

Restart Windows (if prompted).

After installing the latest version of QODBC Online, please test the ARAgingSummary and APAgingSummary reports using QODBC Online.

If you are still facing the issue, please raise a support ticket to the QODBC Technical Support Department from the link below & provide the requested information:

Submit a Ticket

Please attach the following files:

1) Screenshot of QODBC Setup Screen -- > About

2) Screenshot of the issue you're facing.

Share the entire log files as an attachment in text format from

3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages

4) QODBC Setup Screen -- > Messages -- > Review SDK Messages

Refer to: How to take a screenshot: How to take a screenshot

Tags: QuickBooks Online, QBO, Aging Report, Columns Not Found, Column missing, Amount_1 not found, QODBC Online, QO

Problem Description:

Unable to use QODBC Online on Windows 7

Error - session failed. 653 - There was an error while communicating with the Ids Server. - - The Response Stream was null or empty

Solutions: 

You must enable TLS 1.1 and TLS 1.2 on the Windows 7 OS.

To allow QODBC Online to communicate securely with the Intuit QuickBooks Online server using TLS 1.1 and TLS 1.2, you have to do the following:

Please enable the internet connection on your machine. Please follow the steps below to enable TLS 1.2 on your system.

Follow the steps below to launch 'Services' from the start menu:

Click the Windows Start button from the desktop and search for the keyword 'Services.'

Click on 'Services' to launch the 'Services' application.

Start the two services below:

1) Background Intelligent Transfer System (Right-click and go to "Properties" and select start type as "Automatic" and click on the "Start" button)

2) Windows Update (Right-click and go to "Properties" and select start type as "Automatic" and click on the "Start" button)

Create a folder named TLS12 in your C: drive.

Download the two files below and place them in the TLS12 folder.

install-kb.ps1

tls-reg-edit.ps1

To download the compressed file - click here

Run PowerShell (without x86) from the Start menu as "Run as administrator."

In the PowerShell command prompt, navigate to the TLS12 folder and execute the commands below one by one.

PS C:\Windows\system32> cd\

PS C:\> cd .\TLS12

PS C:\TLS12> Set-ExecutionPolicy Bypass -Scope Process ; .\install-kb.ps1

Type "Yes" and press the "Enter" key.

It will update the system to KB3140245.

Restart your machine.

Run PowerShell (without x86) from the Start menu as "Run as administrator."

In the PowerShell command prompt, navigate to the TLS12 folder and execute the commands below one by one.

PS C:\Windows\system32> cd\

PS C:\> cd .\TLS12

PS C:\TLS12> Set-ExecutionPolicy Bypass -Scope Process ; .\tls-reg-edit.ps1

Type "Yes" and press the "Enter" key.

Restart your machine.

After restarting the machine, launch the QODBC Online Test Tool from Start Menu>> QODBC Driver for use with QuickBooks Online>> QODBC Online Test Tool.

Connect using the DSN' QuickBooks Online Data'.

You should be able to connect.

Source of PS  -  How+to+Configure+Microsoft+Windows+7+to+use+TLS+Version+1.2

If you are still facing the issue, install the Windows update KB3140245, either through Windows Update, where it is available as an Optional Update, or download it from the Microsoft Update Catalog (www.catalog.update.microsoft.com).

Download the file MicrosoftEasyFix51044.msi from the following page and install it on your computer:

Update-to-enable-tls-1-1-and-tls-1-2-as-a-default-secure-protocols-in

The file is available for download in the section labeled Easy To Fix on the above-mentioned page. Suppose the easy fix option is not suitable for you, and you prefer to manually edit your computer's registry. In that case, the article also provides that information in the section "How the DefaultSecureProtocols registry entry works."

If you are still facing the issue, please raise a support ticket to the QODBC Technical Support Department from the link below & provide the requested information:

Submit a Ticket

Please attach the following files:

1) Screenshot of QODBC Setup Screen -- > About

2) Screenshot of the issue you're facing.

Share the entire log files as an attachment in text format from

3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages

4) QODBC Setup Screen -- > Messages -- > Review SDK Messages

Refer to: How to take a screenshot: How to take a screenshot

Tags: QuickBooks Online, QBO, OAuth, Windows 7, TLS, Unable to connect, QODBC Online, QO

Why is the QODBC Online upgrade required?

QODBC Online version 19.0.0.333 and lower versions use an old security system that is being discontinued in QuickBooks Online on December 17th, 2019.

What is the Migration process?

Step 1: Download the latest version of QODBC Online - Click here

Step 2: Close all applications

Step 3: Install the latest QODBC Online

Step 4: Skip the CDKEY step during the installation

Step 5: Connect to your QuickBooks Online company file - Click Here.

What is the last date for upgrading QODBC Online?

We recommend upgrading QODBC Online to the latest version before December 15^th, 2019.

What will happen if I continue using the older version of QODBC Online after the end date?

QODBC Online cannot connect to your QuickBooks Online company file.

Can I export the connection key from the older version of QODBC Online and import it into the latest version of QODBC Online?

No, the token is in a different format. We would suggest re-authorizing QODBC Online with your QuickBooks Online company file.

Tags: QuickBooks Online, QBO, OAuth

Problem Description 1:

I have the latest driver version and no errors during installation. When I tried to connect, it just stuck on "connecting."

Problem Description 2:

We installed the trial QODBC for the workstation. We cannot connect to our company file using 'test connection.' QODBC and QuickBooks hang, but can be closed with the task manager.

Problem Description 3:

The QODBC driver passes the Test Connection to QuickBooks after the initial install.

Shortly after, the initial install stops working.
The Test Connection to QuickBooks button hangs.
The screen says Not Responding.
He hangs until the task is killed.

Solution:

Please follow the steps below.

1) Remove Optimizer file(s)

Click the Windows Start button >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source. Go to the "System DSN" Tab, select "QuickBooks Data" DSN & click "Configure". Switch to the Optimizer tab & Click Reset Optimizer File(s)

You may also refer How to Remove Optimizer file.


2) Please verify that the Optimizer File(s) are deleted.

You can find the optimizer folder path from QODBC Setup Screen-> "Optimizer" tab.

The default path: "%appdata%\QODBC Driver for QuickBooks\Optimizer."
For Example, C:\Users\YOURNAME\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer.

3) Please enable the QODBC status panel

Click the Windows Start button >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >> Go To the "System DSN" Tab, select "QuickBooks Data" DSN & click "Configure">> Navigate to Message tab->Select "Display Driver Status" and "Display optimizer Status" options.

You may also refer to How to view details on what QODBC is performing / Display Driver Status / Display optimizer Status.

4) Please try to connect to QuickBooks using QODBC Support Wizard. 

5) Optional - Rebuild the Optimizer file(s)
For more details, please click here. Refer to section "3. Rebuild Optimizer file."

Problem Description:

How to use the QODBC Support Wizard?

Solution:

Please follow the steps below for using the QODBC Support Wizard.

The "QODBC Support Wizard" application is included with QODBC v18.0.0.325 and above. If you are using an older version of QODBC, please upgrade your QODBC to the latest version. Click here to download the newest version of QODBC.

OR Click here to download "QODBC Support Wizard." 

Click on the "QODBC Support Wizard" icon from the Windows Start menu.

"QODBC Support Wizard" is open. Click "Next."

Follow the instructions on the wizard.

On the QODBC DSN Verification step, make sure it passes all the checks.

In case you see a warning icon below. Please click the "FIX" button so the QODBC Support Wizard can automatically fix the issue.

Once the QODBC Support Wizard has performed all checks, please click the "Next" button.

Once the QODBC Support Wizard has performed all checks, please click the "Next" button.

Now, click the "Test Connection" button.

On clicking the "Test Connection" button, you will notice "Connecting to QuickBooks" as shown below.

When QODBC tries to access the company file for the first time, QuickBooks will ask you for permission to allow the driver to access your QuickBooks company file. Switch to the QuickBooks application & accept the certificate.

OR

After accepting the Application Integration certificate, the QODBC Support Wizard should be able to connect to QuickBooks and should report "Test connection successful."

Click the "Next" button, select the default option "Yes, I can connect to QuickBooks," and click "Next."

You may click one of the options on the "Optional Configurations" steps. The default is "I can connect and would like to skip optional configuration/tests."

Click "Next" to navigate to the Finish window.

Testing your SQL Statement:

Start the QODBC Support Wizard from the Windows Start menu.

Click "Test my SQL commands in QODBC directly."

You may test connectivity and the data fetch on the "Test your SQL statements" steps. Click one of the five buttons, write your SQL statement, and click "Query."

Detailed explanation of each check

QODBC latest version check 

This step will check if your machine is installed with the latest QODBC version available on the website.

If your QODBC version is lower than the latest version, it will show a warning/failure mark.

To fix the issue, click the "FIX" button, and you will be navigated to the QODBC website to download the latest QODBC version.

(Note: Internet connectivity is required.

QuickBooks check (Multiple versions of QuickBooks installation) 

This step will check for multiple versions of QuickBooks installed on your machine.

If multiple QuickBooks are installed, you may not be able to use the QODBC Auto-login feature. This is because QODBC uses QuickBooks SDK to work with QuickBooks, and QODBC /QuickBooks SDK will not be able to judge from the company file path to determine which edition of QuickBooks to start.

For Example:

You have QuickBooks 2018 and QuickBooks 2020 installed on your machine.

You have created two DSNs to access the QuickBooks Company file 2018 and 2020 data.

When you auto-connect with the QuickBooks company file 2018, the QuickBooks SDK will not be able to identify which QuickBooks version to start. Hence, it may begin QuickBooks 2020 in the background (since QuickBooks 2020 is the latest QuickBooks version, it will start by default).

The solution is to keep the QuickBooks application running so that the QODBC / QuickBooks SDK does not have to load QuickBooks and can connect to an existing instance.

Or

If you are using only one of the QuickBooks versions, you may uninstall the non-required QuickBooks version.

QRemote Server firewall check 

This step will check whether incoming or outgoing connection requests from the QRemote Server port number are allowed via your Windows firewall.

If there is no entry of the QRemote Server port number 4500 in the firewall, then it will create one in the allowed list.

If you have selected disallowed in the firewall for connection requests coming from or to the QRemote Server port number 4500, then it will not alter those settings. You will need to change it manually.

QuickBooks and QODBC major version check 

This step will check whether your QODBC license OR version is higher than or equal to the QuickBooks version.

If your QODBC license or version is lower than the QuickBooks version, it will show a warning/failure mark.

For Example:

If you use QuickBooks 2020 and have QODBC version 2019, it will show a warning/failure mark.

Or

If you have QuickBooks and the QODBC version is 2020, the QODBC version is 2020, but your QODBC license is 2019, it will show a warning/failure symbol.

To fix this, you will need to activate the QODBC license and the version equal to or higher than QuickBooks.

Office 32/64-bit – QODBC license compatibility check 

This step will check whether you have a compatible QODBC license to work with MS Office 64-Bit.

If you do not have a QODBC license and are using MS Office 64-Bit, it will show a warning/failure symbol.

Once you have purchased and activated the QODBC license, the validation check will change to a green tick mark.

If you don't want to use MS Office 64-bit or purchase a QODBC license, you may switch to MS Office 32-bit.

Click on the "FIX" button and follow the instructions.

Or

Please refer to the link below on how to convert 64-bit to 32-bit MS Office:

http://qodbc.com/links/3000

QuickBooks check (Elevated Mode – Run as Administrator) 

This step will show a warning/failure symbol if your QuickBooks application runs under elevated permissions or as "Run as administrator."

The QuickBooks application and your application, like Microsoft Excel, Microsoft Access, or your custom application, should be running under normal/standard user mode.

For further instructions, click on the "FIX" button and follow the instructions.

Or

You may navigate to the following link:

http://qodbc.com/links/2529

Optional Configurations and Tests:

Set up Auto-login in QuickBooks 

This is an optional step.

This will help you create a QODBC DSN for Auto-login.

QODBC Support Wizard can create a new DSN and configure it to auto-login to QuickBooks.

Click the "Browse" button, provide a DSN Name (in case you want to change the auto-generated DSN name), and click "Create a new DSN."

Start QuickBooks and log in to your QuickBooks company file as QuickBooks user "Admin."

Click "Test QODBC Connection"

Company file path – Specify the QuickBooks company file path for which you wish to configure Auto-login.

You may use the "Browse" button to locate the QuickBooks Company file.

or

You must copy the QuickBooks company file path and paste it into this field. You can find the QuickBooks company file path in the QuickBooks UI (press F2 in QuickBooks UI).

DSN Name – Once you have specified the company file path, the QODBC Support Wizard will automatically generate a DSN name based on your QuickBooks company file name.

For Example:

Suppose the QuickBooks company file name is "sample_product-based business.qbw" (refer to the image).

The wizard will generate a DSN Name – "sample_product-based bu" (refer to the image).

If you want to change it, you may create a new DSN Name per your requirement.

Test QODBC connection – Click on this button to test the QODBC connection to connect to your QuickBooks company file.

Test QRemote connection - Click this button to test the QRemote connection to connect with your QuickBooks company file.

Repeat the above steps to create multiple DSNs if you have numerous companies.

Rebuild the QODBC Optimizer file. 

This is an optional step.

This will help you reset and rebuild your QODBC Optimizer files.

QODBC Support Wizard can reset and rebuild the QODBC optimizer file.

Click "Reset Optimizer Files" to delete the existing QODBC Optimizer file.

Click "Optimize All Tables" - This will start the process and rebuild the entire optimizer file for the opened QuickBooks company file.

This process may take a few hours (4 to 24 hours), depending on the number of records in your QuickBooks company file.

Reset optimizer files – When you click this button, your QODBC Optimizer file will be deleted from the Optimizer folder.

It will ask for confirmation. On confirmation, the optimizer file will be deleted.

If you have multiple QuickBooks company files, it will ask for confirmation for each optimizer file.

Once you have reset/deleted the optimizer file, you can optimize all or the selected QODBC data tables.

Optimize All Tables – If you want to optimize all tables, click on this button.

Or

Optimize Selected Tables – If you want to optimize selected tables, then click on this button.

From the list of tables, select the tables you want to optimize.

For Example:

To optimize Bill, Invoice tables, select Bill, Invoice tables, and then click "Optimize Selected Tables."

QRemote connectivity test 

This is an optional step.

QODBC Support Wizard can also perform a "QRemote Test Connection" to check if QRemote is configured correctly and can connect.

Troubleshooting with QODBC Support Wizard:

If you cannot connect to QuickBooks, select "No, I am not able to connect to QuickBooks" and click "Next."

You may link one or more relevant topics from the FAQs and Knowledgebase, or search our knowledge base using the search button.

If you are still facing the issue, report the problem to us by creating a support ticket.

Also, refer to How to use QODBC Support Wizard - Remote Connectivity.

Download Sample

Sample Code for using QODBC DSN:

$connectstring = "DSN=QuickBooks Data;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Script execution & Result:

Open Windows PowerShell (x86) from the Start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result:

Sample Code for using QRemote DSN:

$connectstring = "DSN=QuickBooks Data QRemote;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Please refer to how to configure QRemote.

Please Note: If you have a 64-bit application, you must use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote."

Also, Refer:

QODBC Reports Architecture Mismatch Problem When Connecting

Problem Description 1:

I am trying to install QODBC on my system installation hung (not responding) at 10 %, then gave the error below:
I could not load the DLL library
C:\Windows\System32\fqqb32.dll. Invalid access to memory location.

Problem Description 2:

I am trying to install QODBC on my system & got the below error:

The ordinal 2874 could not be located in the dynamic link library LIBEAY32.dll

Solution:

You might face this issue if ssleay32.dll & libeay32.dll (OpenSSL files) are corrupted or do not have any version or details of the file in the file properties.

The cause of the corrupted or wrong libeay32.dll file could be any other software that also uses similar technology that might have replaced this. QODBC will not install this file if it already exists and is the same or a newer version.

To resolve this issue, you need to rename the libeay32.dll & ssleay32.dll files & install QODBC again.

You can find libeay32.dll & ssleay32.dll from:

If you have a 32-bit machine:
C:\Windows\System32\

If you have a 64-bit machine:
C:\Windows\SysWOW64\

To rename a file, Right-click on the file & click on "Rename."
Rename both files one by one.

Rename file:

Rename file:

Now install QODBC. Refer: Connecting QODBC Driver to QuickBooks for the First Time (Video)

Verify both OpenSSL files after installation. You will notice that files have versions of other file details in the file property.

Keywords: The ordinal 2874 could not be located in the dynamic link library LIBEAY32.dll

For Windows 8 or 8.1, click here

Installation Steps for Windows 10

After you have downloaded QODBC Installer, navigate to the "Downloads" Folder or the location where you have saved QODBC Installer. Run the installer as administrator by clicking on the Installer and selecting "Run As Administrator." Don't forget to read the "Readme" file during installation for the latest information on QODBC setup, new features, and other driver information.

Please close all the applications. It is better to reboot the machine before starting the installation.  During the installation, you will be asked to select the Region of QuickBooks you are using. This should match the version of the driver you purchased. Next, select the destination directory to install the QODBC support files. Click "OK" to continue.

The FLEXquarters QODBC Driver for QuickBooks^® has been successfully installed on your computer. You can begin to use it immediately because it will run as a free 30-day evaluation edition, serving as all editions of QODBC, depending on how it is configured.

QODBC is installed on the Machine                        

To install a full purchase QODBC registration code to an evaluation copy from a previous download, see the Icons that have been added to your Start Menu on your Windows 10. i.e., "Purchase QODBC Now."

The first step in configuring the driver is to click on the QODBC Setup Screen icon, and the setup screen will become available.

Also, refer to the following:

Connecting QODBC Driver to QuickBooks for the First Time (Video)

How to set up QODBC Driver to auto-connect to QuickBooks. (Video)

How to setup QODBC to run without QuickBooks

When you install QODBC Online below menu items are created under QODBC Driver for use with the QuickBooks Online folder in the Start menu.

1. Configure QODBC Online Data Source 64-bit

Configuring the QODBC Online Data Source 64-bit option will help you to create/configure a QRemote 64-bit DSN, which is used in the 64-bit application.  

2. Configure QODBC Online Data Source

Configuring the QODBC Online Data Source option will help you to create/configure QODBC & QRemote 32-bit DSN, which is used in the 32-bit application.  

3. Purchase QODBC Online Now

The purchase QODBC Online Now option will navigate you to the QODBC website for purchasing a QODBC Online license.  

4. QODBC Online Quick Start Guide

The QODBC Online Quick Start Guide option will open a start guide that will guide you on how to use QODBC Online.  

5. QODBC Online Setup Screen

The QODBC Online Setup Screen option will help you configure different setup options in QODBC Online.  

6. QODBC Online Support Wizard

QODBC Online Support Wizard is a tool available with QODBC, which is useful in testing the connection with QuickBooks. QODBC Online Support Wizard is verifying the configuration to connect to QuickBooks, which will help fix the issue. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks Online\QSW\QODBCSupportWizard.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks Online\QSW\QODBCSupportWizard.exe

7. QODBC Online Test Tool

QODBC Online Test Tool is a tool available with QODBC that is useful in testing queries with QuickBooks. QODBC Online Test Tool can also perform insert/update/delete operations using standard SQL language.

8. QRemote Server for QODBC Online

QRemote Server for QODBC Online will help you start QRemote Server if it is not running.  

9. QuickBooks Online Table Schema

The QuickBooks Online Table Schema option will navigate you to the QuickBooks Online Table Schema/Data Layout page.  

10. README File

README File option will open a README file containing instructions on how to install the QODBC Online, how to use the essential functions of the QODBC Online, and what the QODBC Online does. It also includes a list of recent updates made to the QODBC Online.  

11. Uninstall QODBC Online

Uninstalling the QODBC Online option will uninstall QODBC Online from the system. If you are updating the QODBC Online version, then you need to uninstall the older QODBC Online version & install the newer QODBC version. At that time, uninstalling the QODBC Online option will help you.  

12. VB Demo

VB Demo is a tool available with QODBC Online, which is helpful in testing connections with QuickBooks Online. VB Demo is also useful for querying table data. VB Demo can also perform insert/update/delete operations using standard SQL language.

Tags: QuickBooks Online, QBO, QODBC Online menu, configure QODBC Online

When you install QODBC POS below menu items are created under the QODBC Driver for use with the QuickBooks POS folder in the Start menu.

1. Configure QODBC POS Data Source 64-bit

Configure QODBC POS Data Source 64-bit option will help you to create/configure QRemote 64-bit DSN, which is used in the 64-bit application.  

2. Configure QODBC POS Data Source

Configuring the QODBC POS Data Source option will help you create/configure QODBC & QRemote 32-bit DSN used in the 32-bit application.  

3. Purchase QODBC Driver for QuickBooks POS

The purchase QODBC POS Now option will navigate you to the QODBC website for purchasing a QODBC POS license.  

4. QODBC Driver for QuickBooks POS Quick Start Guide

QODBC Driver for QuickBooks POS Quick Start Guide option will open a start guide that will guide you on how to use QODBC POS.  

5. QODBC POS Setup Screen

THE QODBC POS Setup Screen option will help you to configure different setup options in QODBC POS.  

6. QODBC POS Support Wizard

QODBC POS Support Wizard is a tool available with QODBC, which is useful in testing the connection with QuickBooks. QODBC POS Support Wizard is verifying the configuration to connect to QuickBooks, which will help fix the issue. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks POS\QSW\QODBCSupportWizard.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks POS\QSW\QODBCSupportWizard.exe

7. QODBC POS Test Tool

QODBC POS Test Tool is a tool available with QODBC that is useful in testing queries with QuickBooks. QODBC POS Test Tool can also perform insert/update/delete operations using standard SQL language.

8. QRemote Server for QODBC POS

QRemote Server for QODBC POS option will help you to start QRemote Server if it is not running. Note: QRemote Server has started automatically on system startup.  

9. README File

The README File option will open a README file containing instructions on installing the QODBC POS, how to use the QODBC, and what the QODBC POS does. It also includes a list of recent updates made to the QODBC POS.  

10. Uninstall QODBC POS

Uninstalling the QODBC POS option will uninstall QODBC POS from the system. If you are updating the QODBC POS version, you need to uninstall the older QODBC POS version & install the newer QODBC POS version. At that time, the Uninstall QODBC option will help you.  

11. VB Demo

VB Demo is a testing tool available with QODBC POS, which is helpful in testing the connection with QuickBooks POS. VB Demo is also a valid query table for data. VB Demo can also perform insert/update/delete operations using standard SQL language.

When you install QODBC, the following menu items are created under QODBC Driver for use with the QuickBooks folder in the Start menu.

1. Configure QODBC Data Source 64-bit

Configure QODBC Data Source 64-bit will help you to view/create/configure 64-bit DSNs like "QuickBooks Data 64-bit QRemote" or "QRemote Data 64-bit". 64-bit DSNs are used by the 64-bit application(s). You can run the 64-bit ODBC admin utility to see the data source from:
C:\Windows\System32\odbcad32.exe 

2. Configure QODBC Data Source

Configuring the QODBC Data Source will help you to create/configure QuickBooks Data & QRemote 32-bit DSN, which is used in the 32-bit application. You can run the 32-bit ODBC admin utility to see the data source from:
C:\Windows\SysWOW64\odbcad32.exe 

3. Purchase QODBC Now

Purchase QODBC Now will navigate you to the QODBC website for purchasing a QODBC license. 

4. QODBC Quick Start Guide

QODBC Quick Start Guide will open "QODBC Quick Start Guide.pdf." The Quick Guide document should help you get started and use QODBC. You can find information regarding how to use QODBC for the first time, samples, FAQ & support information. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\QODBC Quick Start Guide.Pd

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\QODBC Quick Start Guide.Pdf

5. QODBC Setup Screen

QODBC Setup Screen will help you to configure different setup options in QODBC. You can perform different configurations from the QODBC Setup Screen, like setting up auto-login to QuickBooks, checking QODBC & QuickBooks SDK logs, setting up the Optimizer option, and configuring other Advanced Setup options. 

6. QODBC Support Wizard

QODBC Support Wizard is a tool available with QODBC that is useful in testing the connection with QuickBooks. QODBC Support Wizard is verifying the configuration to connect to QuickBooks, which will help fix the issue. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\QSW\QODBCSupportWizard.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\QSW\QODBCSupportWizard.exe

Please refer to the How to use QODBC Support Wizard

7. QODBC Test Tool

QODBC Test Tool is a tool available with QODBC that is useful in testing queries with QuickBooks. QODBC Test Tool can also perform insert/update/delete operations using standard SQL language.

8. QRemote Server for QODBC

QRemote Server for QODBC will help you to start QRemote Server if it is not running. Note: QRemote Server has started automatically on system startup. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\QRemote\Server\QRemoteServer.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\QRemote\Server\QRemoteServer.exe 

9. QuickBooks Table Schema

QuickBooks Table Schema will navigate you to the QuickBooks table Schema/Data Layout page, using that link. You can get details about the QODBC table & report schema. You will get information on each table's relation with other tables, table/report columns, and report parameters that are supported.  

10. README File

README File will open a README file containing instructions on how to install the QODBC, how to use the basic functions of the QODBC, and what the QODBC does. It also includes a list of recent updates made to the QODBC. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\readme.rtf

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\readme.rtf

11. Uninstall QODBC

Uninstalling QODBC will uninstall QODBC from the system. If you are updating the QODBC version, you need to uninstall the older QODBC version & install the newer QODBC version. At that time, uninstalling the QODBC option will help you. Link location:

For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\UNWISE32.EXE

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\UNWISE32.EXE

12. VB Demo

VB Demo is a tool with QODBC that is useful in testing the connection with QuickBooks. VB Demo is also useful for querying table data. VB Demo can also perform insert/update/delete operations using standard SQL language. Link location:

For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\VBDEMO32.EXE

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\VBDEMO32.EXE

Problem Description:

I am testing the connection of QODBC & I am getting the QODBC test connection failed error:



Could you advise on the error and its solution? 

Solution:

The above error indicates that multiple instances of QuickBooks are running. Please close all instances of QuickBooks. Verify that no instances of QBW32 are running in the Task Manager (Processes> View all user processes).

Please run only one instance of QuickBooks as a context user, log in to the company file as Admin & try to connect using the QODBC Test Tool.

Please refer to Connecting QODBC Driver to QuickBooks for the First Time (Video)

Problem Description:

I am trying to update records through the select statement. But it is not working.

UPDATE Customer SET CustomFieldProjectNumber = i.JobNumber FROM ( SELECT ListID, {fn SUBSTRING("Name", 1, 5)} AS JobNumber FROM Customer WHERE TimeCreated > {ts '2016-01-01 00:00:00.000'} ) i WHERE i.ListID = Customer.ListID

I am getting the "Unexpected extra token: (" error in QODBC Test Tool.  

Any Assistance Is Appreciated.

Solution:

You can execute the query below in MS Access linked table without any issue.

Please refer to How to Use QODBC with MS Access 2016 / 365 / 2013 32-bit

You need to link the Customer table in MS Access & execute the query in the Access query editor.

MS Access>> create tab >> Query Design >> Paste query >> Execute query using Run button

Update Customer qe inner join Customer de on qe.ListID = de.ListID set qe.CustomFieldProjectNumber = MID (de.Name,1,5) WHERE qe.TimeCreated >#2016-01-01 00:00:01#

Problem Description:

Good Day To You. Using This Sp_report And Having Some Problems With The Column Layout. Command Text Is As Follows: Sp_report Profitandlossbudgetvsactual Show Account name, Account number, Amount, Budget Parameters Fiscalyear=2015, Classfilterfullnames =' health Center,' 'administrative.'

I'm Getting 12 Columns, As One Would Expect, But The 12th Column Is The Sum Of The Previous 11 Columns, Not The 12th Month. This Is True For Both The Amount And Budget Segments. Also, The Budget Amount For The 11th Month is exactly one-half of the Actual Budgeted Amount. I Have Run The Actual Report, And The Totals For Each Month Are Correct For Both the Amount And Budget Until The Last Two Months In The report.

Also, if I replace the FiscalYear=2015, with DateMacro=’ThisYear’ I get this



Any Assistance Is Appreciated.  

Solution:

Please ensure that you are passing the same parameter in the report query you passed in QuickBooks UI.

Also, for the budget report, the Fiscal Year parameter is required. Without it, a report will not execute. So you need to keep the Fiscal Year parameter & add other date parameters.

For Example:

sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, DateFrom={d'2015-10-01'}, DateTo={d'2016-09-30'}, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

QODBC Desktop 

QODBC is a Windows Driver (DLL) with the exact system requirements as the QuickBooks Desktop Accounting version it communicates with.

QODBC POS 

QODBC is a Windows Driver (DLL) with the exact system requirements as the QuickBooks POS Desktop version it is communicating with.

QODBC Online 

QODBC Online supports Windows 7 SP1, Windows Server 2008 R2 SP1 (or above), Windows 8, Windows 10, and Windows 11

The latest Intuit IPP v3 .NET SDK minimum requirement is .NET Framework > 4.6.1

QODBC Online 18.00.00.325  to 19.00.00.333 uses .Net Framework 4.6.1

QODBC Online 19.00.00.334 or later uses .NET Framework 4.7.2

Refer to .NET Framework system requirements for more details about the supported Operating System detail.

Also, refer to Intuit's News:  Upgrading your apps to support TLS 1.2

First, you need to create a Linked Server in SQL Server.

For 32-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote.

For 64-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote.

I have already created a linked server, "QRemote."

After creating a Linked Server, you need to create a stored procedure, which will read data from the Linked Server table & write it into the SQL Server local table.

For storing Linked Server data in the local database. It would be best if you used the INSERT INTO SELECT query as below.

For Example:

SELECT * INTO SQL_Local_TableName FROM YourLinkedServerName...TableName;

First, we will add a single table in the local database. After that, we will create a stored procedure for all tables

We will add an Account table in the local database with the table name as Tbl_Account.

SELECT * INTO Tbl_Account FROM QRemote...Account

The Tbl_Account table is created for the local database with 105 records.

Now we will query the local table Tbl_Account.
Select * from Tbl_Account
Data is available in the local database table.

Now, we will create a stored procedure that will read data from the Linked Server table & write it into the SQL Server local table.

In Object Explorer, connect to an instance of Database Engine and then expand that instance.

Expand Databases, expand your database, and then expand Programmability.

Right-click Stored Procedures and then click the New Stored Procedure.

We will create the stored procedure named Import_QODBC_Tables, which will check in the local database if the table exists. It will drop the table & recreate the table & read all the linked server tables & create a new table in a local database.

After writing the stored procedure, click on Execute to create the stored procedure.

Stored Procedure Created.

Stored Procedure Code:

================================================================

USE [QODBC]
GO
/****** Object:  StoredProcedure [dbo].[Import_QODBC_Tables]    Script Date: 07/29/2016 00:28:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[Import_QODBC_Tables]


AS
begin

IF OBJECT_ID (N'Tbl_Account', N'U') IS NOT NULL
 
Drop table Tbl_Account
   
SELECT * INTO Tbl_Account    FROM QRemote...Account

IF OBJECT_ID (N'Tbl_AccountTaxLineInfo', N'U') IS NOT NULL
 
Drop table Tbl_AccountTaxLineInfo

SELECT * INTO    Tbl_AccountTaxLineInfo    FROM    QRemote...AccountTaxLineInfo

IF OBJECT_ID (N'Tbl_ARRefundCreditCard', N'U') IS NOT NULL
 
Drop table Tbl_ARRefundCreditCard

SELECT * INTO    Tbl_ARRefundCreditCard    FROM    QRemote...ARRefundCreditCard

IF OBJECT_ID (N'Tbl_ARRefundCreditCardRefundAppliedTo', N'U') IS NOT NULL
 
Drop table Tbl_ARRefundCreditCardRefundAppliedTo

SELECT * INTO    Tbl_ARRefundCreditCardRefundAppliedTo    FROM    QRemote...ARRefundCreditCardRefundAppliedTo

IF OBJECT_ID (N'Tbl_BarCode', N'U') IS NOT NULL
 
Drop table Tbl_BarCode

SELECT * INTO    Tbl_BarCode    FROM    QRemote...BarCode

IF OBJECT_ID (N'Tbl_Bill', N'U') IS NOT NULL
 
Drop table Tbl_Bill

SELECT * INTO    Tbl_Bill    FROM    QRemote...Bill

IF OBJECT_ID (N'Tbl_BillExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_BillExpenseLine

SELECT * INTO    Tbl_BillExpenseLine    FROM    QRemote...BillExpenseLine

IF OBJECT_ID (N'Tbl_BillItemLine', N'U') IS NOT NULL
 
Drop table Tbl_BillItemLine

SELECT * INTO    Tbl_BillItemLine    FROM    QRemote...BillItemLine

IF OBJECT_ID (N'Tbl_BillLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_BillLinkedTxn

SELECT * INTO    Tbl_BillLinkedTxn    FROM    QRemote...BillLinkedTxn

IF OBJECT_ID (N'Tbl_BillingRate', N'U') IS NOT NULL
 
Drop table Tbl_BillingRate

SELECT * INTO    Tbl_BillingRate    FROM    QRemote...BillingRate

IF OBJECT_ID (N'Tbl_BillingRateLine', N'U') IS NOT NULL
 
Drop table Tbl_BillingRateLine

SELECT * INTO    Tbl_BillingRateLine    FROM    QRemote...BillingRateLine

IF OBJECT_ID (N'Tbl_BillPaymentCheck', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCheck

SELECT * INTO    Tbl_BillPaymentCheck    FROM    QRemote...BillPaymentCheck

IF OBJECT_ID (N'Tbl_BillPaymentCheckLine', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCheckLine

SELECT * INTO    Tbl_BillPaymentCheckLine    FROM    QRemote...BillPaymentCheckLine

IF OBJECT_ID (N'Tbl_BillPaymentCheckLineLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCheckLineLinkedTxn

SELECT * INTO    Tbl_BillPaymentCheckLineLinkedTxn    FROM    QRemote...BillPaymentCheckLineLinkedTxn

IF OBJECT_ID (N'Tbl_BillPaymentCreditCard', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCreditCard

SELECT * INTO    Tbl_BillPaymentCreditCard    FROM    QRemote...BillPaymentCreditCard

IF OBJECT_ID (N'Tbl_BillPaymentCreditCardLine', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCreditCardLine

SELECT * INTO    Tbl_BillPaymentCreditCardLine    FROM    QRemote...BillPaymentCreditCardLine

IF OBJECT_ID (N'Tbl_BillPaymentCreditCardLineLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCreditCardLineLinkedTxn

SELECT * INTO    Tbl_BillPaymentCreditCardLineLinkedTxn    FROM    QRemote...BillPaymentCreditCardLineLinkedTxn

IF OBJECT_ID (N'Tbl_BillToPay', N'U') IS NOT NULL
 
Drop table Tbl_BillToPay

SELECT * INTO    Tbl_BillToPay    FROM    QRemote...BillToPay

IF OBJECT_ID (N'Tbl_BuildAssembly', N'U') IS NOT NULL
 
Drop table Tbl_BuildAssembly

SELECT * INTO    Tbl_BuildAssembly    FROM    QRemote...BuildAssembly

IF OBJECT_ID (N'Tbl_BuildAssemblyComponentItemLine', N'U') IS NOT NULL
 
Drop table Tbl_BuildAssemblyComponentItemLine

SELECT * INTO    Tbl_BuildAssemblyComponentItemLine    FROM    QRemote...BuildAssemblyComponentItemLine

IF OBJECT_ID (N'Tbl_Charge', N'U') IS NOT NULL
 
Drop table Tbl_Charge

SELECT * INTO    Tbl_Charge    FROM    QRemote...Charge

IF OBJECT_ID (N'Tbl_ChargeLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_ChargeLinkedTxn

SELECT * INTO    Tbl_ChargeLinkedTxn    FROM    QRemote...ChargeLinkedTxn

IF OBJECT_ID (N'Tbl_Check', N'U') IS NOT NULL
 
Drop table Tbl_Check

SELECT * INTO    Tbl_Check    FROM    QRemote..."Check"

IF OBJECT_ID (N'Tbl_CheckApplyCheckToTxn', N'U') IS NOT NULL
 
Drop table Tbl_CheckApplyCheckToTxn

SELECT * INTO    Tbl_CheckApplyCheckToTxn    FROM    QRemote...CheckApplyCheckToTxn

IF OBJECT_ID (N'Tbl_CheckExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_CheckExpenseLine

SELECT * INTO    Tbl_CheckExpenseLine    FROM    QRemote...CheckExpenseLine

IF OBJECT_ID (N'Tbl_CheckItemLine', N'U') IS NOT NULL
 
Drop table Tbl_CheckItemLine

SELECT * INTO    Tbl_CheckItemLine    FROM    QRemote...CheckItemLine

IF OBJECT_ID (N'Tbl_Class', N'U') IS NOT NULL
 
Drop table Tbl_Class

SELECT * INTO    Tbl_Class    FROM    QRemote...Class

IF OBJECT_ID (N'Tbl_ClearedStatus', N'U') IS NOT NULL
 
Drop table Tbl_ClearedStatus

SELECT * INTO    Tbl_ClearedStatus    FROM    QRemote...ClearedStatus

IF OBJECT_ID (N'Tbl_Company', N'U') IS NOT NULL
 
Drop table Tbl_Company

SELECT * INTO    Tbl_Company    FROM    QRemote...Company

IF OBJECT_ID (N'Tbl_CompanyActivity', N'U') IS NOT NULL
 
Drop table Tbl_CompanyActivity

SELECT * INTO    Tbl_CompanyActivity    FROM    QRemote...CompanyActivity

IF OBJECT_ID (N'Tbl_CreditCardCharge', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCharge

SELECT * INTO    Tbl_CreditCardCharge    FROM    QRemote...CreditCardCharge

IF OBJECT_ID (N'Tbl_CreditCardChargeExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardChargeExpenseLine

SELECT * INTO    Tbl_CreditCardChargeExpenseLine    FROM    QRemote...CreditCardChargeExpenseLine

IF OBJECT_ID (N'Tbl_CreditCardChargeItemLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardChargeItemLine

SELECT * INTO    Tbl_CreditCardChargeItemLine    FROM    QRemote...CreditCardChargeItemLine

IF OBJECT_ID (N'Tbl_CreditCardCredit', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCredit

SELECT * INTO    Tbl_CreditCardCredit    FROM    QRemote...CreditCardCredit

IF OBJECT_ID (N'Tbl_CreditCardCreditExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCreditExpenseLine

SELECT * INTO    Tbl_CreditCardCreditExpenseLine    FROM    QRemote...CreditCardCreditExpenseLine

IF OBJECT_ID (N'Tbl_CreditCardCreditItemLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCreditItemLine

SELECT * INTO    Tbl_CreditCardCreditItemLine    FROM    QRemote...CreditCardCreditItemLine

IF OBJECT_ID (N'Tbl_CreditMemo', N'U') IS NOT NULL
 
Drop table Tbl_CreditMemo

SELECT * INTO    Tbl_CreditMemo    FROM    QRemote...CreditMemo

IF OBJECT_ID (N'Tbl_CreditMemoLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditMemoLine

SELECT * INTO    Tbl_CreditMemoLine    FROM    QRemote...CreditMemoLine

IF OBJECT_ID (N'Tbl_CreditMemoLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_CreditMemoLinkedTxn

SELECT * INTO    Tbl_CreditMemoLinkedTxn    FROM    QRemote...CreditMemoLinkedTxn

IF OBJECT_ID (N'Tbl_Currency', N'U') IS NOT NULL
 
Drop table Tbl_Currency

SELECT * INTO    Tbl_Currency    FROM    QRemote...Currency

IF OBJECT_ID (N'Tbl_Customer', N'U') IS NOT NULL
 
Drop table Tbl_Customer

SELECT * INTO    Tbl_Customer    FROM    QRemote...Customer

IF OBJECT_ID (N'Tbl_CustomerAddtionalNote', N'U') IS NOT NULL
 
Drop table Tbl_CustomerAddtionalNote

SELECT * INTO    Tbl_CustomerAddtionalNote    FROM    QRemote...CustomerAddtionalNote

IF OBJECT_ID (N'Tbl_CustomerContacts', N'U') IS NOT NULL
 
Drop table Tbl_CustomerContacts

SELECT * INTO    Tbl_CustomerContacts    FROM    QRemote...CustomerContacts

IF OBJECT_ID (N'Tbl_CustomerShipToAddress', N'U') IS NOT NULL
 
Drop table Tbl_CustomerShipToAddress

SELECT * INTO    Tbl_CustomerShipToAddress    FROM    QRemote...CustomerShipToAddress

IF OBJECT_ID (N'Tbl_CustomerMsg', N'U') IS NOT NULL
 
Drop table Tbl_CustomerMsg

SELECT * INTO    Tbl_CustomerMsg    FROM    QRemote...CustomerMsg

IF OBJECT_ID (N'Tbl_CustomerType', N'U') IS NOT NULL
 
Drop table Tbl_CustomerType

SELECT * INTO    Tbl_CustomerType    FROM    QRemote...CustomerType

IF OBJECT_ID (N'Tbl_CustomField', N'U') IS NOT NULL
 
Drop table Tbl_CustomField

SELECT * INTO    Tbl_CustomField    FROM    QRemote...CustomField

IF OBJECT_ID (N'Tbl_DateDrivenTerms', N'U') IS NOT NULL
 
Drop table Tbl_DateDrivenTerms

SELECT * INTO    Tbl_DateDrivenTerms    FROM    QRemote...DateDrivenTerms

IF OBJECT_ID (N'Tbl_Deposit', N'U') IS NOT NULL
 
Drop table Tbl_Deposit

SELECT * INTO    Tbl_Deposit    FROM    QRemote...Deposit

IF OBJECT_ID (N'Tbl_DepositLine', N'U') IS NOT NULL
 
Drop table Tbl_DepositLine

SELECT * INTO    Tbl_DepositLine    FROM    QRemote...DepositLine

IF OBJECT_ID (N'Tbl_Employee', N'U') IS NOT NULL
 
Drop table Tbl_Employee

SELECT * INTO    Tbl_Employee    FROM    QRemote...Employee

IF OBJECT_ID (N'Tbl_EmployeeAddtionalNote', N'U') IS NOT NULL
 
Drop table Tbl_EmployeeAddtionalNote

SELECT * INTO    Tbl_EmployeeAddtionalNote    FROM    QRemote...EmployeeAddtionalNote

IF OBJECT_ID (N'Tbl_EmployeeEarning', N'U') IS NOT NULL
 
Drop table Tbl_EmployeeEarning

SELECT * INTO    Tbl_EmployeeEarning    FROM    QRemote...EmployeeEarning

IF OBJECT_ID (N'Tbl_Entity', N'U') IS NOT NULL
 
Drop table Tbl_Entity

SELECT * INTO    Tbl_Entity    FROM    QRemote...Entity

IF OBJECT_ID (N'Tbl_EntityAdditionalNote', N'U') IS NOT NULL
 
Drop table Tbl_EntityAdditionalNote

SELECT * INTO    Tbl_EntityAdditionalNote    FROM    QRemote...EntityAdditionalNote

IF OBJECT_ID (N'Tbl_EntityContacts', N'U') IS NOT NULL
 
Drop table Tbl_EntityContacts

SELECT * INTO    Tbl_EntityContacts    FROM    QRemote...EntityContacts

IF OBJECT_ID (N'Tbl_EntityShipToAddress', N'U') IS NOT NULL
 
Drop table Tbl_EntityShipToAddress

SELECT * INTO    Tbl_EntityShipToAddress    FROM    QRemote...EntityShipToAddress

IF OBJECT_ID (N'Tbl_Estimate', N'U') IS NOT NULL
 
Drop table Tbl_Estimate

SELECT * INTO    Tbl_Estimate    FROM    QRemote...Estimate

IF OBJECT_ID (N'Tbl_EstimateLine', N'U') IS NOT NULL
 
Drop table Tbl_EstimateLine

SELECT * INTO    Tbl_EstimateLine    FROM    QRemote...EstimateLine

IF OBJECT_ID (N'Tbl_EstimateLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_EstimateLinkedTxn
SELECT * INTO    Tbl_EstimateLinkedTxn    FROM    QRemote...EstimateLinkedTxn

IF OBJECT_ID (N'Tbl_Host', N'U') IS NOT NULL
Drop table Tbl_Host
SELECT * INTO    Tbl_Host    FROM    QRemote...Host

IF OBJECT_ID (N'Tbl_HostMetaData', N'U') IS NOT NULL
Drop table Tbl_HostMetaData
SELECT * INTO    Tbl_HostMetaData    FROM    QRemote...HostMetaData

IF OBJECT_ID (N'Tbl_HostSupportedVersions', N'U') IS NOT NULL
Drop table Tbl_HostSupportedVersions
SELECT * INTO    Tbl_HostSupportedVersions    FROM    QRemote...HostSupportedVersions

IF OBJECT_ID (N'Tbl_InventoryAdjustment', N'U') IS NOT NULL
Drop table Tbl_InventoryAdjustment
SELECT * INTO    Tbl_InventoryAdjustment    FROM    QRemote...InventoryAdjustment

IF OBJECT_ID (N'Tbl_InventoryAdjustmentLine', N'U') IS NOT NULL
Drop table Tbl_InventoryAdjustmentLine
SELECT * INTO    Tbl_InventoryAdjustmentLine    FROM    QRemote...InventoryAdjustmentLine

IF OBJECT_ID (N'Tbl_InventorySite', N'U') IS NOT NULL
Drop table Tbl_InventorySite
SELECT * INTO    Tbl_InventorySite    FROM    QRemote...InventorySite

IF OBJECT_ID (N'Tbl_Invoice', N'U') IS NOT NULL
Drop table Tbl_Invoice
SELECT * INTO    Tbl_Invoice    FROM    QRemote...Invoice

IF OBJECT_ID (N'Tbl_InvoiceLine', N'U') IS NOT NULL
Drop table Tbl_InvoiceLine
SELECT * INTO    Tbl_InvoiceLine    FROM    QRemote...InvoiceLine

IF OBJECT_ID (N'Tbl_InvoiceLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_InvoiceLinkedTxn
SELECT * INTO    Tbl_InvoiceLinkedTxn    FROM    QRemote...InvoiceLinkedTxn

IF OBJECT_ID (N'Tbl_Item', N'U') IS NOT NULL
Drop table Tbl_Item
SELECT * INTO    Tbl_Item    FROM    QRemote...Item

IF OBJECT_ID (N'Tbl_ItemAssembliesCanBuild', N'U') IS NOT NULL
Drop table Tbl_ItemAssembliesCanBuild
SELECT * INTO    Tbl_ItemAssembliesCanBuild    FROM    QRemote...ItemAssembliesCanBuild where ItemInventoryAssemblyRefFullName='assembly item' and TxnDate={d'2018-01-01'}

IF OBJECT_ID (N'Tbl_ItemDiscount', N'U') IS NOT NULL
Drop table Tbl_ItemDiscount
SELECT * INTO    Tbl_ItemDiscount    FROM    QRemote...ItemDiscount

IF OBJECT_ID (N'Tbl_ItemFixedAsset', N'U') IS NOT NULL
Drop table Tbl_ItemFixedAsset
SELECT * INTO    Tbl_ItemFixedAsset    FROM    QRemote...ItemFixedAsset

IF OBJECT_ID (N'Tbl_ItemGroup', N'U') IS NOT NULL
Drop table Tbl_ItemGroup
SELECT * INTO    Tbl_ItemGroup    FROM    QRemote...ItemGroup

IF OBJECT_ID (N'Tbl_ItemGroupLine', N'U') IS NOT NULL
Drop table Tbl_ItemGroupLine
SELECT * INTO    Tbl_ItemGroupLine    FROM    QRemote...ItemGroupLine

IF OBJECT_ID (N'Tbl_ItemInventory', N'U') IS NOT NULL
Drop table Tbl_ItemInventory
SELECT * INTO    Tbl_ItemInventory    FROM    QRemote...ItemInventory

IF OBJECT_ID (N'Tbl_ItemInventoryAssembly', N'U') IS NOT NULL
Drop table Tbl_ItemInventoryAssembly
SELECT * INTO    Tbl_ItemInventoryAssembly    FROM    QRemote...ItemInventoryAssembly

IF OBJECT_ID (N'Tbl_ItemInventoryAssemblyLine', N'U') IS NOT NULL
Drop table Tbl_ItemInventoryAssemblyLine
SELECT * INTO    Tbl_ItemInventoryAssemblyLine    FROM    QRemote...ItemInventoryAssemblyLine

IF OBJECT_ID (N'Tbl_ItemNonInventory', N'U') IS NOT NULL
Drop table Tbl_ItemNonInventory
SELECT * INTO    Tbl_ItemNonInventory    FROM    QRemote...ItemNonInventory

IF OBJECT_ID (N'Tbl_ItemOtherCharge', N'U') IS NOT NULL
Drop table Tbl_ItemOtherCharge
SELECT * INTO    Tbl_ItemOtherCharge    FROM    QRemote...ItemOtherCharge

IF OBJECT_ID (N'Tbl_ItemPayment', N'U') IS NOT NULL
Drop table Tbl_ItemPayment
SELECT * INTO    Tbl_ItemPayment    FROM    QRemote...ItemPayment

IF OBJECT_ID (N'Tbl_ItemReceipt', N'U') IS NOT NULL
Drop table Tbl_ItemReceipt
SELECT * INTO    Tbl_ItemReceipt    FROM    QRemote...ItemReceipt

IF OBJECT_ID (N'Tbl_ItemReceiptExpenseLine', N'U') IS NOT NULL
Drop table Tbl_ItemReceiptExpenseLine
SELECT * INTO    Tbl_ItemReceiptExpenseLine    FROM    QRemote...ItemReceiptExpenseLine

IF OBJECT_ID (N'Tbl_ItemReceiptItemLine', N'U') IS NOT NULL
Drop table Tbl_ItemReceiptItemLine
SELECT * INTO    Tbl_ItemReceiptItemLine    FROM    QRemote...ItemReceiptItemLine

IF OBJECT_ID (N'Tbl_ItemReceiptLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_ItemReceiptLinkedTxn
SELECT * INTO    Tbl_ItemReceiptLinkedTxn    FROM    QRemote...ItemReceiptLinkedTxn

IF OBJECT_ID (N'Tbl_ItemSalesTax', N'U') IS NOT NULL
Drop table Tbl_ItemSalesTax
SELECT * INTO    Tbl_ItemSalesTax    FROM    QRemote...ItemSalesTax

IF OBJECT_ID (N'Tbl_ItemSalesTaxGroup', N'U') IS NOT NULL
Drop table Tbl_ItemSalesTaxGroup
SELECT * INTO    Tbl_ItemSalesTaxGroup    FROM    QRemote...ItemSalesTaxGroup

IF OBJECT_ID (N'Tbl_ItemSalesTaxGroupLine', N'U') IS NOT NULL
Drop table Tbl_ItemSalesTaxGroupLine
SELECT * INTO    Tbl_ItemSalesTaxGroupLine    FROM    QRemote...ItemSalesTaxGroupLine

IF OBJECT_ID (N'Tbl_ItemSites', N'U') IS NOT NULL
Drop table Tbl_ItemSites
SELECT * INTO    Tbl_ItemSites    FROM    QRemote...ItemSites

IF OBJECT_ID (N'Tbl_ItemService', N'U') IS NOT NULL
Drop table Tbl_ItemService
SELECT * INTO    Tbl_ItemService    FROM    QRemote...ItemService

IF OBJECT_ID (N'Tbl_ItemSubtotal', N'U') IS NOT NULL
Drop table Tbl_ItemSubtotal
SELECT * INTO    Tbl_ItemSubtotal    FROM    QRemote...ItemSubtotal

IF OBJECT_ID (N'Tbl_JobType', N'U') IS NOT NULL
Drop table Tbl_JobType
SELECT * INTO    Tbl_JobType    FROM    QRemote...job type

IF OBJECT_ID (N'Tbl_JournalEntry', N'U') IS NOT NULL
Drop table Tbl_JournalEntry
SELECT * INTO    Tbl_JournalEntry    FROM    QRemote...JournalEntry

IF OBJECT_ID (N'Tbl_JournalEntryCreditLine', N'U') IS NOT NULL
Drop table Tbl_JournalEntryCreditLine
SELECT * INTO    Tbl_JournalEntryCreditLine    FROM    QRemote...JournalEntryCreditLine

IF OBJECT_ID (N'Tbl_JournalEntryDebitLine', N'U') IS NOT NULL
Drop table Tbl_JournalEntryDebitLine
SELECT * INTO    Tbl_JournalEntryDebitLine    FROM    QRemote...JournalEntryDebitLine

IF OBJECT_ID (N'Tbl_JournalEntryLine', N'U') IS NOT NULL
Drop table Tbl_JournalEntryLine
SELECT * INTO    Tbl_JournalEntryLine    FROM    QRemote...JournalEntryLine

IF OBJECT_ID (N'Tbl_ListDeleted', N'U') IS NOT NULL
Drop table Tbl_ListDeleted
SELECT * INTO    Tbl_ListDeleted    FROM    QRemote...ListDeleted

IF OBJECT_ID (N'Tbl_OtherName', N'U') IS NOT NULL
Drop table Tbl_OtherName
SELECT * INTO    Tbl_OtherName    FROM    QRemote... another name

IF OBJECT_ID (N'Tbl_PaymentMethod', N'U') IS NOT NULL
Drop table Tbl_PaymentMethod
SELECT * INTO    Tbl_PaymentMethod    FROM    QRemote...payment method

IF OBJECT_ID (N'Tbl_PayrollItemNonWage', N'U') IS NOT NULL
Drop table Tbl_PayrollItemNonWage
SELECT * INTO    Tbl_PayrollItemNonWage    FROM    QRemote...PayrollItemNonWage

IF OBJECT_ID (N'Tbl_PayrollItemWage', N'U') IS NOT NULL
Drop table Tbl_PayrollItemWage
SELECT * INTO    Tbl_PayrollItemWage    FROM    QRemote...PayrollItemWage

IF OBJECT_ID (N'Tbl_Preferences', N'U') IS NOT NULL
Drop table Tbl_Preferences
SELECT * INTO    Tbl_Preferences    FROM    QRemote...Preferences

IF OBJECT_ID (N'Tbl_PriceLevel', N'U') IS NOT NULL
Drop table Tbl_PriceLevel
SELECT * INTO    Tbl_PriceLevel    FROM    QRemote...PriceLevel

IF OBJECT_ID (N'Tbl_PriceLevelPerItem', N'U') IS NOT NULL
Drop table Tbl_PriceLevelPerItem
SELECT * INTO    Tbl_PriceLevelPerItem    FROM    QRemote...PriceLevelPerItem

IF OBJECT_ID (N'Tbl_PurchaseOrder', N'U') IS NOT NULL
Drop table Tbl_PurchaseOrder
SELECT * INTO    Tbl_PurchaseOrder    FROM    QRemote...PurchaseOrder

IF OBJECT_ID (N'Tbl_PurchaseOrderLine', N'U') IS NOT NULL
Drop table Tbl_PurchaseOrderLine
SELECT * INTO    Tbl_PurchaseOrderLine    FROM    QRemote...PurchaseOrderLine

IF OBJECT_ID (N'Tbl_PurchaseOrderLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_PurchaseOrderLinkedTxn
SELECT * INTO    Tbl_PurchaseOrderLinkedTxn    FROM    QRemote...PurchaseOrderLinkedTxn

IF OBJECT_ID (N'Tbl_ReceivePayment', N'U') IS NOT NULL
Drop table Tbl_ReceivePayment
SELECT * INTO    Tbl_ReceivePayment    FROM    QRemote...receive payment

IF OBJECT_ID (N'Tbl_ReceivePaymentLine', N'U') IS NOT NULL
Drop table Tbl_ReceivePaymentLine
SELECT * INTO    Tbl_ReceivePaymentLine    FROM    QRemote...ReceivePaymentLine

IF OBJECT_ID (N'Tbl_ReceivePaymentLineLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_ReceivePaymentLineLinkedTxn
SELECT * INTO    Tbl_ReceivePaymentLineLinkedTxn    FROM    QRemote...ReceivePaymentLineLinkedTxn

IF OBJECT_ID (N'Tbl_ReceivePaymentToDeposit', N'U') IS NOT NULL
Drop table Tbl_ReceivePaymentToDeposit
SELECT * INTO    Tbl_ReceivePaymentToDeposit    FROM    QRemote...ReceivePaymentToDeposit

IF OBJECT_ID (N'Tbl_Sales', N'U') IS NOT NULL
Drop table Tbl_Sales
SELECT * INTO    Tbl_Sales    FROM    QRemote...Sales

IF OBJECT_ID (N'Tbl_SalesLine', N'U') IS NOT NULL
Drop table Tbl_SalesLine
SELECT * INTO    Tbl_SalesLine    FROM    QRemote...SalesLine

IF OBJECT_ID (N'Tbl_SalesOrder', N'U') IS NOT NULL
Drop table Tbl_SalesOrder
SELECT * INTO    Tbl_SalesOrder    FROM    QRemote...SalesOrder

IF OBJECT_ID (N'Tbl_SalesOrderLine', N'U') IS NOT NULL
Drop table Tbl_SalesOrderLine
SELECT * INTO    Tbl_SalesOrderLine    FROM    QRemote...SalesOrderLine

IF OBJECT_ID (N'Tbl_SalesOrderLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_SalesOrderLinkedTxn
SELECT * INTO    Tbl_SalesOrderLinkedTxn    FROM    QRemote...SalesOrderLinkedTxn

IF OBJECT_ID (N'Tbl_SalesReceipt', N'U') IS NOT NULL
Drop table Tbl_SalesReceipt
SELECT * INTO    Tbl_SalesReceipt    FROM    QRemote...SalesReceipt

IF OBJECT_ID (N'Tbl_SalesReceiptLine', N'U') IS NOT NULL
Drop table Tbl_SalesReceiptLine
SELECT * INTO    Tbl_SalesReceiptLine    FROM    QRemote...SalesReceiptLine

IF OBJECT_ID (N'Tbl_SalesRep', N'U') IS NOT NULL
Drop table Tbl_SalesRep
SELECT * INTO    Tbl_SalesRep    FROM    QRemote...SalesRep

IF OBJECT_ID (N'Tbl_SalesTaxCode', N'U') IS NOT NULL
Drop table Tbl_SalesTaxCode
SELECT * INTO    Tbl_SalesTaxCode    FROM    QRemote...SalesTaxCode

IF OBJECT_ID (N'Tbl_SalesTaxPaymentCheck', N'U') IS NOT NULL
Drop table Tbl_SalesTaxPaymentCheck
SELECT * INTO    Tbl_SalesTaxPaymentCheck    FROM    QRemote...SalesTaxPaymentCheck

IF OBJECT_ID (N'Tbl_SalesTaxPaymentCheckLine', N'U') IS NOT NULL
Drop table Tbl_SalesTaxPaymentCheckLine
SELECT * INTO    Tbl_SalesTaxPaymentCheckLine    FROM    QRemote...SalesTaxPaymentCheckLine

IF OBJECT_ID (N'Tbl_ShipMethod', N'U') IS NOT NULL
Drop table Tbl_ShipMethod
SELECT * INTO    Tbl_ShipMethod    FROM    QRemote...ShipMethod

IF OBJECT_ID (N'Tbl_SpecialAccount', N'U') IS NOT NULL
Drop table Tbl_SpecialAccount
SELECT * INTO    Tbl_SpecialAccount    FROM    QRemote...SpecialAccount

IF OBJECT_ID (N'Tbl_SpecialItem', N'U') IS NOT NULL
Drop table Tbl_SpecialItem
SELECT * INTO    Tbl_SpecialItem    FROM    QRemote...SpecialItem

IF OBJECT_ID (N'Tbl_StandardTerms', N'U') IS NOT NULL
Drop table Tbl_StandardTerms
SELECT * INTO    Tbl_StandardTerms    FROM    QRemote...StandardTerms

IF OBJECT_ID (N'Tbl_TaxCode', N'U') IS NOT NULL
Drop table Tbl_TaxCode
SELECT * INTO    Tbl_TaxCode    FROM    QRemote...TaxCode

IF OBJECT_ID (N'Tbl_Template', N'U') IS NOT NULL
Drop table Tbl_Template
SELECT * INTO    Tbl_Template    FROM    QRemote...Template

IF OBJECT_ID (N'Tbl_Terms', N'U') IS NOT NULL
Drop table Tbl_Terms
SELECT * INTO    Tbl_Terms    FROM    QRemote...Terms

IF OBJECT_ID (N'Tbl_TimeTracking', N'U') IS NOT NULL
Drop table Tbl_TimeTracking
SELECT * INTO    Tbl_TimeTracking    FROM    QRemote...TimeTracking

IF OBJECT_ID (N'Tbl_ToDo', N'U') IS NOT NULL
Drop table Tbl_ToDo
SELECT * INTO    Tbl_ToDo    FROM    QRemote...ToDo

IF OBJECT_ID (N'Tbl_Transaction', N'U') IS NOT NULL
Drop table Tbl_Transaction
SELECT * INTO    Tbl_Transaction    FROM    QRemote..."Transaction"

IF OBJECT_ID (N'Tbl_Transfer', N'U') IS NOT NULL
Drop table Tbl_Transfer
SELECT * INTO    Tbl_Transfer    FROM    QRemote...Transfer

IF OBJECT_ID (N'Tbl_TransferInventory', N'U') IS NOT NULL
Drop table Tbl_TransferInventory
SELECT * INTO    Tbl_TransferInventory    FROM    QRemote...TransferInventory

IF OBJECT_ID (N'Tbl_TransferInventoryLine', N'U') IS NOT NULL
Drop table Tbl_TransferInventoryLine
SELECT * INTO    Tbl_TransferInventoryLine    FROM    QRemote...TransferInventoryLine

IF OBJECT_ID (N'Tbl_TxnDeleted', N'U') IS NOT NULL
Drop table Tbl_TxnDeleted
SELECT * INTO    Tbl_TxnDeleted    FROM    QRemote...TxnDeleted

IF OBJECT_ID (N'Tbl_UnitOfMeasureSet', N'U') IS NOT NULL
Drop table Tbl_UnitOfMeasureSet
SELECT * INTO    Tbl_UnitOfMeasureSet    FROM    QRemote...UnitOfMeasureSet

IF OBJECT_ID (N'Tbl_UnitOfMeasureSetRelatedUnit', N'U') IS NOT NULL
Drop table Tbl_UnitOfMeasureSetRelatedUnit
SELECT * INTO    Tbl_UnitOfMeasureSetRelatedUnit    FROM    QRemote...UnitOfMeasureSetRelatedUnit

IF OBJECT_ID (N'Tbl_UnitOfMeasureSetDefaultUnit', N'U') IS NOT NULL
Drop table Tbl_UnitOfMeasureSetDefaultUnit
SELECT * INTO    Tbl_UnitOfMeasureSetDefaultUnit    FROM    QRemote...UnitOfMeasureSetDefaultUnit

IF OBJECT_ID (N'Tbl_Vehicle', N'U') IS NOT NULL
Drop table Tbl_Vehicle
SELECT * INTO    Tbl_Vehicle    FROM    QRemote...Vehicle

IF OBJECT_ID (N'Tbl_VehicleMileage', N'U') IS NOT NULL
Drop table Tbl_VehicleMileage
SELECT * INTO    Tbl_VehicleMileage    FROM    QRemote...VehicleMileage

IF OBJECT_ID (N'Tbl_Vendor', N'U') IS NOT NULL
Drop table Tbl_Vendor
SELECT * INTO    Tbl_Vendor    FROM    QRemote...Vendor

IF OBJECT_ID (N'Tbl_VendorContacts', N'U') IS NOT NULL
Drop table Tbl_VendorContacts
SELECT * INTO    Tbl_VendorContacts    FROM    QRemote...VendorContacts

IF OBJECT_ID (N'Tbl_VendorCredit', N'U') IS NOT NULL
Drop table Tbl_VendorCredit
SELECT * INTO    Tbl_VendorCredit    FROM    QRemote...VendorCredit

IF OBJECT_ID (N'Tbl_VendorCreditExpenseLine', N'U') IS NOT NULL
Drop table Tbl_VendorCreditExpenseLine
SELECT * INTO    Tbl_VendorCreditExpenseLine    FROM    QRemote...VendorCreditExpenseLine

IF OBJECT_ID (N'Tbl_VendorCreditItemLine', N'U') IS NOT NULL
Drop table Tbl_VendorCreditItemLine
SELECT * INTO    Tbl_VendorCreditItemLine    FROM    QRemote...VendorCreditItemLine

IF OBJECT_ID (N'Tbl_VendorCreditLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_VendorCreditLinkedTxn
SELECT * INTO    Tbl_VendorCreditLinkedTxn    FROM    QRemote...VendorCreditLinkedTxn

IF OBJECT_ID (N'Tbl_VendorType', N'U') IS NOT NULL
Drop table Tbl_VendorType
SELECT * INTO    Tbl_VendorType    FROM    QRemote...VendorType

IF OBJECT_ID (N'Tbl_WorkersCompCode', N'U') IS NOT NULL
Drop table Tbl_WorkersCompCode
SELECT * INTO    Tbl_WorkersCompCode    FROM    QRemote...WorkersCompCode

IF OBJECT_ID (N'Tbl_WorkersCompCodeRateHistory', N'U') IS NOT NULL
Drop table Tbl_WorkersCompCodeRateHistory
SELECT * INTO    Tbl_WorkersCompCodeRateHistory    FROM    QRemote...WorkersCompCodeRateHistory

end

================================================================

All tables are imported to the SQL Server Database by executing this stored procedure.

You can see that all tables are imported into the SQL Server Database.

You can add this stored procedure to SQL Agent & schedule it. So it will automatically add all tables to the SQL Server Database.

Now we will add this stored procedure to SQL Agent.

Right-click on SQL Server Agent & click on New >> Job to create a new Job.

A New Job pop-up will appear. Specify the name of the job.

Click next on the "Steps" in the left menu. An SQL job can contain one or more steps. A step might be simply an SQL statement or a stored procedure call. Add your effort here.

Enter the step name and type. Select the database, write the stored procedure execution command "exec Import_QODBC_Tables," & click ok.

Job step added.

The job is added to SQL Server Agent.

You can run a Job by right-clicking on the Job name & click on "Start Job at Step..."

The job is executing.

Download Sample

Before using the MS Excel application, please ensure you have installed the MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

It would be best if you opened QuickBooks & load the company file in QuickBooks.

Open MS Excel & create a new spreadsheet.
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.

'QuickBooks Data' as an example:

The first time you used MS Excel to access QuickBooks. You will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Trial Balance report query.
sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel spreadsheet.

Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:

Now we will add a button & we will write code for the button click event. On button click, it will open the connection, refresh the report data & close the connection.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to Refresh Data. Double-click on the command button to write code.

Write the code below to open the connection, refresh the report data & close the connection.

Private Sub CommandButton1_Click()
ActiveWorkbook.Connections("Query from QuickBooks Data"). Refresh
End Sub

Note: Query from QuickBooks Data is the name of the connection. You can get it from the Data--> Connections.

Report Data will be refreshed by clicking the Refresh Data button.

Problem Description:

I just purchased a QODBC 2024 license.

I have everything set up, but I can't activate the CDKEY.

"CDKEY has an invalid checksum."

Solution:

QODBC 2024 CDKEY/license can only be activated on QODBC Desktop driver version 24.

QODBC 2023 and below CDKEY/license can be activated on QODBC Desktop driver version 24 and below.

Please download the latest version of QODBC Driver for QuickBooks - https://qodbc.com/qodbc-downloads/

CD Key has an invalid checksum (other than QODBC 2024 CDKEY/license)

Problem Description:

I am moving to a new server.

I have everything set up, but I can't get the license to transfer. On the old machine, when I go to the 'deactivate' using my CD key, I get:

"CD Key has an invalid Checksum."



Please help me to resolve this error.

Solution:

You will get the error "CD Key had invalid Checksum" when you try activating the wrong product's CD KEY.

QODBC Desktop license can only be activated on a QODBC Driver for QuickBooks Desktop. 

How to activate QODBC Desktop

http://qodbc.com/links/1957

QODBC Online license can only be activated on a QODBC Driver for QuickBooks Online.

How to activate QODBC Online

http://qodbc.com/links/2971

QODBC POS license can only be activated on a QODBC driver for QuickBooks Point of Sale.

How to activate QODBC POS

http://qodbc.com/links/3077

Please ensure you have purchased the correct QODBC product and activated it on the correct driver.

Problem Description:

I am trying to connect to QuickBooks Online using QODBC Online. However, I am encountering the error below at the time of connection.



Could you please help me resolve this error?

Solution:

QODBC Online is using the default port 8383. You will get the above error message when you have changed the port number in QODBC Online.

So, if you have changed the port number from the QODBC Online Setup Screen, then please close all the applications, wait for 5 to 10 minutes, and try to connect again.

If the above does not resolve the issue, then please ensure that no other application is using this port.

If any other application is not using the port specified on the QODBC Online Setup Screen, then you need to restart Windows & test again.

If any other application uses the port specified on the QODBC Online Setup Screen, you need to change the port from the QODBC Online Setup Screen.

You can change the port from:

Start >> All Programs >> QODBC Driver for use with QuickBooks Online >> Configure QODBC Online Data Source >> Go To "System DSN" Tab>> select QuickBooks Online Data >> click "configure" >> switch to General tab



Simply enter the port number you want to use in the "Port:" textbox and click "OK." In this example, I am changing it to the default port 8383.



After changing the port, please close all applications, wait for 5 to 10 minutes, and then try to connect again. If it does not work, you need to restart Windows & test again.

If you are still facing issues, then please raise a support ticket to the QODBC Technical Support department by clicking here

Tags: QuickBooks Online, QBO, QODBC Online, Port, Begin session, Unable to load component

Download Sample

In this example, we will show you how to insert an Invoice in QuickBooks using the data available in an Excel spreadsheet.

We have an Excel spreadsheet that contains invoice-related information. The Excel spreadsheet has a CustomerRefFullName, RefNumber, TxnDate, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, InvoiceLineSalesTaxCodeRefFullName, and FQSaveToCache information.

Using this information, we will create an Invoice in QuickBooks, which has 4 line items.

Now we will add a button & we will write code for the button click event. On button click, it inserts the Invoice in QuickBooks.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to ADD Invoice—double-click on the command button to write code.

Write the code below for inserting an invoice in QuickBooks from an Excel spreadsheet.



Private Sub CommandButton1_Click()

Dim sh As Worksheet
Dim row As Range
Dim RowCount As Integer
Dim CustomerRefFullName As String
Dim RefNumber As String
Dim TxnDate As Date
Dim InvoiceLineItemRefFullName As String
Dim InvoiceLineDesc As String
Dim InvoiceLineRate As Single
Dim InvoiceLineQuantity As Single
Dim InvoiceLineSalesTaxCodeRefFullName As String
Dim FQSaveToCache As Boolean
Dim connection As New ADODB.Connection
Dim sConnectString
Dim sSQL As String
Dim rs
Dim sMsg

RowCount = 0
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"
sSQL = "SELECT * FROM InvoiceLine"
Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic
Set sh = ActiveSheet
For Each rw In sh.Rows

If (RowCount > 0), Then
CustomerRefFullName = sh.Cells(rw.Row, 1).Value
If (CustomerRefFullName = "") Then
Exit For

End If

RefNumber = sh.Cells(rw.Row, 2).Value
TxnDate = sh.Cells(rw.Row, 3).Value
InvoiceLineItemRefFullName = sh.Cells(rw.Row, 4).Value
InvoiceLineDesc = sh.Cells(rw.Row, 5).Value
InvoiceLineRate = sh.Cells(rw.Row, 6).Value
InvoiceLineQuantity = sh.Cells(rw.Row, 7).Value
InvoiceLineSalesTaxCodeRefFullName = sh.Cells(rw.Row, 8).Value
FQSaveToCache = sh.Cells(rw.Row, 9).Value

rs.AddNew
rs("CustomerRefFullName") = CustomerRefFullName
rs("RefNumber") = RefNumber
rs("TxnDate") = TxnDate
rs("InvoiceLineItemRefFullName") = InvoiceLineItemRefFullName
rs("InvoiceLineDesc") = InvoiceLineDesc
rs("InvoiceLineRate") = InvoiceLineRate
rs("InvoiceLineQuantity") = InvoiceLineQuantity
rs("InvoiceLineSalesTaxCodeRefFullName") = InvoiceLineSalesTaxCodeRefFullName
rs("FQSaveToCache") = FQSaveToCache
rs.Update

End If

RowCount = RowCount + 1
Next rw
sMsg = sMsg & "Invoice Added!!!"
MsgBox sMsg

rs. Close
oConnection.Close

End Sub

On clicking on the ADD Invoice button, the Invoice will be added to QuickBooks.

Invoice Added.

You can see the same in the QuickBooks Invoice that is added.

Download Sample

There is a limitation with the QuickBooks Desktop SDK: it can connect only one company file to a user session at a time.

It means you cannot access multiple company files at the same time.

You need to close the connection with one company file and switch to another. ( i.e., connection closed for company A and Connection open for company B should have a gap of 15-30 seconds for switching operation.)

This applies to QuickBooks Desktop. When using QuickBooks Online, you can connect to multiple company files simultaneously.

In this article, we will connect to two different QuickBooks Desktop company files using two different QODBC DSNs with auto login configured. In this example, we will use two auto logins, DSN CompanyA & CompanyB.

Please Refer:
How to set up QODBC Desktop for Auto-login into QuickBooks Desktop
How to set up the QODBC Driver to auto-connect to QuickBooks Desktop. (Video)

Here is my DSN configuration for the First QuickBooks Desktop Company file.

It would be best if you opened QuickBooks Desktop & loaded the company file that you have chosen in the auto login DSN. The company file path in QuickBooks & DSN should be matched.

Open MS Excel & create a new spreadsheet.
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.

Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query. 

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.)

'CompanyA' as an example:

The first time you use MS Excel to access QuickBooks Desktop, you will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Trial Balance report query.
sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel sheet.

Repeating the above steps will export another report in sheet 2 using DSN CompanyB.

We need to close the first company file, which is open in QuickBooks Desktop.

Here is my DSN configuration for the Second Company file.

It would be best if you opened QuickBooks Desktop and loaded the company file that you have chosen in the auto login DSN. The company file path in QuickBooks & DSN should be matched.

Navigate to sheet2
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.)

'CompanyB' as an example:

The first time you use MS Excel to access QuickBooks Desktop, you will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Profit & Loss report query.
sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel sheet 2.

After the export is complete, close the company file opened in QuickBooks Desktop.

Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:

We need to disable auto-refresh data for both company file connections (i.e., Query from CompanyA & Query from CompanyB ).

Now we will add a button & we will write code for the button click event. On button click, it will open the connection for CompanyA, refresh the report data & close the connection for CompanyA, sleep for 15 seconds, open the connection for CompanyB, refresh the report data & close the connection for CompanyB.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to Refresh Data. Double-click on the command button to write code.

Write the code below to open a connection for Company A, refresh the report data & close the connection for Company A, sleep for 15 seconds, open the connection for CompanyB, restore the report data & close the connection for CompanyB.

Fifteen seconds of sleep are required to close the company file and switch to another private Sub CommandButton1_Click()
ActiveWorkbook.Connections("Query from CompanyA"). Refresh
Application.Wait (Now + TimeValue("0:00:15"))
ActiveWorkbook.Connections("Query from CompanyB").Refresh
End Sub

Report Data will be refreshed by clicking the Refresh Data button.

Keywords: QuickBooks Desktop, win log with multiple companies, log in with various companies

Problem Description:

I am getting an error SOCKET Closed because of an abnormal package in the QRemote client. I am accessing QuickBooks Data from the remote machine over the internet (WAN). 

Solution:

This issue will occur when the QRemote client & QRemote Server have different network types.The 

Windows component is internally checking the connection every 4 minutes. If there is no activity between the client & server, the Windows component will close the connection after 4 minutes & due to this, when the QRemote client tries to connect to the QRemote Server, it will not be able to connect, & SOCKET is Closed because an abnormal package error occurred.

To resolve this issue, you need to set the AutoClose value to 3 minutes for closing the broken connection in QRemote Server.

You can find the QRemote Server running in the System Tray. If QRemote Server is not running, then you can run it from Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC.



Open QRemote Server. Go to File Menu -> Settings -> Advanced Tab. Set AutoClose broken connection after 3 minutes & apply the setting by clicking the Apply button.



After applying the settings. Please restart the QRemote Server & test again. To continue QRemote Server, go to File Menu -> Exit -> :

You will get a pop-up window with the message. Do you want to close the QRemote Server? Please click on OK to close QRemoteServer.

Please rerun QRemoteServer & test. You can QRemoteServer from:
Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC

Problem Description:

I am using the code below. It is returning records & giving me the correct result. But if I change the query from "SELECT Name FROM customer" to "SELECT * FROM customer," it does not return any records & giving me the incorrect result. I am getting the following error:

[QODBC] Internal error
440: BOF or EOF is True, or the current record has been deleted. The requested operation requires a current record.



Public Sub example select()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(100)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(100)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************"
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub 

Solution:

To resolve the issue, you need to add the line below to your connection string.

OptimizerAllowDirtyReads=N

For Example:

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;OptimizerAllowDirtyReads=N"

After changing this, you can get the correct result.

Problem Description:

How do I know which version of QuickBooks my QODBC license works with?  

Solution:

To find out which version of QuickBooks with your QODBC license works, please follow the steps below:

1. Click Start > All Programs > QODBC Driver for use with QuickBooks
2. Click QODBC Setup Screen
3. Click the About tab


4. In the middle of the About Tab window, you will see a hyperlink that says, "Check website for updates."Just below this, you will see your Serial Number in the format X00X12345678.

The first character in the Serial Number indicates the type of license you have. R indicates Read Only, W indicates Read Write, and S indicates the Server edition of QODBC.

The second and third characters indicate the QuickBooks year number (e.g., 13 is for 2013 and earlier, 12 is for 2012 and earlier) that your QODBC license is compatible with.

The fourth character is "U" for the USA, "O" for Australia/New Zealand/South-East Asia, and "K" for UK/Europe, Middle-East, and Africa editions of QuickBooks. Other letters are for different regions.

The remaining characters are from the rest of your serial number.

So if you have a Serial Number of W16U12345678, your QODBC license is a read-write version compatible with QuickBooks 2016 US edition and earlier.

If you are using QODBC Read Only, which comes with QuickBooks Enterprise, you will notice Licensed To QuickBooks Enterprise User in the About Tab below the "Check website for updates."

Licensing FAQ:

Question 1:

How can I know for which year and QuickBooks my QODBC license is valid? Can you install a higher QODBC version with the lower license?

Answer 1:

QODBC license is licensed for QuickBooks Version(Year). E.g., If you have QODBC licensed for 2015, you can use QODBC with QuickBooks 2015,2014,2013, and so on, but not with QuickBooks 2016.

You can install QODBC v15 and v16 with no upgrade cost. Upgrade cost is only applicable when your license does not match the year.

For Example:
If you have QODBC licensed for 2015 & you are using QODBC v15 & QuickBooks 2015, you can install QODBC v16 and use it with QuickBooks 2015.

Question 2:

How do I know what my CD KEY is??

Answer 2:

You can get your CD KEY from the purchase email from your registered email ID. If you have deleted/lost your email, please raise a support ticket with "Sales - Registration - Activation Department." Click Here to submit a support ticket.

Question 3:

I need to download QODBC for QuickBooks 2016 Desktop; I have changed my computer, and can't find it on the site. Will the current one work?

Answer 3:

Each new edition of QODBC also contains the last builds we did of the previous editions. So you can also use the latest QODBC Version with the previous edition of QuickBooks. Your CDKey will unlock the latest QODBC version of the version you were licensed for.

Also, Refer:

QODBC Licensing Information
QODBC with QRemote Licensing Information
How to activate QODBC
How to deactivate QODBC and How to transfer license to a new computer
To purchase the QODBC License, click here

Problem Description:

We are using an advanced pricing module in QuickBooks and would like to know what tables we need to access to pull that information from the system.  

Solution:

Advanced pricing module data (Price Rules) is unavailable through the Intuit SDK, so they are unavailable through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn’t expose one feature to the application in the SDK, QODBC cannot do it either.

Also, Refer:

How to export QuickBooks data not available in QODBC

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 open it to connect to another database?  

Solution:

SQL Server Linked Server automatically handles disconnections.

SQL Server Linked Server does not have a disconnection command that can be fired/executed.

After executing the query, you will have to wait 30-45 seconds. SQL Server will automatically send the disconnect command if there is no activity on the linked server connection.

Problem Description:

Has anyone run a report that will pull all the details for a range of invoices? In particular, I'm looking to run a report that shows the ship-to addresses for specific products over a date range. 

Solution:

Please use the query below to get the ship-to addresses for particular products over a date range.

Select InvoiceLineItemRefFullName, RefNumber, TxnDate, ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressAddr4, ShipAddressAddr5, ShipAddressCity, ShipAddressState, ShipAddressPostalCode, ShipAddressNote from InvoiceLine where TxnDate >= {d'2009-01-01'} and TxnDate <= {d'2009-03-01'} and (InvoiceLineItemRefFullName = 'Cabinets' or InvoiceLineItemRefFullName = 'Window') order by InvoiceLineItemRefFullName, RefNumber, TxnDate.

Problem Description:

I recently discovered QODBC and have been playing with it for a few days. It's pretty good.

The problem I have is when I try to get the A/P aging. The A/P aging has a default of 30 - 90 days. I need the report to show 180 -720 days. I use the example A/P aging report...

sp_report APAgingDetail shows TxnType_Title, Date_Title, RefNumber_Title, Name_Title, DueDate_Title, Aging_Title, OpenBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, DueDate, Aging, OpenBalance parameters DateMacro = Today, AgingAsOf = 'Today.'

...but this does not let me change the SQL statement as it is accessing a stored procedure report. The report runs fine; it's just the days that are wrong. Can anyone tell me how I can change the days per aging? 

Solution:

AgingAsOf can be either ReportEndDate or Today, depending on how you want to use your system date or QuickBooks date.

When using:

sp_report APAgingDetail show Text, Blank, TxnType, Date, RefNumber, Name, DueDate, Aging, OpenBalance parameters DateMacro = 'All,' AgingAsOf = 'Today.'

You can't change the Interval (days) and Through (days past due) like you can use the QuickBooks UI.

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 changing my MS Access code?  

Solution:

QODBC Online is a different product from the QODBC Desktop driver. So you cannot use the QODBC Desktop driver with QuickBooks Online. It would be best if you used the QODBC Online driver to connect to QuickBooks Online.

Most of the Tables, table columns, reports, report columns & report parameters in QuickBooks Desktop & Online are the same. But not all the tables, reports & fields available in QuickBooks desktop are available in QuickBooks Online. You might have to change a few areas to match QuickBooks Online. You need to refer to QODBC Online reports/tables/fields & make changes in your code.

Please refer Difference between QODBC for QuickBooks Desktop and Online & make necessary changes in your code & use the code with QODBC Online.

Tags: QuickBooks Online, QBO, code, QODBC Online, QODBC Desktop

Problem Description:

How many concurrent connections does the Server edition support?

Is the product QuickBooks version specific, requiring additional purchases with each version? AKA. Upgrading from V15 to V16? 

Solution:

There is no limit on QODBC for the concurrent connection, but the more connections, the more QuickBooks has to work. QuickBooks SDK will process all the requests in a sequence, so if you have three connections and three queries going at the same time, the SDK will execute them in sequence.

QODBC license is licensed for QuickBooks Version(Year). E.g., If you have QODBC licensed for 2015, you can use QODBC with QuickBooks 2015,2014,2013, and so on, but not with QuickBooks 2016.

You can install QODBC v15 and v16 with no upgrade cost. Upgrade cost is only applicable when your license does not match the year.

For Example:

If you have QODBC licensed for 2015 & you are using QODBC v15 & QuickBooks 2015, you can install QODBC v16 and use it with QuickBooks 2015.

Download Sample

ADODB with QODBC

Open VB6, you can find it via Windows Start->All Programs->Microsoft Visual Studio 6.0 :

Create a New Standard EXE project.

Then, add a "Command Button" by double-clicking on the command button Icon. A command button will be added. Rename it to Get Customer via ADODB:

Double click on the Command Button & add the code below in the Command Button click event:

Private Sub Command1_Click()

Dim oConnection As ADODB.Connection
Dim oRecordset As ADODB.Recordset
Dim sMsg As String
Dim sConnectString As String
Dim sSQL As String

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "SELECT top 10 Name FROM Customer"
Set oConnection = New ADODB.Connection
Set oRecordset = New ADODB.Recordset
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(10)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(10)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************" & Chr(10)
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing

End Sub

Now execute the code from Run >> Start With Full Compile

Double click on Get Customer via ADODB, which will get the top 10 customers' names & display them in msg box:

ADODC with QODBC

Now we will add an ADODC control in the same project we created in the above ADODB example.

Now we will configure ADODC control by right-clicking on the ADODC control & clicking on ADODC Properties:

From the General tab, select ODBC Data Source, QuickBooks Data.

Navigate to the RecordSource tab, select Command type to 2 - adCmdTable & table name Customer, and click OK.

Now we will add three text boxes showing the Customer table data through the ADODC control.

Now we will configure text boxes to display data from the ADODC control. Choose DataSource Adodc1 (i.e., ADODC Control name) & DataField FullName (i.e., Field name which you want to show):

Similarly, we will configure the other two textboxes with the Phone & Email fields.

Now execute the code from Run >> Start With Full Compile.

The following customer data will be displayed by clicking on the "Next" button.

Problem Description:

I want to cancel my QODBC Driver for QuickBooks Online monthly subscription.

How to cancel the subscription of the QODBC Driver for QuickBooks Online?

 I received an email saying a subscription was going to be renewed. I need to cancel this subscription.

Solution:

Latest Method:

Please visit the following link: https://flexquarters.onfastspring.com/account.

Input the email address associated with your license.

Check your email inbox, and you should receive an email with the subject:

<Your Fullname>, here is a link to manage your orders

From: FastSpring Checkout <mailer@fastspring.com>

Click the link received to manage your account.

The link (URL) should look like https://flexquarters.onfastspring.com/account/somerandomtext.

Latest Method:

You can manage the subscription of the QODBC Driver for QuickBooks Online using the link you received in your renewal email.

On your monthly subscription renewal receipt, you will see the following, as in this example:

Order ID: FLE1XXXXX-1XXX-6XXXXB
1 QODBC Desktop Driver for QuickBooks Online USA
https://sites.fastspring.com/flexquarters/order/s/ (your order number). Click on this link on YOUR receipt, and you can cancel the subscription.

Example
https://sites.fastspring.com/flexquarters/order/s/FLE111111-1111-111111

Please visit that link & click on Cancel Subscription to cancel your QODBC Online subscription:

Tags: QuickBooks Online, QBO, Subscription QODBC Online, Cancel subscription

Intuit is reinforcing existing security features that help protect QuickBooks customers’ sensitive information.
Refer: Intuit's News: Changes to the Integrated Application Authentication for QuickBooks Desktop users


As a result of this effort, Intuit is delivering an update that impacts third-party applications that connect to QuickBooks using the QuickBooks SDK.

A new file specific to a Windows user on a given machine is created on user login that provides access to an SDK app in QuickBooks.

The implementation of this update will require customers to re-enter their login credentials & authorize the application(i.e., QODBC) at QuickBooks.

For Example, you have four Windows users. All four Windows users use QODBC via Microsoft Excel, Access, Crystal, or other applications. You will need to authorize EACH application from all 4 Windows users. That means you will need to log in to the 1st Windows user, endorse all the applications that will be using QODBC, then log in to the 2nd Windows user and authorize all the applications that will be using QODBC. This is because Intuit's LGB file, which was common earlier, has not changed to a new method, and integration information will be stored for each Windows user separately.

The above is applicable for a Single machine, Terminal Services(Remote desktop connection), or Workstation(s).

Problem Description:

Get the following errors from the QODBC driver:

ERROR [00000] [QODBC] QuickBooks Begin Session Failed. Error = 8004041d,80040418. This application cannot log into this QuickBooks company data file automatically. The QuickBooks administrator can grant permission for automatic login through the Integrated Application preferences.

Test connection to QuickBooks - FAILED: "[QODBC] QuickBooks Begin Session Failed. Error = 8004041a. This application does not have permission to access this QuickBooks company data file. The QuickBooks administrator can grant access permission through the Integrated Application preferences."

Solution 1:

Step 1: Launch QuickBooks and Open the Company File

Open QuickBooks Desktop.

Log in to the desired company file using the QuickBooks user "Admin".

Log in to the QuickBooks user "admin" is required to authorize the connection to the QuickBooks company file.

This is a one-time activity for each new application you want to connect to QuickBooks Data.

Step 2: Test connection using QODBC 32-Bit Test Tool

Go to Start → QODBC Driver for QuickBooks → QODBC 32-Bit Test Tool.

In the QODBC Test Tool, 

Select DSN: QuickBooks Data

Click the "Connect" button.

QuickBooks may prompt you to authorize access: (optional)

If prompted, choose “Yes, always; allow access even if QuickBooks is not running”.

Select the Admin user from the dropdown (if applicable).

Click Continue → Done.

If the connection is successful, you can optionally run a test query (e.g., SELECT * FROM Customer).

Click "Disconnect" to close the connection.

Step 3: Test connection using QODBC 64-Bit Test Tool

Go to Start → QODBC Driver for QuickBooks → QODBC 64-Bit Test Tool.

In the QODBC Test Tool, 

Select DSN: QuickBooks Data

Click the "Connect" button.

This should successfully establish a 64-bit connection. You may not be prompted for permission, as it has been granted in Step 2.

Once confirmed, click "OK" or "Disconnect" to close the session.

Step 4: Connect Using Your Application

In your application (e.g., PHP, .NET, Excel, Access, etc.), use the DSN name "QuickBooks Data" to establish a connection.

QuickBooks may prompt you to authorize access: (optional)

If QuickBooks does not ask for authorization, your application might already be authorized. You can run a few SQL statements/queries to verify that the connection is successful.

If you are unable to connect, please ensure you have logged in to the QuickBooks company file as the QuickBooks user "admin."

Solution 2:

You need to follow the steps below to reauthorize QODBC for QuickBooks.
1. Log in to Windows, Open QuickBooks & log in to the company file as Admin.
2. Switch Company File to Single User Mode.

3. Connect using your application. It should pop up the Data Access Certificate on QuickBooks. Please accept the Certificate. QuickBooks issues a Certificate for each application, so you need to get the Certificate for all applications that you use with QuickBooks.

4. You must log in to each Windows user accessing QuickBooks Data using QODBC.
If Windows User/Work Station changes, you must reauthorize the application using the above steps.

If the company file has Personally Identifiable Information (PII) in QuickBooks, users must log in with a password; the SDK app will only be allowed to connect once the login has been completed for that user. 

Customers will be instructed to set up a strong password when logging in. Customers should use unique letters and numbers in a password, not essential words that can easily be found online or in the dictionary.

Problem Description:

We downloaded the QODBC trial from your website. At the end of the installation process - after clicking the 'finish' button, a dialog appears saying that 'explorer.exe' cannot be found.

I am getting the following error:

Windows cannot find 'C:\Users\SAMMYH~1\WINDOWS\explorer.exe'. Make sure you typed the name correctly, and then try again.


Could not execute the external program

C:\Users\SAMMYH~1\WINDOWS\explorer.exe


The installation process aborts, and the software is then uninstalled.

Solution:

This issue occurs when using the QODBC V16.0.0.317 installer & Windows folder is personalized to point to the User's Roaming profile. We have fixed this issue in our latest version of QODBC (V318 or above). You can download the latest QODBC by clicking here.

If you want to use QODBC V317, then please follow the steps below to resolve the issue:

1. Open Windows Explorer.
Navigate to
C:\Windows\




2. Inside the "Windows" folder, you should find "explorer.exe."


Copy explorer.exe by right-clicking on explorer.exe & clicking copy.


3. Navigate to

C:\Users\Windows Username\Windows\
(Windows Username = The current logged-in Windows Username)

In this example, my Windows username is Dewani. So I am navigated to C:\Users\Dewani\Windows.


4. Paste the copied file in the directory C:\users\Windows Username\Windows\
In this example, my Windows username is Dewani. So I am pasting the file to C:\Users\Dewani\Windows. By right-clicking on the C:\Users\Dewani\Windows folder & clicking Paste.




5. Now try to install QODBC again. You will not get this error.

Either QODBC or QRemote is required on the machine where the SSIS package will be developed and deployed, or SSIS services are installed.

Development - On the machine where you are developing, the SSIS package should have QODBC installed.

Deployment - On the machine you wish to deploy or run the SSIS Package - You should have QODBC installed. 

The first step is to install and test QODBC.

Please refer to Connecting QODBC Driver to QuickBooks for the First Time (Video)

If the QuickBooks application is installed on another machine, you can connect to QuickBooks from a remote device using QRemote Server. You need to install QODBC on both machines.

Please refer to Accessing QuickBooks Data Remotely using QODBC & QRemote. (Video)

When deploying the SSIS package to SQL Server, you need to configure the QRemote client to connect with QuickBooks.

Please follow the steps below to use QODBC with SSIS.

1. Go to the Start menu & open SQL Server Business Intelligence Development Studio from SQL Server.

2. Create a new Integration Service Project by clicking on Integration Service Project & Enter Project Name, setting the project location, and clicking OK.

The Project is created.

3. In this sample project, we will transfer QuickBooks data using the ADO.NET source to the Flat File Destination. Wes, we need to add a Data Flow Task in the Control Flow to the perform tab. You can add it by double-clicking on the Data Flow Task.

4. We need to add an ADO.NET Source in the Data Flow tab. You can add it by double-clicking on ADO.NET Source.

5. Now, we need to configure the ADO.NET source. By right-clicking on ADO.NET Source & click on Edit.

Add a new ADO.NET connection by clicking on New.

Add a new Data connection by clicking on New.

Select Provider to .Net Providers\Odbc Data Provider, select DSN to QuickBooks Data QRemote, and click on Test Connection.

Test Connection Succeeded.

You can select Data Access Mode as Table or view the whole table data. You can also select Data Access Mode as an SQL command for accessing table data with a condition & accessing report data using sp_report. In this example, I have selected Table or view as Data Access Mode & selected the Customer table. Click on OK & you will notice the connection has been added.

You can preview output data by clicking the Preview button.

Close the Preview by clicking the close button & click on OK to save configurations.

6. We need to add a Flat File Destination in the Data Flow tab. You can add it by double-clicking on Flat File Destination.

7. Now, we need to link the ADO.NET Source with the Flat File Destination by dragging the link from the ADO.NET Source to the Flat File Destination.

8. Now, we need to configure the Flat File Destination. By right-clicking on Flat File Destination & click on Edit.

Add a new Flat File connection by clicking on New.

Select Flat File Format & click OK.

Select the Destination file you want to store output using the Browse button.

Enable Unicode & Column names in the first data row option, & Click on OK to save configurations.

You will notice the OK button is disabled. To enable it, you need to click on Mapping, then click OK to complete the configuration.

9. Run the SSIS package to perform the transfer operation by clicking the play icon.

SSIS Package execution completed & 163 rows are transferred from ADO.NET Source to Flat File Destination.

Here is a Flat text file that contains the ADO.NET source result.

Please click here to download the Sample SSIS Package.

Problem Description:

I am evaluating your driver and have had success running SELECT queries against QuickBooks in SSMS, where I could use the QRemote DSN. When setting up the ODBC connection in SSIS, I get an error when enumerating the columns.

Unknown Data type found in column

Solution:

To resolve this error, you need to select “ADO.NET Source” instead of ODBC Source in the SSIS package.

Problem Description:

I am trying to find a way to pull data from a . QBB file without having to restore the. QBB file and going through QuickBooks.

Is QODBC capable of doing so? I watched a few videos, and it appeared that all the files used were regular company files. 

Solution:

It is impossible to pull data from a . QBB file without restoring the. QBW file and going through QuickBooks because the QuickBooks SDK does not provide this feature.

The only way that QuickBooks allows us to access any data is if a QuickBooks session runs on the machine with the company file loaded.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn’t expose one feature to the application in the SDK, QODBC cannot do it either.

Problem Description:

I was able to use QODBC Online. Suddenly, I got the following error when I tried to connect to QuickBooks Online using QODBC Online. It was working properly—no known changes to Windows or QODBC. Begin Session Failed. S0000 00000 [QODBC] QuickBooks Begin Session Failed. Error - Unauthorized-401 - - Fault Exception of type: AUTHENTICATION has been generated.

Solution:

The authorized access token for QuickBooks Online will remain active for 180 days. A privileged access token expires after 180 days. When this occurs, responses to calls to QuickBooks Data Services and the QuickBooks Online API indicate that the access token is invalid.

So, you need to reauthorize/reconnect QODBC Online at QuickBooks Online to resolve the issue.

Please refer to How to Reconnect QODBC Online with QuickBooks Online Edition.

Please follow the steps below.

1) "Disconnect from QuickBooks" 32-Bit.

Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure" >> Switch to "General" tab >>
Click the "Disconnect from QuickBooks" button.

2) "Disconnect from QuickBooks" 64-Bit.
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 64-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 64-bit >> Click "configure" >> Switch to "General" tab >>
Click the "Disconnect from QuickBooks" button.

3) Close all the applications.

4) Connect to QuickBooks:

Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure" >> Switch to "General" tab >>
Click "Connect to QuickBooks"

Please refer to https://qodbc.com/links/2659

5) Use the QODBC Online 32-Bit Test Tool to test the connectivity.


6) Export the connection key from "QuickBooks Online Data" DSN of type 32-bit 

Please use the DSN "QuickBooks Online Data" of type 32-bit to export the connection key:
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure">> Switch to "General" tab >> Click on "Export Connection key" button to the Export key.

7) Import the connection key from the above step to the 64-bit DSN.

Please use the DSN "QuickBooks Online Data" of type 64-bit using 64-Bit ODBC Data Source.
You can import the connection key:
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 64-bit QODBC Online Data Source>> Go To "System DSN" Tab>> "QuickBooks Online Data" of type 64-bit >> Click "configure">> Switch to General tab, click on "Import Connection key" button.

Refer to the knowledgebase article at https://qodbc.com/links/2658 Section "Import / Export connection key:"

8) Use the QODBC Online 64-Bit Test Tool to test the connectivity.

Tags: QuickBooks Online, QBO, Authorization, Authentication, 401, Begin Session Failed

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;DFQ=x:\QuickBooks\MyQBcompanyfile.qbw;OpenMode=F;OLE DB Services=-2;OptimizerOn=No;

This works on pc #1 if the QuickBooks file is open OR closed (desired behavior).

This ONLY works on pc #2 if the QuickBooks File is closed.

Why is it operating differently?? I am using the same access file on both pc.

I need this to be running on some computers in the office. Some computers are allowed to use QuickBooks, and some are not, so I need a flexible DSN connection string that will work for both circumstances.  

Solution:

Please verify the company file path from the non-working machine. The other computer might be accessing the company file mapped via network name, i.e., \\servername\folder\ (e.g., \\QuickBooksfileserver\QuickBooks\MyQuickBookscompanyfile.qbw ) when the connection string is using a mapped drive, i.e., x:\

Please start QuickBooks from the non-working machine, log in to the company file, press F2, verify if the file path is the same in the connection string & test again.

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.

Ms Access file connects to QuickBooks via the connection string:

"ODBC;Driver={QODBC Driver for QuickBooks};DFQ=D:\QuickBooks\MyCompanyFile.qbw;OptimizerOn=No;IAppAutomaticLogin=n;OpenMode=F;OLE DB Services=-2;"

Connection works.

Another user tries to log in as "Admin" into QuickBooks on another computer, but it fails and says already logged in. See screenshot.



Why is QODBC using the "Admin" account?? I was expecting it to use the "odbc" account.

Solution:

You need to remove QODBC integrated application entries in QuickBooks & companyfilename. lgb and delete it to resolve the issue.

Please refer to Connection Lost Between QODBC and QuickBooks. QuickBooks Asking for Password for more details.

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 the invoice table?

If a customer orders some items, we create an invoice. Then, if some things are back-ordered, when the packing slip is made, it shows they were not sent out, and that item is back-ordered. I was looking for a query or an SP that would lead me to only invoices that have not been completely shipped, or all the items in backorder. 

Solution:

All you need to do is use the ItemInventory table to pick up the quantity on hand and compare it with the ordered quantity ordered and invoiced to date (invoiced or not), and call the column "Qty to Pick."

Something along the lines of:

SELECT SalesOrderLine.SalesOrderLIneItemRefFullName as Type, SalesOrderLine.TxnDate as Date, SalesOrderLine.DueDate, SalesOrderLine.RefNumber as Num, SalesOrderLine.CustomerRefFullName as Name, SalesOrderLine.SalesOrderLineQuantity as "Qty Ordered," SalesOrderLine.SalesOrderLineInvoiced as "Qty Invoiced," (SalesOrderLine.SalesOrderLineQuantity - SalesOrderLine.SalesOrderLineInvoiced) as "Qty to Pick," ItemInventory.QuantityOnHand as "Qty Available," ItemInventory.QuantityOnOrder as "Qty on Back Order" FROM SalesOrderLine, ItemInventory where SalesOrderLine.SalesOrderLineItemRefListID = ItemInventory.ListID and SalesOrderLine.IsFullyInvoiced = FALSE and SalesOrderLine.SalesOrderLineInvoiced < SalesOrderLine.SalesOrderLineQuantity

Problem Description:

All our products are Inventory Assemblies. 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 kept outside of QuickBooks, to make sure QuickBooks has the latest inventory data.

I have successfully inserted ItemInventoryAssemblyLine entries, but now I'm trying to update the Quantity information on parts for existing assemblies.

To do the update, I need to join a local table I've created from the external inventory data, which I've loaded with the ListID, ItemInventoryAssemblyLnItemInventoryRefListID, and FQPrimaryKey values from ItemInventoryAssemblyLine, in order to facilitate joins.

Here's my SQL (from MS Access Query Designer):

UPDATE ItemInventoryAssemblyLine INNER JOIN Assemblies_with_PK ON ItemInventoryAssemblyLine.FQPrimaryKey = Assemblies_with_PK.FQPrimaryKey SET ItemInventoryAssemblyLine.ItemInventoryAssemblyLnQuantity = [Qty];

"Qty" is a column in the local table with the updated Quantity that I'd like to edit into the QuickBooks table.

When I run this query, I get the dreaded "Error parsing complete XML return string." error. I have tried alternatively joining on the combination of ListID and ItemInventoryAssemblyLnItemInventoryRefListID instead of FQPrimaryKey with the same result.

I have updated Custom Fields in the parent ItemInventoryAssembly table using the same approach with no problems.

Any help with this would be greatly appreciated! 

Solution:

Please update your QODBC to the latest QODBC version and test again.

Please click here to get the latest QODBC version.

If you are still getting the same error after updating the version, I kindly request you to please raise a support ticket to the QODBC Technical Support department by clicking here.

Problem Description:

I am trying to connect to QuickBooks using QODBC. At a time of connection, QuickBooks Displays a Revoked Certificate Warning.

Solution:

Please update your QODBC to the latest QODBC version and test again.

Please click here to get the latest QODBC version.

If you are still getting the same error after updating the version, please check the digital signature of the application you are using, like Crystal Reports or Excel.

For Example:

If you are using MS Excel and getting errors, please check the digital signature in MS Excel.

Open the task manager, right-click on EXCEL.EXE, and click on Open File Location.

You will be navigated to the application location. In this example, we will navigate to the Excel exe location.

Once we reach the exe location, we must right-click on the exe and click on Properties. We will right-click on EXCEL>EXE in this example and click on Property.

Navigate to the Digital Signatures tab & click on Details.

Click on View Certificate to see certificate validity details.

You can see the certificate validity details.

If your application's Digital Certificate validity has expired, you need to get in touch with the Application Vendor to resolve the issue.

Windows Enforcement of Authenticode Code Signing and Timestamping

On Windows 7 platforms and above, the following are new guidelines from Microsoft:

• Microsoft will no longer allow new SHA-1 code signing certificates to be used for signing files on Windows 7 and above starting Jan. 1, 2016.
• Files signed previously with an SHA-1 certificate will continue to work on Windows 7 and above for an additional year as long as they were time-stamped before January 1, 2016.
• Windows 7 and Server 2008 R2 will require a hotfix for SHA-256 support.
• Starting on Jan. 1, 2017, all SHA-1 signed files would be blocked on Windows 7 and above, regardless of when they were time-stamped.

On Vista and Server 2008 platforms:

• Microsoft will execute end-of-life support for Windows 2004 in April 2017 and Server 2008 in January 2020.
• SHA-1 certificates will be supported through the end-of-life for these operating systems.
• There will be limited support for SHA-256 on these platforms, requiring a patch and only allowing downloads or installations of SHA-256 signed content.

Microsoft TechNet:
Windows Enforcement of Authenticode Code Signing and Timestamping

On Vista and Server 2008 platforms:
Microsoft Authenticode - Dual Code Signing Instructions with SHA1 & SHA256 hashing Algorithm


For more details, refer:
Microsoft Windows SHA-1 & SHA-2 Code Signing Hash Algorithm Support
SSL News
SHA-1 Hash Algorithm Migration for SSL & Code Signing Certificates
SHA-1 Deprecation, On to SHA-2

Also, refer to the following:

Revoked Certificate Errors With QuickBooks Desktop Apps

Problem Description:

How do I share/invite the FLEXquarters Support team to access my QBO company file? 

Solution:

Please follow the steps below to share/invite the FLEXquarters Support team to the QuickBooks Online company file.

1. You can share your QuickBooks Online company file connection key by exporting the connection key.

Please refer to the Import / Export connection key section for more details

2. Email the connection key to the FLEXquarters Support team.

3. Log in to https://qbo.intuit.com/ using your credentials.

4. Click on Company Name & Manage Users.

5. Go to the Manage Users section & click on New.

6. Choose user type, select Regular or custom user & click Next.

7. Set the user's access rights, select All & click Next.

8. Set the user's administrative rights. Select User management to "No," Company Information to "View Only," Subscription & Billing to "View subscription information," & click Next.

9. Enter the FLEXquarters Support team Email address support@qodbc.com, First Name to "QODBC," the Last Name to "Support," & click Next.

Click Finish & you're done.

10. QODBC Support is listed under the Manage Users list as an Invited status.

11. Once we accept the invitation, the status will change to Active.

12. You can delete an invited user by selecting the user & click on Delete.

13. By clicking Invite Accountant, you can share/invite the FLEXquarters Support team to access the QuickBooks Online company file from the Accounting Firms section.

14. Enter the FLEXquarters Support team Email address support@qodbc.com, First Name to "QODBC," the Last Name to "Support," & click Next.

Click Finish & you're done.

15. QODBC Support is listed under the Accounting Firms list as an Invited status.

16. Once we accept the invitation, the status will change to Active.

17. You can delete an invited user by selecting the user & click on Delete.

Tags: QuickBooks Online, QBO, QODBC Online

Problem Description:

I am trying to connect to QuickBooks/QuickBooks POS using QRemote Server, but QRemoteServer is Having on Connection.

Solution:

Please check if QuickBooks or QuickBooks POS has a Certificate Popup.

In the QuickBooks POS certificate, pop-up windows go in the background, so you need to check the pop-up window in the background & accept the certificate.

OR

Try Restarting QRemoteServer.
Close/Exit QRemoteServer. You can find the QRemote Server running in the System Tray.

Go to File Menu -> Exit -> :

Do you want to close the QRemote Server? You will get a pop-up window with the message. Please click "OK" to close QRemoteServer.

Please rerun QRemoteServer & test. You can QRemoteServer from:
Start>> All Programs >> QODBC Driver for use with QuickBooks POS >> QRemote Server for QODBC POS

Start>> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC

OR

Remove QODBC POS/QODBC-related certificates from QuickBooks POS/QuickBooks.

Please refer to How to remove QODBC Related Certificates from Integrated Application Under QuickBooks POS for more details.

For more details, please refer to How to remove QODBC Related Certificates from Integrated Application Under QuickBooks.

Restart QRemoteServer using the above steps, connect again,n and accept the certificate.

Problem Description:

How do I update Customer custom fields? 

Solution:

You can update Customer custom fields using the update query.

For Example:
Update Customer Set CustomFieldContract='QODBCJOB1' where ListID='800000F2-1513344011'

And there you have it! To check that everything worked, just do the following:
Select ListID, Name, FullName, CustomFieldContract from Customer where ListID='800000F2-1513344011'

You can check it on QuickBooks, also.

You can insert Custom fields during Customer creation also. Please refer to the sample query for the same.

INSERT INTO "customer" ("Name," "LastName," "FirstName," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalcode," "CustomFieldBDay," "CustomFieldContract," "CustomFieldSpousesName") values('Juniga, Daphne,' 'Juniga,' 'Daphne,' 'Daphne Juniga,' '561 W 4th St.', 'Carlsbad,' 'CA,' '92009', '26/01/1987', 'QODBCJOB1', 'Jack')

And there you have it! To check that everything worked, just do the following:
Select Name, CustomFieldBDay, CustomFieldContract, CustomFieldSpousesName from Customer where Name='Juniga, Daphne'

You can check it on QuickBooks, also.

Problem Description 1:

I recently installed QODBC to get data out of a QuickBooks company file, but I get this error: "Index was outside the bounds of the array."

Problem Description 2:

We are attempting to import tables into SQL Server, which we use as a staging environment. We are not doing any transformations, simply trying to copy the data from QuickBooks into SQL Server using the Import Wizard. Below is the screenshot of the error we receive when attempting to do so. Details of the configuration are provided below.

Source data (QuickBooks): Microsoft .NET provider for ODBC - QODBC 32-bit configured DSN with default settings. Destination Data ( SQL Server): Microsoft OLEDB provider for SQL Server

We are attempting to import four tables for now.

- Account, Class, Entity, Transactions

The reported error below occurs for each table.

Error: Can not load the column attributes from the schema for the current provider descriptor.
- COLUMN_NAME
- TYPE_NAME
- COLUMN_SIZE
- DECIMAL_DIGITS
- COLUMN_SIZE

Solution:

Please follow the steps below to resolve this error:

1. Go to Import Export Wizard by right-clicking on your SQL Server database, clicking on Task, & Click on Import Data.

2. Choose ODBC Data Source & Enter DSN & Connection String, and click "Next."

3. Choose Destination and click on "Next."

4. Choose the second option (Write a query to specify the data to transfer) and click "Next."

5. Write the query and click on "Next." You can change the table name & question as per your requirement.
For Example:
Select * from the Account

6. Change the destination table name to your requirement and click "Edit Mapping."

7. You must verify the date type of all columns from the "Type" column.

You need to make sure that you are not getting 23 in Type. You will get 23 date-type columns.

You need to replace 23 with the current date for all columns whose Type is 23 and click on "OK."

8. Click on "next" until the finish.

Save and Run Package.

Complete the Wizard.

9. You can see the table data imported to SQL Server's local database table.

Keywords: How to use DTS to import QuickBooks Data into MS SQL Server

Please follow the steps to remove QODBC-related certificates from the Integrated Application Under QuickBooks POS:

Try removing all the integrated application entries in QuickBooks POS again via File Menu -> Preferences -> Company -> Integrated Applications Tab.

Switch to Integrated Applications

To know which application uses "QODBC POS" to communicate with QuickBooks POS.

You can check it from the Application ID & File Name.

To Remove, Select "QODBCPOS-APPLICATION," For Example, "QODBCPOS-MSACCESS.EXE," Click Remove and Save.

( If there are Multiple "QODBCPOS-APPLICATION" Listed, you may remove a single Integration at a time.)

Exit QuickBooks POS.

Please start QuickBooks POS and log in again to your Company file.

Problem Description:

I'm trying to link a general ledger report in Excel. I've created the sp_report SQL query in Microsoft Query just fine, but the data it returns contains subtotals and blank lines. I know I can write a script/filter in Excel to eliminate these lines, but I can't figure out what parameters to use to do it using QODBC.

Solution:

In QODBC, you can remove subtotals and blank lines in sp_report by adding the following condition.

where RowType='DataRow'

For example:

sp_report ProfitAndLossDetail show TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisYearToDate' where RowType='DataRow'

Problem Description:

I have a custom program written in Access 2003 communicating with QuickBooks 2013 using QODBC 13, which works fine. Will upgrading to the latest QuickBooks and QODBC require any code changes to the Access 2003 program?

Problem Description:

We are upgrading from QuickBooks Pro 2011 to QuickBooks Enterprise (the latest) and need a developer/consultant to assist in checking/upgrading queries and code to work with the new versions of QODBC/QuickBooks. Our company uses the QODBC Driver for QuickBooks to synchronize data between our Microsoft Access database and QuickBooks. We push customers, jobs, cheques, and items from Access to QuickBooks and pull data from QuickBooks to Access for reports.  

Solution:

It is unlikely that you will have a problem as QODBC is upward compatible with QuickBooks, so the same queries you have running now should continue to work.

Suggest you purchase the latest version of QODBC, which supports the newest version of QuickBooks, then run some tests. If you have any trouble with connections, please refer to the following:

Connecting QODBC Driver to QuickBooks for the First Time (Video)
How to Use QODBC with Microsoft Access 2010 to Build Linked Table(32-Bit)
How to Configure QODBC to Work With 64-bit MS Access

Or Raise a Support Ticket

There shall be no code change required in your MS-Access Application/Queries, and it should be able to work with the latest QuickBooks & QODBC.

Additional Information:
With every release of QuickBooks/QODBC, new fields or features might be added. If you want to leverage the new areas, you will have to re-link the tables in Access. Refer: How to Re-Link QuickBooks Data in MS Access using QODBC.

Please refer to the link below for details about the updated QODBC table & report schema and the relation between tables. You will also get information on each report column parameter that is supported. You can also view all table/column details.

Please refer to QODBC Data Layout for more details.

Problem Description:

I need to use Between, in a range of dates, to return new customers in one period. I try this:

SELECT FullName FROM Customer Where TimeCreated Between {d'2007-12-18'} AND {d'2007-12-19'} AND SalesRepRefFullName='PD'

Is the use of QODBC correct? 

Solution:

The use of BETWEEN here is fine. It's just TimeCreated is a TIMESTAMP, not a date. It should be:

SELECT FullName FROM Customer Where TimeCreated Between {ts'2007-12-18 00:00:00.000'} AND {ts'2007-12-19 00:00:00.000'} AND SalesRepRefFullName='PD'

Problem Description:

I'm trying to make Inventory Items inactive after a sale programmatically or Active after voiding a sale.

I thought the following SQL statement would have worked fine, but I got error 214217887(80040e21), saying the ODBC driver doesn't support this statement.

sSQL = "UPDATE ItemInventory SET ItemInventory.IsActive = True " & _
"WHERE (((ItemInventory.ListID)='ED0000-1173904787'))" 

Solution:

QODBC doesn't support the Table name used in the SET columns. So you need to remove the table name before the column name in the SET condition. For example:

sSQL = "UPDATE ItemInventory SET IsActive = True " & _
"WHERE (((ListID)='ED0000-1173904787'))"

Problem Description:

What is the fastest way to get the number of records from a table? 

Solution:

QODBC would need to scan the whole table to get the record or row count. So you shouldn't do it if you don't need to. If you must do it, run the query against the optimized table using nosync like this:

SELECT count(listid) as "Table Row Count" from Customer nosync

Problem Description:

I am using the following SQL statement:

sp_report JobProfitabilityDetail show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' , EntityFilterFullNameWithChildren = '{?customer}'

I then create a customer parameter in the parameter list. The customer name is pretty long, so is there a way to search by using a wildcard, i.e., *50-126*, rather than having to enter:

Customer name. location.50-126.projectdescription??

Solution:

Sorry, you can't use wildcards, and you need to use the full name path for a specific job:

sp_report JobProfitabilityDetail parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Abercrombie, Kristy:Remodel Bathroom', SummarizeColumnsBy = 'TotalOnly'

If you wanted, for example, to compare all Remodel Bathroom (or 50-126) jobs for all customers, you would need to run the Job Profitability detail report for each customer to a data file (like an append query in MS Access) and report from the data file instead.

Problem Description:

I've explored the related fields, but cannot determine how a line on the invoice is linked to the estimate.

I'm designing a report that needs to see the item on the estimate and how much has been billed out and retained for each item (as well as other related data). 

Solution:

Please refer to How to convert a Sales Order into an Invoice for the basics of how an Estimate can be made into a Sales Order and an Invoice.

The InvoiceLinkedTxn table shows Payments, Credit Memos, and Deposit Line Items, but doesn't include reimbursements, transfers from Sales Orders, or Estimates (depending on your QuickBooks version). But in QuickBooks, you probably understand it better as the History of an Invoice. The number of InvoiceLinkedTxn lines to Invoice lines won't match where multiple payments or deposits have been made.

To better understand, refer to: How to use InvoiceLinkedTxn query using QODBC.

While the EstimateLinkedTxn table shows all the transfers to Invoice or Sales Order; but does NOT include any transfers to PO.

Problem Description:

I created a report using a custom set with the customer as the parent and InvoiceLine as the child, giving me items ordered in the past.

My problem is that I only want the last time an item was ordered and want to eliminate some things, such as shipping.

When I try to filter out items like shipping, it also takes out any invoice line items.

I'm at a complete loss when it comes to getting only the last time an item was ordered.  

Solution:

You can get items ordered by a customer, in part name order, with the last date ordered first,t by using the below query:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian: Kitchen' and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc



Or for the last time an item was ordered by a customer:

SELECT Top 1 TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName = 'Appliance' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc

Problem Description:

We are involved in a new project with our bank involving a 'Positive Payment' solution that requires I send them a file with the check summary data each time we do a check printing run. The example layout I received from this looks like this: (best viewed with a fixed font)

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8

RECONCILIATIONHEADER0075207990012345600000001000000005
20799001234560000071909000005100020040512 Abelman, Frayne & Schwab
20799001234560000071910000000200020040512 American Express
20799001234560000071911000000200020040512 Arizona Dept. of Revenue
20799001234560000071912000000200020040512 Arrow Electronics Inc.
20799001234560000071913000000200020040512 ASCOM Hasler Mailing Sys

I can manage the formatting if I can get the data into Access, but I need to grab the check number, check amount, issue date, void indicator, if that's available, and the payee name.

Can I do this? 

Solution:

If you are paying bills each day and printing them in a daily batch, then you can try looking at the following:

SELECT RefNumber, Amount, TxnDate, Memo, PayeeEntityRefFullName FROM BillPaymentCheck where BankAccountRefFullname='Checking' and IsToBePrinted is NULL and TxnDate={fn CURDATE()}