List objects in MS Access database on form and selectively delete 1

[IngDep]

Is it possible to create a MS Access form that displays all objects in a database and have several command buttons to delete all objects displayed in the listbox?

For example, can I have 4 command buttons titled accordingly; Queries, Forms, Tables, Modules and then have another command button that will actually delete the objects in the database - those objects that are displayed in the listbox?

Occassionally, I need to delete objects in the database after I compact the database so that a query can be performed.

 

[fneily]

I created a listbox. Then have four buttons to list Forms, Queries, Macros, Reports. The code on each button follows:
(code on the OnClick event. See code for respective object listing)
(You can create one for .AllModules)
(Note you use CurrentProject for most but CurrentData for queries)
Dim intCount As Integer
Dim strSearch
For intCount = 0 To CurrentProject.AllMacros.Count - 1
strSearch = strSearch & "," & CurrentProject.AllMacros(intCount).Name
Next intCount
Me![List0].RowSource = strSearch

Dim intCount As Integer
Dim strSearch
For intCount = 0 To CurrentProject.AllReports.Count - 1
strSearch = strSearch & "," & CurrentProject.AllReports(intCount).Name
Next intCount
Me![List0].RowSource = strSearch

Dim intCount As Integer
Dim strSearch
For intCount = 0 To CurrentProject.AllForms.Count - 1
strSearch = strSearch & "," & CurrentProject.AllForms(intCount).Name
Next intCount
Me![List0].RowSource = strSearch

Dim intCount As Integer
Dim strSearch
For intCount = 0 To CurrentData.AllQueries.Count - 1
strSearch = strSearch & "," & CurrentData.AllQueries(intCount).Name
Next intCount
Me![List0].RowSource = strSearch

You can then probably modify the code to delete objects.

 

[fneily]

Forgot to mention to change the Rowsource Type of the listbox to Value List.

 

[TheAceMan1]

How are ya IngDep . . .

To collect all your objects have a look at MSysObjects
Your biggest problem is deleting parent tables where relationships are involved. Its better to delete child tables 1st.

Also be care deleting all tables! I don't know yet if relationships are maintained!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jrbarnett]

Also, you have to check - you can't delete objects that are open, you also would need exceptions to not delete the form that you were running the listbox from.

Be careful with this; have good backups before testing.

John

 

[PHV]

Why not simply use the database window ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jrbarnett]

IngDep,

Your comment in the original post:

Occassionally, I need to delete objects in the database after I compact the database so that a query can be performed.

suggests that there may be temporary working objects that aren't being deleted reliably to allow (for example) make table queries or VBA code that generates objects to run.

If this is the case, would it be possible to look at this to see if it can be fixed to correctly remove them?

John

 

[TheAceMan1]

IngDep . . .

I can't help but agree with [blue]PHV[/blue], however if you wish persue your course the following is a foundation you could use:

The schema consists of a multiselect listbox to replace the buttons (named [blue]lstObjectTypes[/blue] and an additional multiselect listbox (named [blue]lstObjects[/blue]) to display the object types selected in [blue]lstObjectTypes[/blue].

What I present simply sets-up the two listboxes, so open a new form and using the [blue]Listbox Wizard[/blue], add two listboxes (click the first cancel that comes up). You should now have two listboxes on the form that [blue]are not setup![/blue]

For the listbox used for [blue]lstObjectTypes[/blue], set the following properties:
[ol][li]Column Count [blue]2[/blue][/li]
[li]Column Widths [blue]0";1"[/blue][/li]
[li]Width [blue]1"[/blue][/li]
[li]Row Source Type [blue]Table/Query[/blue][/li]
[li]Row Source (copy/paste the following):

[blue]SELECT Type, Switch([Type]=-32768,'Forms',
                    [Type]=-32766,'Macros',
                    [Type]=-32764,'Reports',
                    [Type]=-32761,'Modules',
                    [Type]=-32756,'Pages',
                    [Type]=1,'Local Tables',
                    [Type]=5,'Queries',
                    [Type]=6,'Linked Tables') AS typName
FROM MSysObjects
GROUP BY Type, Switch([Type]=-32768,'Forms',
                      [Type]=-32766,'Macros',
                      [Type]=-32764,'Reports',
                      [Type]=-32761,'Modules',
                      [Type]=-32756,'Pages',
                      [Type]=1,'Local Tables',
                      [Type]=5,'Queries',
                      [Type]=6,'Linked Tables')
HAVING ([Type] In (-32768,-32766,-32764,-32761,-32756,1,5,6))
ORDER BY Switch([Type]=-32768,'Forms',
                [Type]=-32766,'Macros',
                [Type]=-32764,'Reports',
                [Type]=-32761,'Modules',
                [Type]=-32756,'Pages',
                [Type]=1,'Local Tables',
                [Type]=5,'Queries',
                [Type]=6,'Linked Tables');[/blue]

[/li]
[li]In the [blue]AfterUpdate[/blue] event of [blue]lstObjectTypes[/blue], copy/paste the following:

[blue]   Dim SQL As String, LBx As ListBox, itm
   Dim sqlSw As String, sqlIn As String
   Dim typN As Long, typNam As String
   
   Set LBx = Me!lstObjectTypes

   For Each itm In LBx.ItemsSelected
      typN = LBx.ItemData(itm)
      typNam = LBx.Column(1, itm)
      
      If sqlIn <> "" Then
         sqlIn = sqlIn & "," & typN
      Else
         sqlIn = "IN (" & typN
      End If
      
      If sqlSw <> "" Then
         sqlSw = sqlSw & ",[Type]=" & typN & ",'" & typNam & "'"
      Else
         sqlSw = "Switch(" & "[Type]=" & typN & ",'" & typNam & "'"
      End If
   Next
   
   sqlIn = sqlIn & ")"
   sqlSw = sqlSw & ")"
   
   Me!lstObjects.RowSource = "SELECT Type, " & sqlSw & " AS TypNam, Name " & _
                             "FROM MSysObjects " & _
                             "WHERE ([Type] " & sqlIn & ") AND Left([Name], 4) <> 'MSys '" & _
                             "ORDER BY " & sqlSw & ", Name;"
   
   Set LBx = Nothing[/blue]

[/li]
[li] Bound Column [blue]1[/blue][/li]
[li] Name [blue]lstObjectTypes[/blue][/li] ... check spelling!
[li] Multi Select [blue]Extended[/blue][/li][/ol]
For the listbox used for [blue]lstObjects[/blue], set the following properties:
[ol][li]Column Count [blue]3[/blue][/li]
[li]Column Widths [blue]0";1";3"[/blue][/li]
[li]Width [blue]4"[/blue][/li]
[li]Row Source Type [blue]Table/Query[/blue][/li]
[li]Row Source [blue]Empty/Nothing[/blue][/li]
[li] Bound Column [blue]1[/blue][/li]
[li] Name [blue]lstObjects[/blue][/li] ... check spelling!
[li] Multi Select [blue]Extended[/blue][/li][/ol]
In the [blue]After Update[/blue] event of [blue]lstObjects[/blue], the code would look something like that provided by [blue]fneily[/blue]

The setup is complete! Perform your testing to insure all is well. This should be enough to get you well on your way!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886