Change Allow Zero Length Property to True using ADOX?

Posted on 2006-05-26
Last Modified: 2007-12-19
I have the following but would like to change the field's Allow Zero Length Property to True as well...please help?

Dim cat As New ADOX.Catalog
Dim idxNew As New ADOX.Index
Dim tbl As ADOX.Table

cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat.Tables("PROJ_RM")

' Append new Primary Key index on NumField column
idxNew.Name = "RmID"
idxNew.PrimaryKey = True
idxNew.Unique = True
idxNew.Properties("Allow Zero Length").Value = True

at the last line I get:  Run-time error 3265 Item cannot be found in the collection corresponding to the requested name or ordinal.
Question by:stephenlecomptejr
    LVL 1

    Author Comment

    also tried:

    idxNew.Properties("Jet OLEDB: Allow Zero Length").Value = True

    and got the same error.
    LVL 65

    Expert Comment

    just a thought, if u creating a primary key, why you making it allow zero length to true? surely it should be no, and probably be setup automatically anyway
    LVL 1

    Author Comment

    you're right...

    I used the wrong example....

    idxNew.Name = "Room_Number"
    idxNew.Unique = False
    idxNew.Properties("Jet OLEDB: Allow Zero Length").Value = True

    this is a field definitely that I don't want unique and is just a text field and yet I get the same error.
    LVL 65

    Accepted Solution

    Im not too sure with ADOX, I can look tomorrow, its quite late and Im trying to find my misplaced credit card :(

    in the meantime, here is an DAO example
    creates a field called temp

    Public Sub CP()

        Dim db As Database
        Dim td As TableDef
        Dim fld As Field
        Set db = CurrentDb
        Set td = db.TableDefs("table1")
        'create a temp feild
        td.Fields.Append td.CreateField("temp", dbText, 22)
        td.Fields("temp").AllowZeroLength = True
        td.Fields("temp").DefaultValue = "hello"


    End Sub

    LVL 58

    Expert Comment

    Hello stephenlecomptejr

    I believe there is no "AllowZeroLength" property for indexes. Maybe a confusion with IgnoreNulls?

        idxNew.IgnoreNulls = True ' or False?

    If you need to change the field's property, follow rockiroad's sample above.

    LVL 65

    Expert Comment

    ok, here is another question

    what do you want to update, a column or index?

    that property does work but I think its made for columns only


        Dim cat As New ADOX.Catalog
        Dim idxCol As New ADOX.Column
        Dim tbl As ADOX.Table
        cat.ActiveConnection = CurrentProject.Connection
        Set tbl = cat.Tables("table1")
        Set idxCol = tbl.Columns("temp")
        idxCol.Properties("Jet OLEDB:Allow Zero Length").Value = True

    For index properties, I guess we use

    idxNew.IndexNulls = adIndexNullsAllow

    the type for index is ADOX.Index

    other options for IndexNulls is  adIndexNullsDisallow, adIndexNullsIgnore, adIndexNullsIgnoreAny

    LVL 38

    Expert Comment

    See this link in reference to the use of zero length strings in Access (not recommended, except in special circumstances. Also provided in the link is a function to reset the ZLS property of all your tables.

    Hope this helps.


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now