If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with CHAR type in CREATE TABLE DDL
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) |
#2
|
|||
|
|||
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(). -- 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. "david epsom dot com dot au" david@epsomdotcomdotau wrote in message ... 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) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unable to edit records in a form or query | Merlin | Using Forms | 7 | May 10th, 2005 02:00 PM |
novice needs help with query | terry | Running & Setting Up Queries | 4 | March 12th, 2005 01:09 AM |
Mixed up with Relationships..help! | KrazyRed | New Users | 3 | January 26th, 2005 05:03 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |