Replace hard coded string in SQL statements 1

[eerich]

Hi Everyone,

Is there a way to search and replace a string that is hard-coded in sql queries? I need to change all occurrences of a production server that was hardcoded in the WHERE clause in about 70 sql queries. Not certain how to accomplish this in one swoop.

Any help is appreciated.

 

[dhookom]

Create a backup to work with before you do anything else.

There are a number of good utilities for search and replace in Access. Tony Toews lists about 6 of them on his page.

You can use a small function to pull the SQL of a query:

Function GetSQL(pstrQuery As String) As String
    GetSQL = CurrentDb.QueryDefs(pstrQuery).SQL
End Function

Then create a query like:

SELECT MSYSOBJECTS.Name, GetSQL([Name]) AS SQLView
FROM MSYSOBJECTS
WHERE MSYSOBJECTS.Name Not Like "~*" AND MSYSOBJECTS.Type=5 AND GetSQL([Name]) Like "*ServerName*";

This should find the queries. You can use code like faq701-7433 to update the query SQL property.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Duane,

I bought the Search/Replace utility from the link you provided and that worked perfectly!

Thank you very much.