testing if table is open before deleting

[Joey]

Hello all,

I would like to delete a table; however, I receive an error message if
the table is open. How does one check to see if the table is open? I
would like to close the open table before deleting it.

Thanks,
Joey.

 

[Wayne Morgan]

If you're doing this in code, the error number is 2008. You would attempt
the delete and let you're error handler trap the error if the table is open.

The next thing is to find out what has the table open. It could be a form,
report, query, recordset in code, or outside connection. If you have
multiple users, it could be any one of the users that has permissions to
open the table. I don't know how to tell who or what has a specific table
open.

 

[Joey]

Hi,

Yes I am doing this in VB. Is it possible to determine which table is
affected (i.e. which table is causing the 2008 error number)? I have to
copy then delete a lot of tables before recreating them. I am not too
concerned about errors generated from multiple users -- the operator
does indeed account for that. I'm just worried the single operator has
some tables open for viewing while trying to execute my function.

Thanks,
Joey.

 

[Wayne Morgan]

Yes, you can determine which table caused the error. You will go to your
error handler immediately when you try to delete an open table. If you are
deleting multiple tables, you probably have a variable with the table's name
that is currently being deleted. You could use this information in your
error handler.

Example:
If Err.Number = 2008 Then
Msgbox "The table " & PlaceVariableHere & " is open and can't be
deleted!", vbOkOnly + vbExclamation
Resume Next
End If

You could also log the information somewhere if you wanted to. The variable
may be a string variable with the name of the table or an object variable
such as tdf.Name if you are using a For/Next loop.