You can do this with VBA and a keyboard shortcut assigned to your code.
Public Function swapFieldNames(table, name1, name2) '## Close & save the table in case its open or else you can't rename fields DoCmd.Close acTable, CurrentDb().TableDefs(table).Name, acSaveYes '## Save all touched field names to temporarily variables for later use temp1 = CurrentDb().TableDefs(table).Fields(name1).Name temp2 = CurrentDb().TableDefs(table).Fields(name2).Name '## Rename them to unique names because duplicate names are not allowed CurrentDb().TableDefs(table).Fields(name1).Name = "#$%temp1%$#" CurrentDb().TableDefs(table).Fields(name2).Name = "#$%temp2%$#" '## Rename them again and do the switch CurrentDb().TableDefs(table).Fields("#$%temp1%$#").Name = temp2 CurrentDb().TableDefs(table).Fields("#$%temp2%$#").Name = temp1 '## Open the table to view the result DoCmd.OpenTable CurrentDb().TableDefs(table).Name End Function
I uploaded a demo database where you can press F3 to swap the field names "field5" and "field7"
Step-by-step
- Open your Access database and press ALT+F11 to bring up the VBA editor
- On the left pane right-click on your database name and insert a module
- On the right pane, paste the above code and save it as e.g
VBA swap module
Follow Microsoft's guide on how to use AutoKeys for a quick way to execute your code
- On the Create tab, click Macro (or the arrow below Module and then Macro)
- On the Design tab, in the Show/Hide group, click Macro Names to display the Macro Name column
In the Macro Name column, insert the key to which you want to assign the action
Macro Name Key or keyboard shortcut ^A or ^4 CTRL+A or CTRL+4 F1 ^ CTRL+F1 + SHIFT+F1
- In the Action column, add the action RunCode that runs when your key is pressed
In the Arguments column, insert
swapFieldNames("table1", "field5", "field7")
or whatever you have named your module. Here you also define which table should be used for renaming and what field names should be swappedClick Save or press CTRL+S and name the macro "AutoKeys". Only with this name, Access will know that it should assign a shortcut when reopened
Hint: Don't confuse the terms "macro" and "module". For Access, a macro has nothing to do with VBA as you may think. VBA functions and subroutines are called "modules" and macros are a container where you can define multiple actions like "open this table, filter for value XY and delete all entries"