Export query results to a custom text file

[Matey]

I have a Query, that returns a variable number of records. Is there any way
I can get to a specific field within each record, so I can write it manually
into a file ?

For example, lets say the Query would return (Datasheet view)
Name(text) ID(autonumber) E-mail(text)
Someone1 1123 ma...@nomail.net
Someone2 1223 ma...@nomail.net

Then I have a simple text file (just opened with OPEN "file1" AS #1).

And let's say I'd like to write both the e-mail addresses into this file in
the same
line as in "ma...@nomail.net, ma...@nomail.net". I thought it would best be
done by accessing each of the records in a loop and then write the relative
field value, however I do not know how to access a specific field within a
specific record.

Any help is appreciated, thanks.

[Fletcher]

You could use DAO to do that, e.g.

Public Sub CreateDatFile()

On Error GoTo Err_CreateDatFile

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strFileName As String
Dim intFile As Integer
Dim strOutput As String

strSQL = "SELECT tblPeople.* FROM tblPeople WHERE tblPeople.Email IS NOT
NULL"
strFileName = "C:\Test.dat"
intFile = FreeFile()

Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset(strSQL)
Open strFileName For Output As #intFile

Do While Not rs.EOF
strOutput = strOutput & rs!Email & ","
rs.MoveNext
Loop

Print #intFile, strOutput

MsgBox "Done", vbInformation

Exit_CreateDatFile:
On Error Resume Next
Set rs = Nothing
Set dbs = Nothing
Close #intFile
Exit Sub

Err_CreateDatFile:
MsgBox Err.Description, vbExclamation
Resume Exit_CreateDatFile

End Sub

"Matey" <irm...@slo.net> wrote in message
news:6nNC8.226$mt3....@news.siol.net...

[Andy Kestle]

Snippet from a similar function I have, (air code .. not tested)

Dim rs as recordset
Dim selectSQL as string
Dim OutputStr as string
Dim Filename as string
Dim FileNo as integer

selectSQL = "Select ...........'"
'retrieve records
Set rs = CurrentDb.OpenRecordset(selectSQL)
'if records were returned
If Not rs.EOF Then
rs.MoveFirst
outputStr = ""
While Not rs.EOF
outputStr = outputStr & rs.Fields("Email") & ";"
rs.MoveNext
Wend
'output to file
FileNo = FreeFile()
Open Filename For Append As #FileNo
Print #FileNo, outputStr
Close #FileNo
HTH
Andy

Matey wrote in message <6nNC8.226$mt3....@news.siol.net>...

[Matey]

Thank you very much for your help, the solutions seems to be a good one, yet
I'm stuck with a problem. Excuse me for having these problems, but I'm not
at home with VBA. When I use the code suggested, I always get some sort of
error, when it comes to "set rs = CurrentDB.OpenRecordset (selectSQL)".

Even if I set the selectSQL to something trivial as a Table name (say
"Table1") (Help says it should work) I end up with a "Type mismatch error".
The same occurs for a simple SQL statement ("Select * From Table1"). Now, I
thought, maybe I had an ADO project instead of a DAO database, but I have a
simple *.mdb file so that shouldn't be the case.

Thank you for any further help.

"Andy Kestle" <kake...@nospam.glam.ac.uk> wrote in message
news:abgg93$355$1...@mannews.swan.ac.uk...

[RJE]

Access 2000 or 2002? If yes, ADO is the "default" so unless you specify,
Dim rs as Recordset gets you an ADODB.Recordset object, not a DAO.Recordset
object. But CurrentDB() is a DAO reference.
use:

Dim rs as DAO.Recordset

and make sure you have a reference to the DAO library, in addition to the
ADO ref.

Jay

"Matey" <irm...@slo.net> wrote in message

news:plSC8.240$mt3....@news.siol.net...

[Matey]

Yes, thanks, that was the main problem ... I suspected something weird was
going on when I couldn't get to any DAO.* references at all, so I switched
it on under VBA/Tools/References. Still the problem with Recordset
persistent until I explicitly wrote "Dim rs As DAO.Recordset" OR Moved the
priority for DAO above ADO. So far I've kept both references, but I keep
reading on the internet that it's not a good practice to do so. I'll see
what happens.

Thanks everyone for the immediate and extensive help !
Cheeers.

"RJE" <rje...@hotmail.com> wrote in message
news:AaTC8.4145$ca6.115...@newssvr30.news.prodigy.com...