none
How to scan queries and reports for strings? RRS feed

  • Question

  • I have to revise a database that was developed by someone with little knowledge of the correct way to do things.

    There is one main table and about a dozen or so reference tables. These are updated via drop-downs which have limit to list property set to NO.

    Instead of the main table referring to the reference tables via foreign keys, the full text of the primary field is duplicated in the main table for each record referring to it. The main table has a  lookup added for each of these columns.

    There are no relationships set up, so there is no referential integrity. There are many mismatches between the main table values and the reference tables.

    An issue arises now because the user wants to change the descriptions in one of the most heavily used reference tables.

    I know for a fact that these values are hard-coded in many queries and probably in reports and other places.

    What I'd like to do is to convert the database to use foreign keys and set up appropriate relationships.

    Does anyone know a way to scan the queries and reports in order to be able to find the table names, columns and hard-coded strings.

    There are hundreds of queries and reports so a manual approach would take weeks.


    Tuesday, September 29, 2015 8:21 PM

Answers

All replies

  • Create a new base table and your related tables. Transfer the data. Drop or backup the old table. Create a query which JOINs your base table with related tables and name it like the original table. Then you can change the existing queries when needed.
    Tuesday, September 29, 2015 8:27 PM
  • Hi. See if this utility will work for you: Search For Text
    Tuesday, September 29, 2015 8:28 PM
  • This function finds text in queries. You could use the same approach for reports.

    Public Function FindFieldUsageInQueries(strFieldname As String) As String
    'Examine all querydef's for the strFieldName
        Dim qdf As DAO.QueryDef
        Dim strOut As String
        
        strOut = "Searching for the string: " & strFieldname
        
        'Search the querydefs
        strOut = strOut & vbCrLf & "Used in Queries: "
        For Each qdf In CurrentDb.QueryDefs
            If InStr(qdf.SQL, strFieldname) Then
                strOut = strOut & vbCrLf & "   " & qdf.Name
            End If
        Next
        
    On Error Resume Next
        Set qdf = Nothing
        FindFieldUsageInQueries = strOut
        Exit Function
    End Function


    Paul

    Wednesday, September 30, 2015 8:51 PM
  • That text search form is a very nice utility to have. I made a few improvements to it to help with my purpose. Of course it still leaves me with a ton of work to do.
    Thursday, October 1, 2015 6:58 PM
  • That text search form is a very nice utility to have. I made a few improvements to it to help with my purpose. Of course it still leaves me with a ton of work to do.

    Hi. Glad to hear it was a bit of a help for you. If you made some improvements to it, may I suggest that you submit the updated version to UtterAccess' Code Archive, so that others can benefit from your work as well.

    Good luck with your project.

    Thursday, October 1, 2015 7:11 PM
  • Take a look at Rick Fisher's Find and Replace tool. It has plenty of useful features and the cost is nominal. Will pay for itself many times over.

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

    Friday, October 2, 2015 10:21 AM
  • Of course it still leaves me with a ton of work to do.
    That's why using a view makes live much easier :)
    Friday, October 2, 2015 11:56 AM