Jet OleDB: Allow zero Length property returns True 
Author Message
 Jet OleDB: Allow zero Length property returns True

Using Access 2000 with ADO 2.5 on a Win NT machine, the property   , returns
True everytime - even if it isnt so, for any text field that has been added
using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The end
result is if you have added a field (which defaults to Required=Yes, Allow
Zero Length=False) and did not set these properties earlier, you cannot
simply write a check routine to see if these properties are so and change
them. However, for any existing text fields in the database, the property
works correctly. Appreciate any info. anyone can provide.

Thanks

- Raj



Sun, 23 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True

A few things to note:

* not all properties can be set after initial creation.  So, as you have
found, some properties require that the values be set when the table is
created in order to be effective.

* it can be difficult to get properties to work property when creating
columns through ADOX.   I'm not sure from your statement whether you are
having trouble with tables/columns created through ADOX or through the
Access 2000 UI.  Assuming the former, you should make sure that both your
table and column object are associated with an activeconnection for an open
connection object.  This is the largest general cause of problems.

Conor


Quote:
> Using Access 2000 with ADO 2.5 on a Win NT machine, the property   ,
returns
> True everytime - even if it isnt so, for any text field that has been
added
> using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The end
> result is if you have added a field (which defaults to Required=Yes, Allow
> Zero Length=False) and did not set these properties earlier, you cannot
> simply write a check routine to see if these properties are so and change
> them. However, for any existing text fields in the database, the property
> works correctly. Appreciate any info. anyone can provide.

> Thanks

> - Raj



Sun, 23 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True
Just for the record, I discovered another column property which definitely
misbehaves:

         oCol.Properties("Nullable")

If I use ADOX to create tables and columns from scratch, i.e. no
modification of an existing database/table, create a column and
set  oCol.Properties("Nullable").Value= True, when I later read
back the property for the column it is set to False. Wonderful!

Regards,
-Toby

Quote:

>A few things to note:

>* not all properties can be set after initial creation.  So, as you have
>found, some properties require that the values be set when the table is
>created in order to be effective.

>* it can be difficult to get properties to work property when creating
>columns through ADOX.   I'm not sure from your statement whether you are
>having trouble with tables/columns created through ADOX or through the
>Access 2000 UI.  Assuming the former, you should make sure that both your
>table and column object are associated with an activeconnection for an open
>connection object.  This is the largest general cause of problems.

>Conor



>> Using Access 2000 with ADO 2.5 on a Win NT machine, the property   ,
>returns
>> True everytime - even if it isnt so, for any text field that has been
>added
>> using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The end
>> result is if you have added a field (which defaults to Required=Yes, Allow
>> Zero Length=False) and did not set these properties earlier, you cannot
>> simply write a check routine to see if these properties are so and change
>> them. However, for any existing text fields in the database, the property
>> works correctly. Appreciate any info. anyone can provide.

>> Thanks

>> - Raj



Mon, 24 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True

Unfortunately, there are 2 different mechanisms that determine "nullability"
in Jet, and the values don't always map out properly through every path.

If you have a particular repro scenario, I encourage you to contact PSS and
send in a repro scenario for further investigation.

Thx

Conor



Quote:
> Just for the record, I discovered another column property which definitely
> misbehaves:

>          oCol.Properties("Nullable")

> If I use ADOX to create tables and columns from scratch, i.e. no
> modification of an existing database/table, create a column and
> set  oCol.Properties("Nullable").Value= True, when I later read
> back the property for the column it is set to False. Wonderful!

> Regards,
> -Toby


> >A few things to note:

> >* not all properties can be set after initial creation.  So, as you have
> >found, some properties require that the values be set when the table is
> >created in order to be effective.

> >* it can be difficult to get properties to work property when creating
> >columns through ADOX.   I'm not sure from your statement whether you are
> >having trouble with tables/columns created through ADOX or through the
> >Access 2000 UI.  Assuming the former, you should make sure that both your
> >table and column object are associated with an activeconnection for an
open
> >connection object.  This is the largest general cause of problems.

> >Conor



> >> Using Access 2000 with ADO 2.5 on a Win NT machine, the property   ,
> >returns
> >> True everytime - even if it isnt so, for any text field that has been
> >added
> >> using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The end
> >> result is if you have added a field (which defaults to Required=Yes,
Allow
> >> Zero Length=False) and did not set these properties earlier, you cannot
> >> simply write a check routine to see if these properties are so and
change
> >> them. However, for any existing text fields in the database, the
property
> >> works correctly. Appreciate any info. anyone can provide.

> >> Thanks

> >> - Raj



Mon, 24 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True
Reading up thru the myriad of documents in MDSN, I recall from somewhere ->
the "Nullable" property on a column for Jet is read only with ADOX after its
created. So if you set it at the time of creation, its good, but you cannot
go back later to change  its value.
Going back to my original problem, the 'Allow zero Length' is inconsistent
between an existing field in a table vs. a field that we create via ADOX.
This behaviour stays with it (forever, I would guess) even after restarting
a 2nd/ 3rd/.. sessions of my VB program. Keep in mind this property is NOT
read only. If you overwrite the value without checking it, it does work.
Could you elaborate on PSS & how it works??

Tx

- Raj


Quote:
> Unfortunately, there are 2 different mechanisms that determine
"nullability"
> in Jet, and the values don't always map out properly through every path.

> If you have a particular repro scenario, I encourage you to contact PSS
and
> send in a repro scenario for further investigation.

> Thx

> Conor



> > Just for the record, I discovered another column property which
definitely
> > misbehaves:

> >          oCol.Properties("Nullable")

> > If I use ADOX to create tables and columns from scratch, i.e. no
> > modification of an existing database/table, create a column and
> > set  oCol.Properties("Nullable").Value= True, when I later read
> > back the property for the column it is set to False. Wonderful!

> > Regards,
> > -Toby


> > >A few things to note:

> > >* not all properties can be set after initial creation.  So, as you
have
> > >found, some properties require that the values be set when the table is
> > >created in order to be effective.

> > >* it can be difficult to get properties to work property when creating
> > >columns through ADOX.   I'm not sure from your statement whether you
are
> > >having trouble with tables/columns created through ADOX or through the
> > >Access 2000 UI.  Assuming the former, you should make sure that both
your
> > >table and column object are associated with an activeconnection for an
> open
> > >connection object.  This is the largest general cause of problems.

> > >Conor



> > >> Using Access 2000 with ADO 2.5 on a Win NT machine, the property   ,
> > >returns
> > >> True everytime - even if it isnt so, for any text field that has been
> > >added
> > >> using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The
end
> > >> result is if you have added a field (which defaults to Required=Yes,
> Allow
> > >> Zero Length=False) and did not set these properties earlier, you
cannot
> > >> simply write a check routine to see if these properties are so and
> change
> > >> them. However, for any existing text fields in the database, the
> property
> > >> works correctly. Appreciate any info. anyone can provide.

> > >> Thanks

> > >> - Raj



Mon, 24 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True

PSS is Microsoft Product Support.

You can contact them via email or telephone - I think you can start at
www.microsoft.com/support

Conor

Quote:
> Reading up thru the myriad of documents in MDSN, I recall from
somewhere ->
> the "Nullable" property on a column for Jet is read only with ADOX after
its
> created. So if you set it at the time of creation, its good, but you
cannot
> go back later to change  its value.
> Going back to my original problem, the 'Allow zero Length' is inconsistent
> between an existing field in a table vs. a field that we create via ADOX.
> This behaviour stays with it (forever, I would guess) even after
restarting
> a 2nd/ 3rd/.. sessions of my VB program. Keep in mind this property is NOT
> read only. If you overwrite the value without checking it, it does work.
> Could you elaborate on PSS & how it works??

> Tx

> - Raj



> > Unfortunately, there are 2 different mechanisms that determine
> "nullability"
> > in Jet, and the values don't always map out properly through every path.

> > If you have a particular repro scenario, I encourage you to contact PSS
> and
> > send in a repro scenario for further investigation.

> > Thx

> > Conor



> > > Just for the record, I discovered another column property which
> definitely
> > > misbehaves:

> > >          oCol.Properties("Nullable")

> > > If I use ADOX to create tables and columns from scratch, i.e. no
> > > modification of an existing database/table, create a column and
> > > set  oCol.Properties("Nullable").Value= True, when I later read
> > > back the property for the column it is set to False. Wonderful!

> > > Regards,
> > > -Toby


> > > >A few things to note:

> > > >* not all properties can be set after initial creation.  So, as you
> have
> > > >found, some properties require that the values be set when the table
is
> > > >created in order to be effective.

> > > >* it can be difficult to get properties to work property when
creating
> > > >columns through ADOX.   I'm not sure from your statement whether you
> are
> > > >having trouble with tables/columns created through ADOX or through
the
> > > >Access 2000 UI.  Assuming the former, you should make sure that both
> your
> > > >table and column object are associated with an activeconnection for
an
> > open
> > > >connection object.  This is the largest general cause of problems.

> > > >Conor



> > > >> Using Access 2000 with ADO 2.5 on a Win NT machine, the property
,
> > > >returns
> > > >> True everytime - even if it isnt so, for any text field that has
been
> > > >added
> > > >> using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The
> end
> > > >> result is if you have added a field (which defaults to
Required=Yes,
> > Allow
> > > >> Zero Length=False) and did not set these properties earlier, you
> cannot
> > > >> simply write a check routine to see if these properties are so and
> > change
> > > >> them. However, for any existing text fields in the database, the
> > property
> > > >> works correctly. Appreciate any info. anyone can provide.

> > > >> Thanks

> > > >> - Raj



Mon, 24 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True
I created a "clone" utility which uses ADOX to read the structure of
an existing Access 2000 database and write the program code necessary
for creating an empty MDB from scratch (Tables, Indexes and Keys - no
Views or Procedures).  When I run  the code which creates the "clone"
and then subsequently compare the structure of the clone with the parent,
I find a number of peculiarities; i.e. the clone does not mirror the structure of the
parent MDB 100%.

Case in point 1: the dynamic properties of the Index object.

When I map the parent MDB, I have access to all 11 dynamic properties
of the Index object; when I create the clone, I do not; oIndex.Properties.Count
returns 0 and there is no possibility of doing a

               Set oIndex.ParentCatalog = oCatalog

Case in point 2: the dynamic properties of the Column object.

Try creating a fresh table with ADOX, add a column and doing

         oColumn.Properties("Default").Value = "Default Value"

I can set the default value under Access 2000 and ADOX will "see it" when I
map the database. When I create the MDB with ADOX, the default is *not* set.

The list goes on and my hair is getting thinner and more gray....

Regards,
-Toby

Quote:

>Unfortunately, there are 2 different mechanisms that determine "nullability"
>in Jet, and the values don't always map out properly through every path.

>If you have a particular repro scenario, I encourage you to contact PSS and
>send in a repro scenario for further investigation.

>Thx

>Conor



>> Just for the record, I discovered another column property which definitely
>> misbehaves:

>>          oCol.Properties("Nullable")

>> If I use ADOX to create tables and columns from scratch, i.e. no
>> modification of an existing database/table, create a column and
>> set  oCol.Properties("Nullable").Value= True, when I later read
>> back the property for the column it is set to False. Wonderful!

>> Regards,
>> -Toby


>> >A few things to note:

>> >* not all properties can be set after initial creation.  So, as you have
>> >found, some properties require that the values be set when the table is
>> >created in order to be effective.

>> >* it can be difficult to get properties to work property when creating
>> >columns through ADOX.   I'm not sure from your statement whether you are
>> >having trouble with tables/columns created through ADOX or through the
>> >Access 2000 UI.  Assuming the former, you should make sure that both your
>> >table and column object are associated with an activeconnection for an
>open
>> >connection object.  This is the largest general cause of problems.

>> >Conor



>> >> Using Access 2000 with ADO 2.5 on a Win NT machine, the property   ,
>> >returns
>> >> True everytime - even if it isnt so, for any text field that has been
>> >added
>> >> using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The end
>> >> result is if you have added a field (which defaults to Required=Yes,
>Allow
>> >> Zero Length=False) and did not set these properties earlier, you cannot
>> >> simply write a check routine to see if these properties are so and
>change
>> >> them. However, for any existing text fields in the database, the
>property
>> >> works correctly. Appreciate any info. anyone can provide.

>> >> Thanks

>> >> - Raj



Tue, 25 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True
Another possibility is to just compact the Jet database - this would maike
the copy for you.

However, I do suggest that you contact Microsoft PSS to get a bug report
open.  That way, they can research the issue and work on a resolution.

Conor



Quote:
> I created a "clone" utility which uses ADOX to read the structure of
> an existing Access 2000 database and write the program code necessary
> for creating an empty MDB from scratch (Tables, Indexes and Keys - no
> Views or Procedures).  When I run  the code which creates the "clone"
> and then subsequently compare the structure of the clone with the parent,
> I find a number of peculiarities; i.e. the clone does not mirror the
structure of the
> parent MDB 100%.

> Case in point 1: the dynamic properties of the Index object.

> When I map the parent MDB, I have access to all 11 dynamic properties
> of the Index object; when I create the clone, I do not;

oIndex.Properties.Count
Quote:
> returns 0 and there is no possibility of doing a

>                Set oIndex.ParentCatalog = oCatalog

> Case in point 2: the dynamic properties of the Column object.

> Try creating a fresh table with ADOX, add a column and doing

>          oColumn.Properties("Default").Value = "Default Value"

> I can set the default value under Access 2000 and ADOX will "see it" when
I
> map the database. When I create the MDB with ADOX, the default is *not*
set.

> The list goes on and my hair is getting thinner and more gray....

> Regards,
> -Toby


> >Unfortunately, there are 2 different mechanisms that determine
"nullability"
> >in Jet, and the values don't always map out properly through every path.

> >If you have a particular repro scenario, I encourage you to contact PSS
and
> >send in a repro scenario for further investigation.

> >Thx

> >Conor



> >> Just for the record, I discovered another column property which
definitely
> >> misbehaves:

> >>          oCol.Properties("Nullable")

> >> If I use ADOX to create tables and columns from scratch, i.e. no
> >> modification of an existing database/table, create a column and
> >> set  oCol.Properties("Nullable").Value= True, when I later read
> >> back the property for the column it is set to False. Wonderful!

> >> Regards,
> >> -Toby


> >> >A few things to note:

> >> >* not all properties can be set after initial creation.  So, as you
have
> >> >found, some properties require that the values be set when the table
is
> >> >created in order to be effective.

> >> >* it can be difficult to get properties to work property when creating
> >> >columns through ADOX.   I'm not sure from your statement whether you
are
> >> >having trouble with tables/columns created through ADOX or through the
> >> >Access 2000 UI.  Assuming the former, you should make sure that both
your
> >> >table and column object are associated with an activeconnection for an
> >open
> >> >connection object.  This is the largest general cause of problems.

> >> >Conor



> >> >> Using Access 2000 with ADO 2.5 on a Win NT machine, the property   ,
> >> >returns
> >> >> True everytime - even if it isnt so, for any text field that has
been
> >> >added
> >> >> using ADOX in code, such as, Catalog.Tables(..).Columns.Append. The
end
> >> >> result is if you have added a field (which defaults to Required=Yes,
> >Allow
> >> >> Zero Length=False) and did not set these properties earlier, you
cannot
> >> >> simply write a check routine to see if these properties are so and
> >change
> >> >> them. However, for any existing text fields in the database, the
> >property
> >> >> works correctly. Appreciate any info. anyone can provide.

> >> >> Thanks

> >> >> - Raj



Tue, 25 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True

If you take this suggestion, make sure you have jetsp4 installed, because
without this, the new compacted database screws up with the counter fields.
It starts to duplicate them for new records. Better believe it, I found this
the hard way. The bug is listed on msdn, recently.

- Raj


Quote:
> Another possibility is to just compact the Jet database - this would maike
> the copy for you.

> However, I do suggest that you contact Microsoft PSS to get a bug report
> open.  That way, they can research the issue and work on a resolution.

> Conor



> > I created a "clone" utility which uses ADOX to read the structure of
> > an existing Access 2000 database and write the program code necessary
> > for creating an empty MDB from scratch (Tables, Indexes and Keys - no
> > Views or Procedures).  When I run  the code which creates the "clone"
> > and then subsequently compare the structure of the clone with the
parent,
> > I find a number of peculiarities; i.e. the clone does not mirror the
> structure of the
> > parent MDB 100%.

> > Case in point 1: the dynamic properties of the Index object.

> > When I map the parent MDB, I have access to all 11 dynamic properties
> > of the Index object; when I create the clone, I do not;
> oIndex.Properties.Count
> > returns 0 and there is no possibility of doing a

> >                Set oIndex.ParentCatalog = oCatalog

> > Case in point 2: the dynamic properties of the Column object.

> > Try creating a fresh table with ADOX, add a column and doing

> >          oColumn.Properties("Default").Value = "Default Value"

> > I can set the default value under Access 2000 and ADOX will "see it"
when
> I
> > map the database. When I create the MDB with ADOX, the default is *not*
> set.

> > The list goes on and my hair is getting thinner and more gray....

> > Regards,
> > -Toby


> > >Unfortunately, there are 2 different mechanisms that determine
> "nullability"
> > >in Jet, and the values don't always map out properly through every
path.

> > >If you have a particular repro scenario, I encourage you to contact PSS
> and
> > >send in a repro scenario for further investigation.

> > >Thx

> > >Conor



> > >> Just for the record, I discovered another column property which
> definitely
> > >> misbehaves:

> > >>          oCol.Properties("Nullable")

> > >> If I use ADOX to create tables and columns from scratch, i.e. no
> > >> modification of an existing database/table, create a column and
> > >> set  oCol.Properties("Nullable").Value= True, when I later read
> > >> back the property for the column it is set to False. Wonderful!

> > >> Regards,
> > >> -Toby


> > >> >A few things to note:

> > >> >* not all properties can be set after initial creation.  So, as you
> have
> > >> >found, some properties require that the values be set when the table
> is
> > >> >created in order to be effective.

> > >> >* it can be difficult to get properties to work property when
creating
> > >> >columns through ADOX.   I'm not sure from your statement whether you
> are
> > >> >having trouble with tables/columns created through ADOX or through
the
> > >> >Access 2000 UI.  Assuming the former, you should make sure that both
> your
> > >> >table and column object are associated with an activeconnection for
an
> > >open
> > >> >connection object.  This is the largest general cause of problems.

> > >> >Conor



> > >> >> Using Access 2000 with ADO 2.5 on a Win NT machine, the property
,
> > >> >returns
> > >> >> True everytime - even if it isnt so, for any text field that has
> been
> > >> >added
> > >> >> using ADOX in code, such as, Catalog.Tables(..).Columns.Append.
The
> end
> > >> >> result is if you have added a field (which defaults to
Required=Yes,
> > >Allow
> > >> >> Zero Length=False) and did not set these properties earlier, you
> cannot
> > >> >> simply write a check routine to see if these properties are so and
> > >change
> > >> >> them. However, for any existing text fields in the database, the
> > >property
> > >> >> works correctly. Appreciate any info. anyone can provide.

> > >> >> Thanks

> > >> >> - Raj



Tue, 25 Mar 2003 03:00:00 GMT  
 Jet OleDB: Allow zero Length property returns True
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 4 Oct 2000 15:44:20 -0600, "aarkay"

Quote:

>Using Access 2000 with ADO 2.5 on a Win NT machine, the property   ,
>returns True everytime - even if it isnt so, for any text field that
>has been added using ADOX in code, such as,
>Catalog.Tables(..).Columns.Append. The end result is if you have
>added a field (which defaults to Required=Yes, Allow Zero
>Length=False) and did not set these properties earlier, you cannot
>simply write a check routine to see if these properties are so and
>change them. However, for any existing text fields in the database,
>the property works correctly. Appreciate any info. anyone can
>provide.  

A possibly-related bug that I have encountered is when I try to use
ADOX to retrieve properties about a field, such as Required or Allow
Zero Length.  If I am connecting directly to the MDB containing the
field, then ADOX retrieves the same information shown when checking
the field properties in Access 2000.  If the table containing the
field is actually in a different MDB (i.e., database A has a link to
a table in database B, and I try to read the properties using a
connection to database A), ADOX returns incorrect results.  I
contacted Microsoft Tech Support about this, and they confirmed that
ADOX would retrieve the default metadata, not the actual metadata,
for a linked table.  They recommended looking up the actual table
name and database name, and connecting directly to that database
before retrieving these properties.  I now do that, and the info is
retrieved correctly.

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.1 Int. for non-commercial use
<http://www.pgpinternational.com>

iQA/AwUBOeDjIzMYPge5L34aEQJydACeKXEZXGmn3m76Z0oC1BxXz6FeTVkAoL+w
27I9AhIAKWUlQUDdCacBrOAJ
=rlVj
-----END PGP SIGNATURE-----

--

PGP key available from:
http://www.netforward.com/poboxes/?eldredge/

"There must be, not a balance of power, but a community of power;
not organized rivalries, but an organized common peace." - Woodrow Wilson



Thu, 27 Mar 2003 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. ADOX, Jet OLEDB:Allow Zero Length ERROR...

2. Add Allow Zero Length Property?

3. Column.Properties("Set OLEDB:Allow Zero Length") = true

4. ADO Column.Properties("Jet OLEDB:Allow Zero Length")

5. TableDef/Set all text fields to allow zero length

6. Allow Zero Length String in a field using SQL

7. Allow Zero Length Question

8. Zero length strings not allowed

9. Create Table "Allow Zero Length"???

10. Allow Zero Length

11. 'allow zero length' not working

12. Allow Zero Length in Access DB

 

 
Powered by phpBB® Forum Software