I have a problem with the Query Access. My code is as follows:
string query = "ALTER TABLE Student ADD COLUMN Surname MEMO AFTER 'Name'";
Why always inserts the column at the end of the table? Is there any method to insert a new column in a specific position?
First of all, I don't see any reason to add your column to a specific position. You can always use the column order as you want for a select statement for example..
Why always inserts the column at the end of the table?
Because it is designed like that?
There is a method to insert a new column in a specific position?
As far as I know, there is no way to do it without rebuilding your table.
From ALTER TABLE syntax for changing column order
Today when you use ALTER TABLE ADD to add a column, a new column is always placed as the last column. This is far from often desireable. Often developers and database designers want to keep some logic in a column order, so that related column are close to each other. A standard rule we keep in the system I work with is to always have auditing columns at the end. Furthermore many graphical design tools encourage this kind of design, both bottom-end tools like the Table Designer in SSMS as well as high-end data-modelling tools such as Power Designer.
Today, if you want to maintain column order you have no choice but to go the long way: create a new version of the table and copy over. It takes time, and if not implemented correctly, things can go very wrong.
