Is there a way to delete a column in a table in VBA?

[calihiker]

Hi, I have many tables that have the same columns and some of those columns I need to delete. Is there a way to do this in VBA (or even easier, a query)?

I found out the the ACCESS's Jet Database doesn't accept the 'ALTER TABLE' syntax when I tried that in QBE and VBA... Is there a reference library of something I need to add? Any ideas would be very appreciated, thanks.

 

[TonyJollans]

Hi calihiker,

What do you mean when you say ACCESS's Jet Database doesn't accept the 'ALTER TABLE' syntax ? How did you try and use it?

docmd.runsql "alter table tablename drop columnname"

ought to work without any special references.

Enjoy,
Tony

 

[SkipVought]

Hi,

You must be in Access. The Column Property does NOT apply to TableDef Objects (tables).

However, the Field Object is what refers to a "column" in a table. There is no Delete Method for a TableDef Field Object. A Field can be deleted from a Recordset but not a TableDef.

But here is a procedural method...
1. unload the table
2. scratch the table
3. create a new table with the desired fields

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Tony,

I don't see an Alter Table or Alter anything in Access help.

What am I missing?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Skip,

It is all there but it doesn't appear to be indexed properly. Look at the Help for RunSQL and you should find links to the individual DDL statements.

Enjoy,
Tony

 

[SkipVought]

Ah,

I see Alter Table, amoung other RunSQL Actions, but can find nothing regarding the syntax that you used in the post above.

I also see the RunSQL Method and it does not add much more specific syntax info.

Where's the secret treasure hidden? ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Skip,

On my machine ALTER TABLE in RunSQL Help is a hyperlink to its own Help (in 97 and 2K) where the syntax is given as ..

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL]     [CONSTRAINT index] |
    ALTER COLUMN field type[(size)] |
    CONSTRAINT multifieldindex} |
    DROP {COLUMN field I CONSTRAINT indexname} }

If that doesn't work, in 97 look up DROP COLUMN statement in the Help index. In 2K it's a bit trickier - you can try various words and eyeball the list of topics for a keyword in upper case - most of the DDL helps hyperlink amongst themselves - I just tried a few and CONSTRAINT gave CONSTRAINT Clause as the third entry, from where I could jump via the See Also list at the bottom.

It really looks like the SQL Help was created independently of the rest of the Help.

Enjoy,
Tony

 

[calihiker]

Thanks for the replies,

I ran the line

DoCmd.RunSQL "Alter table 2000 Gifts Drop1 drop Year Code"

and received an error saying "Syntax Error in Alter Table Statement"

Do I need to do anything special with this statement if the table and column names have spaces in them?

Thanks

 

[TonyJollans]

Hi calihiker,

You guessed it. When your names have spaces in them they must be enclosed in brackets, so try ..

DoCmd.RunSQL "Alter table

[

2000 Gifts Drop1

]

 drop

[

Year Code

]

"

Enjoy,
Tony

 

[calihiker]

Great, that worked! I put in through some loops since I have many tables and columns for it to go through, but the code came up with an error once it found a table that didn't have the specified column... Is there a way to easily bypass that so the code doesn't stop midway? Can I use some condition or something? Here's my code if that helps...

Sub AlterTables()

Dim Years As Integer, Drops As Integer

For Years = 0 To 2 'goes through the years 2000 to 2002
For Drops = 1 To 4 'goes through the drops 1 to 4

DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [Year Code]"
DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [List Type Code]"
DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [Drop Code]"
DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [Multi Code]"
DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [Optimized Code]"
DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [Remail Code]"
DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [Date Code]"
DoCmd.RunSQL "Alter Table [200" & Years & " Gifts Drop" & Drops & "] Drop [Tracking Code]"

Next Drops
Next Years


End Sub


Thanks in advance

 

[TonyJollans]

Hi calhiker,

Just add the line ..

On Error Resume Next

.. before your ALTERs (probably before the For Loops in your case). It sets up error handling (to effectively ignore errors) until explicitly changed so only needs coding once.

Enjoy,
Tony