Recordset prob: table already opened exclusively

  • Thread starter Thread starter Matthew Ward
  • Start date Start date
M

Matthew Ward

Hi,
I'm trying to open a recordset against a temporary table in an Access 2000
app and an coming up against an error message. The full error message is: -

"3008 - The table 'tmpDatesInRequest' is already opened exculsively by
another user, or it is already open through the user interface and cannot be
manipulated programmatically".

The line that's causing the error is: -

Set rstLeaveDates = CurrentDb.OpenRecordset("tmpDatesInRequest",
dbOpenTable, dbDenyWrite, dbPessimistic)

Firstly there are no other users of the database, it's currently a single
mdb with no user level security (therefore no users other than admin), plus
it's on my laptop so not available to anyone else to load. I've checked to
ensure that any recordsets opened against the table prior to the line above
have been closed by the .close method, and that the only form which is
linked to the 'tmpDatesInRequest' table is no longer open.

The previous recordsets used against the table were opened using the
following line, and if I change my line above to match, then it works. Only
thing I'm concerned about is that the database is intended to become
multi-user, hence why I wanted to obtain an exclusive lock with this
particular recordset.

Set rst = CurrentDb.OpenRecordset("tmpDatesInRequest", dbOpenTable)

Also something I'm finding weird is that if I debug the code and use F8 on
the line in question, it executes fine and the program continues as desired.

I'm rapidly losing my hair & sanity on this one so any help would be much
appreciated :)
 
Hi,


You may have open the table in design view, which has as side effect, to
lock it, to your second user, your VBA running code. Always keep in mind
that your VBA code 'is' another user, since it can modify the values in the
records, and is definitively a different entity than you, at the keyboard,
who can also modify tables and values in the records! You though you were
alone... but you are not :-)



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top