convert SQL string to new querry

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!
 
M

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;"
 
J

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
 
K

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating TXT files based upon a Querry 2
DLookup 8
Querry link to Report 4
can't find input table or query... 1
review sql querry 2
Excel Export Filtered Form Data To Excel 0
msgboxYESNo 1
faster export to excel? 7

Top