Err 3262 'table locked', but the table is not open 1

[smandoli9]

I want to add a record to a table. Table is in a back-end Access 2003 mdb, and is locked to avoid multi-user conflict. I use OpenDatabase, then open the DAO recordset:

Dim HomerBE As DAO.Database
Dim rsProject As DAO.Recordset
Set HomerBE = OpenDatabase _
    (strPath, False, False, "MS Access; pwd=yimpy")
Set rsProject = HomerBE.OpenRecordset _
    (strSQL, dbOpenDynaset, dbDenyWrite)
rsProject.AddNew
     ' ...  populate fields
rsProject.Update
rsProject.Close
Set rsProject = Nothing
Set HomerBE = Nothing

It works great the first time I run it. Then I get error 3262, table is locked by another user. Since it's just me here that suggests of course that I left my table open.

I do have a form open that uses that table as recordset basis, but that's open before the first time I call the routine.

What am I leaving undone?

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[rubbernilly]

Close the form and run the code twice. I bet you will be able to without getting the error.

 

[smandoli9]

Thanks. Wonder how it works though, since the form was open before the operation both first- and second-run. I wonder a little. Mostly glad I can fix it.

I also had to adjust the form I was calling from, it was dialog; once I made it non-dialog I could properly close the offending form.

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[smandoli9]

I want to write to a recordset which is locked during the operation. But if anyone on the network has a form open with the table (my recordset) as its source, I get error 3262 when I run the operation.

I want to let users have their forms and slip in and out of the recordset. Using dbDenyWrite unless there's something more appropriate. How do I rig this?

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[rubbernilly]

You cannot write to it if it is locked against that. Somewhere you'll have to relax the lock... likely for your users and for yourself, too (so that you don't disrupt the users).

 

[smandoli9]

Okay I think I'm good.

OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)

This lets me go in and out, building a new record without disturbing the other users on network.

I wish for a FAQ on this general issue of recordset-locking for a multiuser environment. My trusty Wrox "Beginning Access 97 VBA" by Smith and Sussman had the best info.


[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]