0

I want to replace name to id only between from clause and where clause in all the queries of MS Access. I've written a code in VBA, but it doesn't support Union/Union All queries and subqueries. Can anyone suggest better way to do this ?

Example 1: input: Select a,b,name,id from x inner join y on x.name=y.name where x IN (select p,r,r from i inner join j on i.name=j.name)

expected output: Select a,b,name,id from x inner join y on x.id=y.id where x IN (select p,r,r from i inner join j on i.id=j.id)

Example 2:

Input: (select p,r,r from i inner join j on i.name=j.name) UNION (select a,b,c from x inner join y on x.name=y.name)

Output: (select p,r,r from i inner join j on i.id=j.id) UNION (select a,b,c from x inner join y on x.id=y.id)

VBA Code:

Public Sub ReusabilityChanges()

Dim qd  As DAO.QueryDef
Dim ErrorQuery, ErrorQueryName As String
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile, oFile1, oFileError As Object
Set oFile = fso.CreateTextFile("E:\Query changes\txtQueries.txt")
Set oFile1 = fso.CreateTextFile("E:\Query changes\txtQueriesUpdated.txt")
 Set oFileError = fso.CreateTextFile("E:\Query changes\txtQueriesWithErrors.txt")

For Each qd In CurrentDb.QueryDefs

    oFile.WriteLine qd.Name & ":"
    oFile.WriteLine qd.SQL
    ErrorQuery = Reusability(qd.SQL)
    ErrorQueryName = qd.Name
    qd.SQL = Reusability(qd.SQL)
    oFile1.WriteLine qd.Name & ":"
    oFile1.WriteLine qd.SQL
    On Error GoTo errHandler



Next
oFile.Close
Set fso = Nothing
Set oFile = Nothing
Set qd = Nothing

errHandler:

oFileError.WriteLine ErrorQueryName & ":"
oFileError.WriteLine ErrorQuery
Resume Next

End Sub

Function Reusability(qd As String)

Dim query As String
query = qd
Dim tillFrom, btwFromAndWhere, afterWhere, inp, oup As String
Dim fromPosition, wherePosition, endPosition As Integer

inp = query
query = UCase(query)
'Debug.Print query
fromPosition = InStr(1, query, "FROM")
endPosition = InStr(1, query, ";")

If query Like "*WHERE*" Then

    wherePosition = InStr(1, query, "WHERE")
    btwFromAndWhere = Mid(query, fromPosition + 4, wherePosition - 4 - fromPosition)

    tillFrom = Left(query, fromPosition + 4)

    afterWhere = Mid(query, wherePosition, endPosition)

    btwFromAndWhere = Replace(btwFromAndWhere, "PRODOFFNAME", "PRODOFFID")

    oup = tillFrom + btwFromAndWhere + afterWhere


Else

    If query Like "*GROUP BY*" Then
        wherePosition = InStr(1, query, "GROUP BY")
        btwFromAndWhere = Mid(query, fromPosition + 4, wherePosition - 4 - fromPosition)

        tillFrom = Left(query, fromPosition + 4)

        afterWhere = Mid(query, wherePosition, endPosition)

        btwFromAndWhere = Replace(btwFromAndWhere, "PRODOFFNAME", "PRODOFFID")

        oup = tillFrom + btwFromAndWhere + afterWhere


     ElseIf query Like "*HAVING*" Then

        wherePosition = InStr(1, query, "HAVING")
        btwFromAndWhere = Mid(query, fromPosition + 4, wherePosition - 4 - fromPosition)

        tillFrom = Left(query, fromPosition + 4)

        afterWhere = Mid(query, wherePosition, endPosition)

        btwFromAndWhere = Replace(btwFromAndWhere, "PRODOFFNAME", "PRODOFFID")

        oup = tillFrom + btwFromAndWhere + afterWhere


    ElseIf query Like "*ORDER BY*" Then
        wherePosition = InStr(1, query, "ORDER BY")
        btwFromAndWhere = Mid(query, fromPosition + 4, wherePosition - 4 - fromPosition)

        tillFrom = Left(query, fromPosition + 4)

        afterWhere = Mid(query, wherePosition, endPosition)

        btwFromAndWhere = Replace(btwFromAndWhere, "PRODOFFNAME", "PRODOFFID")

        oup = tillFrom + btwFromAndWhere + afterWhere


    Else

        wherePosition = InStr(1, query, ";")
        Debug.Print Mid(query, fromPosition + 4, wherePosition - 4 - fromPosition)
        btwFromAndWhere = Mid(query, fromPosition + 4, wherePosition - 4 - fromPosition)
        'Debug.Print btwFromAndWhere
        tillFrom = Left(query, fromPosition + 4)



        btwFromAndWhere = Replace(btwFromAndWhere, "PRODOFFNAME", "PRODOFFID")

        oup = tillFrom + btwFromAndWhere + ";"


    End If


End If
Reusability = oup

End Function

Yash R.
  • 13
  • 1
  • 10
  • try `s2=split(s,"from")` and then `s3=split(s2(x),"where")` `s4=replace(s3(x),"name","id")` split splits by a delimiter and returns array – Nathan_Sav Mar 22 '17 at 08:45
  • Thanks Nathan! Can you please suggest how will split work if we don't have where clause and how can we get output by adding all arrays. – Yash R. Mar 22 '17 at 10:11
  • try it, if you split s by x where x is not found i believe you get s back. So a ubound check would suffice, if only 1 element, we can safely say where wasn't found, more than 1 element in the resulting array, it was found. – Nathan_Sav Mar 22 '17 at 10:51
  • `split(split("Select a,b,name,id from x inner join y on x.name=y.name where x IN (select p,r,r from i inner join j on i.name=j.name)","from")(1),"where")(0)` gives the result `x inner join y on x.name=y.name` have a play with the function. – Nathan_Sav Mar 22 '17 at 10:58
  • I would recommend to use regular expressions. http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Sergey Mar 22 '17 at 16:06
  • If you don't want to reinvent the wheel...There are already tools out there to do this. Such as http://www.rickworld.com/products.html and http://www.skrol29.com/us/vtools.php – June7 Mar 22 '17 at 16:55

0 Answers0