DROP COLUMN by reference rather than name? 1

[BTilson]

Hello all,

Is there any way to use the ALTER TABLE/DROP COLUMN combo to drop a column by reference number? For instance, can I instead "drop the first column" rather than "drop column XYZ"? If there are any other ways to do this besides the SQL statement, then that's fine too.

What is going on is that we receive a spreadsheet from one of our customers of a list of orders. But the way the spreadsheet is formatted coming from them causes an invalid column to be created as the first column in the table. The clincher is this though, the column will have a different name every time they send it (it's based on a date).

I can just manually format the spreadsheet as necessary prior to importing it, but it would be MUCH preferable to handle as much of it through code as possible. The program I am working on is being used by people who may not necessarily grasp the necessary steps to prep the spreadsheet for an easy import.

If anyone has any ideas or suggestions, I'd be very grateful!

Thanks!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[jrbarnett]

I don't know a way of using DROP COLUMN with a numeric index, but if the errant column name is based on a date, can you predict the column name based on the input file details?
Or could your spreadsheet export force a column header to use a particular name, so it would always be consistent?

John

 

[Golom]

Not that I know of but you can

Sub DropColumn(TableName As String, ColNum As Integer)
Dim ColName As String
Dim db      As DAO.Database
Set db = CurrentDb

ColName = db.TableDefs(TableName).Fields(ColNum).Name

db.Execute "ALTER TABLE " & TableName & " DROP COLUMN " & ColName

End Sub

 

[BTilson]

Haven't tested this yet, but it looks like it should work perfectly, and do exactly what I want. Thanks!!

Brooks Tilson
Database Development
Tilson Machine, Inc.