By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Parse a txt field to a runSQL command

P: 8
I have a form that enables the end-user to "build" a SQL query. They can select up to 11 fields, use multiple Select conditions, and 4 sort levels. As they build the query it is put together in a txt field. The next step was to run the SQL, which I do from a command button, the code is as follow.

dim qryOutput as string

qryOutput = me.txtQry.Value ( THE TEXT BOX on the FORM)

docmd.runSQL "qryOutput" (Have tried various forms of this, used brackets and or commas)

I get an error saying the runSQL expects "SELECT" or "INSERT", ETC...

How can I run the built SQL statement?

Thanks for your help!

PW
Aug 30 '07 #1
Share this Question
Share on Google+
3 Replies


JKing
Expert 100+
P: 1,206
The RunSQL command is only for action queries. Deletes, select into, insert into etc.

If you want to create queries on the fly I suggest doing the following.

Step 1: Create a query and save it. It doesn't matter what you include in it as long as you save it. Access doesn't allow you to save a blank query so we're are doing this to get around that. You can call it whatever you want but for this example we will call it qryMyQuery

Step 2: Through code declare a querydef object and change the SQL of the query to reflect your built SQL.

Step 3: Open the altered query.

Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.Database
  2. Dim qdf As DAO.QueryDef
  3. Dim strSQL As String
  4.  
  5. Set db = CurrentDb
  6. Set qdf = db.QueryDefs("qryMyQuery")
  7.  
  8. strSQL = "SELECT * FROM yourTable"
  9. qdf.SQL = strSQL
  10.  
  11. DoCmd.OpenQuery("qryMyQuery")
  12.  
  13. Set qdf = nothing
  14. Set db = nothing
  15.  
Aug 31 '07 #2

P: 8
Still not able to take the data in the txt field and have it run as an SQL statement.

Below is the data in the text field txtqry on the form "test"
Expand|Select|Wrap|Line Numbers
  1. SELECT [ContactFirstName], [ContactLastName], [Delivery City] FROM Customers  WHERE [ContactLastName] > "D" ORDER BY [Delivery City], [ContactLastName]
  2.  
Thanks to your help I have the following code.

The "qrytest1" saved select query outputs zip code only....
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim qdf As DAO.QueryDef
  3.     Dim strSQL As String
  4.     Set db = CurrentDb
  5.     Set qdf = db.QueryDefs("qrytest1")
  6.     strSQL = Me.txtQry.Value                 (Have also tried forms!test.txtqry)
  7.     DoCmd.OpenQuery ("qrytest1")
  8.  
When this run I get the zip code only output, not what the built select query is.

The intent is that the qrytest1 will run select statement from the txtqry text box.

Again, thank for the help
PW
Aug 31 '07 #3

JKing
Expert 100+
P: 1,206
You missed a key line in my previous post.

You're missing this line:
Expand|Select|Wrap|Line Numbers
  1. qdf.SQL = strSQL
  2.  
Aug 31 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.