Problem deleting some field names 1

[AHJ1]

Please look at this sub

Sub DropUnwantedColumns(strTableName As String)
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim strColumnName As String

Set rs = CurrentProject.Connection.OpenSchema _
(adSchemaColumns, Array(Empty, Empty, strTableName))

With rs
Do While Not .EOF
strColumnName = !column_Name
If Left(!column_Name, 5) = "Field" Then
    Debug.Print "ALTER TABLE " & strTableName & " DROP COLUMN " & strColumnName & ";"
    DoCmd.RunSQL "ALTER TABLE " & strTableName & " DROP COLUMN " & strColumnName & ";"
'    'Debug.Print "Dropped "; !COLUMN_NAME
ElseIf !column_Name = "TAB CODE" Or !column_Name = "Company" Or !column_Name = "TYPE" Then
    Debug.Print !column_Name
ElseIf Right(!column_Name, 4) <> "_TBD" Then
    Debug.Print "DROP COLUMN " & Chr$(39) & strColumnName & Chr$(39) & ";"""
    'DoCmd.RunSQL "ALTER TABLE " & strTableName & " DROP COLUMN " & Chr$(39) & strColumnName & Chr$(39) & ";"
End If
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing
End Sub

It produces these results:

DROP COLUMN '2005-06';"
DROP COLUMN '2006-07';"
DROP COLUMN '2007-08';"
DROP COLUMN '2008-09';"
COMPANY
ALTER TABLE Test3 DROP COLUMN Field10;
ALTER TABLE Test3 DROP COLUMN Field11;
ALTER TABLE Test3 DROP COLUMN Field14;
ALTER TABLE Test3 DROP COLUMN Field15;
ALTER TABLE Test3 DROP COLUMN Field16;
ALTER TABLE Test3 DROP COLUMN Field17;
ALTER TABLE Test3 DROP COLUMN Field18;
ALTER TABLE Test3 DROP COLUMN Field19;
ALTER TABLE Test3 DROP COLUMN Field20;
ALTER TABLE Test3 DROP COLUMN Field23;
ALTER TABLE Test3 DROP COLUMN Field24;
ALTER TABLE Test3 DROP COLUMN Field25;
ALTER TABLE Test3 DROP COLUMN Field26;
ALTER TABLE Test3 DROP COLUMN Field27;
ALTER TABLE Test3 DROP COLUMN Field28;
ALTER TABLE Test3 DROP COLUMN Field29;
ALTER TABLE Test3 DROP COLUMN Field32;
ALTER TABLE Test3 DROP COLUMN Field33;
ALTER TABLE Test3 DROP COLUMN Field34;
ALTER TABLE Test3 DROP COLUMN Field35;
ALTER TABLE Test3 DROP COLUMN Field36;
ALTER TABLE Test3 DROP COLUMN Field37;
ALTER TABLE Test3 DROP COLUMN Field38;
ALTER TABLE Test3 DROP COLUMN Field40;
ALTER TABLE Test3 DROP COLUMN Field41;
ALTER TABLE Test3 DROP COLUMN Field42;
ALTER TABLE Test3 DROP COLUMN Field43;
ALTER TABLE Test3 DROP COLUMN Field44;
ALTER TABLE Test3 DROP COLUMN Field45;
ALTER TABLE Test3 DROP COLUMN Field46;
ALTER TABLE Test3 DROP COLUMN Field47;
ALTER TABLE Test3 DROP COLUMN Field48;
ALTER TABLE Test3 DROP COLUMN Field49;
ALTER TABLE Test3 DROP COLUMN Field5;
ALTER TABLE Test3 DROP COLUMN Field50;
ALTER TABLE Test3 DROP COLUMN Field51;
ALTER TABLE Test3 DROP COLUMN Field52;
ALTER TABLE Test3 DROP COLUMN Field53;
ALTER TABLE Test3 DROP COLUMN Field54;
ALTER TABLE Test3 DROP COLUMN Field55;
ALTER TABLE Test3 DROP COLUMN Field56;
ALTER TABLE Test3 DROP COLUMN Field57;
ALTER TABLE Test3 DROP COLUMN Field6;
ALTER TABLE Test3 DROP COLUMN Field7;
ALTER TABLE Test3 DROP COLUMN Field8;
ALTER TABLE Test3 DROP COLUMN Field9;
TAB CODE
TYPE

When I uncomment this line

    DoCmd.RunSQL "ALTER TABLE " & strTableName & " DROP COLUMN " & Chr$(39) & strColumnName & Chr$(39) & ";"

all of the columns that start with Field are dropped, however I receive an error message 3381 that says THERE IS NO FIELD NAMED <FIELD NAME> IN TABLE <TABLE NAME>
Yet, that field has been listed in the debug.print statement.

I added the single quotes because before I did this, it was just coming up with the part of a field name prior to a space and hyphen.

Any insights?

 

[djj55]

Have you tried using the [ ] instead of '

djj

 

[AHJ1]

It worked like a charm! Thanks.