adding fields to MS access tables - extended parameters

See this first

See this link:

http://allenbrowne.com/bug-09.html

scroll down to Zero Length String

Here is the code:

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 = False
   
    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

mx

Sorry ... partly misread Q.  

Hi, thanks for the response.

I need sample VBA syntax to add
'Allow Zero length" and
"Unicode Compression"
to the following code?
tbl.Fields.Append tbl.CreateField(MachineName, dbText, 50)

Are these additional params allowable, or how does one programmatically add these constraints?

Generally, you have to create these properties for a Field, then set the value.

You basically use the CreateProperty Method ... standby - putting together an example.

mx

And Unicode Compression

Public Function mCreateTableFieldProperty() As Boolean

    Dim fld As DAO.Field, db As DAO.Database, prp As DAO.Property
    Set db = CurrentDb

    With db.TableDefs("Table1").Fields("Test")
           
        On Error Resume Next
        Set prp = .CreateProperty("UnicodeCompression", dbBoolean, False)
        If Err.Number = 0 Or Err.Number = 3367 Then
            ' do nothing  3367 means Property already exists
            Err.Clear
        Else
            MsgBox "error " & Err.Number & "  " & Err.Description
            Exit Function
        End If
        .Properties.Append prp
        .Properties("UnicodeCompression") = True  ' need this line
       
    End With

End Function

Thanks for that.
I have to leave, but will implement later.

One wonders why the table designer has all the properties, but to the API?

I'm assuming the new field will 'integrate' with existing wizard field settings e.g. 'AllowZeroLength' will map to your 'AllowZeroLength' (no spaces?)
cheers

"One wonders why the table designer has all the properties, but to the API?"
Yeah ... kind of nuts.  I can't remember the reason right off.

"I'm assuming the new field will 'integrate' with existing wizard field settings e.g. 'AllowZeroLength' will map to your 'AllowZeroLength' (no spaces?)"

Well, that's the trick ... getting the EXACT string name, verses what you 'see' on the Property Sheet. I think for most, there are no Spaces ... definitely for these two.  BUT ... you can actually add a (Custom) property named Zero Length String - with spaces, but you won't see it on the Table Property sheet.


Here is a general purpose function:

==== General Purpose Function

Public Function mCreateTableFieldProperty(sTbl As String, sFld As String, sPrp As String, lType As Long, vVal As Variant)

    Dim db As DAO.Database, prp As DAO.Property
    Set db = CurrentDb

    With db.TableDefs(sTbl).Fields(sFld)
        On Error Resume Next
        Set prp = .CreateProperty(sPrp, lType, vVal)
        If Err.Number = 0 Or Err.Number = 3367 Then
            ' do nothing  3367 means Property already exists
            Err.Clear
        Else
            MsgBox "Error " & Err.Number & "  " & Err.Description
            Exit Function
        End If
        .Properties.Append prp
        .Properties(sPrp) = vVal
    End With

End Function

Useage:
Call mCreateTableFieldProperty("Table1", "Test", "UnicodeCompression", dbBoolean, True)