Alter Table doesn't work under MS Access 64 bi

Question

I am trying to write a simple function to resize a text field in MS Access 64 bit version under Windows 7. It fails with the error 3420, object invalid or no longer set. Why is this? Can't you alter a table in code anymore under MS Access 64bit version?

Here is the code:

Private Function ResizeSingleTextField(sTableName As String, _
                                       sFieldName As String, _
                                       iLength As Integer)

    ResizeSingleTextField = False

    Dim sSQL As String

    sSQL = "ALTER TABLE " & sTableName & " " _
    & "ALTER COLUMN " & sFieldName & " " _
    & "TEXT (" & iLength & ")"

    CurrentDb.Execute (sSQL)

    ResizeSingleTextField = True
    Exit Function

End Function


Public Sub TestIt()

    Dim result As Boolean

    result = ResizeSingleTextField("GregTest", "MyTextField", 12)

    Debug.Print result

End Sub

Answer 1

It's a known bug in that version of Access. See MS Knowledge Base Article 2516493.

Excerpted here:

Issue that this hotfix package fixes
Assume that you try to change the structure of a table by using a Data Definition Language (DDL) query and the ALTER TABLE statement in the 64-bit version of Microsoft Access 2010. The ALTER TABLE statement includes an ALTER COLUMN parameter. In this situation, you receive the following error message: Object invalid or no longer set. When you try to execute the DDL query through VBA code, you receive the following error message: Run-time error '3420': Object invalid or no longer set.

There is a hotfix that came out in April to remedy the issue. Access 2010 Runtime Service Pack 1 came out in August 2011, and according to the release notes includes a fix for this issue.

Access - "Object invalid or no longer set" error occurs when you try to use an ALTER TABLE query to change a field type or size.