Database structure

[tmarsh]

Is there any way to keep track of a database structure in access? For instance, which query relates to which report? Sometimes I create queries that are no longer needed but if there are a lot (which there are!) it can be easy to delete one that's needed.

 

[MStef]

Hello Thomas!

I have solved this problem on this way.
click on query (not open it), click on Properties, and write in
DESCRIPTION: Do you need this qry, and what for.

 

[Len Boorman]

If I think a query or whatever is redundant I rename it using exactly the same as before but prefix z. Then wait.

If nobody shouts in a reasonable time then I figure it is redundant. If someone shouts it is easy to reset the name back to original

Len

 

[allan57]

The following code will tell you where the query is used ie Form Record Source, Form VB Procedure, Report Record Source etc or leaves the field blank if the query is not used.


Create table:-

Application_Query_Where_Used_Analysis

With the following fields

txtQuery_Name - Text
fQuery_In_Use_By_DB – Boolean
txtWhere_Used_Type – Text
txtWhere_Used_Description – Text

Create a form with the following code in it:-

Private Sub Form_Load()

Dim dbs As Database
Dim qdfGetQueryNames As QueryDef

Dim frmGetActiveForm As Form

Dim frmGetActiveReport As Report

Dim ctrForms As Container
Dim ctrReports As Container
Dim ctrModules As Container

Dim mdlModules As Module
Dim mdlFormModules As Module
Dim mdlReportModules As Module

Dim docContainerName As document

Dim strCheckQueryIsUsedByDatabase As String
Dim strGetGetQueryName As String

Dim lngSLine As Long
Dim lngSCol As Long
Dim lngELine As Long
Dim lngECol As Long

Dim intRecordCounter As Integer

On Error Resume Next

Set dbs = CurrentDb

Set ctrForms = dbs.Containers!Forms
Set ctrReports = dbs.Containers!Reports
Set ctrModules = dbs.Containers!Modules

DoCmd.RunSQL ("DELETE [Application_Query_Where_Used_Analysis].* FROM Application_Query_Where_Used_Analysis;")

'Check Form Recordsource & Procedures
For Each docContainerName In ctrForms.Documents

dbs.QueryDefs.Refresh

DoCmd.OpenForm docContainerName.Name, acDesign

Set frmGetActiveForm = Forms(docContainerName.Name)
Set mdlFormModules = frmGetActiveForm.Module

For Each qdfGetQueryNames In dbs.QueryDefs

'Check Recordsource
If Forms(docContainerName.Name).RecordSource = qdfGetQueryNames.Name Then

DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Form Record Source';")

End If

lngSLine = 0
lngSCol = 0
lngELine = 0
lngECol = 0

'Check Procedures
If mdlFormModules.Find(qdfGetQueryNames.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then

DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Form VB Procedure';")

End If

Next qdfGetQueryNames

DoCmd.Close acForm, docContainerName.Name, acSaveNo

Next docContainerName

'Check Report Recordsource & Procedures
For Each docContainerName In ctrReports.Documents

dbs.QueryDefs.Refresh

DoCmd.OpenReport docContainerName.Name, acDesign

Set frmGetActiveReport = Reports(docContainerName.Name)
Set mdlReportModules = frmGetActiveReport.Module

For Each qdfGetQueryNames In dbs.QueryDefs

'Check Recordsource
If Reports(docContainerName.Name).RecordSource = qdfGetQueryNames.Name Then

DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Report Record Source';")

End If

lngSLine = 0
lngSCol = 0
lngELine = 0
lngECol = 0

'Check Procedures
If mdlReportModules.Find(qdfGetQueryNames.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then

DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Report VB Procedure';")

End If

Next qdfGetQueryNames

DoCmd.Close acReport, docContainerName.Name, acSaveNo

Next docContainerName

'Check Module Code
For Each docContainerName In ctrModules.Documents

dbs.QueryDefs.Refresh

For Each qdfGetQueryNames In dbs.QueryDefs

DoCmd.OpenModule docContainerName.Name

Set mdlModules = Modules(docContainerName.Name)

If mdlModules.Find(qdfGetQueryNames.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then

DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'VB Module';")

End If

Next qdfGetQueryNames

DoCmd.Close acModule, docContainerName.Name

Next docContainerName

'Query is used by the database

dbs.QueryDefs.Refresh

For Each qdfGetQueryNames In dbs.QueryDefs

If Left(qdfGetQueryNames.Name, 4) <> "~SQ_" Then

strCheckQueryIsUsedByDatabase = "Not Used"

strCheckQueryIsUsedByDatabase = DLookup("[txtQuery_Name]", "Application_Query_Where_Used_Analysis", "[txtQuery_Name]='" & qdfGetQueryNames.Name & "'")

If strCheckQueryIsUsedByDatabase = "Not Used" Then

DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, fQuery_In_Use_By_DB ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', False;")

End If 'strCheckQueryIsUsedByDatabase = "Not Used" Then

End If 'Left(qdfGetQueryNames.Name, 4) <> "~SQ_" Then

Next qdfGetQueryNames

dbs.Close

Set frmGetActiveForm = Nothing
Set mdlFormModules = Nothing
Set docContainerName = Nothing
Set ctrForms = Nothing
Set ctrReports = Nothing
Set ctrModules = Nothing
Set dbs = Nothing

End Sub

 

[Len Boorman]

Allan57

Great chunk of code. Used it as soon as I say it. Works a treat.

Must say I did not really understand exactly how it worked.

Pushing my luck but is there a modification that can look at queries and establish if they have a query as their source.

Whilst the output is so useful, at times, in the sort of things that I do I, it sometimes takes acouple of queries to "massage" data in the shape the User requires.

Gain thanks for posting the code

Len

 

[DanG]

Access 2003...
Right click on query or tabel and just below "properties" you will see "Object Dependencies". I don't know if older versions have this or not.