Can I change/re-map field names in existing queries?

[Steve Vincent]

Hi,

I have about 60 queries that I run on imported data. Now all of a sudden
about 20 of the field names are now changed/different in my imported data.
Is there an easy way to update those field names for my existing
queries/reports?

Thanks in advance,
Steve

 

[Clifford Bass]

Hi Steve,

How are you importing? I am curious because that should not change the
field names. Unless you are recreating the tables each time. If that is the
case change your import process so that it only puts data into existing
tables. Then you can disregard the names in the import files, thereby
avoiding the need to change your queries and reports fields' names.

Clifford Bass

 

[Ken Sheridan]

Steve:

If you are creating new tables each time then try the following procedure:

Public Sub UpdateQueries(ParamArray ColumnList() As Variant)

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strMessage As String
Dim strQueryName As String
Dim n As Integer

Set dbs = CurrentDb

For Each qdf In dbs.QueryDefs
strQueryName = qdf.Name
' exclude system queries
If Left(strQueryName, 1) <> "~" Then
strMessage = "Update query: " & strQueryName & "?"
If MsgBox(strMessage, vbQuestion + vbYesNo, "Confirm") = vbYes
Then
For n = 0 To UBound(ColumnList) Step 2
qdf.SQL = Replace(qdf.SQL, ColumnList(n), ColumnList(n +
1))
Next n
End If
End If
Next qdf

End Sub

This loops through all queries in the database and replaces substrings in
each SQL statement. The substrings are the old field names and are replaced
with new ones, both being passed into the procedure as pairs of strings in a
parameter array, e.g.

UpdateQueries "OldFieldName1", "NewFieldName1", "OldFieldName2",
"NewFieldName2", "OldFieldName3", "NewFieldName3"

It asks for confirmation before updating each query, but if there is some
naming convention by which you can identify each query to be updated from
others not to be updated then you can probably skip that in the same way that
the system queries are excluded.

I'd be careful that it doesn't make any changes you don't want, e.g. if a
column name is correct in one query but needs changing in another, then it
would change both. You could move the call of the confirmation message box
so it pops up for each column name to be changed and includes the old and new
names (ColumnList(n) and ColumnList(n + 1) in the message string if you
wanted more control over what's updated and what isn't, i.e. put it in the
'For n = 0 To UBound(ColumnList) Step 2' loop before changing the SQL
property.

With reports and forms if the name autocorrect option is turned on
references to the column names in ControlSource properties will change
automatically, but not if they are referenced in VBA code as far as I'm aware.

Ken Sheridan
Stafford, England

 

[John Spencer]

Another way to solve this problem might be to use an intermediate query if
only one imported table is involved. You can alias the changed field names to
the proper names and run all your other queries against the intermediate query.

SO your intermediate query might look something like this short sample query.

SELECT GoodFieldNameA
, BadFieldNameB as GoodFieldNameB
, BadFieldNameC as GoodFieldNameC
, GoodFieldNameD
, GoodFieldNameE
, BadFieldNameF as GoodFieldNameF
FROM [TableImported]

If you don't want to change the 60 queries that you run, then change the name
of TableImported to TableImportedSource and Save the query as TableImported.
Tables and queries share the same name space, so your existing queries will
automatically use the query named TableImported instead of the table that was
named TableImported.

You would just have to change the source table name in the intermediate query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

That's a much better idea, John. My code solution does carry risks and
doesn't take care of any references to column names in code etc.

Now, why didn't I think of it? Too busy looking at the trees to see the
wood I guess!

Ken Sheridan
Stafford, England

Another way to solve this problem might be to use an intermediate query if
only one imported table is involved. You can alias the changed field names to
the proper names and run all your other queries against the intermediate query.

SO your intermediate query might look something like this short sample query.

SELECT GoodFieldNameA
, BadFieldNameB as GoodFieldNameB
, BadFieldNameC as GoodFieldNameC
, GoodFieldNameD
, GoodFieldNameE
, BadFieldNameF as GoodFieldNameF
FROM [TableImported]

If you don't want to change the 60 queries that you run, then change the name
of TableImported to TableImportedSource and Save the query as TableImported.
Tables and queries share the same name space, so your existing queries will
automatically use the query named TableImported instead of the table that was
named TableImported.

You would just have to change the source table name in the intermediate query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have about 60 queries that I run on imported data. Now all of a sudden
about 20 of the field names are now changed/different in my imported data.
Is there an easy way to update those field names for my existing
queries/reports?

Thanks in advance,
Steve