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

Last updated by Brady Stroud [SSW] about 2 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 Cogan
We open source. Powered by GitHub