Programmatically Change Querydef Column Name

Posted on 2005-05-12
Last Modified: 2007-10-28
I would like to change the column names of the queries in an Access97 database.

A user has used captions instead of aliases for column names and I want to change all of the aliases to the captions.

I've figured out how to find all of the names and all of the captions, but it seems that the Querydef.Field.Name property is Read Only.

There is something about how this Name property is Read/Write if the query is somehow NOT appended to a collection.

I know that I can go through the SQL and hunt for commas and stick in "AS Caption1" type strings into the SQL string, but I was hoping for an easier solution.

Is there a way to temporarily un-append a query from the Querdefs collection in order to change the field names?  Or is there a way to create an unappended query?  If I can create an unappended query, I could set all of the names and copy the SQL from the appended query to the unappended query or something like that.

Any help would be much appreciated.
Question by:dastrw
    LVL 44

    Accepted Solution

    I'm afraid you have to deal with the SQL string.  I know you can change the string on the fly, but it does not look like you can play around with

    I keep getting:  'Runtime Error 3291'
    LVL 2

    Author Comment


    Thanks for trying.  I also gave up on trying to change the field name directly.

    I wrote the code to change the field names on the fly, but my routine only works if all of the queries are stand-alone and don't refer to one another.

    In the DB with all of the queries that I am trying to change, there are a number of queries that call other queries.  When I run my routine, the queries that call other queries no longer work because the field names have been changed.

    I would have to write a much more sophisticated query to figure out which queries call which other queries and change everything in the correct order.  I imagine some sort of recursive algorithm would work.

    This might be interesting to work on but I got too much other stuff to worry about right now.

    Here's the code that works if all queries are standalone:
    1) Gets the caption for each column.  Replace all spaces in caption with underscores.
    2) Hunts through SQL for commas and FROM that are not inside parentheses or quotes
    3) For each comma or FROM found, if there is a caption, then insert " AS <caption>" into the SQL string.
    4) If there is already an "AS <name>" then overwrite it with the caption name

    'Main function
    Private Sub cmd_convert_captions_Click()
      Dim db As Database
      Dim i_qry As Integer, n_qry As Integer
      Dim i_fld As Integer, n_fld As Integer
      Dim qName As String, fName As String, qSQL As String, newSQL As String
      Dim fCaption As Variant
      Dim inParen As Integer, iSQL As Long, lSQL As Long, cSQL As String
      Dim inDQ As Boolean, inSQ As Boolean, iStart As Long, iEnd As Long
      Dim lCap As Integer, iCap As Integer, iAS As Long
      Set db = CurrentDb

      'Get no. of queries
      n_qry = db.QueryDefs.Count
      For i_qry = 0 To n_qry - 1
        qName = db.QueryDefs(i_qry).Name
       qSQL = db.QueryDefs(i_qry).SQL
        lSQL = Len(qSQL)
        newSQL = ""
        inParen = 0
        inDQ = False
        inSQ = False
        iSQL = 0
        iAS = 0
        n_fld = db.QueryDefs(i_qry).Fields.Count
        For i_fld = 0 To n_fld - 1
          fName = db.QueryDefs(i_qry).Fields(i_fld).Name
          fCaption = getCaption(db.QueryDefs(i_qry).Fields(i_fld))
          lCap = Len(fCaption)
          For iCap = 1 To lCap
            If (Mid(fCaption, iCap, 1) = " ") Then
              Mid(fCaption, iCap, 1) = "_"
            End If
          Next iCap
          If (lCap > 0) Then
            db.QueryDefs(i_qry).Fields(i_fld).Properties("Caption") = " "
          End If
          iStart = iSQL + 1
          Do While True
            iSQL = iSQL + 1
            If (iSQL > lSQL) Then
              newSQL = qSQL
              Exit Do
            End If
            cSQL = Mid(qSQL, iSQL, 1)
            If (cSQL = "(" Or cSQL = "[" Or cSQL = "{") Then
              inParen = inParen + 1
            ElseIf (cSQL = ")" Or cSQL = "]" Or cSQL = "}") Then
              inParen = inParen - 1
            ElseIf (cSQL = """") Then
              inDQ = Not inDQ
            ElseIf (cSQL = "'") Then
              inSQ = Not inSQ
            ElseIf (UCase(Mid(qSQL, iSQL, 4)) = " AS ") Then
              If ((inParen = 0) And (Not inDQ) And (Not inSQ)) Then
                iAS = iSQL
              End If
            ElseIf (cSQL = "," Or UCase(Mid(qSQL, iSQL, 4)) = "FROM") Then
              If ((inParen = 0) And (Not inDQ) And (Not inSQ)) Then
                If (iAS = 0 Or Len(fCaption) = 0) Then
                  iEnd = iSQL - 1
                  iEnd = iAS
                End If
                If (Len(fCaption) > 0) Then
                  newSQL = newSQL & Mid(qSQL, iStart, iEnd - iStart + 1) & " AS " & fCaption & " " & cSQL
                  newSQL = newSQL & Mid(qSQL, iStart, iEnd - iStart + 1) & " " & cSQL
                End If
                Exit Do
              End If
            End If
        Next i_fld
        newSQL = newSQL & Mid(qSQL, iSQL + 1)
        db.QueryDefs(i_qry).SQL = newSQL
      Next i_qry
    End Sub

    'Helper Function.  There doesn't seem to be a safe way to get the caption if there is no caption so I have to put this in a separate function and error out when no caption is found.
    Private Function getCaption(f As Field)
      On Error GoTo noCaption
      getCaption = f.Properties("Caption")
      Exit Function
      getCaption = ""
    End Function

    LVL 44

    Expert Comment

    dastrw:  Thanks for the summary.  Sorry I could not be of more help.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now