Irregular read-only errors

[CraigB]

I'm getting the infamous "Cannot update. Database or object is
read-only" error, however I'm very unsure as to what could be causing
it.

My best guess is the db seems to becoming locked for a shortwhile as
the problem seems to rectify itself after a while. If I wasn't using
access I'd guess it was a deadlock but if Access is single-threaded
then that wouldn't be possible, would it?

I know it's not a permissions issue because the site runs fine most of
the time, so the db can be updated, and the problem isn't restricted
to any particular page - once the db's locked any page with an update
fails.

The site doesn't experience any heavy load so I don't think it's just
Access being crap at multi-user connections but it's got to be
something quite specific as I've been unable to deduce a specific
cause or replicate it deliberately.

I can only assume that something is leaving the db in an unclosed
state. I'm guessing the actual error is being caused by the provider
not allowing me to open a recordset with the correct cursor and lock
type and so I'm only getting a read-only recordset which is blowing up
when I call update? But what's locking it in the first place is a
mystery to me.

Could someone someone confirm how IIS loads Access into memory. I read
somewhere that if there was an error during page execution or timed
out before my ado code could complete (clean up particularly) then
this might create a lock??

FYI I'm using Access 2K through OLEDB 4.0, with ASP 3.0 on IIS 5.

I'm about ready to switch to using SQL Server (which I know I probably
ought to do anyway) but some of my SQL is rather tortuous so it won't
be an easy switch so if I could sort out Access for the time being
that would be preferrable.

Would setting the ADO connection mode to adModeReadWrite accomplish
much?
Would using a command object with an "UPDATE statement" be a better
bet than using a recordset's update() method?

I hope that's enough info to be starting with

Thanks in advance

Craig

[Aaron [SQL Server MVP]]

> Would setting the ADO connection mode to adModeReadWrite accomplish

> much?

Maybe. Did you try it? You can do that faster than you can wait for
answers here...

> Would using a command object with an "UPDATE statement" be a better
> bet than using a recordset's update() method?

Yes! Think about it, you open a recordset object, which locks the table
temporarily. An UPDATE statement goes in, tweaks a single row, and gets
out.
[url]http://www.aspfaq.com/2191[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)