sql-server - Access 在本地保留未刷新的事务多长时间？

Question

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

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

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

我们首先使用单独的应用程序级锁将其他用户拒之门外。这些例程使用单独的数据库表并传递查询以防止其他用户通过此(或应用程序中可能相关的任何其他点)

getLock "Invoice"
getLock "Item"

我们还需要检查，就在我们到达这一点之前，其他人没有做同样的事情，如果他们做了，就中止。

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

我们不能对 Invoice 和 Item number 字段使用自动递增，因为 Invoice 表上有一个审计触发器，如果​​我们这样做，Access 就会搞砸。所以我们以编程方式进行

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_ 的表是附表

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

我随后更改了上面的最后一个陈述，以试图减轻我将要描述的问题

ws.CommitTrans dbForceOSFlush

现在说这是否有帮助还为时过早。

最后我们释放锁

releaseLock "Item"
releaseLock "Invoice"
in_trans = False

此代码完成后，假定已提交的事务控制权返回给用户表单。有一个单独的按钮用于打印发票，单击该按钮会动态生成一个通过查询 Access 发票和项目选项卡并将其存储在特定名称的查询定义中。然后代码根据该查询调用报告(本质上是发票表格)以生成所有发票的打印预览。它们是从打印预览中打印出来的。

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

偶尔(每天使用时每两个月一次)的错误是

1. 制作发票
2. 发票被打印并发送给客户(由出示发票的同一个人)
3. 后续检查无法在发票表中找到发票。
4. 审计表显示没有与丢失的发票相关的记录。

出于显而易见的原因，我正在尝试设想什么情况可能会导致这种情况发生。我能想出的唯一场景(这似乎很遥远)是

1. 已生成发票并发生 commitTrans，但未刷新交易信息
2. 打印发票
3. 出于某种原因(无法解释)，但可能与接近尾声的 repeatingInvoices 更新有关(已经锁定并超时？)事务从未刷新的提交中回滚。

不幸的是，我找不到关于 dbForceOSFlush 的信息，尤其是 Access 在不使用时在幕后可能做了什么。谁能确认我对正在发生的事情的设想是否可行？是否有任何其他情况可能会导致我所看到的症状。

Answer 1

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

创建一个连接对象以实现作为到 SQL Server 数据库的直接 ODBC 链接。

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

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

“Microsoft Access 2013 不支持 ODBCDirect 工作区。如果您想在不使用 Microsoft Access 数据库引擎的情况下 Access 外部数据源，请使用 ADO。”

因此，即使您不使用 Access 2013，也可能更好地通过使用 ADO.Connection 来验证您的代码

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