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.

Answer 1

Ages ago I wanted to do the same and ISTR that it simply wasn't possible from DDL/SQL - but you can do it from VBA by locating the field (table.fields) and setting AllowZeroLength to the required value

Answer 2

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

Answer 3

I concur: there is no ACE/Jet syntax for this setting. If you think about it, the SQL DLL tends to be the Standard 'portable' stuff, things that can be achieved on most SQL products. Most SQLs don't have an explicit (Dis)AllowZeroLength feature so it hasn't made it into the Standard hence not in the ACE/Jet syntax.

FWIW the 'portable' solution, which works for ACE/Jet too, is to use a CHECK constraint e.g. something like:

ALTER TABLE [Applicant Table] ADD
   CONSTRAINT Applicant_ID__not_zero_length 
      CHECK (LEN([Applicant ID]) > 0);

Allowing a zero length value would require not creating the CHECK constraint in the first place (!!) or DROPing it if it already existed... but why would you want to allow an identifier ("Applicant ID") to be zero length anyhow?

Answer 4

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

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