473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
2 6209
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: GrantS | last post by:
I am unable to get the connection to work with using the app.config file. the connection works when I use 'in line' connection as below:...
9
by: Pam Ammond | last post by:
I need the code to update the database when Save is clicked and a text field has changed. This should be very easy since I used Microsoft's wizards for the OleDBAdapter and OleDBConnection, and...
4
by: Annick Van Hoof | last post by:
Hi, I'm having my ASP.NET application hosted at a Windows 2003 server (IIS6). This works almost all the time (I have uptimes of 100% on most days), but then all of a sudden a few days in a row I...
14
by: Mark B | last post by:
Our webhost (www.usbusinessweb.net) had a W2K IIS5 server crash after a scheduled hard-boot occurred during a ms-security patch install overnight. They couldn't get the server working again so they...
5
by: Paul | last post by:
Hi, I am a self taught VBA programmer, and I'm trying to learn VB2005 Express (The price was right). I like the look of the treeview control, and I'd like to use it as a menu system for my users,...
2
by: JohnR | last post by:
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...
4
by: raju5725 | last post by:
have a MS access table and I want to export it to comma delimited text file. How do I do this programmatically using VB.NET or C#? Thanks for any help in advance. Raju
0
Kermit
by: Kermit | last post by:
Hi. I looked through few pages of threads here, but didn't find the answer to my questions. I'm writting a C# application (Windows Forms) that uses MSAccess database, and I have the following...
3
by: Icemokka | last post by:
Hi, I've got a table in MsAccess with 100+ fields. If I fill a tabletable with this table , change some values , get the update-command via commandbuilder , the update fails. This because the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.