Using DDL to alter fieldsize

[Dave Meckley]

I'm using VB6 to connect to an Access database. When designing a table in
Access, if I set a text field size to a value, the data returned from a
query from that column has a length of only the data in the field. Using
DDL to alter the field size results in data that is always the length of the
columns field size. For example, when initially setting a field size of a
column to 10, if the data in that column only has a length of four, the
returned value is "ABCD". When altering the field size via DDL to 20, the
data returned for that same row will now always have a length of 20.( "ABCD
"). Is there any way to eliminate the spaces Access adds to 'fill up' the
field size? An exapmple of the DDL statement I use would be: "ALTER TABLE
Parts ALTER Description CHAR(20)"

Thanks in advance for any replys!

 

[Brendan Reynolds]

I haven't tested this, but what happens if you use VARCHAR instead of CHAR?

The reason I suggest this is that in SQL Server, VARCHAR specifies a
variable-width field while CHAR specifies a fixed-width field. Until now, I
had no idea that Jet supported fixed-width text fields and would have
assumed that VARCHAR and CHAR would be equivalent in Jet, but your post
seems to indicate that it isn't so.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.