Access将本地未处理的交易保留多长时间? [英] How long does Access keep unflushed transactions locally?

问题描述

作为我维护的大型(250mb fron最终文件)Microsoft Access应用程序的一部分，我有一个进行发票处理的部分.在应用程序启动时，所有表都动态地附加到SQL Server后端数据库.

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.

发票是通过两个阶段的过程获得的.对数据库其他部分的查询将所需的信息汇总在一起，并在Access前端内部创建一个临时表，该表将需要放入发票中.

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.

然后，该过程的第二阶段在Access数据库引擎上打开一个事务，并通过链接的表生成发票.这是一些相关的代码片段.

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

我们不能在发票"和项目编号"字段中使用自动递增，因为发票"表上有一个审计触发器，如果​​这样做，Access就会搞砸了.所以我们以编程方式做到

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

这是发票的内容.名称前面带有dbo_的表是所附表

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.

最后我们释放锁

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.

如上所述，发票表上有一个更新触发器，该触发器在审计表中记录DELETED.发票的一部分.

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. 已生成发票
2. 将发票打印并发送给客户(由产生发票的同一人)
3. 后续检查在发票表中找不到发票.
4. 审核"表没有显示与缺少发票相关的记录.

出于明显的原因，我试图设想出哪种情况可能导致这种情况发生.我唯一能想到的场景(似乎很遥远)是

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. 已生成发票并发生commitTrans，但未刷新交易信息
2. 发票已打印
3. 由于某种原因(无法解释)，但可能与更新快要结束的重复发票(已经锁定并超时?)有关.事务从未取消的提交中回滚.

不幸的是，我几乎找不到关于dbForceOSFlush的信息，尤其是当不使用Access时，幕后情况可能会怎样.谁能确认我所发生的情况是否可行?是否还有其他可能导致我看到的症状的情况.

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.

推荐答案

我建议不要使用动态附加的表

Rather than using dynamically attached tables I'd suggest

创建一个连接对象以实现与SQL Server数据库的直接ODBC链接.

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

这将消除抽象层和潜在的混乱，因为当您打开事务时，您将知道它是直接在SQL Server表上创建的.它还应该有助于加快您的代码.

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.

您没有说您正在运行哪个版本的Access，但对于习惯于使用DAO对象的开发人员来说，坏消息是微软说

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

"Microsoft Access 2013不支持ODBCDirect工作区.如果要在不使用Microsoft Access数据库引擎的情况下访问外部数据源，请使用ADO."

因此，即使您不使用Access 2013，也最好通过使用ADO.Connection进行将来的验证，以证明您的代码

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

这篇关于Access将本地未处理的交易保留多长时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！