none
Batch queries text modifications RRS feed

  • Question

  • Hi

    How can I go through all queries that begin with 'Export' and change column names (where applicable) from [Column Name] to ColumnName ie remove square brackets and any spaces in column names and save?

    Thanks

    Regards

    Friday, April 14, 2017 9:09 AM

Answers

  • Not sure what you are trying to accomplish, but Rick Fisher's Find and Replace has worked great for me for many years. http://www.rickworld.com/products.html

    Be aware that query columns must be the field name of the table, except for alias', calculations, etc., and once you open the query in design view, Access will put the brackets back.

    • Marked as answer by Y a h y a Friday, April 14, 2017 10:23 PM
    Friday, April 14, 2017 10:21 AM
  • This is untested so make a backup copy of your database first.  Something like this should replace ALL occurrences of [Column Name] with ColumnName in queries whose name begins with "Export".  

    Sub QReplace()

        Dim qdf As DAO.QueryDef

        For Each qdf In DBEngine(0)(0).QueryDefs
            If qdf.Name Like "Export*" Then
                qdf.SQL = Replace(qdf.SQL, "[Column Name]", "ColumnName")
            End If
        Next

    End Sub

    -Bruce


    • Edited by Bruce Hulsey Friday, April 14, 2017 3:38 PM
    • Marked as answer by Y a h y a Friday, April 14, 2017 10:23 PM
    Friday, April 14, 2017 3:37 PM

All replies

  • Not sure what you are trying to accomplish, but Rick Fisher's Find and Replace has worked great for me for many years. http://www.rickworld.com/products.html

    Be aware that query columns must be the field name of the table, except for alias', calculations, etc., and once you open the query in design view, Access will put the brackets back.

    • Marked as answer by Y a h y a Friday, April 14, 2017 10:23 PM
    Friday, April 14, 2017 10:21 AM
  • This is untested so make a backup copy of your database first.  Something like this should replace ALL occurrences of [Column Name] with ColumnName in queries whose name begins with "Export".  

    Sub QReplace()

        Dim qdf As DAO.QueryDef

        For Each qdf In DBEngine(0)(0).QueryDefs
            If qdf.Name Like "Export*" Then
                qdf.SQL = Replace(qdf.SQL, "[Column Name]", "ColumnName")
            End If
        Next

    End Sub

    -Bruce


    • Edited by Bruce Hulsey Friday, April 14, 2017 3:38 PM
    • Marked as answer by Y a h y a Friday, April 14, 2017 10:23 PM
    Friday, April 14, 2017 3:37 PM