How to Reset a Table Layout in Microsoft Access – Hack

Feature/Problem

In Microsoft Access, when you open a Table in “Datasheet View”, each column postion, order and column width can be changed to suit.  These settings comprise the “Table Layout”.

If you change the Table Layout in this way, when you come to close the Table, you will see the following message:

“Do you want to save changes to the layout of table: ‘tablename’”

If you click “Yes” to save the Layout, the Table layout will look the same next time you open the table.  However, there may be reasons that you want the Table Layout to revert to the original layout, and restore the column position, order and width to its default.

As an Access Developer, I’m often working with tables at this level and I sometimes need to do this.  One example is if I want to copy and paste data from one Access database copy of the same design to another copy, which you can do using the Datasheet View in each, where the column orders are different in the two database files.  If the Table Layout of the table in question has columns in a different position, this won’t work.

Solution to Reset the Column Layout

As far as I can tell, there is no obvious button or command to “reset” a Table to it’s original layout, i.e. to put the columns back to their original width and order.

However, this will reset the column order (but not the column widths – but see below)

1. Open the Table in the regular Datasheet View
2. Sort any of the Table columns, by clicking the little down arrow next to one of the column names.
3. Close the table, and click “Yes” to save the layout.
4. Open the table in “Design View”
5. Open the Table’s “Property Sheet”
6. Delete the sort order text in the “Order By” property.
7. Close the table, and click “Yes” to save the layout.

When you open the table again, the column order will be reset and thus will match the same order of that shown in the Table Design View. 

Solution to Reset the Column Widths?

1. Open the Table in the regular “Datasheet View”
2. Click the little square in the top left of the Table (this selects all columns)
3. Move your mouse to the right edge of any column heading box in the column heading row, and when the double-headed arrow cursor appears, double-click on the mouse left button.

The column sizes will all be reset to match the widest entry in the current screenful, which may be the width of the column name or the width of the widest data in the column visible.