QueryDef.SQL vs. QueryDef.Parameters (1 Viewer)

[SyntaxSocialist]

Noob question, probably, but some cursory searches failed to deliver what I'm sure is a pretty simple answer.

QueryDef objects (http://msdn.microsoft.com/en-us/library/office/bb177500(v=office.12).aspx) have a couple properties I'm having a hard time understanding the difference between:

• Use the SQL property to set or return the query definition.
• Use the QueryDef object's Parameters collection to set or return query parameters.

What's the difference between a query definition and query parameters? I was under the impression that parameters are essentially the WHERE clause, and thus a part of the definition... but I'm starting to think I'm wrong on that one.

 

[mdlueck]

The SQL property is where the actual SQL query is placed.

The Parameters collection is where the values are stored to be plugged into the SQL where the parameter place holders are. I have an example of such here:

Example of using DAO.Parameters against Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=244345&page=3#post1246441

Oh no... that post got lost... :banghead: I apologize.

Anyway, I find it FAR EASIER to use Parameter query with ADO objects than DAO objects. Here are some example posts of that type of coding:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

One benefit of using Parameter queries is that the Parameter objects encapsulate the data to be sent to the database rather than the data having to be transported within the SQL query. This avoids challenges with properly sending quote characters within string data to be stored in the database table.

Granted Parameter queries might be more complicated up front, but in the long run they are more reliable.

 

[SyntaxSocialist]

Ok, so it looks to me like Parameters are beyond what I need. I think I just need the SQL property. Here's what I'm trying to do:

I've got a form (frmEdit) that allows users to (1) search tblMain, (2) view those results in a subform, and (3) view the current record in a set of bound controls.

I want to add code for a button to run a query (in table view) based on the user's search. Since every search will be a little bit different and there could be millions (edit: an infinite number) of combinations of search criteria, I'm thinking I want to use a temporary QueryDef object. So how do I make that work? What I've got so far looks like:

Private Sub btnRunQuery_Click()

    Dim mySQL As String
    Dim strSel As String
    Dim strWhere As String
        
    'Build SELECT Statement (strSel) based on user input (checkboxes) [DONE]

    'Build WHERE clause (strWhere) based on user input [DONE]

    'Build mySQL
    mySQL = "SELECT " & strSel & "FROM tblMain " & "WHERE " & strWhere

    'Run a query based on mySQL [GREAT MYSTERY OF LIFE]

End Sub

So really, I just need to figure out what do with this SQL in order to allow the user to run a query based on it with the click of a button.

(Should I just put this in a new post, do you think?)

 

[mdlueck]

Add this:

    'Build mySQL
    mySQL = "SELECT " & strSel & "FROM tblMain " & "WHERE " & strWhere
[B]Debug.Print mySQL[/B]

Press Ctrl-G to get the Immediate window open (in the VBA Editor window), run the code, then Copy/Paste what is outputted in the Immediate window.

 

[SyntaxSocialist]

Example:

SELECT [RecID],[StartDate],[EndDate],[LastName],[Sponsor],[Status],[Notes]
FROM tblMain
WHERE ([LastName] LIKE "Lee*" AND [StartDate] <= #5/1/2013# And [EndDate] >= #5/1/2010# _
And ([Status] = "Complete" OR [Status] = "Abandoned")[COLOR="Red"][B])[/B][/COLOR]

This is just one of an infinite number of possible values for mySQL, though... not sure what use it will be to you, since it's just a SELECT statement, FROM statement, and WHERE clause, like I said.

 

[mdlueck]

If that indeed was the output from variable mySQL, then I spot an unmatched ( with out closing ).

You have an Open ( after WHERE / AND, and only a Closing ) at the end of the line starting with AND.

That is an example of what I was hoping to see from your sending the SQL query.

 

[SyntaxSocialist]

Guess I missed the last ")" when I copied and pasted; it prints fine to the immediate window. It reads as it now does above.

 

[mdlueck]

Guess I missed the last ")" when I copied and pasted; it prints fine to the immediate window. It should read as it now does above.

Oh, I guess I was getting threads confused... I thought you were getting a SQL error.

So really, I just need to figure out what do with this SQL in order to allow the user to run a query based on it with the click of a button.

Looks like a good start.

You will need to read the form controls and dynamically build some of the SQL based on the states / values of the controls read.

Is that what you are asking how to do, or something else specific?

 

[SyntaxSocialist]

You will need to read the form controls and dynamically build some of the SQL based on the states / values of the controls read.

Is that what you are asking how to do, or something else specific?

I'm already dynamically building the SQL successfully. The SQL above was the result of that; I popped some criteria into my various controls and ran the btnRunQuery_Click procedure. All is working as it should.

What I want to do now is add code to the btnRunQuery_Click procedure that will run a query based on my dynamically constructed SQL.

 

[mdlueck]

What I want to do now is add code to the btnRunQuery_Click procedure that will run a query based on my dynamically constructed SQL.

I am fuzzy at what is the stimuli apart from pushing a button which would need to cause the query to be executed... however I think you are asking how to programmatically push the button, yes? If so, then I would suggest:

1) Take the button click event, and duplicate it just below the event.
2) Remove the "btn" specific part of the name which is specific to the button control name that event is associated with.
3) Also make this new event a Public event, not Private
4) In the button click event, change that to simply call the new custom event:
5) Now, to programmatically push the button, you have the syntax already! (In the button click event).

Example from my own code...

Private Sub btnPartEdit_Click()

  [B][COLOR=Blue]Call Me.PartEdit_Click[/COLOR][/B]

End Sub

[B][COLOR=Blue]Public[/COLOR][/B] Sub PartEdit_Click()

  'Worker code here...

End Sub

Now as a bonus, you have a Public event to "push the button" that even other Forms are able to push programmatically.

 

[SyntaxSocialist]

No, I think I've got a pretty good hold on that.

When I click the button (with my mouse) I want the btnRunQuery_Click event to do what it currently does (i.e.: dynamically build the mySQL variable), but then to also take that SQL and run/create a query based on it. My suspicion is that I'll want to use a temporary QueryDef object, since no two searches will be exactly alike. I just don't know how to construct this set of instructions.

 

[mdlueck]

My suspicion is that I'll want to use a temporary QueryDef object, since no two searches will be exactly alike. I just don't know how to construct this set of instructions.

You may refer to my post here for reference about creating DAO.QueryDef objects on-the-fly...

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

The .Execute event of DAO.QueryDef objects is what actually runs them. If you simply create a new one, put the SQL in it, .Close it, and do not delete it, then yes that QueryDef will remain in your database after the code finishes executing.

In that example, the inner QueryDef object configured for Pass-Through query mode is created / saved. The outer QueryDef is what actually gets executed, and the outer QueryDef is what refers to the inner QueryDef object. So what I am saying is it a bit more complicated than what you are asking for, but should be good reference.

 

[SyntaxSocialist]

Thanks very much, I'll see if I can get it to co-operate with those resources you've given me.

On somewhat of a tangent, are you able to explain to me the difference between a QueryDef Object and a DAO.QueryDef Object? I honestly just do not understand the whole "references" thing, and the more I learn, the more apparent it is that I should probably get to know that stuff.

 

[mdlueck]

DAO is the programming interface to interact with Access QueryDef objects.

A QueryDef object is the graphical object in the database, which a Query is defined in.

Via DAO, you can manipulate the QueryDef object.

 

[SyntaxSocialist]

Since mySQL will always be a SELECT query, won't I run into problems with the .Execute event, since SELECT queries can't be executed?

 

[mdlueck]

SELECT QueryDef objects may indeed be .Execute'ed! That is the act of running the SELECT via VBA code, and not having to "double click the QueryDef object in the Navigation Pane"

 

[SyntaxSocialist]

SELECT QueryDef objects may indeed be .Execute'ed! That is the act of running the SELECT via VBA code, and not having to "double click the QueryDef object in the Navigation Pane"

Hmm. I only ask because

Private Sub btnRunQuery_Click()

    Dim mySQL As String
    Dim strSel As String
    Dim strWhere As String
    Dim qdf As DAO.QueryDef
        
    'Build SELECT Statement (strSel) based on user input (checkboxes)

    'Build WHERE clause (strWhere) based on user input

    'Build mySQL
    mySQL = "SELECT " & strSel & "FROM tblMain " & "WHERE " & strWhere

    [B]'Run a query based on mySQL
    Set qdf = CurrentDb.CreateQueryDef("", qdefSQL)
    qdf.Execute[/B]

End Sub

returns run-time error 3065: "Cannot execute a select query."

 

[mdlueck]

Hmm. I only ask because

    [B]'Run a query based on mySQL
    Set qdf = CurrentDb.CreateQueryDef("", qdefSQL)
    qdf.Execute[/B]

End Sub

returns run-time error 3065: "Cannot execute a select query."

Why are you trying to create a nameless QueryDef?! Go back and review / compare with my working example I shared with you in #12.

 

[SyntaxSocialist]

Why are you trying to create a nameless QueryDef?! Go back and review / compare with my working example I shared with you in #12.

I was creating a nameless QueryDef because the MS help file indicates that that is the way to create a temporary QueryDef object, which I was under the impression I would want to use, since I don't want my database to grow and grow and grow as more and more QueryDef objects are saved to disk when btnRunQuery is clicked.

Your example is going pretty far over my head, but I'm working on trying to figure it out.

 

[mdlueck]

I was creating a nameless QueryDef because the MS help file indicates that that is the way to create a temporary QueryDef object, which I was under the impression I would want to use, since I don't want my database to grow and grow and grow as more and more QueryDef objects are saved to disk when btnRunQuery is clicked.

hhhhmmmm.... I think you are being a bit optimistic in your hopes for M$ technology... I have had to develop a database cleanup process to delete temp query objects which M$ does not clean up themselves:

VBA to Cleanup A2007 DB Extra Objects
http://www.access-programmers.co.uk/forums/showthread.php?t=226466

And on that topic, to reduce DB bloat, I found it necessary to decompile the database:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB

If you are running one query with variable search criteria, I would think you could always recycle one query name. And if you do not need to save it, all the more reason to write-over the last saved query.