Deleting Tables that are not being used in any querys

[wren17]

Hello Everyone,

I have a question regarding Database Maintenance. I am trying to remove any and all tables that are not being used in querys and/or macros and modules. Does anyone have script that can accomplish this?

Thanks

 

[dk]

Hi,

I think that this could prove to be difficult. In order for a VBA procedure to work out what tables are in use it would have to loop through all code and all queries, parse any queries it comes across both within Access and within any VBA code (could be near to impossible) and then work out what tables are in use/not in use. The code would have to be written perfectly (not possible) in order that tables are not accidentally deleted. What happens if vital tables are deleted because of a simple flaw in your procedure? AAAAAAAAAAAAAAAAHHHHHHHH!!!!

My question would be, why are there unused tables in your database? If you use temporary tables then delete them once you've finished with them. If you have inherited a messy database from somewhere then you're probably best off going through the database with a fine tooth comb, working out what it's supposed to do, working out what it does and working out how it does it. By that time you'll know what can be deleted, or archived at least.

If this post is of no help then please post back saying what you're trying to achieve and maybe I can offer some additional ramblings

 

[wren17]

Thanks for your reply. I am the creator and maintainer of the db. Unfortunately, I was not as organized I should have been while creating. I added every table from about 4 SQL databases and, now, am only using about 7 tables out of a couple hundred. I am going to transition this database to someone here in the next few weeks and I do not want to scare them with all of the unused tables.

Back to what you were saying about the querys and the loops. Lets just focus on the queries for now. Do you know of any code that will allow me to loop through the sql statements of a query and pull back the text? That will be very helpful. I could create a function that returned a 1 or 0 if the a table name exists in any of my query's code. I could then store the tablenames in a separate table and view them when I am finish. If I am satisfied with the results I can create a statement that will delete any table that is not in the tablenames table.

My problems with this is: I do not know how to look a the sql for a query(In VBA). and I do not know how to Loop through the tables and the tablenames in a database using VBA.

If you have any suggestions regarding the code, it would be greatly appreciated.

Thanks,
Wren

 

[mdmilner]

Here's the relevant code snippets.

Function sample(strObjectType As String)
Dim dbs As Database
Dim tbl As TableDef
Dim qry As QueryDef
Dim i as Integer

Set dbs = CurrentDb()
' tables
For Each tbl In dbs.TableDefs
   ' What to do with each or review
Next tbl
'queries
For Each qry In dbs.QueryDefs
  ' What to do with each or review
Next qry
'form/reports/module
For i = 0 To dbs.Containers(strObjectType & "s").Documents.Count - 1
  ' What to do with each or review
Next i
'Macros
For i = 0 To dbs.Containers("Scripts").Documents.Count - 1
   ' What to do with each or review
Next i

End Function

 

[dk]

Hi,

Here's how you get the SQL of each query in your database:-

Sub GetQueryText()
    Dim q As QueryDef

    For Each q In CurrentDb.QueryDefs
        Debug.Print q.Name; q.sql
    Next q

End Sub