Access does not reserve space for unused portions of a text field?

[George Hester]

Really now. Let's try to prove this statement incorrect which can be found in the Help for Microsoft Access 2002 by going to a mdb database and select a table which has a field of datatype Text and is set to the maximum of 255 characters highlight the word Text and hit F1.

Take any such table and enter some values for this field. Say:

Hi
Bye
Afghanistan
The White House
Moscow

Remember the field width is 255. Now export this table to a Text file. Choose {space} as the field delimiter and the text qualifier {none}. File format delimited. Include field names on first row. Finish.

Now look at the text file. You will see that Access 2002 carried over the full 255 characters (mostly spaces) to the text file. Well now, "Access does not reserve space for unused portions of a text field?" I may be a rock but the only thing I can think of that carried over these spaces is something called Access 2002 which must have reserved the spaces in the unused portions of the text field to get them into the exported text file.

I would prefer it not do this. How? All I want is a text file without all the added junk (spaces).
--
George Hester
__________________________________

[Douglas J. Steele]

I suppose it's possible that things changed in Access 2002, but I know that
previous versions of Access don't reserve space.

Here's how you can test:

Create a table with 10 text fields, each of size 255. Access will let you
(even though a single row in a table cannot exceed 2000 characters). Start
populating your table. Put large bits of text into each field. You won't run
into a problem until you try to put the 2001st character in the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"George Hester" <heste...@hotmail.com> wrote in message
news:e0zcnCrN...@TK2MSFTNGP12.phx.gbl...

[Van T. Dinh]

I think the problem might be in your choice of the Field delimiter Access
was guessing and padded the Field values. Try a non-white-space character
or as Doug suggested.

--
HTH
Van T. Dinh
MVP (Access)

"George Hester" <heste...@hotmail.com> wrote in message
news:e0zcnCrN...@TK2MSFTNGP12.phx.gbl...

[Cheryl Fischer]

George,

Using Access 2002 (SP-2 with no prior Office versions installed) and
following your instructions with the following test table:

Table in 2002 format:

Field_1 - 255 chars
Field_2 - 50 chars

Field_1 Values
Sidney
The Hague
San Francisco

Field_2 Values
Australia
Netherlands
California

I get the following Text File:

Field_1 Field_2
Sidney Australia
The Hague Netherlands
San Francisco California

Did the export with table open, using File|Export, and with the table closed
by right-clicking on the table name and selecting export. Looking at the
records in the Wizard's window while performing the operation, the space
between the fields never varied.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

"George Hester" <heste...@hotmail.com> wrote in message
news:e0zcnCrN...@TK2MSFTNGP12.phx.gbl...

[Tony Toews]

"George Hester" <heste...@hotmail.com> wrote:

>Really now. Let's try to prove this statement incorrect which can be found in the Help for Microsoft Access 2002 by going to a mdb database and select a table which has a field of datatype Text and is set to the maximum of 255 characters highlight the word Text and hit F1.

Create a new MDB and a table with two fields, one an autonumber, the
other a text field size of 255. Cut and paste 1000 records with a
single letter in the text field. If it took 255 characters then
you'd expect the MDB to be approximately a minimum of 255 kb. It's
size is 172 032 bytes.

Remove all the records but leave the table definition in. Compact the
MDB. It's size is 135 168 bytes or a difference of .36684. Thus that
1000 records took at most 36 bytes per record.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[George Hester]

OK OK I did what needed to be done. My example still holds though. After you export to the text file in Notepad you have to start at the bottom with your mouse and run the cursor up to the top. All the blue you see is the 255 characters of the field. Mostly all spaces.

But here is what I did to avoid this issue. I changed the schema.ini that is used when programmatically exporting the Linked Table(to THE text file) to a text (the same) file. I am not really exporting the table to the text file just refreshing the linked table.

//schema.ini.
[MyTextFile.txt]
ColNameHeader=True
Format=Delimited( )
MaxScanRows=11
CharacterSet=OEM
Col1="#Field_One" Char Width 10
Col2="Field_Two" Char

This solved the problem which caused me to investigate what Access 2002 does when exporting a Table to a text file.
--
George Hester
__________________________________

"George Hester" <heste...@hotmail.com> wrote in message news:e0zcnCrN...@TK2MSFTNGP12.phx.gbl...

[Douglas J. Steele]

The fact that there are 255 characters when exported doesn't prove diddly
about whether or not there are 255 characters in the database.

Since the field is defined as Text 255, I suspect Access pads when
exporting, just as it automatically creates fields of Text 255 when it
imports fields, even if they're smaller.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"George Hester" <heste...@hotmail.com> wrote in message

news:OQqpwftN...@TK2MSFTNGP12.phx.gbl...

[George Hester]

Yes I know that. I suppose I have to set the export up correctly. Note though that without a schema.ini we get the full character width of the field. As far as I can tell this behavior is not customizable from Access. It can only be stopped through a schema.ini that is only accessible through VBA. At least I saw no option for this in the export wizard.

--
George Hester
__________________________________
"Douglas J. Steele" <djst...@canada.com> wrote in message news:%2371BEI3...@tk2msftngp13.phx.gbl...