Using VBA to Edit Multiple Queries in one Step 1

[kjv1611]

I just had a need to edit multiple queries in one database. The need was changing a table name that was referenced in all the databases. Of course, I suppose I could have forced the table name to remain the same, but it wasn't the way I wanted to do things in the given scenerio.

Anyway, I went searching for ways to do this via VBA, b/c I didn't want to manually go in and edit every query.

So, I found the code posted by LittleSmudge in this thread, and put a star on his post there.
Here's the example code he gave, use for his specific scenario:

Public Sub ParamToPT(strQueryName As String, strClause As String)
Dim strSQL As String
Dim intPosn As Integer
   
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)

strSQL = qd.SQL
' in case the existing version has been saved with a Where Clause
' Strip the Where clause off the end
intPosn = InStr(strSQL, "WHERE")
If intPosn > 0 Then
    strSQL = Left(strSQL, intPosn - 1)
End If
' Now add the new Where clause
strSQL = Trim(strSQL) & " WHERE " & strClause

' now update the query code itself
qd.SQL = strSQL

Set qd = Nothing
Set db = Nothing

End Sub

Well, my situation was different, so I used the same idea, but of course the final bit of code was different. Here is what I used to edit every query in my database (when the condition existed, of course):

Private Sub ModifyQueries()
[GREEN]'This code was used on 20100930 to replace the wrong table name in several queries.  The name is wrong, b/c I changed the original table naming scheme.
'I used the idea from code found in the forum thread, [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1120020,[/URL] but modified to fit this scenerio[/GREEN]
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb

    For Each qdf In db.QueryDefs
        strSQL = Replace(qdf.SQL, "OldTableName", "NewTableName")
        qdf.SQL = Trim(strSQL)
        qdf.Close
    Next qdf
    
    strSQL = vbNullString
    Set qdf = Nothing
    db.Close
    Set db = Nothing
End Sub

Also, the qdf.Close line may not be necessary, but it works with it, so that's what I'm going with for now, unless someone gives me a convincing argument why it would be bad in the location. The code ran practically instantaneously, and there were 13 regular queries to sort through, beside however many temp queries were in existence at the time. You'll notice I didn't think about temp queries in the code, above, but if you had a seriously large number of queries to work through, then it might be advantageous to limit those out up front.

Hopefully that'll help someone else who runs into the same or similar situation.

 

[PHV]

And what about the forms and reports ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

In this particular instance, I didn't have any that needed changing. Actually, no reports in the database, and the only one form doesn't pull any data. It just gives an easy way for the user to perform the necessary tasks for the specific process - in Access and out of Access.

 

[kjv1611]

But that bit would be good to add in for anyone with other scenarios. I just hadn't thought of it, since I didn't need it at the time.

Feel free to modify it, repost it, whatever, if you (or anyone) has any better suggestions, of course