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
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
- The invoices are produced
- The invoices are printed and sent to customers (by the same person who produced the invoices)
- A subsequent check can't find the invoices in the invoices table.
- 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
- Invoices are produced and commitTrans happens but the transaction information is not flushed
- The invoices are printed
- 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.