By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,601 Members | 1,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,601 IT Pros & Developers. It's quick & easy.

Export Access DB to several text files

P: 2
I have to first admit that I am in no way an Access wizard.

I am trying to automate the export of an access table to several text files.

My table has a field named Ballot_Style. I want to export all records with like values in the ballot style field to a text file that is named <ballot_style>.txt.

I can do this one style at a time using the following query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [Sample_Ballot].* INTO [Text;DATABASE=C:\sample].1.txt
  3. FROM Sample_Ballot
  4. WHERE trim([Ballot_Style])=1;
It is kind of a pain to have to edit the query for each of 90 - 300 different values that the Ballot_Style field will contain.

Suggestions would be appreciated.
Thanks,
May 16 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,627
I have to first admit that I am in no way an Access wizard.

I am trying to automate the export of an access table to several text files.

My table has a field named Ballot_Style. I want to export all records with like values in the ballot style field to a text file that is named <ballot_style>.txt.

I can do this one style at a time using the following query:

SELECT [Sample_Ballot].* INTO [Text;DATABASE=C:\sample].1.txt
FROM Sample_Ballot
WHERE trim([Ballot_Style])=1;

It is kind of a pain to have to edit the query for each of 90 - 300 different values that the Ballot_Style field will contain.

Suggestions would be appreciated.
Thanks,
Just for clarification, are you requesting that each Ballot Style be exported to a different Text File as in?
  1. ballot_style_1.txt
  2. ballot_style_2.txt
  3. ballot_style_3.txt
  4. ballot_style_4.txt
  5. ballot_style_5.txt
  6. ballot_style_6.txt
  7. ballot_style_7.txt
  8. ballot_style_8.txt
  9. ballot_style_9.txt
  10. ...
  11. ballot_style_300.txt
NOTE: This can easily be accomplished, but I must know this little detail in advance.
May 17 '07 #2

JConsulting
Expert 100+
P: 603
I have to first admit that I am in no way an Access wizard.

I am trying to automate the export of an access table to several text files.

My table has a field named Ballot_Style. I want to export all records with like values in the ballot style field to a text file that is named <ballot_style>.txt.

I can do this one style at a time using the following query:

SELECT [Sample_Ballot].* INTO [Text;DATABASE=C:\sample].1.txt
FROM Sample_Ballot
WHERE trim([Ballot_Style])=1;

It is kind of a pain to have to edit the query for each of 90 - 300 different values that the Ballot_Style field will contain.

Suggestions would be appreciated.
Thanks,
How about this

Expand|Select|Wrap|Line Numbers
  1. Function ExportTxt()
  2. Dim rs As dao.Recordset
  3. Dim myStyle As Long
  4. Set rs = CurrentDb.OpenRecordset("select distinct Ballot_Style from Sample_Ballot;")
  5. If rs.EOF Then Exit Function
  6. rs.MoveFirst
  7. myStyle = rs!Ballot_Style
  8. strSQL = "SELECT * INTO [Text;DATABASE=C:\sample]." & myStyle & ".txt FROM Sample_Ballot" & _
  9. " WHERE trim([Ballot_Style])=" & myStyle & ";"
  10. rs.MoveNext
  11. Loop
  12. rs.Close
  13. Set rs = Nothing
  14. End Function
  15.  
May 17 '07 #3

P: 2
Just for clarification, are you requesting that each Ballot Style be exported to a different Text File as in?
  1. ballot_style_1.txt
  2. ballot_style_2.txt
  3. ballot_style_3.txt
  4. ballot_style_4.txt
  5. ballot_style_5.txt
  6. ballot_style_6.txt
  7. ballot_style_7.txt
  8. ballot_style_8.txt
  9. ballot_style_9.txt
  10. ...
  11. ballot_style_300.txt
NOTE: This can easily be accomplished, but I must know this little detail in advance.
You are correct. I may not have mentioned it, but each exported file needs to be in comma delimited format. In addition, all fields in the Access table are text fields.
Thanks
May 17 '07 #4

JConsulting
Expert 100+
P: 603
You are correct. I may not have mentioned it, but each exported file needs to be in comma delimited format. In addition, all fields in the Access table are text fields.
Thanks
This should get it.
J

Expand|Select|Wrap|Line Numbers
  1. .
  2. Function ExportTxt()
  3. Dim rs As dao.Recordset
  4. Dim qDf As QueryDef
  5. Dim strSQL As String
  6. Dim myStyle As String
  7. Dim myFile As Long
  8. Set rs = CurrentDb.OpenRecordset("select distinct Ballot_Style from Sample_Ballot;")
  9. If rs.EOF Then Exit Function
  10. rs.MoveFirst
  11. On Error Resume Next
  12. DoCmd.DeleteObject acQuery,   '<--- This deletes the old query getting ready to make a new one
  13. myStyle = rs!Ballot_Style
  14. myFile = "C:\sample\ballot_style_" & myStyle & ".txt"  'ballot_style_1.txt
  15. strSQL = "SELECT * FROM Sample_Ballot WHERE trim([Ballot_Style])='" & myStyle & "';"
  16. Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
  17. DoCmd.TransferText acExportDelim, , "MyQuery", myFile, False
  18. rs.MoveNext
  19. Loop
  20. rs.Close
  21. Set rs = Nothing
  22. End Function
  23.  
May 17 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.