CODEWITHSUNDEEP
sqldatabasems-access
diox8tony
diox8tony

Reputation: 346

Search access database, finding tableName.columnName in queries

our code is C++ MFC.

is there an easy way to search an Access Database for a column being used?

we have a large database, 100+ tables, 300+ queries. and i have the honor of moving 5 columns from one of the most used tables, to a new table. I was easily able to search the SQL in our code and fix it.

but i can't find a way to search the queries in the database file for these 5 columns.

example: queryGetAllItemNumbers = "SELECT tblItems.Number FROM tblItems;"

that query is not shown in plain text anywhere, so i can't easily search for the "tblItems.Number" column being used. So far the easiest way is to run my program, wait for it to crash, then find out which query it crashed on. OR i go through all 300+ queries using my eyes to find the bad SQL...OR change .mdb to .txt and find my column names, which leads to some query names.

I'm hoping someone knows of an Access add-in or something similar that can search the SQL inside.

Upvotes: 0

Views: 881

Answers (1)

Beth
Beth

Reputation: 9607

you need to search the .sql property of all querydef's in your database

Private Function qdefSQL() As Boolean
    Dim db As Database
    Dim qdef As QueryDef
    Dim sSQL As String
    Dim iqdef As Integer

    Set db = CurrentDb
    For iqdef = 0 To db.QueryDefs.count - 1
        Set qdef = db.QueryDefs(iqdef)
        sSQL = qdef.SQL
        If InStr(sSQL, "target") > 0 Then
            Debug.Print qdef.Name
        End If
    Next

    qdefSQL = True
End Function

Upvotes: 2

Related Questions