asked on

Programmatically Change Querydef Column Name

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.

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dastrw

ASKER

GRayL,

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
Else
iEnd = iAS
End If
If (Len(fCaption) > 0) Then
newSQL = newSQL & Mid(qSQL, iStart, iEnd - iStart + 1) & " AS " & fCaption & " " & cSQL
Else
newSQL = newSQL & Mid(qSQL, iStart, iEnd - iStart + 1) & " " & cSQL
End If
Exit Do
End If
End If
Loop
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
noCaption:
getCaption = ""
End Function

GRayL

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

Ray