Solved

Creating ASCII text from MS Access 2003

Posted on 2008-10-15
6
801 Views
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.
Regards
Bruce
0
Comment
Question by:rbscott
  • 4
  • 2
6 Comments
 
LVL 92

Accepted Solution

by:
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.

Regards,

Patrick
0
 

Author Comment

by:rbscott
ID: 22726552
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
0
 

Author Comment

by:rbscott
ID: 22726682
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

0
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...
0
 

Author Comment

by:rbscott
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.

Regards
Bruce
0
 

Author Closing Comment

by:rbscott
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
Regards
Bruce
0

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