Irregular read-only errors

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: CraigB (kaldvelski_at_yahoo.co.uk)
Date: 08/02/04 

Date: 2 Aug 2004 09:04:46 -0700

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

Relevant Pages

  • Re: Locking question when using Select clause with For Update and Skip locked
    ... This is working fine in SQL Server and multiple session can get the ... But in Oracle the first session only return 1 row but locks all the ... It looks like both the session got the ROW-X lock but one session is ...
    (comp.databases.oracle.server)
  • Re: OLE DB Cursors
    ... You don't do this with properties in the SQL Server OLE DB provider. ... client-side cursor, otherwise you have a server-side cursor managed from the ... I have to do a lot of updates() here, ... > its optimistic lock / client cursor. ...
    (microsoft.public.data.oledb)
  • Re: How does CREATE INDEX impact current users?
    ... take an X lock on the table, thus preventing any access to the table at all. ... This problem is alleviated in SQL Server 2005 with the concept of online ... Creating a clustered index does not copy the table per se - the heap ...
    (microsoft.public.sqlserver.server)
  • Re: Hung up using a SELECT MAX when an alter database has been execute in a SQL Server database
    ... exclusive lock, as it does for the ALTER TABLE statement. ... see "Isolation Levels" in SQL Server Books Online. ... Public Sub StartConnection() ... Set objRstSel = New ADODB.Recordset ...
    (microsoft.public.data.oledb)