recordset.Update extremely slow

[TooGoon]

Anytime I use:
Dim rst as recordset
Set rst = currentdb.openrecordset(tblName)

rst.edit/addnew
...
rst.update

The operation takes an exponential amount of time. Currently for a table with 600 records in it, with one field being edited, with a small amount of text being inserted, I've been waiting for a half hour. Yet, were I to make a quick form, and whip up a script, such an operation would take less than a second.

Am I doing something wrong? Is there anything I can do to speed up adding/editing record information via code?

thx

 

[RoyVidar]

Queries will usually perform faster, and is often recommended over using recordsets.

For instance something like this

[tt]dim strSql as string
strSql="update mytable set somefield = somevalue where..."
currentdb.execute strSql[/tt]

Half hour - seems like there is something wrong somewhere. It shouldn't take that long with so few records. Have you compacted/repaired lately?

Roy-Vidar

 

[payback]

You are opening the whole table. Try something like -

dim rst as recordset
dim strCriteria as string

strCriteria = "SELECT fieldname FROM tablename WHERE fieldname = 'yourcriteria';"

set rst = currentdb.openrecordset(strCriteria)

do until rst.eof
rst.edit
'your code goes here
rst.update
loop
rst.close

I generally use code for table updates because I can also produce a status box showing the user how far within the job they are.

 

[TooGoon]

Ok
By changing a few things I've been able to bring it down to a minute. But that is still really slow so I'll give these a go.

Gracias