Solved QueryDefs (1 Viewer)

[HealthyB1]

G'day, I am coming across references to using querydefs in lots of answers to questions in the "Last week's most popular topics" weekly email. I seem to have missed querydefs in my early days of building a database with a about 120mb front end and also a large back end database. I only use Access and not SQL linked back ends like sql server.
Is there somewhere where I can go to understand what is a querydef, what are their benefits, how would I use one and how would I construct one of more of them? Thanks in advance.

 

[Gasman]

Querydefs are just saved queries?
You create a query and save it. That is it AFAIK. ?

 

[NauticalGent]

Querydefs are just saved queries?
You create a query and save it. That is it AFAIK. ?

That is my understanding as well...

 

[jdraw]

I agree generally with Gasman. However, if you search the forum with

Querydefs and member Markk -- you will see various usages

 

[sonic8]

A QueryDef is a DAO object representing the structural/design definition of a query.
It may or may not be saved.
One common use case, amongst others, is executing a query from VBA when you need to control more properties than just the SQL statement of the query. For this use case the QueryDef is very often created in memory and not saved.

 

[MajP]

For all practical purposes that definition works, but if being technical I do not think it is correct.

AFAIK that the term querydef only resides in the database object model Data Access Objects (DA0). ADO does not have a querydef and you must use the Catalog object. So in fact you can reference a stored query through ADO, but it is not a querydef.

So unless referencing DAO, I do not think the stored queries that you see in the the Navigation window are by themselves querydefs. Also not all querydefs are in fact "stored."

You can also create temporary QueryDef objects. Unlike permanent QueryDef objects, temporary QueryDef objects are not saved to disk or appended to the QueryDefs collection. Temporary QueryDef objects are useful for queries that you must run repeatedly during run time but do not not need to save to disk, particularly if you create their SQL statements during run time.

I guess a temporary querydef is "stored", but only in memory.

As part of the DAO object model the QueryDef contains a lot of information beyond just the SQL statement

Like

Description
CacheSize	Sets or returns the number of records retrieved from an ODBC data source that will be cached locally. Read/write Long.
Connect	Sets or returns a value that provides information about the source of database used in a pass-through query. Read-only String.
DateCreated	Returns the date and time that an object was created (Microsoft Access workspaces only). Read-only Variant.
Fields	Returns a Fields collection that represents all stored Field objects for the specified object. Read-only.
LastUpdated	Returns the date and time of the most recent change made to an object. Read-only Variant.
MaxRecords	Sets or returns the maximum number of records to return from a query against an ODBC data source.
Name	Returns or sets the name of the specified object. Read/write String.
ODBCTimeout	Indicates the number of seconds to wait before a timeout error occurs when a QueryDef is executed on an ODBC database.
Parameters	Returns a Parameters collection that contains all of the Parameter objects of the specified QueryDef. Read-only.
Prepare	NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine. Sets or returns a value that indicates whether the query should be prepared on the server as a temporary stored procedure, using the ODBC SQLPrepare API function, prior to execution, or just executed using the ODBC SQLExecDirect API function (ODBCDirect workspaces only). Read/Write QueryDefStateEnum.
Properties	Returns the Properties collection of the specified object. Read-only.
RecordsAffected	Returns the number of records affected by the most recently invoked Execute method.
ReturnsRecords	Sets or returns a value that indicates whether an SQL pass-through query to an external database returns records (Microsoft Access workspaces only).
SQL	Sets or returns the SQL statement that defines the query executed by a QueryDef object.
StillExecuting	NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine. Indicates whether or not an asynchronous operation (that is, a method called with the dbRunAsync option) has finished executing (ODBCDirect workspaces only).
Type	Sets or returns a value that indicates the operational type or data type of an object. Read-onlyInteger.
Updatable	Returns a value that indicates whether you can change a DAO object. Read-only Boolean.

There are even more properties that have to be referred from the properties collection.

A queryDef also has methods

Close	Closes an open QueryDef.
CreateProperty	Creates a new user-defined Property object (Microsoft Access workspaces only).
Execute	Executes an SQL statement on the specified object.
OpenRecordset	Creates a new Recordset object and appends it to the Recordsets collection.

 

[Pat Hartman]

Some of the experts here will NEVER use querydefs because they are obsessed with pretty-printing their SQL strings and the Access QBE (Query By Example) messes up whatever they type. I on the other hand learned SQL back in the 80's and from my earliest experience, I used to dream of having a tool such as the QBE to build the SQL for me. I hated having to memorize all the table and column names from the many applications I supported. The task was made harder since many were not built by me so I couldn't rely on my "style" to help me out. It wasn't the formatting in code that was a problem, it was the time and typos. Back in those ancient days, we submitted our code to compile and had to wait for the results (could be hours) and then fix all the typos. Today's development environment is ever so much more productive. We have intellisense to point out lots of our typos The Access QBE is a pretty poor tool in the greater scheme of things but it is what we have unless you obtain a third party tool. It is drag and drop though so you are selecting objects rather than having to type their names so typos are rarely a problem.

The first time a saved querydef runs, Access creates an execution plan for the query and saves it. Computers are pretty fast so this is less important than it was in the past but not having to generate an execution plan every time a query runs as you have to do with embedded SQL, does save time. I C&R my FE's at least monthly to force the querydefs to "uncompile" so they will recreate a new execution plan so they will keep up with the current db stats because the stats do affect the execution plans and can alter the path chosen.

I have a good naming scheme and I reuse queries so I don't have to remember to find all instances of something and change it in a dozen procedures. I just change the querydef. Sometimes I switch to SQL view if I need to create complex where clauses but I try to not be offended by the poor text display. So, since I don't have to look at the mass of SQL text, I don't worry about it. I just use the visual view.

Having most queries as saved querydefs - obviously when the query is actually dynamic, you need to build the SQL with code - lets me build tools to analyze the queries so it is pretty easy to find all the queries that use tblA or use a particular field.

 

[The_Doc_Man]

I'm going to take a slightly different turn here. I'm going high-level functional. Access stores things in objects, some of which can be simple; others, however, can be more complex. In older versions of Access, there is a collection object called QueryDefs - which is a collection of individual QueryDef objects. In newer versions of Access, you look into the AllQueries collection, which serves a similar purpose.

A QueryDef is a placeholder object for the information that Access keeps about a query. A stored query must have a unique name, but a memory-only query merely needs to have an object name for the as-yet-unstored querydef object. The QD usually contains the SQL of the query but depending on the nature of the query (Action, Select, Union, etc.) may keep other information such as locking style, sensitivity to changes made by queries that share the same data, parameters used by the query, and many other features. The COM interface can return a list of fields used in the query and can identify the tables referenced by the query.

QueryDef object (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

But here is the raw truth: Since Access is not open-sourced, we don't know as much as we might like about what is actually kept in a querydef.

 

[HealthyB1]

The first time a saved querydef runs, Access creates an execution plan for the query and saves it.

Just wondering where are the execution plans saved exactly? Or it as simple as, once the query is run the first time it flags somewhere that the particular query has been run/executed before?
A supplementary question if you will. All the comments that may be included in a VBA module, procedure or function are purely there for documenting the purpose of the program module or procedure and the program flow for the programmers or others viewing the code. I assume once it is compiled the comments are stripped off as you would not want them using up memory space. So where are these compiled modules/ functions etc stored?.

 

[The_Doc_Man]

Just wondering where are the execution plans saved exactly? Or it as simple as, once the query is run the first time it flags somewhere that the particular query has been run/executed before?
A supplementary question if you will. All the comments that may be included in a VBA module, procedure or function are purely there for documenting the purpose of the program module or procedure and the program flow for the programmers or others viewing the code. I assume once it is compiled the comments are stripped off as you would not want them using up memory space. So where are these compiled modules/ functions etc stored?.

To your supplementary question:

If you have converted the .ACCDB or .MDB into an .ACCDE or .MDE (as appropriate for your Access version and data format) then all of the code and comments are stripped away. If you have not converted, they are still there. During development, you need to be able to compile and, if needed, make a change and recompile. If you stripped away all the text form of the code you could not perform the cycle of test-compile, test operation, identify bug, edit bug, test-compile, identify next bug.... etc. The full code is all in the module until you perform the .ACCDE/.MDE conversion. After that, the "stripped down" modules cannot be altered. Which is why you NEVER EVER get rid of that developer's version that has all those juicy code lines and comments.

There is, of course, one more part to the answer. Where are the code and comments kept? In the Front End, of course. A true Back End file has only tables with, at most, some table properties that might have some text as descriptives.

 

[isladogs]

Comments are indeed removed when files are compiled as MDE/ACCDE.
This is explained in the article about reverse engineering compiled projects at

MDE to MDB Conversion - Retrieve VBA code from your MDE/ADE Databases

Convert your MDE applications back to MDB form, including VBA source code.

www.everythingaccess.com

Execution plans are stored as part of the database file and cleared when the file is compacted

 

[HealthyB1]

To your supplementary question:

If you have converted the .ACCDB or .MDB into an .ACCDE or .MDE (as appropriate for your Access version and data format) then all of the code and comments are stripped away. If you have not converted, they are still there. During development, you need to be able to compile and, if needed, make a change and recompile. If you stripped away all the text form of the code you could not perform the cycle of test-compile, test operation, identify bug, edit bug, test-compile, identify next bug.... etc. The full code is all in the module until you perform the .ACCDE/.MDE conversion. After that, the "stripped down" modules cannot be altered. Which is why you NEVER EVER get rid of that developer's version that has all those juicy code lines and comments.

There is, of course, one more part to the answer. Where are the code and comments kept? In the Front End, of course. A true Back End file has only tables with, at most, some table properties that might have some text as descriptives.

Thanks for the explanation Doc Man. I tried to do a compile on the F/E but it is too big. Also the documenter will not work.
No matter it will give me a task to do tonight.

 

[sonic8]

In older versions of Access, there is a collection object called QueryDefs - which is a collection of individual QueryDef objects. In newer versions of Access, you look into the AllQueries collection, which serves a similar purpose.

I propose a revision to the above statement:

In the DAO library, there is a collection object called QueryDefs - which is a collection of individual QueryDef objects. In the Microsoft Access object library, there is the AllQueries collection, which is a collection of AccessObjects providing high-level information on the query but not the actual query definition.

 

[HealthyB1]

I propose a revision to the above statement:

In the DAO library, there is a collection object called QueryDefs - which is a collection of individual QueryDef objects. In the Microsoft Access object library, there is the AllQueries collection, which is a collection of AccessObjects providing high-level information on the query but not the actual query definition.

Thanks Sonic8. Ques:- How do I access the DAO library?

 

[sonic8]

How do I access the DAO library?

The DAO library ("Microsoft Office xx.x Access database engine Object Library") is usually linked as a reference into an Access project automatically.
The CurrentDb function of the Access.Application object will return a DAO.Database object, which is the root object to access any other DAO objects within your database.

 

[NauticalGent]

All good points and explanations of course, but I am a simple man and Querydefs and other Access Objects to me are like posi-traction: No one knows HOW they work, they just do. When someone asks me to explain it, I tell them PFM (Pure F@%!ing Magic)

Edit: For the European, especially German members here, substitute "posi-traction" with "vorsprung durch technik" and that might make it more clear!

 

[MajP]

Access is not open-sourced, we don't know as much as we might like about what is actually kept in a querydef.

I do not understand this statement. The QueryDef is part of the DAO object model which is well documented. We know every property and method. The only thing you might not easily know is those value properties that you have to reference through the properties collection. Some of these are created through the properties window. But if you loop those.

Name
DateCreated
LastUpdated
Type
SQL
Updatable
Connect
ReturnsRecords
ODBCTimeout
RecordsAffected
MaxRecords
StillExecuting
CacheSize
Prepare
RecordLocks
RecordsetType
OrderByOn
Orientation
DefaultView
GUID
FilterOnLoad
OrderByOnLoad
TotalsRow
PublishToWeb
DisplayViewsOnSharePointSite
HideNewField
BackTint
BackShade
ThemeFontIndex
AlternateBackThemeColorIndex
AlternateBackTint
AlternateBackShade
ReadOnlyWhenDisconnected
DatasheetGridlinesThemeColorIndex
DatasheetForeThemeColorIndex
RowHeight

 

[The_Doc_Man]

I propose a revision to the above statement:

In the DAO library, there is a collection object called QueryDefs - which is a collection of individual QueryDef objects. In the Microsoft Access object library, there is the AllQueries collection, which is a collection of AccessObjects providing high-level information on the query but not the actual query definition.

In Access 97, which IS an older version of Access, what I said was exactly true. In fact, there were six collection items on the opening screen - Tables, Queries, Forms, Reports, Macros, and Modules. Each of those was the name of a collection item that held the xxxDef items or other items according to the names used for them at the time. As I pointed out and as others have pointed out, some of that has changed as Office expands to share things that it didn't always share quite as well.

I do not understand this statement.

Simply that since we cannot see the actual code because Access is not open-sourced like, say, UNIX, we don't know the exact layout of the data structures internally. Yes, they document various fields - but the COM methodology hides details of things like descriptors and where they are used. But those descriptors exist. In older documentation before 2003, I saw them. We don't know how records tie together in a recordset as we navigate. We can surmise a doubly-linked queue-like list - but it is a surmise, not a certainty.

I agree that we have a lot of documentation regarding contents. But COM is like an ultimate Class object where you don't actually touch the data elements - you do Property Get, Property Let, and Property Set calls via COM on the underlying objects.

 

[sonic8]

In Access 97, which IS an older version of Access, what I said was exactly true.

In Access 97 the QueryDefs collection was part of the DAO library exactly as it is today.
The DAO library also contains the Containers/Documents collections which provides more high-level access to the objects in the database, similar to the AllQueries (AllForms, AllReports, etc.) collections. This was as true for Access 97 as it is for the most recent version.
The AllQueries (etc.) collections were added in Access 2000 (literally in the the Access object library!) to provide an alternative to Containers/Documents for ADP projects, which do not (and cannot) use DAO.
None of this has changed since.

 

[Pat Hartman]

Just wondering where are the execution plans saved exactly?

Someplace safe no doubt where we can't get our hands on it Joking aside, it is stored as part of the object model. I don't know of any way to just look at it as you are browsing through the querydefs object. But, you can use the Show Plans feature to display it when a query is executed. I know there's a couple of threads here on using Show Plans. It's a bit of a PITA in that you've got to turn it on and off using Windows settings.