ms access - VBA: OpenRecordset .AddNew method runs slow

Question

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.

I have the following code:

    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

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

The table has about half a million records by now.

Is there any way I can lower the this time?

Answer 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`