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

[jack1234]

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?

 

[Treason]

I fail to see why you would want to change your database structure in this manner. I wouldn't delete the table objects, instead I would delete all data from the tables.

DoCmd.RunSQL "DELETE * FROM table1;"

Then use DoCmd.TranferText or TransferDatabase to the bring new data back in.

If you are saying that the tables have a different structure, just use different table names.