[QODBC ALL] MS Access + QODBC: Editing, Validation, and Rollback Behavior with Linked Tables

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: