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"