Thread: 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@rogers.com

Your syntax is not supported (and is not in ANSI SQL).

There _may_ be syntax (I don't recall) to rename the column.  If not, you
can do it through ADOX - the operation to rename a column is supported by
the Jet engine, if not through the SQL syntax (it's been awhile, so you
should check the Jet SQL help file).

As far as ordering, there is no official order of columns in the table.  If
you want them in an order, you can do a select statement and get them out in
whatever order you specify.

Conor

--
Please do not reply to this address, as it is not monitored.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. � 2002 Microsoft Corporation. All rights
reserved.
"Chetan" <progrocker@rogers.com> wrote in message
news:e_X29.272514$WJf1.135895@news01.bloor.is.net.cable.rogers.com...
> 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@rogers.com
>
>