Re: Fastest record create method with JET 4.0
- From: "Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom>
- Date: Tue, 12 Sep 2006 15:29:41 +0100
Basically, all my application does with the Access database is connect,open
the table, add a record, fill in the field values, save the record, closethe
table and disconnect. (I actually add lots of records in each table open/
connection, but that isn't the point.)
Actually it is the point.
What I do directly depends on the number of records being inserted.
Amazing that you dismiss that.
If you adding a few records or 1-at-a-time (slowest) then a Connection
Execute is best with "INSERT INTO yourtable VALUES ( ...)". That is faster
than constructing a Recordset.
If you adding a many records then I can think of 3 approaches which never
having timed with Access I would have to check
(i) Using an Access query and calling it via a persistent Command object
with parameters (so SP approach)
(ii) Using a adhoc SQL INSERT statement with a persistent Command object
with parameters (so temporary SP approach).
With both above be sure to do "SET NOCOUNT ON" and call the Command object
with options set saying there is no Recordset returned.
(iii) Using a client-sided Recordset with UpdateBatch and a SQL command like
"SELECT fld1,fld2,fld3... FROM yourtable WHERE 1=2". You get an empty
Recordset which you can do many AddNew's and finally UpdateBatch.
Last I checked, what you cannot do with Access is present multiple
statements at once unlike SQL Server.
Stephen Howe
.
- Follow-Ups:
- Re: Fastest record create method with JET 4.0
- From: Bob Barrows [MVP]
- Re: Fastest record create method with JET 4.0
- Prev by Date: Re: Data from MS SQL db to Excel
- Next by Date: Re: Fastest record create method with JET 4.0
- Previous by thread: Re: Fastest record create method with JET 4.0
- Next by thread: Re: Fastest record create method with JET 4.0
- Index(es):
Relevant Pages
|
|
