Drscription on Tables, Forms, Reports

[Kenny1943]

Hello,

I have inherited a large database to maintain. There are literally hundreds of queries and reports in the database.

Every time a user has a problem with a report, my first step is to find the report in the database (there is no naming convention). What I've decided to do is insert a number label in the lower corner on each of the reports then put that number on the description of the report in the database window.

Does anyone know how this can be don't programmatically. This is not the only database I have to do.

Thanks for any help you can give.

 

[SteveR77]

Kenny,

I do not know a simple way to do what you need to do. The greater issue is the naming convention or lack there of is what will be used in relating the data in your tables, queries and reports.


I know that FMS offers tools for tracking what is actually used in an Access DB, but price would be an issue in considering those tools to help you resolve what you are attempting to resolve.

Good luck and I am sorry that I could not offer more help.


Steve

 

[Kenny1943]

Steve,

Thanks for your input. I didn't think there was an easy way to accomplish the task.

 

[Quehay]

You've touched a nerve...the need to document db contents and modify objects globally is really needed (and I'm working on a code library to provide a polished toolset for this).

This is a routine, partially lifted from the MS KB, that will open each report and read/change properties (they have to be opened in design to do this unfortunately, and that's why the Application.Echo is set to OFF so that you don't have 50 reports opening on your screen). You should be able to tweak this to do what you need:

[tt]
Private Sub ShowReportSources(DB As DAO.Database)
On Error GoTo Exit_ShowReportSources

Dim Con As DAO.Container
Dim Doc As DAO.Document

Set Con = DB.Containers("Reports")
Application.Echo False

For Each Doc In Con.Documents
Debug.Print "Report Name:", Doc.name
DoCmd.OpenReport Doc.name, acViewDesign
Debug.Print "RecordSource:", Reports(Doc.name).RecordSource
Debug.Print
DoCmd.Close acReport, Doc.name
Next

Exit_ShowReportSources:
Application.Echo True
Exit Sub
End Sub 'ShowReportSources
[/tt]
******************
This is made to insert the SQL for query defs into a text file:
[tt]
Public Sub DocumentQueriesinTextFile()
On Error GoTo Error_DocumentQueriesinTextFile

Dim Qdf As New DAO.QueryDef
Dim strSQL As String
Dim strTextFile As String
Dim strQDf As String
DoCmd.SetWarnings False

Dim intFileNum As Integer
Dim strAppName As String

strAppName = Left(CurrentProject.Name, (Len(CurrentProject.Name) - 4))

intFileNum = FreeFile

strTextFile = CurrentProject.Path & "\" & strAppName & "_Query_SQL.txt"

Open strTextFile For Append As #intFileNum

Print #intFileNum, "QUERY DEFINITIONS FOR " & UCase(CurrentProject.Name) & _
vbCrLf & "Created: " & Format(DATE, "long date")

For Each Qdf In CurrentDb.QueryDefs

If Left(Qdf.Name, 1) <> "~" Then

strQDf = Qdf.Name & vbCrLf & vbCrLf & Qdf.SQL

Write #intFileNum, strQDf
End If

Next

Close #intFileNum

MsgBox "DONE"

Exit_Error_DocumentQueriesinTextFile:
Set Qdf = Nothing

Exit Sub

Error_DocumentQueriesinTextFile:
Select Case Err.Number

Case 3371

Resume Next

Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Error_DocumentQueriesinTextFile

End Select

End Sub[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[mrgulic]

Can you display and edit all the container descriptions (e.g. Table's, Query's, Form's. Macro's, Module's).