How long does Access keep unflushed transactions locally?

Question

As part of an large (250mb fron end file) Microsoft Access application I maintain, I have a section that does Invoicing. All tables are attached dynamically at application start to a SQL Server backend database.

Invoices are achieved via a two stage process. Queries to other parts of the database pull together the information needed and create a temporary table inside the Access front end with, what is the information that will need to be placed into the invoice.

The second stage of the process then opens a transaction on the Access Database Engine, and produces the invoices through the linked tables. Here are some relevant snippets of code.

We first use separate application level locks to keep other users away. These routines use a separate database table and pass through queries to prevent other users coming past this (or any other point in the application that might be relevant)

getLock "Invoice"
getLock "Item"

We also need to check that just before we get to this point someone else didn't do the same thing, and abort if they have.

stage = "Pre Transaction"
Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb
in_trans = True
ws.BeginTrans
stage = "check no one else did this invoicing whilst we were thinking"
SQL = "SELECT i.ID FROM tmpUKRepeatInvoices i INNER JOIN dbo_RepeatInvoicing ri ..."
rs.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
    rs.Close
    ws.Rollback
    releaseLock "Item"
    releaseLock "Invoice"
    DoCmd.Hourglass False
    MsgBox "Some else has already completed this. ..."
    GoTo Trans_Exit
End If
rs.Close

We can't use auto increment for the Invoice and Item number fields because the Invoice table has an audit trigger on it, and Access gets screwed if we do that. So we do it programatically

stage = "Get Invoice and Item Nos"
SQL = "SELECT Max(InvoiceNumber) AS MaxInvNo FROM dbo_Invoice"
rs.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
CurrentInvoiceNumber = rs.Fields("MaxInvNo")
rs.Close
SQL = "SELECT Max(ItemID) As MaxItemNo FROM dbo_Item"
rs.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
CurrentItemNumber = rs.Fields("MaxItemNo")
rs.Close

This is the meat of the invoicing. Tables with dbo_ at the front of their name are attached tables

stage = "Create Invoice Table Entries"
SQL = "INSERT INTO dbo_Invoice ..."
db.Execute SQL, dbFailOnError
stage = "Create Item Table Entries"
SQL = "INSERT INTO dbo_Item ..."
db.Execute SQL, dbFailOnError
stage = "Update Repeat Invoicing Table"
SQL = "UPDATE dbo_repeatInvoicing c INNER JOIN tmpUKRepeatInvoices i ON ..."
db.Execute SQL, dbFailOnError
stage = "Remove Entries from Temp Table"
SQL = "DELETE FROM tmpUKRepeatInvoices WHERE HoldInvoice = 0"
db.Execute SQL, dbFailOnError
stage = "Complete Transaction"
ws.CommitTrans

I have subsequently changed the last statement above in an attempt to mitigate the problem I am about to describe to

ws.CommitTrans dbForceOSFlush

it is too early to say whether that has helped.

And then finally we release the locks

releaseLock "Item"
releaseLock "Invoice"
in_trans = False

After this code is complete, and the transaction supposedly committed control is returned to the user form. There is a separate button to print the invoices which when clicked dynamically produces a pass through query accessing the Invoice and Item tabls and stores it in a query def of a particular name. The code then calls a report (in essence the invoice form) based on that query to produce a print preview of all the invoices. They are printed from the print preview.

As mentioned above, there is a update trigger on the Invoice table which records in an audit table the DELETED. part of the invoice.

The occasional (once every couple of months with daily use) bug is that

1. The invoices are produced
2. The invoices are printed and sent to customers (by the same person who produced the invoices)
3. A subsequent check can't find the invoices in the invoices table.
4. The Audit table shows no records associated with the missing invoices.

For obvious reasons I am trying to envisage what scenario could possibly cause this to happen. The ONLY scenario I can come up with (and that seems remote) is that

1. Invoices are produced and commitTrans happens but the transaction information is not flushed
2. The invoices are printed
3. For some reason (unexplained), but maybe something to do with the update to repeatingInvoices near the end (already locked and times out?) the transaction is rolled back from the unflushed commit.

Unfortunately I can find very little information about dbForceOSFlush and in particular what Access might be doing behind the scenes when it is NOT used. Can anyone confirm whether my scenario of what is happening is feasible? Is there any other scenario that could be feasible to cause the symptoms I am seeing.

Answer 1

Rather than using dynamically attached tables I'd suggest

Creating a Connection Object to implement as direct ODBC link to the SQL Server Database.

This will remove a layer of abstraction and potential confusion as when you open a transaction you'll know that it's directly created on the SQL Server Table. It should also help speed up your code.

You don't say which version of Access you're running but the bad news for a developer who is used to using DAO Objects is that Microsoft say

"ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine."

So even if you're not using Access 2013 it might be better to future proof your code by doing this with ADO.Connection

http://msdn.microsoft.com/en-us/library/office/jj249940(v=office.15).aspx