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,
4 1750
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? - ballot_style_1.txt
- ballot_style_2.txt
- ballot_style_3.txt
- ballot_style_4.txt
- ballot_style_5.txt
- ballot_style_6.txt
- ballot_style_7.txt
- ballot_style_8.txt
- ballot_style_9.txt
- ...
- ballot_style_300.txt
NOTE: This can easily be accomplished, but I must know this little detail in advance.
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 -
Function ExportTxt()
-
Dim rs As dao.Recordset
-
Dim myStyle As Long
-
Set rs = CurrentDb.OpenRecordset("select distinct Ballot_Style from Sample_Ballot;")
-
If rs.EOF Then Exit Function
-
rs.MoveFirst
-
myStyle = rs!Ballot_Style
-
strSQL = "SELECT * INTO [Text;DATABASE=C:\sample]." & myStyle & ".txt FROM Sample_Ballot" & _
-
" WHERE trim([Ballot_Style])=" & myStyle & ";"
-
rs.MoveNext
-
Loop
-
rs.Close
-
Set rs = Nothing
-
End Function
-
Just for clarification, are you requesting that each Ballot Style be exported to a different Text File as in?- ballot_style_1.txt
- ballot_style_2.txt
- ballot_style_3.txt
- ballot_style_4.txt
- ballot_style_5.txt
- ballot_style_6.txt
- ballot_style_7.txt
- ballot_style_8.txt
- ballot_style_9.txt
- ...
- 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
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 - .
-
Function ExportTxt()
-
Dim rs As dao.Recordset
-
Dim qDf As QueryDef
-
Dim strSQL As String
-
Dim myStyle As String
-
Dim myFile As Long
-
Set rs = CurrentDb.OpenRecordset("select distinct Ballot_Style from Sample_Ballot;")
-
If rs.EOF Then Exit Function
-
rs.MoveFirst
-
On Error Resume Next
-
DoCmd.DeleteObject acQuery, '<--- This deletes the old query getting ready to make a new one
-
myStyle = rs!Ballot_Style
-
myFile = "C:\sample\ballot_style_" & myStyle & ".txt" 'ballot_style_1.txt
-
strSQL = "SELECT * FROM Sample_Ballot WHERE trim([Ballot_Style])='" & myStyle & "';"
-
Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
-
DoCmd.TransferText acExportDelim, , "MyQuery", myFile, False
-
rs.MoveNext
-
Loop
-
rs.Close
-
Set rs = Nothing
-
End Function
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Janne Ruuttunen |
last post by:
Hello DB2 people,
I'm having problems exporting >= 250000 lobs to IXF files with the
LOBSINFILE option, using a legacy DB2 2.1 system on Win NT.
If I don't specify a path for the lobs,...
|
by: Bridget Willey |
last post by:
I am using ACT 6 and am trying to "split" the database between records
for customers and junk records. The accounts designated as "customers"
have that word in the ID field, and I am using that...
|
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much...
|
by: Jorge Cecílio |
last post by:
Hi!
I would like to export some MS-Access reports output to pdf.
However, the only possibility offered by Access (afaik) for me to export
formatted output is snp (snapshot) (I use MS-Office...
|
by: excyauseme |
last post by:
Hi guys! Do you know what is the best way to export a text file, this
one is a log file that is already comma delimited thru a module run by
my access database, to an excel spreadsheet? I need to...
|
by: Wandering |
last post by:
I know you guys are heavy duty coders, while I do ad-hoc analysis, and
rarely write code. And, I may be in the wrong groups because this is
about an install issue, and I don't think it's a code...
|
by: johnvonc |
last post by:
I am using Access 2003, and trying to Export query results in code to
a csv file.
I am using DoCmd.TransferText. When I run the query, there are two
memo fields: Description and...
|
by: =?Utf-8?B?YzY3NjIyOA==?= |
last post by:
Hi all,
I have a question for you.
I have a .csv file which has many lines of data.
Each line has many data fields which are delimited by ",".
Now I need to extract part of data from this...
|
by: Edward Reid |
last post by:
OK, I've been watching my DB grow and have known for a long time I
would have to move the back end, but of course I waited until I
started getting odd behavior (at about 1.92GB). So today I...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |