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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

717 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