Mass Query Find and Replace

Littlemalky

Board Regular
Joined
Jan 14, 2011
Messages
223
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Here is VBA code that will do that for you.
Code:
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!!!
 
Last edited:
Upvote 0
Hi, thank you for your help! I'm getting an error saying User-defined type not defined. It stops at Dim qdf As QueryDef.
.
.
 
Upvote 0
What libraries/VBA references do you have selected?
 
Upvote 0
I figured it out! I needed to active the DAO reference library. It worked! Thank you so much for your help!
 
Upvote 0
Here is VBA code that will do that for you.
Code:
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
 
Upvote 0
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) ;)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top