• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • 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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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