473,287 Members | 1,426 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,287 software developers and data experts.

padding text field with blank in MS Access vs OLEDB Jet 4.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 3116
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Mark | last post by:
Hi, Im trying to validate a form, all the validating works apart from one field. This particular field must consist of the first 2 characters as letters, & the following 5 as numbers. And if it...
3
by: Josh Armstrong | last post by:
I need to import text from a text.txt file. The file separates records by an #. Below are 2 sample records they are all fixed size. Any help would be great. Each line represents a field, but of...
6
by: Null Reference | last post by:
Anybody here who can explain or point me to a link ? I wish to create a blank MS Access DB file programmatically using C# . Thanks, nfs
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: Shane | last post by:
I would like to update Access by using a txtBox. Is it possible to update Access this way? Example: If I selected the txtBox and input a line of text I would like that line of text to be sent...
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...
9
by: Neil | last post by:
I need to implement a rich text box in an MDB file for a user base that consists of Access 2000 and Access 2002. Unfortunately, I'm using Access 2003 on my development machine. My understanding is...
2
by: Mimi | last post by:
In Access 2007, I have successfully imported a text file with fixed width fields. However the import did not correctly interpret a date. In the text file the date field had no delimiter, was just...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
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: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.