Allow Zero length property not working when using create table sql statement

Access 2000 will allow me to insert a blank into a field with the
Allow zero length property set to no. Here is what I did.

Created a table in access with SQL. CREATE TABLE TestTable (String1
char(4),String2 char(3),String3 char(40),String4 char(13))

Went to the design view of the table in Access and verified that the
"Allow zero length" property is "no".

Created an insert query like this: INSERT INTO TestTable ( String1,
String2, String3, String4 ) SELECT "Test2", '' , '' , ''

Ran the query and I will get a row of data in the testtable.

If I go into the design of the table and change the length of field
"string4" from 13 to 15 and save my changes. If I re-run the insert
query above I will get an access error "microsoft Access can't append
all the records in the append query" due to a validation rule
violation.

What is going on? Is my create table incorrect? I need to create the
table with SQL (external app actual creates the table, but problem can
be reproduce in Access)

Thanks in advance,

Hi EK.

Access is not violating the Allow Zero Length property, but you have created
a table with a different field type than you expected.

In Access (JET), you would normally create text fields with the TEXT keyword
rather than the CHAR keyword, i.e.:
CREATE TABLE TestTable
(String1 TEXT(4),String2 TEXT(3),String3 TEXT(40),String4 TEXT(13));
If you do that, everything works as expected.

If you use the CHAR keyword, Access creates a *fixed-width* field. Then when
you execute your insert query, Access does not insert zero-length strings,
but fills the fields with spaces. You can see this if you open the table and
look at the data: you can select the text (the spaces) in each field.

You can also use ADOX or DAO to demonstratethat the field is fixed length
when the table created using the CHAR keyword, and False for the table
created using the TEXT keyword:

Function ShowPropsADOX(strTable As String)
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)

For Each col In tbl.Columns
Debug.Print col.Name, col.Properties("Fixed length")
Next

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function

Function ShowPropsDAO(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)

For Each fld In tdf.Fields
Debug.Print fld.Name,
Debug.Print ((fld.Attributes And dbFixedField) 0)
Next

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EK" wrote in message
om...
Access 2000 will allow me to insert a blank into a field with the
Allow zero length property set to no. Here is what I did.

Created a table in access with SQL. CREATE TABLE TestTable (String1
char(4),String2 char(3),String3 char(40),String4 char(13))

Went to the design view of the table in Access and verified that the
"Allow zero length" property is "no".

Created an insert query like this: INSERT INTO TestTable ( String1,
String2, String3, String4 ) SELECT "Test2", '' , '' , ''

Ran the query and I will get a row of data in the testtable.

If I go into the design of the table and change the length of field
"string4" from 13 to 15 and save my changes. If I re-run the insert
query above I will get an access error "microsoft Access can't append
all the records in the append query" due to a validation rule
violation.

What is going on? Is my create table incorrect? I need to create the
table with SQL (external app actual creates the table, but problem can
be reproduce in Access)

Thanks in advance,

Your are correct sir! I did notice that that the fields were filled
with spaces, but never knew why. Thanks for the explanation.


"Allen Browne" wrote in message ...
Hi EK.

Access is not violating the Allow Zero Length property, but you have created
a table with a different field type than you expected.

In Access (JET), you would normally create text fields with the TEXT keyword
rather than the CHAR keyword, i.e.:
CREATE TABLE TestTable
(String1 TEXT(4),String2 TEXT(3),String3 TEXT(40),String4 TEXT(13));
If you do that, everything works as expected.

If you use the CHAR keyword, Access creates a *fixed-width* field. Then when
you execute your insert query, Access does not insert zero-length strings,
but fills the fields with spaces. You can see this if you open the table and
look at the data: you can select the text (the spaces) in each field.

You can also use ADOX or DAO to demonstratethat the field is fixed length
when the table created using the CHAR keyword, and False for the table
created using the TEXT keyword:

Function ShowPropsADOX(strTable As String)
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)

For Each col In tbl.Columns
Debug.Print col.Name, col.Properties("Fixed length")
Next

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function

Function ShowPropsDAO(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)

For Each fld In tdf.Fields
Debug.Print fld.Name,
Debug.Print ((fld.Attributes And dbFixedField) 0)
Next

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EK" wrote in message
om...
Access 2000 will allow me to insert a blank into a field with the
Allow zero length property set to no. Here is what I did.

Created a table in access with SQL. CREATE TABLE TestTable (String1
char(4),String2 char(3),String3 char(40),String4 char(13))

Went to the design view of the table in Access and verified that the
"Allow zero length" property is "no".

Created an insert query like this: INSERT INTO TestTable ( String1,
String2, String3, String4 ) SELECT "Test2", '' , '' , ''

Ran the query and I will get a row of data in the testtable.

If I go into the design of the table and change the length of field
"string4" from 13 to 15 and save my changes. If I re-run the insert
query above I will get an access error "microsoft Access can't append
all the records in the append query" due to a validation rule
violation.

What is going on? Is my create table incorrect? I need to create the
table with SQL (external app actual creates the table, but problem can
be reproduce in Access)

Thanks in advance,