• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

adding fields to MS access tables - extended parameters

Hi All.
does anyone have 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?

thanks
0
COACHMAN99
Asked:
COACHMAN99
  • 6
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... partly misread Q.  

0
 
COACHMAN99Author Commented:
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?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here is example for ZLS (tested):

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("AllowZeroLength", 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("AllowZeroLength") = False
       
    End With

End Function

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
COACHMAN99Author Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now