Changing "Allow Zero Length" property for Table Fields of type Text programmatically?

[Wolfgang Kaml \(MS\)]

I have plenty tables in MS Access 2000 SP-3 that have fields of type "Text"
and unfortunately, the fields' property "Allow Zero Length" is set to "No".
Is there any way to change those properties automatically to "Yes"? I am
trying to write some 0 length strings with VB ADO.NET to those tables and I
get an error unless I change that property "Allow Zero Length" from "No" to
"Yes". I tried "ALTER TABLE ALTER COLUMN..." but couldn't figure out, how...

Any help is greatly appreciated.
Thanks,
Wolfgang

 

[Neil McKechnie]

Wolfgang,

From Access 2000's help on Allow Zero Length property:

Note To access a field's AllowZeroLength property by using Visual Basic,
use the DAO AllowZeroLength property or the ADO Column.Properties("Set
OLEDB:Allow Zero Length") property.

See also the help topic "Set Properties of ActiveX Data Objects in Visual
Basic".

Hope this helps,

Neil.

 

[Allen Browne]

IMHO, you would be better writing yourself a little wrapper function to
convert the ZLS to Null, but if you want to mess up your database anyway,
open it in Access and run this:

Function FixZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = True

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
If fld.Properties(conPropName) Then
Debug.Print tdf.Name & "." & fld.Name
fld.Properties(conPropName) = False
End If
Next
End If
Next

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Function

 

[Wolfgang Kaml]

Thanks, Allen!!

Any way to do that with VB.NET? (e.g. using System.Data.Odbc.*)
I tried a few things today e.g. messing with Columns, but that did not work either.

For those of who are interested in a "safe" transaction not crashing on non TEXT type fields, I modified the function slightly.

Function FixZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = True

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print "Table: " & tdf.Name & " nr of fields: " & tdf.Fields.Count
For Each fld In tdf.Fields
Debug.Print " "; fld.Name & " of type: " & fld.Type
If fld.Type = 10 Then
If Not fld.Properties(conPropName) Then
fld.Properties(conPropName) = True
Debug.Print " changed Allow Zero Length"
End If
End If
Next
End If
Next

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

 

[Allen Browne]

With an ADOX reference, you could set the property of the column:
col.Properties("Jet OLEDB:Allow Zero Length") = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen!!

Any way to do that with VB.NET? (e.g. using System.Data.Odbc.*)
I tried a few things today e.g. messing with Columns, but that did not work
either.

For those of who are interested in a "safe" transaction not crashing on non
TEXT type fields, I modified the function slightly.

Function FixZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = True

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print "Table: " & tdf.Name & " nr of fields: " &
tdf.Fields.Count
For Each fld In tdf.Fields
Debug.Print " "; fld.Name & " of type: " & fld.Type
If fld.Type = 10 Then
If Not fld.Properties(conPropName) Then
fld.Properties(conPropName) = True
Debug.Print " changed Allow Zero Length"
End If
End If
Next
End If
Next

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function