Batch Deletion of Database Objects

[Tzokas]

How can i batch delete Access objects i.e queries,forms, etc ?
Suppose that in DB_A i want to import forms from DB_B. In case that a form (i.e Frm) already exist in DB_A, the imported form will be renamed as Frm1. It would work well from the Imort Wizard, but what's up in case i want to import much more objects ?
I'm looking for solution 1 month ago.

 

[gwinn7]

I would suggest you collect all of the names of objects that you want to remove first in an array or a collection. To discriminate which items that are duplicates, say all objects that end with a "1", then use the RIGHT function in a comparison. Then, when you have a match, delete the object.

Instead of creating an array or collection of objects, you can also use the MsysObjects hidden system table as a reference for removing these items.

Is this enough or are you looking for some sample code?

Gary
gwinn7

 

[7ony]

In the following, tbl.name is the name of the table. Use it to decide if you really want to delete the table.
As is, this will delete ALL the tables. Quick & deadly!


for each tbl in currentdb.TableDefs
DoCmd.DeleteObject acTable,tbl.name
next

7ony

 

[gwinn7]

Past experience tells me that 7ony's method will not work properly. Like I said, you should enumerate all objects you want to remove first before actually removing them. Otherwise, it will not remove all items you want to delete. I am unsure why 7ony's proposed method has problems, but I know how to get around it.

Sorry 7ony, I have tried that before, but it never worked right for me.

Gary
gwinn7

 

[7ony]

You are right. As is it does have problems...
I didn't bother to place the DIM statement &
I didn't bother to insert a statement skipping system tables.
Cut the following code & try it...
Your tables WILL vanish!
---
Sub Test()
Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If Left$(tbl.Name, 4) <> &quot;MSys&quot; Then
DoCmd.DeleteObject acTable, tbl.Name
End If
Next
End Sub
---
7ony

 

[Tzokas]

Thank you for the response.

1) In case of tables 7ony's method will not work because relationships must first be deleted manually.
2) The gwinn7's method does my work, as i just want to delete everything except from Tables.

Thank both of you.