Macro in Access VBA to find and replace text in SQL view

[arianbet3]

Hello,

I wanted to create a VBA macro in Access that would find and change text in a query in an SQL view.

I wanted to do it like in Excel:

Sub Find_and_Replace()

Range("A1:A10").Replace What:="text1", Replacement:="text2"

End Sub

But I don't know how to implement it in Access VBA. How to refer to a query?

 

[theDBguy]

Hi. Welcome to AWF!

Unfortunately, Access does not have cells like Excel does, so you can't really refer to a cell like that. Instead, you can use an UPDATE query to change the data.

 

[arianbet3]

Hi. Welcome to AWF!

Unfortunately, Access does not have cells like Excel does, so you can't really refer to a cell like that. Instead, you can use an UPDATE query to change the data.

I know Access doesn't have cells like Excel.
Is it possible to do something similar in Access VBA to the example I gave, but instead of Range("A1:A10"), refer to a query in Access?
I wanted to create a macro in Access VBA which will open the query, change the text in the SQL view, save and close the query.

 

[theDBguy]

I know Access doesn't have cells like Excel.
Is it possible to do something similar in Access VBA to the example I gave, but instead of Range("A1:A10"), refer to a query in Access?
I wanted to create a macro in Access VBA which will open the query, change the text in the SQL view, save and close the query.

Can you post a sample SQL for your query and then show us what you would like to change in it?

 

[kentgorrell]

The simple answer is

Public Sub ReplaceInSQL()
Dim qdf As DAO.QueryDef
Dim strFind As String
Dim strWith As String

    strFind = "Whatever"
    strTo = "Whichever"
    Set qdf = CurrentDb.QueryDefs("QueryName")
    With qdf
        .SQL = Replace(.SQL, strFind, strTo)
    End With
End Sub

However, this will work OK only if the Find string is totally unique. This code does not account for if you are looking for a whole word for example.
BTW, Access now has find and replace built into the SQL view and it works pretty well.
but you haven't really given us a whole lot of why you want to do this. Even an example would help.

 

[arianbet3]

The simple answer is

Public Sub ReplaceInSQL()
Dim qdf As DAO.QueryDef
Dim strFind As String
Dim strWith As String

    strFind = "Whatever"
    strTo = "Whichever"
    Set qdf = CurrentDb.QueryDefs("QueryName")
    With qdf
        .SQL = Replace(.SQL, strFind, strTo)
    End With
End Sub

However, this will work OK only if the Find string is totally unique. This code does not account for if you are looking for a whole word for example.
BTW, Access now has find and replace built into the SQL view and it works pretty well.
but you haven't really given us a whole lot of why you want to do this. Even an example would help.

Thank you, that's exactly what I meant.
I know it's hard to tell without a specific example, but I would have to write and explain too much. To put it briefly, at my work I have an Access database with many queries with different criteria for different company departments. Each query serves as a data source for a separate Excel file (queries are like data splitters from the entire database). I need to change/update query parameters from time to time (for example: year, month, special criteria) and I didn't want to do it manually. Now I will write one macro that will change this automatically in all queries. Just find and replace the appropriate criteria in the SQL code. e.g. find the period 2023.08 and replace it with 2023.09. I know that Access has a find and replace option, but I would have to open each query separately and do a find and replace for each query.
Thank you again, best regards.

 

[CJ_London]

Just find and replace the appropriate criteria in the SQL code. e.g. find the period 2023.08 and replace it with 2023.09.

Usual way to do that is to have a configuration table and/or form to contain these values and modify your queries once to reference them

 

[strive4peace]

@arianbet3 when you're looking at SQL, you can press:

Ctrl-F to Find
Ctrl-H to Replace

that was added awhile back but not many know about it unless someone tells you ~