Use parameter Query to export to multiple text files
| Author |
Message |
|
Jim
#1 / 6
|
 Use parameter Query to export to multiple text files Hello, This is driving me crazy! I've created the code to step thru a table, retrieve a value (rs1![Value]), export "qToSchools" (query) as a fixed-length text file using rs1![Value] as part of the name of the exported file. However, I have tried unsuccessfully now, for several days to use the
rs1![Value] as a filter on "qToSchools" (or parameter query) which can then
be exported. The goal is for the code to generate multiple text files.
Any insight would be GREATLY appreciated! And, thanks in advance!
Here is my code:
' ----- begin code ----------------------------
Private Sub btn_Test2_Click()
Dim eFileName As String
Dim rsSelect As String
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
rs1.Open "qEFileSchools", CurrentProject.Connection, adOpenKeyset
Set rs2 = New ADODB.Recordset
While Not rs1.EOF
eFileName = rs1![District#] & ".txt"
rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
[qToSchools].[District] = 'rs1![District#]';"
'***** "qToSchools" is a QUERY
rs2.Open rsSelect, CurrentProject.Connection, adOpenKeyset
DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec",
rsSelect, "G:\" & eFileName, 0
rs1.MoveNext
rs2.Close
Wend
rs1.Close
End Sub
|
| Sun, 15 May 2005 05:05:35 GMT |
|
 |
|
Ken Snel
#2 / 6
|
Use parameter Query to export to multiple text files Try resolving the value of the recordset's field outside the quotes: rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
[qToSchools].[District] = '" & rs1![District#] & "';"
By the way, it's not good practice to use the # character in field or
control names. # is used by ACCESS for delimiting date/time values and for
other things. You may confuse ACCESS someday and then you'll get unexpected
results.
--
Hoping that this is helpful...
Ken Snell
<MS ACCESS MVP>
Quote: > Hello, > This is driving me crazy! I've created the code to step thru a table, > retrieve a value (rs1![Value]), export "qToSchools" (query) as a > fixed-length text file using rs1![Value] as part of the name of the exported > file. > However, I have tried unsuccessfully now, for several days to use the
> rs1![Value] as a filter on "qToSchools" (or parameter query) which can
then
> be exported. The goal is for the code to generate multiple text files.
> Any insight would be GREATLY appreciated! And, thanks in advance!
> Here is my code:
> ' ----- begin code ----------------------------
> Private Sub btn_Test2_Click()
> Dim eFileName As String
> Dim rsSelect As String
> Dim rs1 As ADODB.Recordset
> Dim rs2 As ADODB.Recordset
> Set rs1 = New ADODB.Recordset
> rs1.Open "qEFileSchools", CurrentProject.Connection, adOpenKeyset
> Set rs2 = New ADODB.Recordset
> While Not rs1.EOF
> eFileName = rs1![District#] & ".txt"
> rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> [qToSchools].[District] = 'rs1![District#]';"
> '***** "qToSchools" is a QUERY
> rs2.Open rsSelect, CurrentProject.Connection, adOpenKeyset
> DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec",
> rsSelect, "G:\" & eFileName, 0
> rs1.MoveNext
> rs2.Close
> Wend
> rs1.Close
> End Sub
|
| Sun, 15 May 2005 11:22:41 GMT |
|
|
|
Jim
#3 / 6
|
Use parameter Query to export to multiple text files thanks for the input, ken. however, sadly, the code still crashes. (rs1![District#] is a text value) i changed the line of code to read: rsSelect = "SELECT * FROM [qToSchools] Where [District] = '" & [rs1]![District#] & "'" ' I also tried the following code...
rsSelect = "SELECT * FROM [qToSchools] Where Int([District]) = " &
Int([rs1]![District#])
But, the code crashed both times on the following line:
DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec", rsSelect, "G:\"
& eFileName, 0
Quote: >>Run-time error 3011
MS KB was no help. http://support.microsoft.com/default.aspx?scid=kb;en-us;225987 and... http://support.microsoft.com/default.aspx?scid=kb;en-us;237592 were of no value. so, any ideas???
and you make a great point on the "#" symbol. i've only used it in a few
databases, so it will be easy to de-install.
-jim
Quote: > Try resolving the value of the recordset's field outside the quotes: > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> [qToSchools].[District] = '" & rs1![District#] & "';"
> By the way, it's not good practice to use the # character in field or
> control names. # is used by ACCESS for delimiting date/time values and for
> other things. You may confuse ACCESS someday and then you'll get
unexpected
> results.
> --
> Hoping that this is helpful...
> Ken Snell
> <MS ACCESS MVP>
> > Hello,
> > This is driving me crazy! I've created the code to step thru a table,
> > retrieve a value (rs1![Value]), export "qToSchools" (query) as a
> > fixed-length text file using rs1![Value] as part of the name of the
> exported
> > file.
> > However, I have tried unsuccessfully now, for several days to use the
> > rs1![Value] as a filter on "qToSchools" (or parameter query) which can
> then
> > be exported. The goal is for the code to generate multiple text files.
> > Any insight would be GREATLY appreciated! And, thanks in advance!
> > Here is my code:
> > ' ----- begin code ----------------------------
> > Private Sub btn_Test2_Click()
> > Dim eFileName As String
> > Dim rsSelect As String
> > Dim rs1 As ADODB.Recordset
> > Dim rs2 As ADODB.Recordset
> > Set rs1 = New ADODB.Recordset
> > rs1.Open "qEFileSchools", CurrentProject.Connection, adOpenKeyset
> > Set rs2 = New ADODB.Recordset
> > While Not rs1.EOF
> > eFileName = rs1![District#] & ".txt"
> > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> > [qToSchools].[District] = 'rs1![District#]';"
> > '***** "qToSchools" is a QUERY
> > rs2.Open rsSelect, CurrentProject.Connection, adOpenKeyset
> > DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec",
> > rsSelect, "G:\" & eFileName, 0
> > rs1.MoveNext
> > rs2.Close
> > Wend
> > rs1.Close
> > End Sub
|
| Sun, 15 May 2005 11:34:59 GMT |
|
|
|
Ken Snel
#4 / 6
|
Use parameter Query to export to multiple text files OK - I see the problem. To my knowledge, you cannot use an SQL statement directly in TransferText. That command will export only an existing table or query. What you'd need to do is to create a temporary query from your code, save
that query, export it, and then delete it. I've not actually done this on my
own, so I'm not sure if I know the exact way to set it up. But look up
CreateQueryDef in Help (it is used with DAO recordsets) to start. Not sure
of how ADO might do it.
--
Hoping that this is helpful...
Ken Snell
<MS ACCESS MVP>
Quote: > thanks for the input, ken. > however, sadly, the code still crashes. (rs1![District#] is a text value) > i changed the line of code to read: > rsSelect = "SELECT * FROM [qToSchools] Where [District] = '" & > [rs1]![District#] & "'" > ' I also tried the following code...
> rsSelect = "SELECT * FROM [qToSchools] Where Int([District]) = " &
> Int([rs1]![District#])
> But, the code crashed both times on the following line:
> DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec", rsSelect,
"G:\"
> & eFileName, 0
> >>Run-time error 3011
> MS KB was no help.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;225987 and...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;237592 were of no
> value.
> so, any ideas???
> and you make a great point on the "#" symbol. i've only used it in a few
> databases, so it will be easy to de-install.
> -jim
> > Try resolving the value of the recordset's field outside the quotes:
> > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> > [qToSchools].[District] = '" & rs1![District#] & "';"
> > By the way, it's not good practice to use the # character in field or
> > control names. # is used by ACCESS for delimiting date/time values and
for
> > other things. You may confuse ACCESS someday and then you'll get
> unexpected
> > results.
> > --
> > Hoping that this is helpful...
> > Ken Snell
> > <MS ACCESS MVP>
> > > Hello,
> > > This is driving me crazy! I've created the code to step thru a table,
> > > retrieve a value (rs1![Value]), export "qToSchools" (query) as a
> > > fixed-length text file using rs1![Value] as part of the name of the
> > exported
> > > file.
> > > However, I have tried unsuccessfully now, for several days to use the
> > > rs1![Value] as a filter on "qToSchools" (or parameter query) which can
> > then
> > > be exported. The goal is for the code to generate multiple text
files.
> > > Any insight would be GREATLY appreciated! And, thanks in advance!
> > > Here is my code:
> > > ' ----- begin code ----------------------------
> > > Private Sub btn_Test2_Click()
> > > Dim eFileName As String
> > > Dim rsSelect As String
> > > Dim rs1 As ADODB.Recordset
> > > Dim rs2 As ADODB.Recordset
> > > Set rs1 = New ADODB.Recordset
> > > rs1.Open "qEFileSchools", CurrentProject.Connection, adOpenKeyset
> > > Set rs2 = New ADODB.Recordset
> > > While Not rs1.EOF
> > > eFileName = rs1![District#] & ".txt"
> > > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> > > [qToSchools].[District] = 'rs1![District#]';"
> > > '***** "qToSchools" is a QUERY
> > > rs2.Open rsSelect, CurrentProject.Connection, adOpenKeyset
> > > DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec",
> > > rsSelect, "G:\" & eFileName, 0
> > > rs1.MoveNext
> > > rs2.Close
> > > Wend
> > > rs1.Close
> > > End Sub
|
| Sun, 15 May 2005 11:43:11 GMT |
|
|
|
Jim
#5 / 6
|
Use parameter Query to export to multiple text files ahhh! thanks for the help. i'll see if there's an ADO solution out there. i'd prefer not to have to incorporate DAO. by the way... i checked my other databases, and no #'s to be found. i
usually use the _ID suffix (DistrictID) to identify an "indexable" field.
this particular database must manipulate text strings from schools across
Wisconsin... and who knows how the data will come to me? this is the reason
for the lack of indexes in the tables.
if i have success with the CreateQueryDef , i will post to this thread.
thanks for your help.
-jim
Quote: > OK - I see the problem. To my knowledge, you cannot use an SQL statement > directly in TransferText. That command will export only an existing table or > query. > What you'd need to do is to create a temporary query from your code, save
> that query, export it, and then delete it. I've not actually done this on
my
> own, so I'm not sure if I know the exact way to set it up. But look up
> CreateQueryDef in Help (it is used with DAO recordsets) to start. Not sure
> of how ADO might do it.
> --
> Hoping that this is helpful...
> Ken Snell
> <MS ACCESS MVP>
> > thanks for the input, ken.
> > however, sadly, the code still crashes. (rs1![District#] is a text
value)
> > i changed the line of code to read:
> > rsSelect = "SELECT * FROM [qToSchools] Where [District] = '" &
> > [rs1]![District#] & "'"
> > ' I also tried the following code...
> > rsSelect = "SELECT * FROM [qToSchools] Where Int([District]) = " &
> > Int([rs1]![District#])
> > But, the code crashed both times on the following line:
> > DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec", rsSelect,
> "G:\"
> > & eFileName, 0
> > >>Run-time error 3011
> > MS KB was no help.
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;225987 and...
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;237592 were of
no
> > value.
> > so, any ideas???
> > and you make a great point on the "#" symbol. i've only used it in a
few
> > databases, so it will be easy to de-install.
> > -jim
> > > Try resolving the value of the recordset's field outside the quotes:
> > > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> > > [qToSchools].[District] = '" & rs1![District#] & "';"
> > > By the way, it's not good practice to use the # character in field or
> > > control names. # is used by ACCESS for delimiting date/time values and
> for
> > > other things. You may confuse ACCESS someday and then you'll get
> > unexpected
> > > results.
> > > --
> > > Hoping that this is helpful...
> > > Ken Snell
> > > <MS ACCESS MVP>
> > > > Hello,
> > > > This is driving me crazy! I've created the code to step thru a
table,
> > > > retrieve a value (rs1![Value]), export "qToSchools" (query) as a
> > > > fixed-length text file using rs1![Value] as part of the name of the
> > > exported
> > > > file.
> > > > However, I have tried unsuccessfully now, for several days to use
the
> > > > rs1![Value] as a filter on "qToSchools" (or parameter query) which
can
> > > then
> > > > be exported. The goal is for the code to generate multiple text
> files.
> > > > Any insight would be GREATLY appreciated! And, thanks in advance!
> > > > Here is my code:
> > > > ' ----- begin code ----------------------------
> > > > Private Sub btn_Test2_Click()
> > > > Dim eFileName As String
> > > > Dim rsSelect As String
> > > > Dim rs1 As ADODB.Recordset
> > > > Dim rs2 As ADODB.Recordset
> > > > Set rs1 = New ADODB.Recordset
> > > > rs1.Open "qEFileSchools", CurrentProject.Connection,
adOpenKeyset
> > > > Set rs2 = New ADODB.Recordset
> > > > While Not rs1.EOF
> > > > eFileName = rs1![District#] & ".txt"
> > > > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> > > > [qToSchools].[District] = 'rs1![District#]';"
> > > > '***** "qToSchools" is a QUERY
> > > > rs2.Open rsSelect, CurrentProject.Connection, adOpenKeyset
> > > > DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec",
> > > > rsSelect, "G:\" & eFileName, 0
> > > > rs1.MoveNext
> > > > rs2.Close
> > > > Wend
> > > > rs1.Close
> > > > End Sub
|
| Sun, 15 May 2005 11:58:09 GMT |
|
|
|
Ken Snel
#6 / 6
|
Use parameter Query to export to multiple text files Alternatively, don't use TransferText; instead, write your code-generated SQL recordset directly into a text file. This can be done via a few different methods: look up TextStream (CreateTextFile Method) or Open (Open Statement) in Help. In these situations, you'd run your code-generated query to open a
recordset, then open a text file, then loop through the recordset and write
each record to a line in the text file; you'd need to insert your own
delimiter character as part of writing the line to the text file.
--
Hoping that this is helpful...
Ken Snell
<MS ACCESS MVP>
Quote: > OK - I see the problem. To my knowledge, you cannot use an SQL statement > directly in TransferText. That command will export only an existing table or > query. > What you'd need to do is to create a temporary query from your code, save
> that query, export it, and then delete it. I've not actually done this on
my
> own, so I'm not sure if I know the exact way to set it up. But look up
> CreateQueryDef in Help (it is used with DAO recordsets) to start. Not sure
> of how ADO might do it.
> --
> Hoping that this is helpful...
> Ken Snell
> <MS ACCESS MVP>
> > thanks for the input, ken.
> > however, sadly, the code still crashes. (rs1![District#] is a text
value)
> > i changed the line of code to read:
> > rsSelect = "SELECT * FROM [qToSchools] Where [District] = '" &
> > [rs1]![District#] & "'"
> > ' I also tried the following code...
> > rsSelect = "SELECT * FROM [qToSchools] Where Int([District]) = " &
> > Int([rs1]![District#])
> > But, the code crashed both times on the following line:
> > DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec", rsSelect,
> "G:\"
> > & eFileName, 0
> > >>Run-time error 3011
> > MS KB was no help.
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;225987 and...
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;237592 were of
no
> > value.
> > so, any ideas???
> > and you make a great point on the "#" symbol. i've only used it in a
few
> > databases, so it will be easy to de-install.
> > -jim
> > > Try resolving the value of the recordset's field outside the quotes:
> > > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> > > [qToSchools].[District] = '" & rs1![District#] & "';"
> > > By the way, it's not good practice to use the # character in field or
> > > control names. # is used by ACCESS for delimiting date/time values and
> for
> > > other things. You may confuse ACCESS someday and then you'll get
> > unexpected
> > > results.
> > > --
> > > Hoping that this is helpful...
> > > Ken Snell
> > > <MS ACCESS MVP>
> > > > Hello,
> > > > This is driving me crazy! I've created the code to step thru a
table,
> > > > retrieve a value (rs1![Value]), export "qToSchools" (query) as a
> > > > fixed-length text file using rs1![Value] as part of the name of the
> > > exported
> > > > file.
> > > > However, I have tried unsuccessfully now, for several days to use
the
> > > > rs1![Value] as a filter on "qToSchools" (or parameter query) which
can
> > > then
> > > > be exported. The goal is for the code to generate multiple text
> files.
> > > > Any insight would be GREATLY appreciated! And, thanks in advance!
> > > > Here is my code:
> > > > ' ----- begin code ----------------------------
> > > > Private Sub btn_Test2_Click()
> > > > Dim eFileName As String
> > > > Dim rsSelect As String
> > > > Dim rs1 As ADODB.Recordset
> > > > Dim rs2 As ADODB.Recordset
> > > > Set rs1 = New ADODB.Recordset
> > > > rs1.Open "qEFileSchools", CurrentProject.Connection,
adOpenKeyset
> > > > Set rs2 = New ADODB.Recordset
> > > > While Not rs1.EOF
> > > > eFileName = rs1![District#] & ".txt"
> > > > rsSelect = "SELECT [qToSchools].* FROM [qToSchools] Where
> > > > [qToSchools].[District] = 'rs1![District#]';"
> > > > '***** "qToSchools" is a QUERY
> > > > rs2.Open rsSelect, CurrentProject.Connection, adOpenKeyset
> > > > DoCmd.TransferText acExportFixed, "qToSchools_Export_Spec",
> > > > rsSelect, "G:\" & eFileName, 0
> > > > rs1.MoveNext
> > > > rs2.Close
> > > > Wend
> > > > rs1.Close
> > > > End Sub
|
| Sun, 15 May 2005 11:58:20 GMT |
|
| |
|
