[RESOLVED] Export to Text File

I'm not sure if this belongs here or in the Database forum as the topic is technically VBA, but I'm posting here in the hopes that someone can help me.

I need to, in code, run a query and then have the results of that query exported to a pipe-delimited text file. I have tried several different methods of doing this with zero success:

DoCmd.TransferText - I create the specification file and name the location of where to export, but I keep getting an error indicating that the jet engine cannot find the output file "text#txt". Notice that it's putting in the # where I would put in the period (I name the file test.txt).

DoCmd.OutputTo - I create all the sql and the code runs, but when I view the test file something is wrong. For starters, all fields are truncated to a length of 50 characters. Secondly, before and after every record, a record of dashes (--------) is being inserted.

I haven't tried doing file I/O yet, namely because I'm not sure how I would go about accomplishing that. If that's the way I have to go, then I'm all willing to learn and get it done.

Can anybody out here give me a little help on this? I've included the code for the TransferText and OutputTo commands so you can see what I've got coded. If doing file I/O is better, just point me in the right direction on how to get this accomplished. Thanks!

TransferText:

Code:

---

Private Sub cmdExport_Click()
    Dim intCount As Integer
    Dim intMsg As Integer
    Dim strSelect As String
   
    cmbTables.SetFocus
    If cmbTables.Text = "" Or IsNull(cmbTables.Text) Then
        intMsg = MsgBox("You must select a table to export.", vbOKOnly + vbInformation, "No Table Selected")
        Exit Sub
    End If
   
    lstLLK.SetFocus
    If lstLLK.ListCount < 1 Then
        intMsg = MsgBox("You must select at least 1 Load Log Key to export.", vbOKOnly + vbInformation, "No LLK Selected")
        Exit Sub
    End If
   
    If lstLLK.ItemsSelected.Count = 0 Then
        intMsg = MsgBox("You must select at least 1 Load Log Key to export.", vbOKOnly + vbInformation, "No LLK Selected")
        Exit Sub
    End If
   
    cmbTables.SetFocus
    strSelect = "SELECT * INTO QueryResults " & _
                "FROM " & cmbTables.Text & " " & _
                "WHERE Load_Log_Key IN("
   
    lstLLK.SetFocus
    For intCount = 0 To lstLLK.ListCount - 1
        If lstLLK.Selected(intCount) = True Then
            strSelect = strSelect & lstLLK.Column(0, intCount)
            Exit For
        End If
    Next
   
    strSelect = strSelect & ")"
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSelect)
    strSelect = "e:\testing.txt"
    DoCmd.TransferText acExportDelim, "Claim_Service_Errors_Spec", "QueryResults", strSelect
End Sub

---

OutputTo:

Code:

---

'  all code stays the same until you get to the last DoCmd command
    DoCmd.OutputTo acOutputTable, "QueryResults", acFormatTxt, "e:\test.txt"

---

Try:

Code:

---

Debug.Print(strSelect)

---

I don't think you want FROM text.file. Where are you getting the query?

I used a message box to show the contents of strSelect, and the query is correct. For this example, I'll just fill in bogus table and load log key values:

Code:

---

Select * from table1 Where load_log_key=001

---

So the problem here isn't in the SQL statement (and to answer your question, I wrote the statement free-hand). I don't know what you mean by "select FROM text.file", though. I don't see anywhere in my code where I am selecting from a text file?

But you posted this:

Code:

---

  cmbTables.SetFocus
    strSelect = "SELECT * INTO QueryResults " & _
                "FROM " & cmbTables.Text & " " & _
                "WHERE Load_Log_Key IN("

---

cmbTables is a combobox filled with the table names from the database itself. So when the code states

Code:

---

strSelect = "SELECT * INTO QueryResults " & _
                "FROM " & cmbTables.Text & " " & _
                "WHERE Load_Log_Key IN("

---

it goes out and gets the text value from cmbTables and fills that in the query. The query works fine. I'm just not sure why I'm unable to export to a text file.

Record a macro, and see what's different.

http://www.databasedev.co.uk/export-macro.html

After looking at the code I wrote and reading up on file management, I decided to use file I/O and just write to a text file instead of trying to use an export function. And it works. Thanks for the help, though!