Frank,
I've only tested deleting the tables, but it should work.
Public Sub KillHiddenObjects()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sPlace As String
On Error GoTo Proc_Err
Set db = CurrentDb
'*** Kill the Tables ********************
sPlace = "deleting the tables."
sSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = 1 AND Flags = 8"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
db.TableDefs.Delete rs!Name
rs.MoveNext
Loop
db.TableDefs.Refresh
rs.Close
'*** Kill the Queries ********************
sPlace = "deleting the queries."
sSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = 5 AND Flags = 8"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
db.QueryDefs.Delete rs!Name
rs.MoveNext
Loop
db.QueryDefs.Refresh
rs.Close
'*** Kill the Forms ********************
sPlace = "deleting the forms."
sSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = -32768 AND Flags =
8"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
DoCmd.DeleteObject acForm, rs!Name
rs.MoveNext
Loop
rs.Close
'*** Kill the Reports ********************
sPlace = "deleting the reports."
sSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = -32764 AND Flags =
8"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
DoCmd.DeleteObject acReport, rs!Name
rs.MoveNext
Loop
rs.Close
'*** Kill the Data Access Pages ********************
sPlace = "deleting the data access pages."
sSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = -32756 AND Flags =
8"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
'Kill the physical file
Kill Access.CurrentProject.AllDataAccessPages(rs!Name).FullName
DoCmd.DeleteObject acDataAccessPage, rs!Name
rs.MoveNext
Loop
rs.Close
'*** Kill the Macros ********************
sPlace = "deleting the macros."
sSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = -32766 AND Flags =
8"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
DoCmd.DeleteObject acMacro, rs!Name
rs.MoveNext
Loop
rs.Close
'*** Kill the Macros ********************
sSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = -32761 AND Flags =
8"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
DoCmd.DeleteObject acModule, rs!Name
rs.MoveNext
Loop
Proc_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Proc_Err:
DoCmd.Beep
MsgBox "An error " & Err.Number & " occurred while " & sPlace & _
vbCrLf & vbCrLf & Err.Description, _
vbOKOnly + vbExclamation, "Something went wrong!"
Resume Proc_Exit
End Sub
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
Frank Martin said:
I have some hidden objects (tables, Queries, Forms, Reports) that I hid long
ago to tidy the screen, and I want to delete these.
There are so many
objects on the screen that I need some way of displaying the "Hidden"
attribute to save trawling through all of them.
I cannot find how to do
this. Can someone point me in the right direction. Regards, Frank
