Deleting All db Objects starting with a Certain Prefix

[commandolomo]

Hello

Does anybody have any vb code - either as a procedure or function - that deletes all tables, queries and macros, based on wheteher or not the object has a certain prefix?

My db has over two hundred tables, and twice as many queries, and so instead of writing a macro and using "DeleteObject" to name every object, is there a bit of code anyone is willing to share that deletes all objects - data and structure?

Cheers

 

[bretts]

commandolomo

If you go to the access options can can turn on system objects.

One of the tables is called MSysObjects

Inside this table are all the objects (Form, Tables, Reports etc..) and their name and type and so on.

You can use this table in queries and SQL and then use a like statement to get just the names you want.

Now create a recordset based on the query (or SQL) and use the "DeleteObject" you mentioned in a loop passing in the object name.

"For Reference - object types (Access 97)"
1 - Tables
5 - Queries
-32764 - Reports
-32768 - Forms
-32766 - Modules


Hope this helps

Brett

 

[commandolomo]

Nice one, cheers Brett, will give this a go

Thanks

 

[ghudson]

I had something simular that will delete all import error tables. I modified it to search and delete any tables that contain XXXX 'within' the table name.

Public Function DeleteXXXXTables()
On Error GoTo Err_DeleteXXXXTables
    
    Dim tblDef As TableDef
    
    For Each tblDef In CurrentDb.TableDefs
    If InStr(1, tblDef.Name, "xxxx") > 0 Then
        Beep
        MsgBox "Found table " & tblDef.Name
        DoCmd.DeleteObject acTable, tblDef.Name
    End If
    Next tblDef
    
Exit_DeleteXXXXTables:
    Exit Function
    
Err_DeleteXXXXTables:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_DeleteXXXXTables
    
End Function

 

[commandolomo]

Many thanks for this code ghudson, is almost what I am after! I have just got a further couple of questions.........

1. How would one amend it so that it would delete those tables that don't start with/contain a certain prefix? For example, for it to delete all tables that do start with "KEEP_"?

2. How would would amend this code to delete Queries and Modules?

As always, any help and assitance is most appreciated

 

[commandolomo]

Please ignore the above! Have solved both issues.

FYI, to sort 1. I replaced "IntStr" with the "Left" Function to find which tables/querys had the prefix, and for 2., where TableDef is referenced, the references for QueryDef and ModuleDef work in the same way.

PJL