Problem with CHAR type in CREATE TABLE DDL

[david epsom dot com dot au]

codedb.execute "CREATE TABLE [tblBK] ( [idxRecord] COUNTER CONSTRAINT pk
PRIMARY KEY ,[str] CHAR(255))"


That created a table with an autonumber primary key, and a 255 character
text field with UniCode compression set to No, Not Required, Not Allow Zero
Length.

But the fields were all fixed length 255! I didn't know Jet even supported
a fixed length text field! When I appended records, all the text fields were
filled to 255 characters.

Actually, Jet doesn't support a fixed length text field: when I turned
Unicode compression to Yes, the field became variable length... although
when I tried to change it back to no compression, I got an error message
about 'too long'... and then it did change back to no compression, in spite
of saying it wouldn't.

If I use Text(255) instead of Char(255) I get correct behaviour:

Access trims trailing spaces when entering data:
Trailing spaces are preserved when data is appended using SQL:
No Trailing spaces are inserted by Jet.

BTW, when I first used the new table, I appended a set of records from a
different database. As well as space filling the text records, Access
managed to damage the autonumber key value. I guess that still has problems
too.

Access 2000 9.0.6926 SP-3
MSJET40.dll 4.0.8618.0

(david)

 

[Allen Browne]

Yes, JET does create a fixed-width field when you execute a DDL query using
the CHAR type.

While I haven't experimented much with this, David, I suspect the other
problems you describe spring from the fact the the interface does not
provide support for fixed-width text fields, even though there is a bit in
the DAO Attributes property for dbFixedField, and it can be set in code when
you CreateField().