Releasing recordset lock

[Guest]

Hello,

I am trying to lock a table in Access 2003, then perform an operation and
then release the lock. The first time I attempt to aquire the lock with,

Set objTableRst = CurrentDb.OpenRecordset(strSQL, dbOpenTable, dbDenyWrite,
dbPessimistic)

it is successful, but the second time the same code is executed I get error
3008.
I have checked for open snapshot type recordsets and there is none.

I am wondering if since the Access VBA form has lists populated by queries
at startup, it is holding a lock in it's address space that is getting
escalted from read to read/write? Or what else could possibly be causing
this?

Thanks, for any advice

 

[Guest]

Locks are released by a low priority back ground process

Try using
Application.dbengine.Idle

to see if you can boost the background process priority and
release the locks.

 

[Guest]

Thanks David,
I went ahead and tried the idle method but got the same result. I'm just
testing
write now so there is only me accessing the application.

Do you know what is the best reference on multiuser applications in Access, or
where I could get more information on this? I have read the Office 2000
Visual
Basic Programmer's Guide but it does not seem to help me with this issue.

 

[Guest]

MS released a couple of white papers on locking for Access 2,
Access 95 and Access 97. And the white paper on multi-user
performance for Access 97 (32 client pc's) included information
on locking.

But with Access 2000 MS silently depreciated multi-user access
for Jet files, and released an integrated SQL Server back end
for Access. AFAIK, they've never released any information on
locking for Jet 4.0, other than updating the KB article on concurrent
autonumber schemes.

You also need to know that Windows 2000 had new networking
components, which were only gradually fixed over the next 3 or
4 years: if you are having locking problems, you need to make sure
that all operating system patches are applied.

You should search for the A95/A97 white papers (sorry, I don't
have the URL's). But I can't think of anything that would be relevant,
just make sure you are closing and releasing the recordset.

(david)

 

[Guest]

Thanks David!

I'm pretty sure that the bound querries in the Access form is holding
a shared read lock on the table that is escalated when I lock it, and then
not released. Of course I can not verifiy that since I know of no way to
view locks that
Access/Jet uses internally except maybe through some third party/addon tool.
I have tested the same code in VB6 and it works fine so I am implementing a
work around that will work in VBA with bound form controls.