Macro in Access VBA to find and replace text in SQL view (1 Viewer)

[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.

 

[Pat Hartman]

In a relational database, you use an update query. The WHERE clause selects the row you want to update based on a unique index. If you don't use unique criteria, your update query will update multiple rows. Use the .Execute method to run the update query.

 

[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.

 

[Pat Hartman]

Without knowing the exact problem, it is difficult to formulate SQL.

    Dim db As DAO.Database
    Dim strSQL As String

    Set db = CurrentDb()
    strSQL = " UPDATE tbl1 SET tbl1.SomeField = " & Me.yourDataControlName
    strSQL = strSQL & " WHERE tbl1.tbl1ID = "  & Me.yourKeyControlName 
    db.Execute strSQL, dbSeeChanges

In this example, you are concatenating the two values into the query string Don't forget that string values need to be delimited with quotes and dates with ##

 

[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

 

[Pat Hartman]

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

You should NEVER be storing hard coded parameters. That is the problem. Parameter values should be provided at runtime from a form or a calculation. If you have hardcoded parameters, you probably have a huge number of "duplicate" queries. That lets you have one query that you use regardless of the year and month. If it makes sense, you can also set it up as a range. Your form can have default logic so that if the user normally wants to always see last month, you can calculate last month and make that the default when the form opens. The user can then override it if he wants to see July or what's happened so far in September.

 

[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 ~

 

[Pat Hartman]

Or, you can hardcode whatever you want and set "notes to self" so you remember to use Find and Replace to fix your problem.