Finding Unused Tables

[Shaz1]

Good Morning!

I was wondering if there is a quick way of finding out which tables are unused or not used as a control source in any of the modules of a database.

I have many, many Queries, Forms and Reports that have been modified on a frequent basis by many users. I was wanting to clean up the unused tables and data without having to check each query.

Any recommendations?

Shaz1

 

[IanAble]

Oh no, you let users modify the db?!! Lock them out! Seriously though, If you want to check the created/last modified dates for all database objects add a ListView Control, ctlListView, with 3 columns, to a Form and load all the database objects into it.

Dim wspCurrent as DAO.Workspace
Dim dbCurrent as DAO.Database
Dim cntCurrent as DAO.Container
Dim docCurrent as DAO.Document
Dim lvwCurrent as ListView
Dim litCurrent as ListItem

Set lvwObject = ctlListView.Object
Set wspCurrent = DbEngine.Workspaces(0)
Set dbCurrent = wspCurrent.OpenDatabase(CurrentDb.Name)
For Each cntCurrent in dbCurrent.Containers
For Each docCurrent in cntCurrent.Documents
Set litCurrent = lvwCurrent.ListItems.Add , , docCurrent.Name
litCurrent.ListSubItems.Add , , docCurrent.DateCreated
litCurrent.ListSubItems.Add , , docCurrent.LastUpdated
Next docCurrent
Next cntCurrent

With a little more work you could cycle through each object with a RecordSource or SQL string property (Forms, Reports, and Queries.
), and open the object in design view to see if that property matches, or contains the name of any ListItem.Text. That'll tell you if the object is referenced by Forms, Reports, and Queries. You'll also need to look at the SourceObject property fo any subForm control, to let you know what subForm is linked to which Form. For the Forms collection it would be:

Dim frmCurrent as Form

For Each frmCurrent in CurrentProject.AllForms
Docmd.OpenForm frmCurrent.Name,acDesignView
For Each litCurrent in lvwCurrent.ListItems
if litCurrent.Text = frmCurrent.RecordSource then
...Add another ListSubItem to the ListItem
end if
Next
Docmd.Close acForm frmCurrent.Name
Next

Modules are going to be more tricky, but it's another text match using the Find method.


As you can see it would be better to be the only person driving the car as it were...


Have Fun!


Ian

 

[MichaelRed]

Weeeeeeeeeeeeeeeeeeeeeeel, you may get close to the ones which have not been modified recently. But modification for Tables refers to the design, not thte content (records). In the grand scheme of thiings this is at least as important as the design.

MichaelRed

 

[ZmrAbdulla]

IanAble,
I was just trying the code. It was not working as it is. May need modification.
[tt]
Set litCurrent = lvwCurrent.ListItems.Add , , docCurrent.Name[/tt]
getting highlighted.
Can you please look into this?

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!