Problem: ALTER TABLE with Jet as provider

Article View: comp.databases.postgresql.questions

Article #1

Back to comp.databases.postgresql.questions Previous Next

From: Chetan
Date: Sat, 03 Aug 2002 21:32

31 lines
2353 bytes

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

Technical Details

Message-ID: <e_X29.272514$WJf1.135895@news01.bloor.is.net.cable.rogers.com>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-01!sn-xit-04!supernews.com!64.152.100.70.MISMATCH!ps01-sjc1!news.webusenet.com!news03.bloor.is.net.cable.rogers.com!news01.bloor.is.net.cable.rogers.com.POSTED!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 10992f,ab7b1d96f3794f47,start
X-Google-Attributes: gid10992f,public
X-Google-Thread: f6611,ab7b1d96f3794f47,start
X-Google-Attributes: gidf6611,public
X-Google-Thread: 11301e,ab7b1d96f3794f47,start
X-Google-Attributes: gid11301e,public
X-Google-ArrivalTime: 2002-08-03 14:40:33 PST
Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-01!sn-xit-04!supernews.com!64.152.100.70.MISMATCH!ps01-sjc1!news.webusenet.com!news03.bloor.is.net.cable.rogers.com!news01.bloor.is.net.cable.rogers.com.POSTED!not-for-mail
From: "Chetan" <progrocker@rogers.com>
Newsgroups: comp.databases.postgresql.questions,bit.databases.mssql-l,microsoft.public.sqlserver.programming
Subject: Problem: ALTER TABLE with Jet as provider
Lines: 33
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <e_X29.272514$WJf1.135895@news01.bloor.is.net.cable.rogers.com>
Date: Sat, 03 Aug 2002 21:32:58 GMT
NNTP-Posting-Host: 24.112.239.232
X-Complaints-To: abuse@rogers.com
X-Trace: news01.bloor.is.net.cable.rogers.com 1028410378 24.112.239.232 (Sat, 03 Aug 2002 17:32:58 EDT)
NNTP-Posting-Date: Sat, 03 Aug 2002 17:32:58 EDT