Problem: ALTER TABLE with Jet as provider

Hello,

I have this problem where I want to rename a field in a table in my Access
DB and then move it before another field in the same table. This is proving
to be very difficult because the ALTER TABLE command in the Jet engine
doesn't allow you to MOVE or RENAME any of the fields!

here's my code:

'I want to delete the old field date and replace it with the
temporary one

'move the temporary field before the field I want to remove <-- this
doesn't work
cnn.Execute "ALTER TABLE MyTable MOVE TempField BEFORE OldField"

'delete the field I want to remove
cnn.Execute "ALTER TABLE MyTable DROP COLUMN OldField"

'rename the temporary field to "NewField" <--this doesn't work
either
cnn.Execute "ALTER TABLE MyTable RENAME TempField TO NewField"

I think the ALTER TABLE command is great in SQL and it would be great if I
could use it with the MSJet engine for everything but it doesn't look like I
can.

Does anyone have any suggestions?

Chetan
progrocker(at)rogers(dot)com