sql drop column results in error msg "field not found" - the field exists!

[tfulmer]

It's crunch time for a financial reporting macro to work and of course all of a sudden I hit something that I cannot figure out, being a novice. I have searched for similar errors in the forum, but didn't see any.

Have a table with a field named "actuals". Have a macro runsql command of ALTER TABLE tablename DROP COLUMN actuals.
Stepping through the entire macro stops at this command and returns the error msg "There is no field named 'actuals' in table tablename'.
Macro has worked in the past.

Mispelling has been ruled out in both the sql line and the table. I ran documenter for the table and it reports the field "actuals". The sql line refers to "actuals".

I am using MSAccess 2007-10 and the source of the data is xlsx file.

Any help would be appreciated.



Thanks
Tom

[orange]

What is the real name of the Table that contains the field "actuals"?

[tfulmer]

Thanks for responding.
The name of the table is ecombs_imports
I noticed something that may be a clue as to what is happening.
When I cut and paste the sql command from the macro and paste it to a query, the query executes just fine.

[NTC]

since sql code in a database cannot delete a column in an excel file - - - the naming of things is very interesting and a bit confusing. just as Orange is asking about the table name, I am wondering if other things are going on...you mention stepping thru an entire macro and often a macro is a convenient technique to hold a series of sequential steps ; so that perhaps one/some of the prior steps are actually causing the issue......

[tfulmer]

NTC thanks for helping also. To isolate the error, I minimized the macro to import several xlsx files, that all have the same column headers. They are appended into one table. The next macro step is the RUNSQL command to drop the column.
As I said in my reply to Orange, when I cut and paste the sql drop column statement to a query, it executes just fine.
Now, I discovered that when I tell Access to use the 2002-2003 file format and go through the identical steps, the macro works and deletes the column.
This leads me to think the problem has something to do with macro security setting in 2007-10. I have enabled macros in 07-10, but there is something I think in security that is not allowing a destructive action.

[NTC]

It is not particularly difficult and a reasonable design requirement to delete all the DATA out of a column of a table. But it is a very unusual and difficult design that would delete the column/field itself from the table - and is a table parameter code task not an sql event. So there may be a terminology misunderstanding as to what is truly going on. That it runs in the mdb format but not accdb implies that the issue is visual code syntax as typically sql code moves between those versions without problem. None of this commentary however helps you resolve your issue. Consider rebuilding in parallel from scratch a test macro step by step. Sometimes when there is a real head scratcher simply replacing the code is quicker than attempting to fix the old code - - but whether or not that is true in your case I can't say...

[tfulmer]

Thanks NTC. It is weird that access allows sql statements to add columns, and then populate the added columns with an expression, but refuses to allow a destructive event like drop column to occur. Very frustrating.
I am reverting back to the 03 file format versions for the time being, deadlines being what they are, and will have to spend more time researching when the time becomes available.
Thanks for your interest and suggestions.