Why ADO hangs after 3000 records while inserting or updating ?

[Ken]

Hi,

The table has more then 1,000,000 records. The ado hangs for 2 - 5 minutes after update or insert 3000 record. Only option is to move those updated or inserted record, then it runs fast.

Field datatype - Text - length 50
Why does ado hangs after 3000 record with table having more then 1,000,000 records ?

[highlight #FCE94F]How to update or insert ado without hanging as no response ?[/highlight]

Please advise.

Thanks,

Ken

 

[Andrzejek]

Could you share the VBA code to represent the issue?
And what do you mean by: "move those updated or inserted record"?


---- Andy

There is a great need for a sarcasm font.

 

[sxschech]

Since you mentioned ADO not sure if below will work since the code I use is for DAO or if this is the same issue.

'This line allows processing more than 9000 records without
    'getting the dreaded "Run-Time error '3052' ;
    'File sharing lock count exceeded. Increase you MAXLocksPerFile Registry Entry"
    '[URL unfurl="true"]http://www.access-programmers.co.uk/forums/showthread.php?t=185985[/URL]
    'First link Method 2 from Post #4
    '20150529
    DAO.DBEngine.SetOption dbMaxLocksPerFile, 800000

 

[lameid]

I have seen several posts that suggest that working with small chunks of data with SQL server is better than writing one big block for performance... Since ADO is appropriate for SQL it may be ADO that is the issue. This may fall into the category of it is easier to fix than to explain.

Edit: To clarify I mean you would write or commit records very frequently vs. trying to batch load say from a disconnected recordset that you reconnect.

 

[Andrzejek]

you would write or commit records very frequently

That's why I asked for code.
If it is in the loop, COMMIT every 500 records or so should do the trick.


---- Andy

There is a great need for a sarcasm font.