Create ms access table''s field that allow zero length

Question

Create ms access table''s field that allow zero length

alan-205411

SSC-Addicted

Points: 473
More actions
February 1, 2005 at 1:26 am

#86701

hi all
how to create a table's field in ms access that allow zero length, means allow null value using vb.net , i am not able to create a field that allow zero length with the sample below
Dim
cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim con As ADODB.Connection
' Create the new database.
cat =
New ADOX.Catalog()
cat.Create( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "c:\newdata.mdb")
' Create a new table.
tbl =
New ADOX.Table()
tbl.Name = "TestTable"
tbl.Columns.Append("VesselName", ADOX.DataTypeEnum.adVarWChar, 50)
tbl.Columns.Append("ApproveDate", ADOX.DataTypeEnum.adDate)
tbl.Columns.Append("ExpDate", ADOX.DataTypeEnum.adDate)

tbl =
Nothing
cat =
Nothing

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I had to add the following line to get the table to save, but when I did, the text columns had the "Allow Zero Length" property already set to yes.

cat.Tables.Append tbl

Allow Zero Length and NULL are two different things. The "Required" field determines whether or not NULL's are allowed. Allow Zero Length means that the field will accept an empty string.

To turn off the "Required" property, you do this:

dim col as ADOX.Column
set col = tbl.Columns("VesselName")
col.Attributes = adColNullable

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

alan-205411 SSC-Addicted Points: 473 More actions · Answer 2

Thanks veteran,

i added your code and it work well

The following code is for the field allow zero length is ms access table's field

tbl.Columns.Append("VesselName", ADOX.DataTypeEnum.adVarWChar, 50)

tbl.Columns.Append("ApproveDate", ADOX.DataTypeEnum.adDate)

tbl.Columns.Item("ApproveDate").Properties("Jet OLEDB:Allow Zero Length").Value =

True

tbl.Columns.Append("ExpDate", ADOX.DataTypeEnum.adDate)

tbl.Columns.Item("Expdate").Properties("Jet OLEDB:Allow Zero Length").Value =

True

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

The "Allow Zero Length" property is set by default to true on text fields. I don't think it is a valid property of the date field type. For dates, you need either a NULL or a real date.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

alan-205411 SSC-Addicted Points: 473 More actions · Answer 4

hi veteran,

ya the date field have the problem, i have change it.......

Thanks..

*0*