ALTER TABLE tb ADD COLUMN newcol [AFTER|BEFORE] curcol, in MS Acce

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
Mysql does, is there any option in MS Access 2003?

If it has this option, How to use it?

Thanks,
Potjaman
 
I don't believe there's an equivalent method, but why does it matter? The
physical sequence of fields in a table should be completely immaterial.

If there's some time where it really does matter, create a query that orders
the fields in your desired order, and use the query wherever you would
otherwise have used the table.
 
Hi,


As far as I know, Jet, and MS SQL Server, store their columns in the way
they consider to be the most efficient way, not necessary in the order we
declare them, so there is no physically fixed "before" of "after" column.
You just have "an" additional column, with an ALTER TABLE statement to ADD a
COLUMN.


Otherwise, you could end up with a bit, 63 unused bit, a double-word, a
bit, again 63 unused bits, a double-word, etc (to align the double words on
double word boundaries). Clearly, more efficient is to store: double-word,
double-word, ..., word, word, ..., octet, octet, .... bit, bit, bit. and
each field is on its "boundary".


(Well, I "assume" here that the optional [AFTER | BEFORE] someColumn
intention is to insert a new column before or after a given one, since I
don't know very much MySQL).



Use a SELECT statement with an explicit list to retrieve the columns in a
specific given order.



Hoping it may help,
Vanderghast, Access MVP
 
You can create a view with the column names in the expected
order. In fact, you can rename the base view, and give the
new view the old name of the base view. This has no effect
on query optimisation.

Theoretically, a 'table' is just a 'view', but in practice
indexed views are not that common, and not possible in an MDB
except for on the base view (tabledef). (The DAO 'seek' method
can't be run on a 'linked table' or other view).

Note that when you run Alter Table DDL against an MDB, the
physical re-organisation is not complete until after you
have compacted the database: the table may run slower until
you compact the database.

(david)
 
Back
Top