Search and replace query SQL with VBA

[gdesreu]

Hi guys, I have to alter my front end to point to postgres instead of local access tables. The table names will be identical except that postgres will include "_public" in the naming convention. I will need to switch the front end back and fourth between local tables and Postgres so I was hoping to simply search the query SQL for "tablename" and replace with "public_tablename". This way I can quickly modify my front end without a lot of effort when I need to do this. I have multiple queries I need to do this to and its getting quite labor intensive. If I can see how to search and replace one query, then I can apply it to the rest of my queries. I know Ive done this before but I cannot find my working example anywhere. Does anyone have some VBA for this? I just need an example on one query.
Thanks

 

[james_lankford]

"will need to switch the front end back and fourth between local tables and Postgres "


so you want to change the front end to use postgres tables and then back to access tables and then back to postgres and back to ... and so on ?

 

[gdesreu]

"will need to switch the front end back and fourth between local tables and Postgres "


so you want to change the front end to use postgres tables and then back to access tables and then back to postgres and back to ... and so on ?

yes, its a front end and I dont want to have to revise two front ends when there are revisions, plus we need the capability to work remotely and postgres tables are simply not portable enough.

 

[xenou]

Here is an example:

Sub foo()
Dim qdf As QueryDef

    For Each qdf In CurrentDb.QueryDefs
        qdf.SQL = Replace(qdf.SQL, "Table4", "public_Table4")
    Next qdf

End Sub

 

[gdesreu]

Here is an example:

Sub foo()
Dim qdf As QueryDef

    For Each qdf In CurrentDb.QueryDefs
        qdf.SQL = Replace(qdf.SQL, "Table4", "public_Table4")
    Next qdf

End Sub

Yes this is great!!!! Sorry it took me a while to get back to you. (work calls once again with a different fire to put out). This is EXACTLY what I wanted to do. I am trying to think ahead where I may want to run this on selected queries only. Is it possible to select a single query and run this? for example can I just define the qdf as my query name?
Either way....Thanks again, you've saved me hours of work!

 

[xenou]

you can use qdf.Name to check query names.

You have to be careful with this. You can easily ruin a query (most easily, turn a table name into public_public_Table4 by running 2x).

Here's a "toggle" approach:

Sub foo()
Dim qdf As QueryDef
    
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Name = "Query9" Then
            If InStr(qdf.SQL, "public_") > 0 Then
                qdf.SQL = Replace(qdf.SQL, "public_", "")
            Else
                qdf.SQL = Replace("Table4", "public_Table4")
                qdf.SQL = Replace("Table5", "public_Table5")
                qdf.SQL = Replace("Table6", "public_Table6")
            End If
    Next qdf

End Sub

 

[gdesreu]

you can use qdf.Name to check query names.

You have to be careful with this. You can easily ruin a query (most easily, turn a table name into public_public_Table4 by running 2x).

Here's a "toggle" approach:

Sub foo()
Dim qdf As QueryDef
    
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Name = "Query9" Then
            If InStr(qdf.SQL, "public_") > 0 Then
                qdf.SQL = Replace(qdf.SQL, "public_", "")
            Else
                qdf.SQL = Replace("Table4", "public_Table4")
                qdf.SQL = Replace("Table5", "public_Table5")
                qdf.SQL = Replace("Table6", "public_Table6")
            End If
    Next qdf

End Sub

Thanks Xenou! I cant thank you enough. This is exactly what I was looking for. Ill be able to easily modify my front end now as opposed to carrying two different front ends around. Very slick! We like to keep most small projects in access but some of them eventually outgrow access capacity and therefore we switch to postgres (for some tables) but would still like to maintain access as the front end as that is where all of our processes are. We also work on sections of the data at home a lot and postgres is not user friendly in that area for our needs. This makes it very easy to move data to a local access version of the table to work on at home, then move back, append and switch back to postgres.