Re: Ascii Line Separated File Format - Export

The Pipe wrote:

On 11 Apr, 18:38, Salad <o...@xxxxxxxxxxx> wrote:

The Pipe wrote:

On 10 Apr, 22:03, Salad <o...@xxxxxxxxxxx> wrote:

The Pipe wrote:

On 10 Apr, 14:24, Salad <o...@xxxxxxxxxxx> wrote:

The Pipe wrote:

Hello

I have a table that I need to export to a .asc file format. I have had
several attempts at getting this to work but with no luck. Basically
the file would need to have every record displayed on a separate line
- If you like a CrLF delimited file. The unfortunate thing is that the
file is to be then imported into a third party piece of software and
as such I have no other alternative but to use this file format.

Does anyone have any bright ideas as to how I might achieve this?

Any pointers would be appreciated.

You mention you want a record displayed on a separate line then mention
a CrLF delimited file. Did you want a field per line?
123 Tom 1/1/2008
is 1 record per line. Or do you want
123
Tom
1/1/2008
as in a field per line?

If the second, you'll have to roll your own. The code below will get
you close.

Sub WriteFld()
Dim strFile As String
Dim rst As Recordset
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("JunkTable", dbOpenSnapshot)
If rst.RecordCount > 0 Then
strFile = "C:\Test\Junk.Txt" 'output filename
Open strFile For Output As #1

rst.MoveFirst
Do While Not rst.EOF

'loop thru each field in the recordset and print value
For i = 0 To rst.Fields.Count - 1
Print #1, rst(i).Value
Next
rst.MoveNext
Loop

Close #1 'close output file
End If

rst.Close
Set rst = Nothing
End Sub

Traffichttp://www.youtube.com/watch?v=FG00Y1M6cDg

Thanks Salad

I appreciate that my requirements were, at best, described in a
somewhat loose manner (Time is of the essence and all that). It was
the "second" one that I was after - one field per line - I shall give
your "get you close" code a trial as soon as I am able. I kinda get
the concept - whatever happens I am sure I will learn something new.

About all that needs to be changed is the table/query name...you could
pass that as an argument...and the output filename...which could also be
passed as an argument. Beyond that? I suppose you could add an error
routine.

I will be sure to post the results.

That'd be nice.

Pipe

PS Top link (Traffic) - Reminds me of something.....- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I have given this a go but Access keeps calling runtime error 3001 -
Invalid Argument.

I have done a bit of investigation but have come up with nothing - any
ideas?

No, I don't.

The program should stop on the line it fails on. It it doesn't, comment
on the OnError routine temporarily.

I changed the line
Set rst = CurrentDb.OpenRecordset("JunkTable", dbOpenSnapshot)
to
Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
which happens to be a table I have.

I then changed
strFile = "C:\Test\Junk.Txt" 'output filename
to
strFile = "C:\Table1.Txt" 'output filename
and then ran the code. It ran as expected and it created Table1.Txt as
the output file in the format you desire.

I suppose you could make it generic and have the sub be
Sub WriteFld(strFile As String, strTable As String)
and change the rst line to
Set rst = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
and remove the lines
Dim strFile As String
strFile = "C:\Test\Junk.Txt" 'output filename

Then from a program or immediate window w/o the Dim statements you could
call the routine like this.
Dim strFile As String
Dim strTable As String

strFile = "C:\Junk.Txt"
strTable = "Customers"
WriteFld strFile, strTable

Bakermanhttp://www.youtube.com/watch?v=ymdssZOAx3Q- Hide quoted text -

- Show quoted text -


Finally got to try this at home and has worked a treat!

I put in a little extra that catches any nulls and ouputs them as zero
length strings (The output file contained the text "Null" wherever
there was one from the source).

Can't thank you enough for your help with this salad.

You're welcome. Compliments are our pay and are appreciated.

This group is par excellent in assisting people.

CDMA Made Me Do It.
http://www.youtube.com/watch?v=HzeZhCt5PVA

.

Relevant Pages

  • Need Help | Rolling out new App. | Trouble with some computers.
    ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ... ' Data file not found in application folder - try to ask the user ...
    (microsoft.public.access.modulesdaovba)
  • RE: Recordset looping (and debug looping!)
    ... Is there any way to address this Sndx issue for multi-users? ... I added Sndx to my table, but "String" is not an available Data Type. ... Dim rst As DAO.Recordset, strNames As String ... Set rst = CurrentDb.OpenRecordset ...
    (microsoft.public.access.formscoding)
  • RE: Recordset looping (and debug looping!)
    ... I added Sndx to my table, but "String" is not an available Data Type. ... 'NEW SOUNDEX CODE FROM STEVES ... Dim rst As DAO.Recordset, strNames As String ... Set rst = CurrentDb.OpenRecordset ...
    (microsoft.public.access.formscoding)
  • RE: Need Help | Rolling out new App. | Trouble with some computers.
    ... Option Compare Database ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Ascii Line Separated File Format - Export
    ... Dim strFile As String ... Set rst = CurrentDb.OpenRecordset ... Sub WriteFld(strFile As String, strTable As String) ...
    (comp.databases.ms-access)