Text-search in properties Access objects

Question

Is there a way in Access to search for a certain text in object properties and so on? Just not only in the VBA source code.

I'm asking this because if I change for example the name of a field in a table I've to check a lot of object properties (Record Source, Control Source, Order By, ...). This can be done by trail-and-error or by checking all properties of each control of the forms, but that takes a lot of time.

One option is the Find and Replace tool (nice tool!), but it's a bit of overkill for me. I don't need a text replace (only 'find') and it's 37 dollar for a tool I'll only use a few times a year.

Other suggestions?

Chuff

Answer 1

There is something I often use to find out where some function or query may be hidding somewhere unexpected (in a bound control's RowSource of within a sub-query for instance).

I use an undocumented feature to export all Access objects as raw text files.
Using a text editor that can search within files recursively under a folder(like the free Notepad++ for instance) I am then confident that I find all occurrences, however buried, of a particular string.

The Code for exporting all objects includes my IsBlank() function:

'====================================================================
' Name:    DocDatabase
' Purpose: Documents the database to a series of text files
' From:    http://www.datastrat.com/Code/DocDatabase.txt
' Author:  Arvin Meyer
' Date:    June 02, 1999
' Comment: Uses the undocumented [Application.SaveAsText] syntax
'          To reload use the syntax [Application.LoadFromText]
'          Modified to set a reference to DAO 8/22/2005
'          Modified by Renaud Bompuis to export Queries as proper SQL
'====================================================================
Public Sub DocDatabase(Optional path As Variant = Null)
    If IsBlank(path) Then
        path = Application.CurrentProject.path & "\" & Application.CurrentProject.Name & " - exploded view\"
    End If

    On Error Resume Next
    MkDir path 
    MkDir path & "\Forms\"
    MkDir path & "\Queries\"
    MkDir path & "\Queries(SQL)\"
    MkDir path & "\Reports\"
    MkDir path & "\Modules\"
    MkDir path & "\Scripts\"

    On Error GoTo Err_DocDatabase
    Dim dbs As DAO.Database
    Dim cnt As DAO.Container
    Dim doc As DAO.Document
    Dim i As Integer

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

    Set cnt = dbs.Containers("Forms")
    For Each doc In cnt.Documents
        Application.SaveAsText acForm, doc.Name, path & "\Forms\" & doc.Name & ".txt"
    Next doc

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

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

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

    Dim intfile As Long
    Dim filename as String
    For i = 0 To dbs.QueryDefs.count - 1
         Application.SaveAsText acQuery, dbs.QueryDefs(i).Name, path & "\Queries\" & dbs.QueryDefs(i).Name & ".txt"
         filename = path & "\Queries(SQL)\" & dbs.QueryDefs(i).Name & ".txt"
         intfile = FreeFile()
         Open filename For Output As #intfile
         Print #intfile, dbs.QueryDefs(i).sql
         Close #intfile
    Next i

    Set doc = Nothing
    Set cnt = Nothing
    Set dbs = Nothing

Exit_DocDatabase:
    Debug.Print "Done."
    Exit Sub

Err_DocDatabase:
    Select Case Err

    Case Else
        MsgBox Err.Description
        Resume Exit_DocDatabase
    End Select

End Sub

To use it, just call DocDatabase from the Immediate window in the Access IDE, it will create a set of directories under and 'Exploded View' folder that will contain all the files.

Ecto

+1 I added link to IsBlank() funtion. Nice blog. That will be useful to me. – Bookstack

Think I would prefer Optional path As String = "", but that's probably just me avoiding variant wherever I can. – Bookstack

@HansUp: thanks for the addition, I use the IsBlank() so often I often forget it's not part of the language. You're right about using an explicit string instead of a Variant, where performance matters I usually care about these things, not here through I admit. – Ecto

A small warning - the MkDir function will crash if an object name contains a character which will be invalid in a path name. In my case, a module with a '*' in the name. – Voluminous

@Renaud: you've made my day. I'm VBA rookie and I was looking for long time for something I ca execute immediately on my database. it worked like a charm. It had sme troubles to convert some queries to SQL. ( problem with cascaded joins) but I don't mind as non-SQL query export is OK. You've actually solved my problem with version control of Access code. Thanx a lot – Assr

+1 Upvote For IsBlank()... such beauty in simplicity. Your DocDatabase() function wasn't bad either... just looking for a more holistic approach... may have to automate Access for that one though. – Intromit

Answer 2

Another option is to temporarily turn on the NAME AUTOCORRECT option. It's a badly implemented feature and can damage your database if left on for production deployment, but I very often use it when taking over an Access app created by somebody else in order convert it to use my naming conventions.

You basically turn it on, let it build the dependencies table, then make your changes. You can then walk the tree of dependencies to confirm that it got them all. When you're done, you turn it off.

However, it doesn't work for VBA code. But for changing field names and the like, it's pretty useful if used carefully.

Dereism

Thanks! After some googling I also found this: allenbrowne.com/ser-73.html – Chuff

Answer 3

I amended the code above to strip out temporary objects with "~" in the object name as follows:

Set cnt = dbs.Containers("Scripts")
For Each doc In cnt.Documents
    If Not doc.Name Like "~*" Then
        Application.SaveAsText acMacro, doc.Name, path & "\Scripts\" & doc.Name & ".txt"
    End If
Next doc

Venusian