DoCmd.RunSQL/Currentdb.Execute

[Todd Waldron]

Hello,

I am trying to figure out a way to run a SELECT query SQL
statement in VBA. I keep getting errors when I
use "DoCmd.RunSQL" or "Currentdb.Execute" because my SQL
statement is a SELECT query instead of an Action query.
It is my understanding from all I have read that I can
only use these two methods with Action queries.

What I want to do is create a dynaset to export data
(TransferText/ExportDelim) and then close the dynaset,
all without having to have a physical query saved in the
query object.

I am trying to keep all the components within the sub
procedure of the "cmdExport_click" event instead of
having to call to saved queries and macros elsewhere in
the DB.

Even if this seems kinda stupid is it possible???

Thank you in advance...

Todd

 

[Joe Fallon]

It is mostly possible.
What is wrong with saving the query?
In your code you can build it, save it, use TransferText against it, and
then delete it.

RunSQL is for action queries.

Build a query and then save it.
In code build your SQL and then try:
db.QueryDefs("MySavedQueryName").SQL = strSQL
This changes the saved query on the fly.

Then you can also do things like:
intRecs = DLookup("Field1", "MySavedQueryName")

Set rs = db.OpenRecordset("MySavedQueryName")
etc.

 

[Todd Waldron]

Hi Joe,

Thank you for your response. Sorry, but I guess I don't
understand what you mean when you suggested: "In code
build your SQL". Can you expand on that a little more?

Thank you,

Todd