Docmd.RunSQL with a select query

[merry_fay]

Hi,

I'm trying to use code to open a select query when a button is clicked.
I'm running access 2000 & so as the select query has several nested tables, saving a pre-written query means it won't open due to 'syntax errors' (where it keeps changing ( to [ for the nested stuff). This means I would have to create several different queries to run it, or just write the query in code.
My problem is, I can't get the query results to open up using a select query written in code!

I've tried this:
Dim strSQLsel As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

strSQLsel = "sql code"
rs.Open strSQLsel, CurrentProject.Connection

But nothing opens. What am I doing wrong or is it not possible?

Thanks

 

[pbaldy]

Opening a recordset in code is not meant to be visible to the user (and will not be visible). It is used to operate on data in the background. You might grab a value from the recordset, update a value, etc. If you're trying to make the result visible to the user, one option would be to set the source of a form/subform to your SQL.

 

[datAdrenaline]

>> it won't open due to 'syntax errors' (where it keeps changing ( to [ for the nested stuff) <<

If the Query objects SQL does not change, you can write the SQL Statement with the nested stuff in parenthesis ...

So this (statement A):

SELECT * FROM [SELECT * FROM someTable]. AS vTable

Would be written as this (statement B):

SELECT * FROM (SELECT * FROM someTable) AS vTable

----

Access will save the Query object and display the resultant rowset of the without issue ... BUT ... when you Open the Query object in design view again, you will see the bracketing (statement A) ... BUT ... if you DON'T change ANYTHING you can still run it because Access saves the "compiled/optimized" version of the SQL Statement (created from statement B), which does not have the notorious nested brackets!

------

So ... if you are willing to build the SQL statement via code, then you can still have a stored query object in your db --- just overwrite the SQL property of the Query object anytime you want to ensure the SQL Statement that is ran by the db engine is correct (ie: no nested brackets). The core line of code that will do that is:

CurrentDb.QueryDefs("nameOfYourQueryObject").SQL = "SELECT ...<blah>...<blah>..."

{the SELECT would use the parenthesis syntax as shown in statement B}

----
Hope that helps!