How to close a table to update the same table? (1 Viewer)

[bg3075]

I have a VBA code (below) to run at a form "On Open" event, but am getting the error, "Run-time error '3211'; The database engine could not
lock table 'Items' because it is already in use by another person or process". Anyone know what I can add to my code to make sure the table instance (which is somehow being locked through the main switchboard form) is closed?

Code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Close acTable, "tblWell_ID"
DoCmd.OpenQuery "qryWell_ID"
End Sub

 

[boblarson]

That isn't likely the problem. You might have the table being used by a combo box or a list box or something else at the same time. Or you could have done some code which didn't release. Can you give more info about what you are trying to accomplish?

 

[bg3075]

The switchboard form has a command button to a form which contains a combo box (object source of the "tblWell_ID", but there are no text fields or combo box on the main switchboard form at all.

I have the "Autoexec" macro to first display and close a splash screen, then "RunCode" to run the "make-table query" in the initial post (above), and finally open the main switchboard.

 

[boblarson]

The switchboard form has a command button to a form which contains a combo box (object source of the "tblWell_ID", but there are no text fields or combo box on the main switchboard form at all.

I have the "Autoexec" macro to first display and close a splash screen, then "RunCode" to run the "make-table query" in the initial post (above), and finally open the main switchboard.

If a combo is on an open form and it's recordsource has that table as part of it, regardless of whether it is being used or not, it will keep you from doing a make table. I would question why do a make table anyway. Just delete all records using a delete query and then use an APPEND query instead. Then it doesn't matter if it is open or not.

 

[bg3075]

The whole reason for trying this is due to the problem that I can set permissions for a typical "Read-Only" user to allow them to 'Modify Design' of the table, which is updated upon opening the db, and therefore running the 'Autoexec' macro. But, once I log in as Administrator, close the db, and then login again with a "Read-only" login, I get a run-time error '3033', do not have the necessary permissions to use the 'tblWell_ID' object, and notice that the permission to modify design of this table is dropped from the "Read-Only" permissions once logged back in as Administrator. If I set the permission and login as "Read-Only" it continues to work as long as I don't login again as Administrator.

 

[bg3075]

Now I am thinking the problem is because the Autoexec macro is firing up a VBA module to open the query, which in turn updates the table, and the module is where the permission is not set up. Mr Larson, do you know how to set permissions for a VBA script, preferably within the script?

Thanks

 

[bg3075]

As of now, this is the module code to run the update-table query on open of the db.

Public Function Startup()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryWell_ID"
End Function

 

[bg3075]

OK, I figured out the security problem...what I had to do is set all permissions, but 'Administer' for the table to be updated each time the db is opened, for the read-only user. In addition, had to set the same permissions for <New Tables/Queries> and a linked table from which the make-table query pulls data from. I think I will be able to adjust the permission settings more once the db is split.