Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Programmatically Change Querydef Column Name

Posted on 2005-05-12
Medium Priority
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
  • 2
LVL 44

Accepted Solution

GRayL earned 2000 total points
ID: 14000778
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'

Author Comment

ID: 14014816

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

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


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Implementing simple internal controls in the Microsoft Access application.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

571 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