Creating ASCII text from MS Access 2003

Posted on 2008-10-15
Last Modified: 2013-11-27
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.
Question by:rbscott
  • 4
  • 2
LVL 92

Accepted Solution

Patrick Matthews earned 250 total points
ID: 22726124
Hello rbscott,

It would be useful to see some sample data.  EE now allows you to directly upload
files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it.  Therefore, be very careful about
posting proprietary, confidential, or other sensitive information.  If necessary, use "fake"
and/or obfuscated data in your sample.



Author Comment

ID: 22726552
Hi Matthew
Some sample data may be
If I do a sort ascending or export the table to a text file I get the following sort order
True ascii sort is as follows
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.



Author Comment

ID: 22726682
I have attached the code that creates the text file

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;")


FileNo = FreeFile()

strFilename = "D:\Downloads\Parts.txt"

Open strFilename For Output As FileNo



        strTemp = rst.Fields("Part")

        Print #FileNo, strTemp


Loop Until rst.EOF


    Close FileNo

End Sub

Open in new window

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 22743796
Try an ORDER BY clause like this:

ORDER BY IIf(Left(SomeField, 1) = "-", 1, IIf(Left(SomeField, 1) = "#", 2, 3)), SomeField

That sorts everything starting with -, then everything starting with #, then everything else...

Author Comment

ID: 22750331
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.


Author Closing Comment

ID: 31506514
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now