[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

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
0
lknight1017
Asked:
lknight1017
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
harfangCommented:
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
0
 
harfangCommented:
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 :)
0
 
flavoCommented:
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
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
harfangCommented:
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:)
0
 
flavoCommented:
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
0
 
flavoCommented:
www.fmsinc.com has a good tool for this sort of stuff if you got some $$
0
 
Gustav BrockCIOCommented:
Have a look at Rick Fisher's excellent Search and Replace:

  http://www.rickworld.com/products.html

It's free for trial and cheap to purchase.

/gustav
0
 
lknight1017Author Commented:
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
0
 
lknight1017Author Commented:
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
0
 
harfangCommented:
Lee, please forgive me. I tried to recreate something too fast, and, as you noticed, there were several errors in the code... and some more :( This is acceptable for short extracts of templates, not for an entire module.
As a bonus for you, it now addresses the problem of "qryClients", "qryClientsAccounts", "qryClientsAccountsActive", etc. Let me post it once more, you might still find the use for it.

Sorry again for waisting your time. Hope it won't happen again

Good Luck

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

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

Private Function RowSource(pctl As Control) As String

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

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

End Function

 Function Contains(pstrIn As String, pstrWhat As String) As Boolean

' Uses Instr to find pstrWhat in pstrIn, but also check the previous and next character.
' Returns if the "word" is found...

    Dim lngPos As Long
   
    lngPos = InStr(pstrIn, pstrWhat)
    If lngPos Then
        If lngPos > 1 Then
            Select Case Mid(pstrIn, lngPos - 1)
                Case "A" To "Z"
                Case "0" To "1"
                Case "_"
                Case Else
                    Contains = True
            End Select
        Else
            Contains = True
        End If
        If Contains Then
            Contains = False
            Select Case Mid(pstrIn, lngPos + Len(pstrWhat), 1)
                Case "A" To "Z"
                Case "0" To "1"
                Case "_"
                Case Else
                    Contains = True
            End Select
        End If
    End If
   
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 qdf As QueryDef

    With CurrentDb
        For Each qdf In .QueryDefs
            If Left$(qdf.Name, 4) = "~sq_" Then
                ' system query, managed by Access
            Else
                FindQuery qdf.Name
            End If
        Next qdf
    End With
   
End Sub

Sub FindQuery(pstrQryName As String)

' Finds any name (e.g. a query name) in:
'   - queries using it as a subquery
'   - forms and form controls using it
'   - reports and report controls using it
' Note: can be used to find any other string (table name, field name, etc)

    Dim qdf As QueryDef
    Dim doc As Document
    Dim ctl As Control
    Dim fContainer As Boolean

    Debug.Print pstrQryName
    With CurrentDb
       
        ' Used in other queries?
        For Each qdf In .QueryDefs
            If Left$(qdf.Name, 4) = "~sq_" Then
            Else
                If Contains(qdf.SQL, pstrQryName) Then _
                    Debug.Print "   * "; qdf.Name
            End If
        Next qdf
       
        ' Used in Forms?
        For Each doc In .Containers("Forms").Documents
            fContainer = False
            DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acIcon
            With Forms(doc.Name)
                If Contains(.RecordSource, pstrQryName) Then
                    Debug.Print "   * "; doc.Name
                    Debug.Print "      .RecordSource"
                    fContainer = True
                End If
                For Each ctl In .Controls
                    If Not IsNull(RowSource(ctl)) Then
                        If Contains(RowSource(ctl), pstrQryName) Then
                            If Not fContainer Then
                                Debug.Print "   * "; doc.Name
                                fContainer = True
                            End If
                            Debug.Print "      > "; ctl.ControlName
                        End If
                    End If
                Next ctl
            End With
            DoCmd.Close acForm, doc.Name
        Next doc
       
        ' Used in Reports?
        For Each doc In .Containers("Reports").Documents
            fContainer = False
            DoCmd.OpenReport doc.Name, acViewDesign
            ' DoCmd.Minimize
            With Reports(doc.Name)
                If Contains(.RecordSource, pstrQryName) Then
                    Debug.Print "   * "; doc.Name
                    Debug.Print "      .RecordSource"
                    fContainer = True
                End If
                For Each ctl In .Controls
                    If Not IsNull(RowSource(ctl)) Then
                        If Contains(RowSource(ctl), pstrQryName) Then
                            If Not fContainer Then
                                Debug.Print "   * "; doc.Name
                                fContainer = True
                            End If
                            Debug.Print "      > "; ctl.ControlName
                        End If
                    End If
                Next ctl
            End With
            DoCmd.Close acReport, doc.Name
        Next doc
    End With
               
End Sub
0
 
flavoCommented:
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
0
 
Gustav BrockCIOCommented:
> 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
0
 
lknight1017Author Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now