Overview
This lecture explains how to rename a column in a Microsoft Access table using a three-step process, as Access SQL does not support direct column renaming.
Limitations in Microsoft Access SQL
- Microsoft Access SQL does not provide a simple command to rename columns directly.
- Other databases (like Oracle or MySQL) offer straightforward ALTER TABLE...RENAME COLUMN commands, but Access does not.
Step-by-Step Process to Rename a Column
- To rename a column in Access, follow a three-step workaround:
- Step 1: Add a new column with the desired name using ALTER TABLE.
- Step 2: Copy data from the old column to the new column with an UPDATE statement.
- Step 3: Delete the old column using ALTER TABLE...DROP COLUMN.
Example: Renaming DOB to Birthday
- Step 1:
ALTER TABLE captain ADD COLUMN birthday DATE; adds a new "birthday" column to the "captain" table.
- Step 2:
UPDATE captain SET birthday = DOB; copies data from "DOB" to "birthday".
- Step 3:
ALTER TABLE captain DROP COLUMN DOB; removes the original "DOB" column.
Additional Notes
- After renaming, you can rearrange the column order manually in Access by dragging the column to the desired position.
- Data is preserved as long as it is copied before deleting the old column.
Key Terms & Definitions
- ALTER TABLE — SQL command used to modify an existing table structure.
- UPDATE — SQL command used to modify existing data in a table.
- DROP COLUMN — SQL clause used to remove a column from a table.
- DOB — Common abbreviation for "date of birth".
Action Items / Next Steps
- Practice renaming a column in Access using the three-step process.
- Prepare for the next module on advanced and nested queries, including UNION queries.