Tags: access, aretext, blanks, char, creating, database, example, field, fields, itself, jet40, microsoft, msaccess, mysql, oledb, oracle, padded, padding, sql, text

padding text fields with blank - MSAccess vs. OLEDB Jet4.0

On Database » Microsoft Access

7,193 words with 2 Comments; publish: Wed, 02 Jan 2008 19:41:00 GMT; (250140.63, « »)

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

All Comments

Leave a comment...

  • 2 Comments
    • 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" <JohnR104.ms-access.todaysummary.com.hotmail.com> wrote in message

      news:pMhPf.12217$Cc3.11377.ms-access.todaysummary.com.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

      #1; Wed, 02 Jan 2008 19:42:00 GMT
    • 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" <AllenBrowne.ms-access.todaysummary.com.SeeSig.Invalid> wrote in message

      news:440e9c54$0$14552$5a62ac22.ms-access.todaysummary.com.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" <JohnR104.ms-access.todaysummary.com.hotmail.com> wrote in message

      > news:pMhPf.12217$Cc3.11377.ms-access.todaysummary.com.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

      #2; Wed, 02 Jan 2008 19:43:00 GMT