How to Export a table to fixed width Txt file and different extension

[neelsfer]

I have been giving this issue a lot of my time and got stuck now. i use Access 2007

I append order data to a table, and then this data must be converted to a delimited text file with an "in" extension and not txt.
name = "order_.in"

From here onwards I use RDM to send the file electronically to the warehouse, after conversion using other software.

My code gives me an error - "cannot update.database or object is read only" ;Runtime 3027

DoCmd.TransferText acExportDelim, , "rdmexport", "C:\pisprg\order_.in", True, ""

Would it be better to first create a txtfile and then rename the extention to .in?

table name RdmExport
new file name "Order_.in"

I will include an example of another .in file that can be opened with a Notepad to see the format/layout.
i changed the extension to .txt to upload the example.

The Access file with the table in Acc 2003 format is also included.

Info about conversion to text also included

Anybody with ideas to get me out of this nightmare?

[neelsfer]

After many more hours i get it to export to an Order_.in file as required in text format, but the layout or formatting is incorrect. I changed the titles names to the actual field name in the table

Private Sub Command18_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst As DAO.Recordset
Set rst = MyDB.OpenRecordset("rdmexport", dbOpenDynaset)
 
Open "C:\pisprg\order_.in" For Output As #1 ' Open file for output.
 
With rst
.MoveLast
.MoveFirst
Do Until .EOF
'Print the contents of the first set of fields on the first line
Print #1, !tblFacility_1_DemanderCode; Tab(8); !tblFacility_DemanderCode; Tab(16); !Order_No; Tab(26); !DemCodeT; Tab(32); !Icn; Tab(41); !OrderQty; Tab(51); !fms_code; Tab(52); !recurring_indicator; Tab(54); !use_loc_dem_no; Tab(56);
 
 
If Not .EOF Then .MoveNext
Loop
Close #1 'Close the text file.
.Close 'Close the recordset
End With
 
Set rst = Nothing
Set MyDB = Nothing
End Sub
 

The layout has to do with the "tab" in the code somehow. See attachment below pls Suggestions?

[ADezii]

Export as a Delimited Text File, then Rename to proper Extension (.in), as in:

Const conPATH As String = "C:\pisprg\order_.txt"
 
'Export as Text Delimited
DoCmd.TransferText acExportDelim, , "rdmexport", conPATH, True, ""
 
'Rename the File
Name conPATH As Left$(conPATH, InStrRev(conPATH, ".")) & "in"

[neelsfer]

hi Adezii thx for the trouble
I get an error - runtime 3441
textfile specification field separator matches decimal separator or text delimiter
Please advise

[zmbd]

neelsfer
hi Adezii thx for the trouble
I get an error - runtime 3441
textfile specification field separator matches decimal separator or text delimiter
Please advise

:This is a new question:

Text file specification field separator matches decimal separator or text delimiter. (Error 3441)

Text file specification field separator matches decimal separator or text delimiter. (Error 3441)
Two or more of the following delimiters settings used by the Text ISAM are the same:

• TxtDelimiter
• NumberDecimalSymbol
• CurrencyDecimalSymbol
• Format

You need to check the values in your recordset. At least one of the data entry has a symbol that matches the specified deliminator for the TXT/CSV file.
This normally happens when using the comma as a decimal point or visa/versa.
There's a fix for this, the specification file... been awhile since I used one so let me look at some anceint DB and see if I remember how those were made.

[neelsfer]

This problem has kept me busy for 3 evenings now. I also cannot see the advance button to set the "specification", when exporting manually.
Anybody with suggestions?

[zmbd]

Ok,
Still working on this, I know that somewhere in the manual export you can get the Schema file to be created and saved... and there's now an XML version for this in at least 2010.

Here's just a little primer, not a lot of help, but better than sitting stuck in the mud :(

MSDN - Schema.ini File (Text File Driver)


[edit] Just found this too: Ramblings and other such stuff from Martin - Text file specification field separator matches decimal separator or text delimiter