convert SQL string to new querry

[Jey]

I have a form that asks for user input (a couple of combo boxes and check
boxes). When they hit 'OK' it generates a SQL statement, which I then want
to be able to do two things with:
1, run and export to excel (I got this to work!), and
2, open as a new querry in access so the user can fine-tune the querry (and
then save and/or export).
How can I do the latter? Assume I have a sql statement stored as a string
ie.
Dim strSQL as string
strSQL = "SELECT blah blah blah"
'now open strSQL as a querry in the current database

Any help would be greatly appreciated! Thanks!

 

[MichaelRay via AccessMonster.com]

You can create a querydef and define it's sql string. From Access help:

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Employee List")
qryTest.SQL = "SELECT * FROM Employees;"

 

[Jey]

Thanks, that got me going in the right direction. I just had to modify it a
bit (used the CreateQueryDef method) to get it to work:

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDbqryTest.SQL = "SELECT * FROM Employees;"

Jey

 

[Klatuu]

That would be correct when there is no stored query with the name you are
using. If the query already exists, you would use the
dbsCurrent.QueryDefs("Employee List")
method.