[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Change Allow Zero Length Property to True using ADOX?

Posted on 2006-05-26
Medium Priority
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

Author Comment

ID: 16772142
also tried:

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

and got the same error.
LVL 65

Expert Comment

ID: 16772498
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

Author Comment

ID: 16772532
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.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 65

Accepted Solution

rockiroads earned 2000 total points
ID: 16772803
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

ID: 16773304
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

ID: 16774505
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

ID: 16774957
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

873 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