What is a QueryDef and hows it used

[Sahil123]

Hi

I cant seem to find a proper good explanation of what it is and how/why its used

is it a stored procedure/vba function?

Any code examples to help understand what its doing would really be appreciated

 

[Joe4]

I use it in Access when I build SQL code in VBA and want to apply it to an existing Access Query. This is great if you have a Report or Form that you want to base on a Query that is ever changing.
I will often create a Form where the user can select the parameters/conditions they want. I then build the SQL code off of that, and then apply it to the Query that my Form or Report runs off of.
Here is an example: http://www.mrexcel.com/forum/micros...c-applications-sql-statement.html#post3099809

 

[james_lankford]

you know all those queries you write in Access using the query designer ?

think of those as querydefs

so if you have a query named as "list_employees"

then you can refer to that query in VBA by using a querydef variable

your variable won't be a type of int or string or variant
it will be a type of querydef

you can even create queries using code in VBA and assign them to a querydef variable

Modifying a QueryDef - MS-Access Tutorial

Creating Queries using QueryDef | Database Solutions for Microsoft Access | databasedev.co.uk

https://msdn.microsoft.com/en-us/library/office/ff194892(v=office.14).aspx

 

[welshgasman]

Does this help?

Private Sub cmdApply_Click()
On Error GoTo ErrHandler
Dim db As Database
Dim qdf As QueryDef
Dim strQryAdd As String, strQryUpdate As String


strQryAdd = "qryASharedCommission"
strQryUpdate = "qryUSharedCommission"


SetStatusBar ("Adding Commissions ....")
Set db = CurrentDb()


Set qdf = db.QueryDefs(strQryAdd)
qdf.Execute


SetStatusBar ("Setting processed date for commission shared")
Set qdf = db.QueryDefs(strQryUpdate)
qdf.Execute

 

[Norie]

Query(Def)inition?

 

[Sahil123]

Thank you

Is that not the same as stored procedure then or is there a difference?

 

[xenou]

It is similar. In general access users don't have any command "CREATE STORED PROCEDURE" but you can do the same things that stored procedures do (ignoring the fact for now that Access can work with other databases such as SQL server that do have features such as stored procedures).

 

[Sahil123]

Thank you all

 

[Micron]

A querydef (query definition, as it was correctly pointed out) is an object. If the sql that defines the query is named and saved, that means it happens to be a query in the queries collection, where it can be seen in the navigation pane. However, a querydef is not the same as a procedure. When working with the stored query in code you might simply refer to it by name, but there are times when you cannot call a query this way (perhaps because it requires parameters). You'd need to create a query definition object (often named qdef) based on the query, so that you can pass the parameters (created as objects) to it.

qdefs are also created on the fly. You can assign a sql statement that you construct in code to a qdef object and run that. The advantage might be that this doesn't expose a query in the collection to prying eyes. I little known trick (at least I think so) is that if you save a query def without a name, it is temporarily saved in the queries collection and can be worked with as you would a stored query. Another example of a powerful use for a qdef can be read about here, where 40 or so queries had to be modified: Change Table name with Find/Replace