Selectively drop many tables in Access?

Question

I need to drop 175 of 180 tables in Access 2003 (using an Access 2000 format database). The first thing I tried was going to the table list and trying to select all, control-clicking the ones I want to keep, and choosing "Delete". However, it turns out you can only select one at a time.

Then I found out a way to get a list of all the table names as plain text and made a bunch of drop statements using Vim:

drop table table_1;
drop table table_2;
...
drop table table_175;

(Obviously, those aren't the real names.) These statements work fine by themselves (i.e., just executing one at a time, but when I try to run 2 or more of them, I get an error message:

Syntax error in DROP TABLE or DROP INDEX.

That seems to defeat the purpose. Oh, Access... you always make me want to pull my hair out.

What am I missing to make this work? Any process that will let me keep the few tables I need for my purposes would be great. (I'd like to avoid learning any Visual Basic, though.)

Answer 1

Use VBA.

For Each tbl In CurrentDb.TableDefs
    If Left(tbl.Name,4) <> "Msys" Then
        s = "DROP TABLE " & tbl.Name
        CurrentDb.Execute s
    End If
Next

For i = ( CurrentDb.TableDefs.Count - 1 ) To 0 Step -1
    If Left(tbl.Name,4) <> "Msys" Then
        ''Debug.Print CurrentDb.TableDefs(i).Name
        CurrentDb.TableDefs.Delete CurrentDb.TableDefs(i).Name
    End If
Next

You may have to provide an array or recordset and iterate through that if you want to delete specific tables or if tables are related.

Answer 2

Remou has the answer to your problem, but as a suggestion, you may want to import the tables you want to keep into a new file. You can select multiple files and their data. You may end up with a cleaner file unless you have a lot of tables that remain.