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.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Neil McKechnie
Microlink Associates Ltd
Ne...@MicrolinkAssociates.couk

"Wolfgang Kaml (MS)" <m...@no-spam.kaml.com> wrote in message
news:e7IeU9ak...@TK2MSFTNGP11.phx.gbl...

[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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Wolfgang Kaml (MS)" <m...@no-spam.kaml.com> wrote in message
news:e7IeU9ak...@TK2MSFTNGP11.phx.gbl...

[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" <allen...@SeeSig.invalid> wrote in message news:erJnb$ckDHA...@TK2MSFTNGP09.phx.gbl...

[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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Wolfgang Kaml" <m...@no-spam.kaml.com> wrote in message
news:OC7NFYek...@TK2MSFTNGP09.phx.gbl...