asked on

Creating ASCII text from MS Access 2003

How can I create an ascii text file from within Access VBA so that a word starting with - appears at the start of a text file.
If I export a table now as a text file or create a text using vba code it would put a word like --Caltex in with the words starting with c.
How can I get the table to export in true ascii format so that the words that start with - appear at the start of the text file.
Creating a recordset in vba using the Order By clause but it still does export in true ascii format.

Any help would be appreciated.
Regards
Bruce

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rbscott

ASKER

Hi Matthew
Some sample data may be
#123
-123
123
#lf3000
-lf3000
lf3000
If I do a sort ascending or export the table to a text file I get the following sort order
#123
#lf3000
123
-123
lf3000
-lf3000
True ascii sort is as follows
-123
-lf3000
#123
#lf3000
123
lf3000
Used the sort.exe command in the dos window to get the sort order above.
sort parts.txt
I put these parts into a table in an access database called table1.
Used the export command on the toolbar to export to a text file.
Also created the same file using vba code using the Order By clause in a recordset based on table on.
Acheived the same result both ways.
Hope you can do something with this information.

Thanks
Bruce

Parts.txt

rbscott

ASKER

Matthew
I have attached the code that creates the text file

Cheers
Bruce

Private Sub Command0_Click()
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset
Dim strTemp As String
Dim FileNo As Integer
Dim strFilename As String
Dim FN
Set rst = dbs.OpenRecordset("SELECT Table1.Part From Table1 Order By Part;")
rst.MoveFirst
FileNo = FreeFile()
 
strFilename = "D:\Downloads\Parts.txt"
 
Open strFilename For Output As FileNo
Do
        
        strTemp = rst.Fields("Part")
        Print #FileNo, strTemp
        rst.MoveNext
Loop Until rst.EOF
 
   
    Close FileNo
End Sub

Open in new window

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rbscott

ASKER

Thankyou Matthew
I guess that would work but it may be hard to work out all the different ascii codes that someone may use.
the - and the # were just an example, they may use ^ % $ etc.
but this is a start.
I thought that there may have been a command like the DOS Sort.exe available in VBA.
It might be just as easy to say to people, do not use ASCII symbols in the part Number field.
Thanks for your help so far.

Regards
Bruce

rbscott

ASKER

It did not help as I explained in my last post.
I cannot assume what ASCII charactors people are going to use and what order they may be sorted in.
The other option for me is too when I create the text file is to sheell to DOS and do a sort.exe on the text file
I accepted this and posted points at least a month ago
Regards
Bruce