asked on

How to Search MDB Object Properties for a String

Using Access 2003 under Win XP.

It often happens that I want to modify a certain query, but before doing so I want to review all dependent queries, forms, control record sources, table lookups, etc. to make sure that the change is not going to screw up something else.

So I would like to be able to search all database object properties for a particular string (but I don't want to search database records).

I know about the Documenter, but that only seems to produce a printable (as opposed to searchable) output, and in any case I can't find an object model for that.

In a web search I tracked down the code listed below, which seems to come close to what I want (because it creates text files which I can then search), but the portion with respect to tables is causing the following error:

Runtime error 2487: The Object Type argument for the action or method is blank or invalid.

So I either need to fix that error or find some other way to output table properties to text. Or perhaps someone has a better solution entirely.

Thanks,

Lee

********************************

Option Compare Database
Option Explicit

Public Sub DocDatabase()
'====================================================================
' Name: DocDatabase
' Purpose: Documents the database to a series of text files
'
' Author: Arvin Meyer
' Date: June 02, 1999
' Modified: Charles D Clayton Jr
' Date: April 14, 2004
' Comment: Uses the undocumented [Application.SaveAsText] syntax
' To reload use the syntax [Application.LoadFromText]
'====================================================================
On Error GoTo Err_DocDatabase
Dim dbs As Database
Dim cnt As Container
Dim doc As Document
Dim i As Integer
Dim F As String 'folder for forms
Dim Q As String 'folder for queries
Dim R As String 'folder for reports
Dim M As String 'folder for modules
Dim S As String 'folder for scripts
Dim T As String 'folder for tables
Dim strDayPrefix As String 'date
Dim strPath As String 'pathway

'Initial creation of folders
strDayPrefix = Format(Now, "mm-dd-yyyy--hh-nn-ss")
strPath = "C:\4P Backup\"

'Check for the folder
If Len(Trim(Dir(strPath, vbDirectory))) > 0 Then
'the file already exists
Else
MkDir (strPath) 'Main Folder for all backups
End If

MkDir (strPath & strDayPrefix) 'folder for today's backup
MkDir (strPath & strDayPrefix & "\forms\") 'can only create only subdirectory at a time
MkDir (strPath & strDayPrefix & "\Reports\")
MkDir (strPath & strDayPrefix & "\Scripts\")
MkDir (strPath & strDayPrefix & "\Modules\")
MkDir (strPath & strDayPrefix & "\Queries\")
MkDir (strPath & strDayPrefix & "\Tables\")

'Define variables
F = strPath & strDayPrefix & "\Forms\"
R = strPath & strDayPrefix & "\Reports\"
S = strPath & strDayPrefix & "\Scripts\"
M = strPath & strDayPrefix & "\Modules\"
Q = strPath & strDayPrefix & "\Queries\"
T = strPath & strDayPrefix & "\Tables\"

Set dbs = CurrentDb() ' use CurrentDb() to refresh Collections

'Extract information and save to text file
Set cnt = dbs.Containers("Forms")
For Each doc In cnt.Documents
Application.SaveAsText acForm, doc.Name, F & doc.Name & ".txt"
Next doc

Set cnt = dbs.Containers("Reports")
For Each doc In cnt.Documents
Application.SaveAsText acReport, doc.Name, R & doc.Name & ".txt"
Next doc

Set cnt = dbs.Containers("Scripts")
For Each doc In cnt.Documents
Application.SaveAsText acMacro, doc.Name, S & doc.Name & ".txt"
Next doc

Set cnt = dbs.Containers("Modules")
For Each doc In cnt.Documents
Application.SaveAsText acModule, doc.Name, M & doc.Name & ".txt"
Next doc

For i = 0 To dbs.QueryDefs.Count - 1
Application.SaveAsText acQuery, dbs.QueryDefs(i).Name, Q & dbs.QueryDefs(i).Name & ".txt"
Next i

Set cnt = dbs.Containers("Tables")
For Each doc In cnt.Documents
Application.SaveAsText acTable, doc.Name, T & doc.Name & ".txt"
Next doc

'Clear Memory
Set doc = Nothing
Set cnt = Nothing
Set dbs = Nothing

Exit_DocDatabase:
Exit Sub

Err_DocDatabase:
Resume Next
' Select Case Err
' Case Is = 2001
' Resume Next
' Case Else
' MsgBox Err.Number & " " & Err.Description
' Resume Next
'' Resume Exit_DocDatabase
' End Select

End Sub

Markus Fischer

You could do something like this:

------ 8< --------------------------------------------------------------
Option Compare Database
Option Explicit

' Basic Queries documentation...
' Author: Markus Fischer, Geneva
' Use freely

Private Function RecordSource(pctl As Control) As String

' Returns a control's .RecordSource, if it has any, or Null otherwise

On Error Resume Next
RecordSource = Null
RecordSource = pctl.RecordSource

End Function

Sub DocumentQueries()

' Prints a list of query names to the debug window, followed by:
' - queries using it as a subquery
' - forms and form controls using it
' - reports and report controls using it

Dim qdfCurrent As QueryDef
Dim qdf As QueryDef
Dim doc As Document
Dim ctl As Control

With CurrentDb
For Each qdfCurrent In .QueryDefs
If Left$(qdfCurrent.Name, 4) = "~sq_" Then
' system query, managed by Access
Else
Debug.Print qdfCurrent.Name

' Used in other queries?
For Each qdf In .QueryDefs
If Left$(qdf.Name, 4) = "~sq_" Then
Else
If InStr(qdf.SQL, qdf.Name) Then _
Debug.Print " *"; qdf.Name
End If
Next qdf

' Used in Forms?
For Each doc In .Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acIcon
With Forms(doc.Name)
If InStr(.RecordSource, qdfCurrent.Name) Then _
Debug.Print " *"; doc.Name
For Each ctl In .Controls
If Not IsNull(RecordSource(ctl)) Then
If InStr(RecordSource(ctl), qdfCurrent.Name) Then _
Debug.Print " >"; ctl.ControlName
End If
Next ctl
End With
DoCmd.Close acForm, doc.Name
Next doc

' Used in Reports?
For Each doc In .Containers("Reports").Documents
DoCmd.OpenReport doc.Name, acViewDesign
' DoCmd.Minimize
With Reports(doc.Name)
If InStr(.RecordSource, qdfCurrent.Name) Then _
Debug.Print " *"; doc.Name
For Each ctl In .Controls
If Not IsNull(RecordSource(ctl)) Then
If InStr(RecordSource(ctl), qdfCurrent.Name) Then _
Debug.Print " >"; ctl.ControlName
End If
Next ctl
End With
DoCmd.Close acReport, doc.Name
Next doc

End If
Next qdfCurrent
End With

End Sub
------ 8< --------------------------------------------------------------

Missing here would be:
* queries used as record source for combo boxes in tables and queries (do you use that, really?)
* queries used in modules

Both can be coded as well, but I never needed it.

Incidentally, this will work very well if you used a good naming convention. If not, you might get some garbage :)

Cheers

Markus Fischer

By the way, why do you even try to save the table? The only reason would be if you had queries used a source for a combo box in a field... I'm sure this is not the case, now, is it?

Good Luck :)

flavo

But it doesnt save the Index's / data types / field size etc.. for the table's.

Why not just copy the whole db?? that's what i do, and if it gets too big, its backed-up on the server ever night anyway by the IT guys

Markus Fischer

The point of the whole thing is to search for query names everywhere they can possibly appear. Hence the idea of creating flat searchable files... This is not meant as a backup, although the code in the question seems to have been written for that purpose.
Cheers:)

flavo

oh shirt! Sorry.

Does this do enough for you??
Function getTable()
'Refrences DAO Object Lib
'In VB window, Tools - Refrences and tick it from the list
' if its not already
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb

For Each tbl In db.TableDefs
Debug.Print tbl.Name & "*************************"

For Each fld In tbl.Fields
Debug.Print fld.Name
Next

Next
'cleanup
Set fld = Nothing
Set tbl = Nothing
db.Close
Set db = Nothing

End Function

flavo

www.fmsinc.com has a good tool for this sort of stuff if you got some $$

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lknight1017

ASKER

harvang,

Thanks for the effort, but that code did not produce accurate results for me.
I have a lot of query names that may be part of other query name strings. Perhaps this is not good or common practice, but I'll tell you why I do it. If I have a ClientMatter table and a ClientMatterParty table, I like to create special queries on which to base my forms, so my convention is to create qryClientMatter (based on ClientMatter) as the source of frmClientMatter, and qryClientMatterParty (based on ClientMatterParty) as the source of frmClientMatterParty.

So, in the following statement, where the code's checking form recordsources:

If InStr(.RecordSource, qdfCurrent.Name) Then _
Debug.Print " *"; doc.Name

It will say erroneously that qryClientMatter is the source of frmClientMatterParty (whose source is actually qryClientMatterParty).

I seem to get better results with:

If .RecordSource = qdfCurrent.Name Then _
Debug.Print " *"; doc.Name

However, the same problem occurs when InStr is used to check whether a query is used in another query. So perhaps for that approach to work with query SQL strings, it would need to do a little more elaborate parsing of the appropriate strings.

Incidentally, I believe there is also a coding error in the following line:

If InStr(qdf.SQL, qdf.Name) Then _
Debug.Print " *"; qdf.Name

I think it should be:

If InStr(qdf.SQL, qdfCurrent.Name) Then _
Debug.Print " *"; qdf.Name

But again that won't work for the reasons mentioned above.

The code that looks up the record source of form controls doesn't seem to be working either, but I haven't yet had time to debug that.

Lee

lknight1017

ASKER

gustav -

I take it you've actually used Rick Fisher's utility that you recommend, or know of others who have recommended it?

Thanks,

Lee

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

flavo

did you look at http://www.fmsinc.com/Products/analyzer/index.html??

I know Jadedata and Nico both use this (1 & 3 Access TA Experts) and many others. I personally havent need to use something like this (my apps are small and easy to keep a track of)

Dave

Gustav Brock

> I take it you've actually used Rick Fisher's utility that you recommend, or know of others who have recommended it?

Yes, both, though I only have had the need twice for two versions of Access so I only used the free unregistered versions which did excellent and fast for my purpose.

/gustav

lknight1017

ASKER

harfang - Thanks - that revision produced much better results.

gustav (cactus_data) - Thanks - that utility also solves my problem and looks very useful in other ways.

Dave (flavo) - Thanks for the suggestion re the FMS analyzer, but at this point paying $299 for this functionality would be overkill for me.