[QODBC Desktop] MS Access + QODBC: Date Literals, Connection Methods, and FQSaveToCache Caching

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: