Mass Query Find and Replace

[Littlemalky]

Hi, I have hundreds of queries that reference a UDF name that I need to change to something else. I'd prefer not to manually go in to all of the queries. Is there a way to loops through my database and change all occurrences of the UDF name CC_PAYOUT_DATE and change it to #11/30/2018?

 

[Joe4]

Here is VBA code that will do that for you.

Sub UpdateQueries()
    
    Dim qdf As QueryDef
    Dim mySQL As String
    Dim myNewSQL As String

'   Loop through all queries
    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 1) <> "~" Then
'           Capture SQL code of query
            mySQL = qdf.SQL
'           Replace UDF with hard-coded date in SQL code
            myNewSQL = Replace(mySQL, "CC_PAYOUT_DATE", "#11/30/2018#")
'           Set new query code
            qdf.SQL = myNewSQL
        End If
    Next qdf
    
    MsgBox "Updates complete"

End Sub

Note: Check out how the UDF is used in your queries. If it is referenced like "CC_PAYOUT_DATE()", make sure to include the parends in the Replace statement in the code above.

Also, always back up your database before doing something like this that could potentially update a lot of code!!!

 

[Littlemalky]

Hi, thank you for your help! I'm getting an error saying User-defined type not defined. It stops at Dim qdf As QueryDef.
.
.

 

[Joe4]

What libraries/VBA references do you have selected?

 

[Littlemalky]

I figured it out! I needed to active the DAO reference library. It worked! Thank you so much for your help!

 

[james_lankford]

Here is VBA code that will do that for you.

Sub UpdateQueries()
    
    Dim qdf As QueryDef
    Dim mySQL As String
    Dim myNewSQL As String

'   Loop through all queries
    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 1) <> "~" Then
'           Capture SQL code of query
            mySQL = qdf.SQL
'           Replace UDF with hard-coded date in SQL code
            myNewSQL = Replace(mySQL, "CC_PAYOUT_DATE", "#11/30/2018#")
'           Set new query code
            qdf.SQL = myNewSQL
        End If
    Next qdf
    
    MsgBox "Updates complete"

End Sub

Note: Check out how the UDF is used in your queries. If it is referenced like "CC_PAYOUT_DATE()", make sure to include the parends in the Replace statement in the code above.

Also, always back up your database before doing something like this that could potentially update a lot of code!!!

are you able to write stuff like that off the top of your head ?
I consider myself pretty knowledgeable about VBA, Access and Excel

and even though I've written code almost that exact same function before I can't do off the top of my head

I'd have to open access, create a module, start typing and hope for help from autocomplete LOL

I know the objects and the logic, but not all the syntax

 

[Joe4]

I figured it out! I needed to active the DAO reference library. It worked! Thank you so much for your help!

You are welcome!

are you able to write stuff like that off the top of your head ?
I consider myself pretty knowledgeable about VBA, Access and Excel

and even though I've written code almost that exact same function before I can't do off the top of my head

I'd have to open access, create a module, start typing and hope for help from autocomplete LOL

I know the objects and the logic, but not all the syntax

I'd like to say I can do it all off the top of my head, but I don't always get the syntax right.
I have a "little friend" help me out sometimes (Google search)

 

[welshgasman]

Why not just change the UDF to return that date?