Hi Paul,
I just received the solution from Allen Browne in the ms-access forum.
Seems like MDB files can support both fixed len char fields as well as
varchar fields. When creating a field in the Access UI interface you can
ONLY specify TEXT which is a varchar. You have no way to specify a fixed
width char field.
However, using DDL you CAN specify either type of field. What I was doing
was using a fieldname defn of CHAR (10) which created a fixed length field.
What I should have been doing was using TEXT (10) which would create a
varchar field.
After changing my DDL from CHAR to TEXT in my string declarations all my
problems disappeared. My problem was that I thought that TEXT and CHAR were
synonyms and THEY ARE NOT!
This is also why my tables were correct when I created them in the Access
UI, and not correct when I created them programatically using DDL. I was
simply using the wrong ddl field defn. Hopefully this explanation will
educate others as to the difference between Char and Text type fields in MDB
files.
Thanks for taking the time to respond... it is appreciated.
John
"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:as********************************@4ax.com...
On Tue, 07 Mar 2006 15:25:26 GMT, "JohnR" <Jo******@hotmail.com> wrote:
¤ When creating an msAccess db within the Access UI itself the fields that
are
¤ text are NOT padded with blanks. For example, if I have a 10 char field
and
¤ put in "HI" and then when I come back to the field and click my mouse on
it
¤ the cursor is just after the "I" in "HI"... that is, no blanks were
added
¤ to the field. However, when I create an MDB database programatically in
¤ VB.Net using ADOX and create the tables using SQL stmts (ie: Create
table
¤ blah, blah) the text fields DO pad with blanks. So if you open the
¤ programatically created MDB file in access and, just as before, add "HI"
¤ then come back and click on the field, the cursor is positioned to the
end
¤ of the 10th char (ie: the field contains "HI ") with blank
padding.
¤ This is causing me some problems and I was wondering what setting am I
¤ missing.
¤ When creating the tables programatically I tried the "With Compression"
¤ field modifier (which turns on unicode compression) but that didn't seem
to
¤ do it. I also read about a setting called ANSI_PADDING, but am not sure
how
¤ or where to use it (I kept getting syntax errors).
¤
¤ Can anybody explain to me how I can programatically create an MDB file
that
¤ does NOT pad text fields with blank?
I can't repro this issue. I never get any padding unless I explicitly add
spaces programmatically.
How are you adding the data to the database?
In any event, you could use the Trim or RTrim function to remove any
trailing spaces.
Paul
~~~~
Microsoft MVP (Visual Basic)