Passing a SQL statement to a recordset 1

[zimmer9]

I created a query in Access using the QBE grid. Then I wanted to pass a string containing the SQL code into a Recordset. Therefore, I ran the query, went into SQL view and cut and paste the generated SQL code into a string variable named strFSSQL. The SQL code was a lengthly statement so I split it up into multiple lines using concatenation.

I was wondering if there was another way to run the query and get the resulting SQL code into a recordset that doesn't entail manually writing out the SQL code into a string variable which is then used by the recordset ?

Currently, anytime I modify my SQL code via the QBE grid, then obviously I will have to make corresponding changes to my string variable named strFSSQL.

I was just wondering if there was another way. If this the way to go, then that's fine.

The following is a snippet of the code I used:

Dim rstQueryFS As ADODB.Recordset
Dim objXL As Excel.Application
Dim objWS As Excel.Worksheet
Dim fld As ADODB.Field
Set rstQueryFS = New ADODB.Recordset

rstQueryFS.Open strFSSQL, CurrentProject.Connection

 

[RoyVidar]

If you are doing this from Access, and currentproject.connection, you can retrieve/open any unparameterized queris by providing the query name. Since you're using default cursortype, locktype, you could also use this syntax:

[tt]set rstQueryFS=CurrentProject.Connection.execute("queryname",,adcmdstoredproc)[/tt]

Roy-Vidar