Rules

Secret ingredients to quality software

Microsoft Access - Do you know the fastest way to add a new record in a large table?

Last updated by Tiago Ara├║jo [SSW] on 14 Jun 2021 10:35 pm (6 months ago) See History

When adding a new record should we use ADO rst.AddNew or a SQL INSERT statement?

The INSERT statement will give you better performance because you don't have to create and pass around a recordset object. However the Recordset object gives you protection from data conflicts by raising an error if the user tries to update a record that has been modified by another user. Also on an error the Recordset object will crash out on the offending line, so you can find the problem easier. And when dealing with a memo field, which may contain single quotes or double quotes the AddNew method is easier to construct. But in general if you can do without the data conflict protection and want the performance then use the ADO connection object to execute a SQL statement.

Adam CoganAdam Cogan

We open source. Powered by GitHub