Access Alter Table "Allow Zero Length"

Question

I'm working with an old Access database (yes, it's very ugly and I hate it). I need to modify some of the columns from a VB app that I'm creating. I have most the modifications setup correctly, but I'm fighting with the fact that modifying a column to text has it default to "Allow Zero Length" to false.

SO ALTER TABLE [Applicant Table] ALTER COLUMN [Applicant ID] Text(255)

I need that alter to have "Allow Zero Length" set to true.

I have tried ALTER TABLE [Applicant Table] ALTER COLUMN [Applicant ID] Text(255) NULL

but that doesn't seem to work either. I've looked all over for the solution, but can't seem to find a straight answer.

Any ideas?

Thanks, Ryan.

---

Thanks for the info. I'm glad that it's Access and not me.

I guess I'm just going to hack my way through this application since the entire data model is trash anyway.

Answer 1

This option isn't available with Jet sql. You can do it in the Access gui or with vba code. Example:

Public Function setAllowZeroLenStr()    
    On Error GoTo Proc_Err

    Dim db As Database
    Dim tbl As TableDef
    Dim fld As DAO.Field

    Set db = CurrentDb
    Set tbl = db.TableDefs![Applicant Table]
    Set fld = tbl.Fields![Applicant ID]
    fld.AllowZeroLength = True

Proc_Exit:    
    Set fld = Nothing
    Set tbl = Nothing
    Set db = Nothing

    Exit Function

Proc_Err:    
    MsgBox Err.Number & vbCrLf & Err.Description
    Err.Clear
    Resume Proc_Exit    
End Function