SELECT string as Recordsource

[Guest]

I have a form that on the OnOpen Event has the following code:
'------------------------------------------------------------------------------
Dim strRecSource, strOne As String
strOne = "[05] + [06]"

strRecSource = "SELECT Table2.Account, " + strOne + " AS Field1 From Table2;"
Me.RecordSource = strRecSource
'---------------------------------------------------------------------------------
All is fine and the form opens and displays the records properly.

Then I was thinking I want to export the form recordset to excel. I tried:

DoCmd.TransferSpreadsheet acExport, 8, "strRecSource", "C:\My
Documents\Table2Export.xls", True, ""

And I received an error 3011 saying Access could not find the object
"strRecSource"

How would I make this export work exporting the Me.RecordsetClone without
using a Table or Query object but using the string created here
'strRecSource' or the Recordset.

Note: this is a simple example and the actual SELECT is more complex than
just exporting the table. I just wanted to keep it simple here in the
example.


Thank you for your help.

Steven

 

[Dirk Goldgar]

I have a form that on the OnOpen Event has the following code:
'------------------------------------------------------------------------------
Dim strRecSource, strOne As String
strOne = "[05] + [06]"

strRecSource = "SELECT Table2.Account, " + strOne + " AS Field1 From
Table2;" Me.RecordSource = strRecSource
'---------------------------------------------------------------------------------
All is fine and the form opens and displays the records properly.

Then I was thinking I want to export the form recordset to excel. I
tried:

DoCmd.TransferSpreadsheet acExport, 8, "strRecSource", "C:\My
Documents\Table2Export.xls", True, ""

And I received an error 3011 saying Access could not find the object
"strRecSource"

How would I make this export work exporting the Me.RecordsetClone
without using a Table or Query object but using the string created
here 'strRecSource' or the Recordset.

Note: this is a simple example and the actual SELECT is more complex
than just exporting the table. I just wanted to keep it simple here
in the example.

Are you wanting to do this export while the form is open, and is the
active object? If so, you could use the DoCmd.OutputTo method to export
it with its modified recordset:

strRecSource = "SELECT Table2.Account, " + strOne + _
" AS Field1 From Table2;"

Me.RecordSource = strRecSource

' ...

DoCmd.OutputTo acOutputForm, , acFormatXLS, "C:\Temp\MyOutput.xls"

However, I believe this form of export always uses an older Excel file
format, so if you need one of the later formats that can support a
greater number of rows, it won't work for you.

 

[Guest]

Dirk,

Thank you for your response. That is limited. The most I could get was
15,000 to output to the file; otherwise it gave me an error. Then I thought
maybe the acFormatTxt would work but it created a line above and below each
record. Is there not a way to do it to a csv file.

Also, do I need the form or is there some type of code that I could put on
the OnClick Event on a Command Button of my main form and it would recognize
to export that string strRecSource I had made. The only reason I was using a
form was to collect the records but I would prefer to not actually have to
open the form at all.

Thank you,

Steven

 

[Dirk Goldgar]

In

Dirk,

Thank you for your response. That is limited. The most I could get
was 15,000 to output to the file; otherwise it gave me an error.
Then I thought maybe the acFormatTxt would work but it created a line
above and below each record. Is there not a way to do it to a csv
file.

Also, do I need the form or is there some type of code that I could
put on the OnClick Event on a Command Button of my main form and it
would recognize to export that string strRecSource I had made. The
only reason I was using a form was to collect the records but I would
prefer to not actually have to open the form at all.

There are two approaches I can think of offhand:

1. You can open a recordset on the SQL statement and loop through the
recordset, using standard VB I/O statements to write out each record to
a text file in CSV format. Dimitri Furman has posted a text export
class module to handle this sort of thing for you:

http://www.mvps.org/access/modules/mdl0058.htm

I haven't tried it, but it looks very comprehensive, and would probably
serve your purpose quite well.

2. You can define a special QueryDef in your database, specifically for
the purpose of on-the-fly exports. Each time you want to export the
results of a dynamically built SQL statement, you'd just set the SQL
property of this querydef to the SQL statement, and then use
TransferSpreadsheet or TransferText to export the querydef.

 

[Guest]

Dirk,

Thank you for all your help on this. I think the best solution is Create a
temporary QueryDef and export and then delete the temporary query. The other
thing was very complex. I did learn a couple things. The DoCmd.OutputTo is
nice and probably would handle the job. I know at my current employer we
would not have more than 15,000 records to export at one time. But I wanted
to build something bigger and the QueryDef definitely handles that. I also
learned that apparently in a report that the send to "XL" Toolbar Button item
must be using the DoCmd.OutputTo because I thought I would be tricky and
process all this through a report and then send to excel but it gave me the
same error if there were too many records. In conclusion the QueryDef is the
best solution.

Thanks again for all your help.

Steven