Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

ms access - VBA: OpenRecordset .AddNew method runs slow

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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`

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...