-
January 4th, 2011, 05:25 PM
#1
[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"
-
January 4th, 2011, 06:08 PM
#2
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?
-
January 4th, 2011, 06:38 PM
#3
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?
-
January 4th, 2011, 07:25 PM
#4
Re: Export to Text File
But you posted this:
Code:
cmbTables.SetFocus
strSelect = "SELECT * INTO QueryResults " & _
"FROM " & cmbTables.Text & " " & _
"WHERE Load_Log_Key IN("
-
January 5th, 2011, 09:59 AM
#5
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.
-
January 5th, 2011, 11:53 AM
#6
-
January 5th, 2011, 12:25 PM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|