Hi I need an SQL Statement for Access 2000   to export the results of
a query to a text file.
The user should just have to click the button , and the text file has
to go to a specific location on the hard disk. For further processing

Example
the query display email addresses produces a list of people with email
addresses. there is only 1 field in this query.
the query then has to output that list  (with no text qualifiers,) to
a text file which needs to go  into a specific folder on the hard
disk. for example c:\output\list.txt.  Can access sql do this Or will
I need to combine it with VB?

I will only have to do this once and don?t have the time or
inclination to learn vb. I know a bit of sql but not enough to do
this.

What I want
1  The code to do this.
2  Instructions on how I implement this code in access.
I will upload the database with some sample data on Friday Aug 4th at 09:00 (GMT)to
the following location http://martinri.freeshell.org/database.mdb  But
any answers in the mean time will be appreciated.

Thank you

---

Clarification of Question by martin_sullivan-ga on 05 Aug 2006 10:50 PDT

This question has been answered that peace of code does the job lovely.

---

Clarification of Question by martin_sullivan-ga on 05 Aug 2006 10:51 PDT

Thank you very much.

---

Clarification of Question by martin_sullivan-ga on 06 Aug 2006 07:36 PDT

Hi Problem encountered with code. It works fine when I run it  in a
query with only 1 data field. however if the query has more than one
data field  The code is outputting a blank file.  And saying that Both
eof and bof are true.  I would be most grateful if you could comment
with a modification  so that the code outputs correctly for the
following query Members Mobile Who Have No E-Mail which u can find in
the sample data.

Would it be possible to get the code to output The colem headings as well?
Example
FName,SName,Mobile,
Joe,Blogs,123456789

If you wish me to re submit this as a new question please say so. 

Once again  thank you for the first answer it does exactly what I want.


Martin

Hi martin_sullivan-ga, try this.

NOTE: If you know how to create buttons on a form then create a button
on the Form you wish and jump to Step 8


1. Open the Access Database

2. Goto Menu ----> Window | 1 database : Database ... (this should be the 1st item)

3. Select the Forms from the Objects tab

4. Right-Click the Form from the list that you would like the button
to be on (e.g. "Main Menu") and choose "Design View"

5. From the Toolbox (should be a floating box on the left) click on
Command Button (Tooltip will tell you which icon it is)

6. Click on the Form (the cursor will show a + icon when you can click)

7. If a wizard comes up, click Cancel

8. Right-Click on the button that you just created and choose "Properties"

9. Select the "All" Tab

10. Go Down to "Caption" and enter "Export Email" in the text box

11. Go Down to "On Click" and Double-Click the text box (after you do
this you should see "[Event Procedure]" in the text box)

12. Press the "..." to the right of that text box

13.  Paste the following:


On Error GoTo ErrorHandler:

    Dim f As Integer
    Dim rsEmails As New ADODB.Recordset
    
    ' Get the next availbe file number
    f = FreeFile
    
    ' Open the file
    Open "c:\output\list.txt" For Output As #f
    
    ' Open Table/Query
    rsEmails.Open "select * from [Failed Emails]",
CurrentProject.Connection, adOpenForwardOnly
    
    Do While Not rsEmails.EOF
        ' Do not output empty strings
        If Trim(Nz(rsEmails(0), "")) <> "" Then
            Print #f, rsEmails(0)
        End If
        rsEmails.MoveNext
    Loop
    
    rsEmails.Close

    ' Close the file
    Close #f
    
    Exit Sub
ErrorHandler:
    MsgBox "The following error occured: " & vbCrLf & Err.Source & " -
#" & Err.Number & " - " & Err.Description, vbExclamation
    On Error Resume Next
    Close #f



14. Goto Menu ----> File | Close and Return to Microsoft Access

15. Goto Menu ----> File | Save

16. Goto Menu ----> View | Form View

Once you click the Export Email your file should be in the location
specified (c:\output\list.txt)

NOTE: 
  - Replace   c:\output\list.txt    with the path that you want to output to
  - Replace   Failed Emails     with the table name or query name that has the data


Please let me know if you need anything else or if you get any errors

Thanks
 Mike

Hi martin_sullivan-ga, Google truncated the code so here is the
reformatted code to paste:




On Error GoTo ErrorHandler:

    Dim f As Integer
    Dim rsEmails As New ADODB.Recordset
    
    ' Get the next availbe file number
    f = FreeFile
    
    ' Open the file
    Open "c:\output\list.txt" For Output As #f
    
    ' Open Table/Query
    rsEmails.Open "select * from [Failed Emails]", _
CurrentProject.Connection, adOpenForwardOnly
    
    Do While Not rsEmails.EOF
        ' Do not output empty strings
        If Trim(Nz(rsEmails(0), "")) <> "" Then
            Print #f, rsEmails(0)
        End If
        rsEmails.MoveNext
    Loop
    
    rsEmails.Close

    ' Close the file
    Close #f
    
    Exit Sub
ErrorHandler:
    MsgBox "The following error occured: " & vbCrLf & Err.Source & _
" - #" & Err.Number & " - " & Err.Description, vbExclamation
    On Error Resume Next
    Close #f