CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    100

    [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"

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Export to Text File

    Try:

    Code:
    Debug.Print(strSelect)
    I don't think you want FROM text.file. Where are you getting the query?
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Aug 2009
    Posts
    100

    Re: Export to Text File

    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?

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Export to Text File

    But you posted this:
    Code:
      cmbTables.SetFocus
        strSelect = "SELECT * INTO QueryResults " & _
                    "FROM " & cmbTables.Text & " " & _
                    "WHERE Load_Log_Key IN("
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Aug 2009
    Posts
    100

    Re: Export to Text File

    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.

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Export to Text File

    Record a macro, and see what's different.

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Aug 2009
    Posts
    100

    Re: Export to Text File

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured