Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Creating ASCII text from MS Access 2003

Posted on 2008-10-15
Medium Priority
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 93

Accepted Solution

Patrick Matthews earned 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 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