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

padding text fields with blank - MSAccess vs. OLEDB Jet4.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
John, you are creating the wrong field type, i.e. a fixed-width field
instead of a variable-width type.

In ADOX, you need to specify adVarWChar, not adWChar.
In DDL, you need to use TEXT (10), not CHAR (10).
Be sure to specify the size part: in some scenarios you can get a "memo" if
you don't.

If it's any use, here's the chart I use to keep track of what the field
types mean under JET, DDL, DAO, and ADOX:
http://allenbrowne.com/ser-49.html

--
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.

"JohnR" <Jo******@hotmail.com> wrote in message
news:pMhPf.12217$Cc3.11377@trnddc08...
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 8 '06 #2

P: n/a
Hi Allen,

Thank you so very much for your reply... you are absolutely correct.
Fortunately, our application is designed for multiple databases, each one
having it's own name for different field types, so we have a 'virtual'
fieldname function. Bottom line is we only had to change one single line of
code from "CHAR" to "TEXT" and it worked perfectly!! No more trailing
blanks!
I cannot begin to tell you how crazy this situation was making me. I just
didn't realize that there was a difference between 'text' and 'char'... I
thought they were synonyms.
Anyway, just wanted to let you know what a difference you made half way
round the world (here in NY).
Thanks again,
John

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
John, you are creating the wrong field type, i.e. a fixed-width field
instead of a variable-width type.

In ADOX, you need to specify adVarWChar, not adWChar.
In DDL, you need to use TEXT (10), not CHAR (10).
Be sure to specify the size part: in some scenarios you can get a "memo"
if you don't.

If it's any use, here's the chart I use to keep track of what the field
types mean under JET, DDL, DAO, and ADOX:
http://allenbrowne.com/ser-49.html

--
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.

"JohnR" <Jo******@hotmail.com> wrote in message
news:pMhPf.12217$Cc3.11377@trnddc08...
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 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.