By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Mar 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
Mar 8 '06 #2

P: n/a
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)

Mar 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.