How to checked whether the access database table is open by the user?

[jack1080]

I wish to delete all the tables from the database using code and import the other set of database from the other file.

Now the focus is on delete the tables, following code does the job:

For intLoop = intTableCount To 0 Step -1
Dim strTable as String
strTable = dbOld.TableDefs(intLoop).Name
If Left(strTable, 4) <> "MSys" Then
DoCmd.DeleteObject acTable, strTable
End If
Next intLoop

However if any of the table is currently open, an error will be prompt when deleting the table

Run-time error '2008':
You can't delete the database object 'Table1' while it's open.

So what I decided to do is to do a checked for any of the table is open by the user, and prompt the user to close the table before running deletion.

1. How to checked whether the access database table is open by the user with VBA?
2. Is there other scenario(s) that may cause the deletion failed?

 

[PHV]

Why not simply open the database in exclusive mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jack1080]

Thanks, but the button for doing this is on a user form in the mdb file. After the user open the mdb file, and click on the button, how to open the mdb file in exclusive mode since the current mdb file has already been opened...

 

[jack1080]

Some extra info..the access form will only be use by local user, not remote user. Has there is only 1 user access it locally. If the user open the database, either in exclusive mode or not, open a table, and run the delete code on the form...the problem will still persist IMHO.

 

[n554386]

Why not just trap the error and then prompt the user to close the table.

Actually, why not check to see what database object are open (ie tables, queries, forms) and if any objects are open prompt the user to close them before deleting the tables.