2

I am looking for a way to change field names in Access.

I am importing data through a macro, from text files. Which i then review through a crystal report. Now, sometimes, the report needs to read the field which I imported at field 7 instead of field 5. So I dont want to restructure the whole import process because I am often working on imports for 500000-1,000,000 records and importing that many records from a text file takes around 5-9 minutes each. I'd really rather only do the import once and switch the field names if this is one of those times. So far I've just been right click rename-ing.

Is there a way for me to click a macro and Rename Field5 -> FieldTemp, Field7 -> Field5, and FieldTemp -> Field7

Ultimately I need to rename Field5 <-> Field7. Suggestions?

UPDATE: I've found this but I'm not sure how to implement that. Would that work like I think it will? If so, how do I get it working?

nixda
  • 27,634
PsychoData
  • 1,447

1 Answers1

2

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"

enter image description here

Step-by-step

  1. Open your Access database and press ALT+F11 to bring up the VBA editor
  2. On the left pane right-click on your database name and insert a module
  3. On the right pane, paste the above code and save it as e.g VBA swap module

  4. 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}            F1
^{F1}           CTRL+F1
+{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 swapped enter image description here

    • Click 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"

nixda
  • 27,634