Run-time error 2465 can't find the field '|1' with alter column

Question

My code:

Private Sub UpdateTables_Click()

    DoCmd.SetWarnings False
    MsgBox "Please Wait for the 'Done' message to appear"
    DoCmd.RunMacro "Update_Tables"
    DoCmd.RunSQL "ALTER TABLE " & [tablename] & " ALTER COLUMN StartDateAll DATE;"
    MsgBox "Done"
    DoCmd.SetWarnings True

End Sub

The DoCmd.RunSQL line is what is highlighted in the Debug window. I started with my table name as t_tablename and got a Syntax error. After reading I found that the "" might be a problem so I dropped the "t", that got rid of my syntax error but now I'm getting this 2465 error. My column was originally "Start_Date_All", so I renamed to "StartDateAll"

I have a multiple make table queries which combines start dates for employees in various departments, the resulting combined column is text instead of date. I have a button on a form which runs the above code to run a macro which runs the make table queries and that runs fine, but it gets stuck at this alter table part (which I just included one for test purposes)

I've checked the spelling of the column name, tried it with double quotes, also tried with "& StartDateAll &" ...got the 2465 error each time.

Answer 1

This is where you said Access complains it can't find the field:

DoCmd.RunSQL "ALTER TABLE " & [tablename] & " ALTER COLUMN StartDateAll DATE;"

There is a better approach, which may not solve the problem, but should at least give you a better chance to understand why it happens:

Dim strTable As String
Dim strAlter As String
strTable = "t_tablename"
DoCmd.SetWarnings True
strAlter = "ALTER TABLE [" & strTable & "] ALTER COLUMN StartDateAll DATE;"
Debug.Print strAlter '<- inspect in Immediate window; Ctrl+g will take you there
CurrentProject.Connection.Execute strAlter
MsgBox "Done"

I tested that code in Access 2010 with StartDateAll as a text field in t_tablename. The code ran without error and afterward I confirmed the field had been changed from Text to Date/Time datatype.

Key points there are:

1. Give yourself an opportunity to examine the completed ALTER TABLE statement you're asking the db engine to execute --> Debug.Print strAlter. IOW, make sure it is what you intend.
2. Your table name, t_tablename, should not require bracketing. But if you want to bracket the name, move those brackets inside the string segments.
3. Turning off SetWarnings suppresses information. When troubleshooting you want every last tidbit of information you can get. So keep SetWarnings on when you execute the ALTER TABLE.

If Access still can't find the StartDateAll field, double check the names of the fields which Access thinks do exist. Here is an example from an Immediate window session:

set db = currentdb
for each fld in db.TableDefs("t_tablename").Fields : ? fld.name : next
id
StartDateAll