In Datasheet View of a Microsoft Access query, if the columns are re-ordered by dragging them around, they no longer follow the order defined in the underlying Access SQL. If the column order is changed in the SQL, the order in the Datasheet no longer follows. Even if the order is dragged back to match the SQL, it still won't begin following the SQL again. The order of the columns in Datasheet View has essentially become disconnected from the SQL and is stuck in "manual mode".
How can this be reset so the column order in Datasheet View begins following the SQL again?
I've searched StackOverflow and Google for the answer to this, but found none.
Note: I'm highly fluent in Access VBA, so if anyone knows of a static or transient property for this buried deep in the properties collection of the query, I can work with that. Thanks.
For each field in your QueryDef, set the field's .Properties("ColumnOrder").Value = 0
Here is an example in the Immediate window. I had manually moved the first column into the last position on the datasheet and saved the query with that layout.
? CurrentDb.QueryDefs("qryBase").Fields.Count
3
for i = 0 to 2 : ? CurrentDb.QueryDefs("qryBase").Fields(i).Properties("ColumnOrder").Value : next
3
1
2
Changing ColumnOrder to 0 causes Access to use the querydef's SQL to determine the column order for datasheet view:
for i = 0 to 2 : CurrentDb.QueryDefs("qryBase").Fields(i).Properties("ColumnOrder").Value = 0 : next
for i = 0 to 2 : ? CurrentDb.QueryDefs("qryBase").Fields(i).Properties("ColumnOrder").Value : next
0
0
0