VBA:OpenRecordset .AddNew 方法运行缓慢 [英] VBA: OpenRecordset .AddNew method runs slow

问题描述

我有一个 Access 应用程序，其中有一个表 (AuditTrail) 链接到 SQL Server 2008.我正在尝试以编程方式将记录添加到审计跟踪表中.

I have an Access application which has a table (AuditTrail) linked to SQL Server 2008. I am trying to add records to the audit trail table programmatically.

我有以下代码:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("AuditTrail", dbOpenDynaset, dbSeeChanges)

    With rs
        .AddNew
        rs("dtDateTime") = Now()
        rs("txtComment") = Nz(Mycomment, "")
        .Update
        .Close
    End With

Set db = Nothing
Set rs = Nothing

我最近注意到的问题是运行 .AddNew 语句需要几秒钟(最多 13 秒).

The problem that I have noticed recently is that it takes several seconds (up to 13 seconds) to run the .AddNew statement.

该表现在大约有 50 万条记录.

The table has about half a million records by now.

有什么办法可以降低这个时间吗?

Is there any way I can lower the this time?

推荐答案

使用您当前的代码，您会无缘无故地打开整个表格.有几个选项.
一种是添加一个不会返回任何记录的 where 子句.类似
选择 dtDateTime, txtComment FROM AuditTrail WHERE <yourIdField>= -1.

With your current code, you are opening the entire table for no reason. There are a couple of options.
One is to add a where clause that will return no records. Something like
Select dtDateTime, txtComment FROM AuditTrail WHERE <yourIdField> = -1.

第二个(我的偏好)是根本不使用记录集.使用插入语句.

The second (my preference) would be to not use a recordset at all. Use an insert statement.

Dim strSql as String strSql = "INSERT INTO AuditTrail (dtDateTime, txtComment) Values(#" & Noe() & "#,'" & nz(MyComment,'') & "')" 
Db.Execute strSql, dbFailOnerror + DbSeeChanges`

这篇关于VBA:OpenRecordset .AddNew 方法运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！