Exporting w/ specific paramters

[Antonio]

I need to export data in an access table to a text file with strict fixed
width parameters...but I have no idea how to do this.

I need to export the data in tblUpload to a file named TMIMPORT.REC (save as
type must be "All Files" as opposed to .txt or .xls) so that the file looks
as such
(must be fixed widths)
Field 1 (not in Access table), Length 1, Value "D"
Field 2 (in Access table), Length 4, Value is going to be 1 through 9 but
the text file must display it as "0001" or "0007", etc.
Field 3 (in Access table), Length 15, Value is free text.
Field 4 (in Access table), Length 26, Value is free text
Field 5 (in Access table), Length 11, Value is text
Field 6 (In Access table), Length 30, Value is numeric
Field 7 (not in Access table), Lenght 2, Value is "rs"

The file has to be just so either the program doing the upload/updating will
error out. (I am uploading a text file into another program to update that
programs files)

I would appreciate any help on how to get this accomplished.

TIA

 

[Andy Hull]

Hi Antonio

Do the following steps...
1) Set up a query with the relevant columns. The SQL of which will look
soemthing like...
select "D", format(field2,"0000"), field3, field4, field5, field6, "rs"
from yourtable

Obviously, put the proper field names in.

2) Create an export specification...
Select the query, click on File then Export.
In the "Save as type" drop down, choose "Text Files" and click Save
(Don't worry about the .REC extension yet)
Choose Fixed Width and click the Advanced button
In the bottom half of the window that now shows, set up the field names
start and width as follows...
Field1 1 1
Field2 2 4
Field3 6 15
Field4 21 26
Field5 47 11
Field6 58 30
Field7 88 2

Then click save as, enter a name for the export specification, click OK and
then cancel out of the export (we only needed to get as far as saving the
spec).

3) Now, you can use the following command to perform the export...

DoCmd.TransferText, acExportFixed, ExportSpecName, ExportQryName,
ExportFileName, False

where ExportSpecName is the name you used above - to save the spec,
ExportQryName is the name of your query and ExportFileName is the full path &
filename ending with TMIMPORT.REC

HOWEVER, ONE LAST ISSUE!!!

If you use the above command you will probably get a message saying the
object is read only. This is because Access only recognises certain file
extensions and doesn't like .REC

See the following article for ways around this...

http://support.microsoft.com/?id=304206

Hope this helps

Andy Hull