How to DROP Columns with DDL?

[deko]

I have an Access 2003 mdb with code that helps users import data from Excel.
Oftentimes the Excel spreadsheets contain a variable number of superfluous
columns. So I want to import everything into a Temp table, then
automatically drop all columns EXCEPT the ones that are needed (rather than
making the user do this manually when importing the spreadsheet).

I need to run something like this against the Temp table:

[pseudo code]
ALTER TABLE [DB1489_CLAIMS2007Q4] DROP COLUMN * WHERE (COLUMN Not In
("MyCol1", "MyCol2", "MyCol3", "MyCol4", "MyCol5", "MyCol6", "MyCol7",
"MyCol8"));

Is it possible to do this? If so, what is the correct syntax?

The thing is I need to preserve the name of the Temp table for use with
other code. Could I define a Make Table Query (qryMTQ), drop the original
Temp table, refresh TableDefs, then run qryMTQ to recreate the original Temp
table with the same name and only the required fields: "SELECT INTO
DB1489_CLAIMS2007Q4 ... " (?)

Other options?

Thanks in advance.

 

[Douglas J. Steele]

No, you can't do something like that. You must explicitly list each column.

However, you could use DAO:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strColumns As String
Dim strDrop As String

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("DB1489_CLAIMS2007Q4")
For Each fldCurr In tdfCurr.Fields
Select Case fldCurr.Name
Case "MyCol1", "MyCol2", "MyCol3", "MyCol4", "MyCol5", "MyCol6",
"MyCol7", "MyCol8"
Case Else
strColumns = strColumns & fldCurr.Name & ", "
End Select
Next fldCurr
Set tdfCurr = Nothing
Set dbCurr = Nothing

If Len(strColumns) > 0 Then
strDrop = "ALTER TABLE [DB1489_CLAIMS2007Q4] " & _
"DROP COLUMN " & Left$(strColumns, Len(strColumns) - 2)
End If

or

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("DB1489_CLAIMS2007Q4")
For Each fldCurr In tdfCurr.Fields
Select Case fldCurr.Name
Case "MyCol1", "MyCol2", "MyCol3", "MyCol4", "MyCol5", "MyCol6",
"MyCol7", "MyCol8"
Case Else
tdfCurr.Fields.Delete fldCurr.Name
End Select
Next fldCurr
Set tdfCurr = Nothing
Set dbCurr = Nothing

 

[deko]

Set dbCurr = CurrentDb()

Set tdfCurr = dbCurr.TableDefs("DB1489_CLAIMS2007Q4")
For Each fldCurr In tdfCurr.Fields
Select Case fldCurr.Name
Case "MyCol1", "MyCol2", "MyCol3", "MyCol4", "MyCol5", "MyCol6",
"MyCol7", "MyCol8"
Case Else
tdfCurr.Fields.Delete fldCurr.Name
End Select
Next fldCurr

This looks like it should do the trick

Thanks!