convert SQL string to new querry

  • Thread starter Thread starter Jey
  • Start date Start date
J

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!
 
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;"
 
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
 
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.
 
Back
Top