Check Tables in Use

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 97
I need to delete a heap of tables. But first I need to check if they are in
use. If one table is being used then I don't want to delete any of them.

I know I need two loops, one to check if the tables are open or being used,
and the second to delete (deleting is the easy part!)
How do I check if the tables can be deleted?
 
There is, as far as I know, no 100% foolproof automated solution. For
example, suppose you have two tables named Foo1 and Foo2. (These would, of
course, be incredibly poor table names, but hey this is just an example! :-)
Now suppose you have some code that does something like this ...

strTablename = "Foo"
If <some condition> Then
strTableName = strTableName & "1"
Else
strTablename = strTableName & "2"
End If
Set rst = db.OpenRecordset(strTableName)

It is unlikely that any tool will recognize that this code has dependencies
on tables Foo1 and Foo2.

With that caveat, check out the following resources ...

Rick Fisher's Find and Replace
www.rickworld.com

SpeedFerret
www.moshannon.com

Total Access Analyser
www.fmsinc.com
 
carl said:
I'm using Access 97
I need to delete a heap of tables. But first I need to check if they
are in use. If one table is being used then I don't want to delete
any of them.

I know I need two loops, one to check if the tables are open or being
used, and the second to delete (deleting is the easy part!)
How do I check if the tables can be deleted?
Click to expand...

What do you mean by "in use"? Are you trying to determine whether they
are used by the application somewhere, or just whether they are
currently open or providing data to an open form, report, query, combo
or list box?

If the former, see Brendan's reply to your question. If the latter, you
could try deleting them all inside a transaction, and then if there's an
error deleting any of them, roll back the transaction. I'm not sure
whether that will catch tables being used as rowsources for combo or
list boxes; one would have to check.
 
If the database is split front end / back end I would just rename the tables
that I thought were not in use and see if anyone reported a problem. If a
problem occured then just rename them back. If NO problems then copy the
tables into a blank database as a backup copy just in case.
 
Back
Top