padding text field with blank in MS Access vs OLEDB Jet 4.0

7 Mar 2006 3:25 PM

JohnR

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?

Thanks, John

8 Mar 2006 4:04 PM

Paul Clement

On Tue, 07 Mar 2006 15:25:26 GMT, "JohnR" <JohnR***@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)

8 Mar 2006 4:23 PM

JohnR

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

Show quoteHide quote

"Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message
news:asvt02t254q0fhgatfdq11bia70u9t9d0m@4ax.com...
> On Tue, 07 Mar 2006 15:25:26 GMT, "JohnR" <JohnR***@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)