Access table "Allow zero length"

Question

I am working with an old Access database (yes, it is very ugly and I hate it). I need to change some of the columns from the VB application that I am creating. I have most of the settings correctly configured, but I struggle with the fact that the default value is "Allow Zero Length" to change the column to text.

SO ALTER TABLE [Applicant's table] ALTER COLUMN [Applicant's identifier] Text (255)

I need this parameter to change the "Allow zero length" value to true.

I tried ALTER TABLE [Applicant table] ALTER COLUMN [Applicant identifier] Text (255) NULL

but it doesn’t work either. I considered everything for a solution, but I can not find a direct answer.

Any ideas?

Thanks, Ryan.

---

Thanks for the information. I am glad that this is Access, not me.

I assume that I will simply hack my way through this application, since the entire data model is in any case a basket.

Answer 1

This option is not available in Jet sql. You can do this in 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 2

I agree: there is no ACE / Jet syntax for this parameter. If you think about it, the SQL DLL tends to be standard “portable” material, which can be achieved on most SQL products. Most SQL queries do not have an explicit (Dis) function AllowZeroLength, so it did not fall into the standard, therefore, not in the ACE / Jet syntax.

The FWIW "portable" solution, which also works for ACE / Jet, is to use the CHECK constraint, for example. sort of:

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

Resolving a value of zero length would require not creating a CHECK constraint in the first place (!!) or DROP if it already exists ... but why do you want to allow an identifier ("Applicant Identifier")) is zero in any case?

Answer 3

Ages ago, I wanted to do the same thing and ISTR, that it is simply impossible from DDL / SQL, but you can do it from VBA by specifying the field (table.fields) and setting AllowZeroLength to the required value

Answer 4

Use Interop.ACCDBLIB

Try the following:

  DBLib dbLib = new DBLib(); dbLib.ConnectionString = ConnectionString; dbLib.Initialize(); dbLib.ModifyTextFieledSetAllowZeroLength(ref TableName, ref FiledName);