Export MS Access Table to Fixed Width Text File

[2Plan]

I would like to export a MS Access Table to Fixed Width Text File.

I would like to use VBA script to set the start/end of field and the width of the field.

Does anyone know a simple way to do this?

Thanks for any help.

 

[2Plan]

Also would it be possible to set name of fields in the script?

 

[traingamer]

then Mid() statemet is your best friend here. A start:

strRptLin = space(80) ' fixed length string
mid(strRptLin, 1, 5) = "ID" 
mid(strRptLin, 6, 25) = "   address"
mid(strRptLin, 26, 10) = "Inv amt"
...
' write header line
(write strRptLin)
...loop...
strRptLin = space(80) ' fixed length string
mid(strRptLin, 1, 5) = mytable.userid ' if longer than 5 is truncated
mid(strRptLin, 6, 25) = mytable.homeaddress
mid(strRptLin, 26, 10) = right(space(10) & mytable.amt, 10) ' right justify a column
...
' write data line
(write strRptLin)
...repeat...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[2Plan]

Just a little more help.

Say the table name is "0705trp" and field names are:

Rout Block_ _Date_ D Bus_ D Trip Time_ On_ Off

451 451001 052807 3 2 6 0837 17.0 3 0
451 451001 052807 3 2 1 0854 28.0 4 3
451 451001 052807 3 2 0 0930 18.0 9 4

Could someone show me how to set up my DIM statements and reference my table?

 

[CautionMP]

traingamer,
I'm not sure where [tt]Mid()[/tt] would help with EXPORT.

2Plan,
Take a look at [tt]TransferText()[/tt] it will allow you to export to a fixed width file.

It takes the name of a table of query as the data source so if you want to change the start/end fields you can create a [tt]QueryDef[/tt] in VBA that has the fields you want and the names for those fields.

Hope this helps,
CMP

 

[traingamer]

I love this forum. I had never used the 'fixed width' option with the TransferText() method. Looks much simpler than my idea (which I have used.)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[2Plan]

When I try to save the specification or export specific fields in the table, I get the error "the number of fields in export specification does not the number of fields in table.

Can I save a specification with specific fields or export specific fields from table to text file?

 

[JA3395]

OK, the easiest way to do this is with a Macro

Step 1.
First of all create an export specification by pre-exporting the table

[File]
Export...
Export Table x as
Text File (*.txt, *.csv, *.tab, *.asc)
Click [Save]
Click "Fixed Width"
Click [Advanced...]
Click [Save As...]
Save the export specification

Step 2.
Create a macro

TransferText
Transfer Type: Export fixed width
Specification Name: <select the export spec you just created from the dropdown list>
Table Name: <your table>
File Name: <full file spec>
Has Field Names: <Yes/No>

Save the macro
then run it.

J.

 

[2Plan]

You've given me several ways to do this.
This forum is great!! Thanks.