ACC: Changed Field Names in Table Are Not Reflected in Query

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

If you create a query using an existing table and then change a field name in that table, the new field name does not show in the query grid when the query is reopened. The old field name in the query grid has been made into an expression similar to this:

   Expr1: TableName.[Old FieldName]

CAUSE

Changes to table field names do not propagate up through the queries based on that table.

RESOLUTION

Replace the new expression in the field cell of the query grid with the correct field name from the table. The field list in the upper half of the query window will show the current field names.

STATUS

This behavior is by design.

MORE INFORMATION

This behavior actually preserves the original query so that you can see what was originally included in the query.

Steps to Reproduce Behavior

1. Create the following table. Save it, and enter a few records:

         Table: Table1
         --------------------
         Field Name:  OldName
         Data Type:   Text
   
   

2. Create the following query adding the table that you created in step 1:

         Query Name: Query1
         -------------------
         Field Name: OldName
         Show:       True
   
   

3. Run the query. Note that data in the OldName field shows up in Datasheet view of the query.

4. Open the Table1 table in Design view and change the OldName field to NewName. Close and save the table.

5. Open the Query1 query in Design view. Note that the field cell now shows the following:

         Expr1: Table1.[OldName]
   

   If you run the query, this field will be blank. To correct this, choose NewName from the list in the field cell of the query.