Access .addnew speedup to SQL Server

Question

Post reply

Access .addnew speedup to SQL Server

Martin Bastable

SSCommitted

Points: 1766
More actions
February 23, 2006 at 2:55 am

#68290

Hi there all
Ive a small snippet of code...
Set logrs = conPubs.OpenRecordset("Log", dbOpenDynaset, dbExecDirect, dbOptimisticValue)
logrs.addnew
etc.etc.
logrs.update

Ive been using that method for access based adding of new records to sql server linked table for donkeys years - as have any examples and documentation I have read.

Recently it started failing.
The log table im using has grown to 2.5million records. The Set logrs = line started to fail to open the Log table for adding new records. It would take ages to open, then relevant time outs and failures would occure. Sometimes it would work - randomly.

So I wonder to myself.... what if.....
I change my line of code from
Set logrs = conPubs.OpenRecordset("Log", dbOpenDynaset, dbExecDirect, dbOptimisticValue)
to
Set logrs = conPubs.OpenRecordset("SELECT * FROM Log WHERE LogId = -1", dbOpenDynaset, dbExecDirect, dbOptimisticValue)

And what do you know - opens straight away no delay no pause no problems.

That suprised me. it got me thinking, does opening a table with rs.openrecordset("Tablename", etc.) in access bring down the entire table dataset before you even access it, or more importantly, even if you arent going to read any data in it at all but just add to it? Is this just with linked tables? (Which would be why it was failing here, if out network was too slow to pass all the data in time to miss timeouts triggering)
Can I use this method to speed up other bits of my code!
Ive a few ideas whats happening, but does anyone else out there have any insights into what might be going on?

Many thanks

Martin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

Surely you can speed it up this way.

To speed it further up, issue the proper sql statement instead of using .addnew, .update,.delete

This is a great site concerning does & don't in msaccess

http://www.mvps.org/access/

Martin Bastable SSCommitted Points: 1766 More actions · Answer 2

i normally do it using faster direct sql access, but sometimes the ms access way is quicker and easier to get a job done in very short space of time (thats quicker development time wise, not quicker in execution of funcionality - before anyone chomps my head off )

Im still interested to know why opening a table without accessing the data in it is so slow - when its a documented method of opening a recordset to add data.

many thanks

martin

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

Freely translated from Access97 help:

The position of the new record depends on the type of Recordset:

If the type is dynaset, the new record is added at the end.

If the type is Table and the property Index is set, all records are retrieved in their sortorder. If you didn't apply the index property, all records at the end of the recordset will be retrieved.

Note:

When one uses the method Addnew in a Microsoft-Jet environment AND

the database-engine has to make a new "page" for the current record, the whole page is locked. If the new record fits on an existing page, the page is locked partially.