QueryDefs
By Richard Rost
20 hours ago
Create Saved Microsoft Access Queries with VBA In this lesson, we will walk through creating a saved QueryDef in Microsoft Access using VBA. You will learn how CreateQueryDef saves SQL as a named query, why QueryDefs are useful when different users need different selected fields or query structures, and how QueryDefs differ from parameter queries that only change values. Marissa from Albuquerque, New Mexico (a Platinum Member) asks: Different people need different columns in my Access customer list. One person wants names and email addresses, while another wants names and phone numbers. I don't want to maintain a separate query for every possible combination. Can my database create the saved query someone needs when they make their selection? MembersIn the extended cut, we will learn how to create and replace saved queries with VBA, use temporary in-memory QueryDefs, and use parameterized queries. I will also show you how to display query results on a form, dynamically choose fields to display, and combine multiple fields into one using an SQL alias. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, DAO QueryDef, CreateQueryDef VBA, Access QueryDef, create saved query with VBA, dynamic SQL query, CurrentDb.CreateQueryDef, dynamic SELECT fields, VBA query builder, temporary QueryDef, parameter queries, replace saved query, SQL field picker
Subscribe to QueryDefs
Get notifications when this page is updated
More InformationTranscript Ever wish your Access database could build a query for you instead of making you create a separate saved query for every possible combination of fields?
Maybe different people need different versions of the same customer list.
Maybe some people want email address and other people want phone number and other people want cousin's sister's brother's former roommate's name.
Well, welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to talk about QueryDefs in Microsoft Access. A QueryDef lets your code create and save a query definition right in your database.
We'll start with a simple saved customer query, and you'll see why this becomes useful when your program needs to decide what a query should contain.
Today's question comes from Marissa in Albuquerque, New Mexico, one of my Platinum members.
Marissa says different people need different columns in my Access customer list. One person wants names and email addresses, while another wants names and phone numbers. I don't want to maintain a separate query for every possible combination. Can my database create the saved query someone needs when they make their selection?
Yes, it can. Access can create and save a query definition from code, which is especially useful when the query needs to be built for the situation at hand.
Let's take a look at the basic idea.
So, a QueryDef is simply an Access object that represents a query definition. Get it? QueryDef.
In plain English, it lets VBA create a query for you.
This is useful when your database needs to build the query itself. Different users need different columns. Maybe your code needs to decide what conditions belong on a query. Instead of manually creating every possible query ahead of time, VBA can create the one it needs.
So we're going to start with the simplest possible version: creating a saved query named CustomerQ that selects records from our Customer table.
Now, before we get into code, let's quickly review what a QueryDef is from a developer's perspective.
A QueryDef is a DAO object that represents a query definition in Access. It contains the SQL statement and other properties associated with that query.
When you create a query in Access and save it, you're creating a QueryDef. We can do exactly the same thing programmatically using VBA code.
We can create new queries, modify their SQL, delete existing queries, and even save temporary QueryDefs that only exist in memory. We'll cover that in the extended cut.
A named QueryDef is saved in the database and can be used just like any other query that you create manually in Access.
An unnamed QueryDef is temporary and is useful when you want to execute SQL without saving another query object.
So that's what we're going to explore in this lesson: using VBA to work with query definitions dynamically instead of creating and maintaining them all by hand.
So here's the basic code we're going to use.
First, we'll declare q as a QueryDef, and then we'll set it equal to CurrentDb.CreateQueryDef. Give the query a name, and then specify whatever you want as the SQL for it. It's that simple.
Before we get into the code, though, this is a developer-level video. So if you've never done any VBA programming before, go watch this guy. It's about 20 minutes long. It'll teach you everything you need to know to get started.
And you should know some basic SQL. So go watch this video. These are both free videos. They're on my website. They're on my YouTube channel. Go watch them and then come on back.
All right. Here I am in my TechHelp free template. This is a free database. You can grab a copy from my website if you want to.
And in here, I'm going to hijack this Hello World button.
Design View. We're going to come right into here. Right-click, go to Build Event.
I'm going to resize this so it fits on my screen. There we go. Turn that off.
All right. So right here in the status Hello World button subroutine, we're going to get rid of that.
And we're going to:
Dim q As QueryDef
And then:
Set q = CurrentDb.CreateQueryDef("CustomerQ", _
"SELECT CustomerID, FirstName, LastName FROM CustomerT")
You could give a field list like CustomerID, FirstName, LastName, right from CustomerT. You could put a WHERE condition in there. You could put an ORDER BY condition in there. Whatever you want.
But I'm just going to close it up.
And then maybe, when we're done, give a nice little beep there so we know it's finished.
Save it. Debug, Compile.
Close it. Close it. Open it. And click it.
All right. Now I know it worked.
Now, this isn't going to refresh itself automatically. Yes, there's code to refresh it. We'll cover that in the extended cut. But for now, just click on one of these guys anywhere in here and hit F5. That will refresh it for you.
There you go. And there's your CustomerQ. Double-click. Open it up. And there it is.
You just created that query all in VBA code. Give yourself a round of applause. The crowd is going wild.
And it's just like any other query. You can right-click, go to Design View. There it is.
And that's what that is. That's how, basically, in a nutshell, all a QueryDef does. It creates a query for you.
But there's a lot more you can do with it too.
And speaking of, by now you're probably thinking, "Okay, Richard, that's nice. Why not just create the CustomerQ manually in Query Design View?"
And, of course, you certainly can. If you always need the exact same query, building it manually is perfectly fine.
And you can use parameter queries if you want the user to be able to select various things, like what state they want or what minimum order date they want.
But the reason to create a query with VBA is when the query structure itself needs to change.
For example, maybe one department wants FirstName, LastName, and EmailAddress. And another department wants FirstName, LastName, and PhoneNumber.
Those aren't just different values being searched for. Those are different fields being selected and put in the query.
VBA can build the SELECT list based on the user preferences, based on the choices that they make. You can give them little select boxes. Do you want this field? Do you want that field?
It can assemble one SQL statement that selects FirstName, LastName, and Email. Or another one that selects PhoneNumber. Or another one that puts them together in one field.
CreateQueryDef can save whichever query definition the application needs.
And our little two-line example is the foundation for that kind of automation. We're going to build a field picker in the extended cut.
And speaking of parameter queries, that point sometimes confuses a lot of people. So let's separate the two different jobs out.
A parameter query is for changing values. For example, you might have a saved query that asks for a starting date, an ending date, or a customer ID.
The query structure stays the same.
Watch this video for more information on building parameter queries. And that's where you can say, "Enter the state," and it pops up a little parameter value for you.
With a parameter query, the query structure stays the same. You're just applying different values each time it runs. And that's a perfectly valid technique. And it's often exactly what you want.
A QueryDef becomes useful when your code needs to change the definition of the query itself. Choosing which fields appear in the SELECT list, creating an optional WHERE condition, an ORDER BY, all that stuff. Or even building some other part of the SQL statement. Joining in different tables, and there's all kinds of things you can do.
Parameters supply values, but they don't normally supply field names or whole pieces of SQL.
All right. So, quick review.
A QueryDef is an Access object that represents a query definition that stores the SQL instructions for retrieving data. The data itself still lives in your tables.
To save a query from VBA, use CurrentDb.CreateQueryDef and give it two things: the query name and the SQL you want Access to save.
Now, one little gotcha: make sure that you specify a new name, because if CustomerQ already exists and you run the same QueryDef again, Access will complain. And I'll show you how to deal with that in the extended cut.
Access doesn't like duplicate objects any more than I like getting duplicate invoices or duplicate charges on my credit card.
Now, in the extended cut, we're going to take QueryDefs to the next level. I'm going to show you how to create and replace saved queries using VBA, work with temporary in-memory QueryDefs that don't clutter up your navigation pane, and use parameterized queries to make your SQL cleaner, easier to maintain, and more secure.
We'll also learn how to display those query results directly on a form and dynamically choose which fields to show, including combining multiple fields into one using an SQL alias.
Lots of great stuff coming up in the extended cut. So click that blue button right now to join.
Silver members and up get access to all of my extended cut videos, not just this one. There are hundreds of them. So join today.
But there you go. Now you know how to build and use a basic QueryDef, and that is going to be your TechHelp video for today.
I hope you learned something. Live long and prosper, my friends. I'll see you next time. Intro In this lesson, we will walk through creating a saved QueryDef in Microsoft Access using VBA. You will learn how CreateQueryDef saves SQL as a named query, why QueryDefs are useful when different users need different selected fields or query structures, and how QueryDefs differ from parameter queries that only change values. Quiz Q1. What is a QueryDef in Microsoft Access?
A. An object that represents a query definition
B. A table that stores query results
C. A form control that displays SQL
D. A report section used for filtering data
Q2. What does the CreateQueryDef method allow VBA code to do?
A. Create and save a query definition
B. Convert a query into a table
C. Export a query to Excel
D. Add fields directly to a table
Q3. Which statement creates a saved QueryDef named CustomerQ?
A. Set q = CurrentDb.CreateQueryDef("CustomerQ", "SELECT * FROM CustomerT")
B. Set q = CurrentDb.OpenQueryDef("CustomerQ", "SELECT * FROM CustomerT")
C. DoCmd.OpenQuery "CustomerQ"
D. CurrentDb.Execute "CREATE TABLE CustomerQ"
Q4. What are the two main pieces of information supplied to CreateQueryDef when creating a saved query?
A. A query name and SQL statement
B. A table name and form name
C. A report name and filter value
D. A field name and recordset type
Q5. When is creating a QueryDef with VBA especially useful?
A. When the structure of the query must change based on user choices
B. When users need to enter a single date value
C. When a table needs a new primary key
D. When a form needs to be printed
Q6. Which example represents a change to a query's structure?
A. Selecting EmailAddress for one user and PhoneNumber for another
B. Entering a different customer ID each time the query runs
C. Changing the value of a date parameter
D. Typing a different state abbreviation into a prompt
Q7. What is a parameter query primarily used for?
A. Supplying different values while the query structure stays the same
B. Selecting completely different fields for each user
C. Creating new tables from VBA
D. Renaming fields in a table
Q8. Which part of an SQL statement could VBA dynamically build to choose which columns appear in the results?
A. The SELECT list
B. The primary key
C. The table relationships window
D. The form header
Q9. What happens if code attempts to create a saved QueryDef using a name that already exists?
A. Access reports an error because duplicate object names are not allowed
B. Access automatically replaces the existing query
C. Access creates a second query with a number added to its name
D. Access merges both queries into one QueryDef
Q10. What is the difference between a named and an unnamed QueryDef?
A. A named QueryDef is saved in the database, while an unnamed QueryDef is temporary and exists only in memory
B. A named QueryDef can only select data, while an unnamed QueryDef can only update data
C. A named QueryDef must use parameters, while an unnamed QueryDef cannot use parameters
D. A named QueryDef is used for forms, while an unnamed QueryDef is used for reports
Q11. Where does the actual data returned by a QueryDef reside?
A. In the underlying tables
B. Inside the QueryDef object
C. In the Navigation Pane
D. In the VBA module
Q12. Besides selecting fields, which SQL features can be dynamically added when building a QueryDef?
A. WHERE conditions and ORDER BY clauses
B. Form buttons and report controls
C. Table primary keys and indexes only
D. Macros and ribbon commands
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary Today's video from Access Learning Zone explains how to use QueryDefs in Microsoft Access VBA to create and save queries programmatically.
A QueryDef is an Access object that represents a query definition. In simple terms, it allows VBA code to create a query for you. This can be very useful when your application needs to build a query based on the situation instead of relying on a large collection of manually created saved queries.
For example, different users may need different versions of the same customer list. One person may need customer names and email addresses, while another needs names and phone numbers. Someone else may need an entirely different collection of fields. Rather than maintaining a separate saved query for every possible combination, I can let VBA build the query that is needed at that moment.
When you create and save a query manually in Access, Access stores it as a QueryDef object. VBA can work with those same objects. I can create new saved queries, modify the SQL behind existing queries, delete query definitions, and create temporary queries that exist only while my code is running.
A named QueryDef is stored in the database. Once created, it appears in the Navigation Pane and can be opened and used like any query created in Query Design View. An unnamed QueryDef is temporary. It exists only in memory and is useful when I want to execute SQL without creating another saved object in the database.
To create a basic saved QueryDef, I first declare a variable as a QueryDef. Then I use the CurrentDb.CreateQueryDef method, providing two pieces of information: the name of the query to create and the SQL statement that the query should contain.
For this example, I create a query named CustomerQ. Its SQL statement selects CustomerID, FirstName, and LastName from the CustomerT table. Of course, the SQL could include any valid SQL components that I need, such as additional fields, criteria in a WHERE clause, sorting with ORDER BY, joins to other tables, calculated fields, and more.
After running the code, the CustomerQ query is saved in the database. It works just like a manually created query. I can open it in Datasheet View to see the records, or I can open it in Design View to examine its fields and settings.
If I always need the same exact query, there is nothing wrong with creating it manually in Query Design View. A standard saved query is often the best choice when the structure will never change.
Parameter queries are also useful when the structure of a query remains the same but the values change. For example, I might create a query that prompts the user for a start date, end date, state, customer ID, or other search value. The query still selects the same fields and uses the same tables. The only thing that changes is the criteria value entered by the user.
QueryDefs are different because they are useful when I need to change the actual definition or structure of the query. I may need to choose which fields appear in the SELECT list, add or remove a WHERE condition, change the ORDER BY clause, alter joins, or create calculated fields. Parameters are generally used for supplying values. They are not normally used for dynamically supplying field names or entire sections of SQL.
For example, one department may want FirstName, LastName, and EmailAddress, while another may want FirstName, LastName, and PhoneNumber. Those are not simply different criteria values. They are different query structures. VBA can build the appropriate SELECT list based on user preferences or options selected on a form, and then create a saved query definition containing exactly the fields that person needs.
There is one important consideration when creating saved queries with CreateQueryDef. The query name must be new. If a query named CustomerQ already exists and I try to create another one with that same name, Access will generate an error. In practical applications, I often need to check whether the query already exists and delete or replace it before creating the new version.
Also, in today's Extended Cut, we will create and replace saved queries with VBA, work with temporary in-memory QueryDefs that do not clutter the Navigation Pane, and use parameterized QueryDefs to make SQL cleaner, easier to maintain, and more secure.
I will also demonstrate how to display QueryDef results directly on a form, dynamically choose which fields are shown, and combine multiple fields into a single display field by using SQL aliases.
A QueryDef stores the SQL instructions that tell Access how to retrieve data. The actual records remain stored in your tables. By using CurrentDb.CreateQueryDef, I can create a saved query from VBA by specifying a query name and the SQL statement that Access should save.
This gives me a flexible way to let my database create the queries it needs instead of requiring me to design and maintain every possible variation manually.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends. Topic List QueryDef objects in Access VBA
Creating saved queries with CreateQueryDef
Writing SQL for a QueryDef
Creating a customer SELECT query in VBA
Viewing VBA-created queries in Access
When to use QueryDefs vs saved queries
QueryDefs vs parameter queries Article A QueryDef is a Microsoft Access object that represents a saved query definition. When you create and save a query manually in Query Design view, Access stores that query as a QueryDef. VBA can create the same type of saved query programmatically.
This is useful when your application needs to decide what a query should contain at runtime. For example, one user may need a customer list with names and email addresses, while another needs names and phone numbers. Rather than maintaining a separate saved query for every possible combination of fields, your database can build the appropriate query when the user makes a selection.
A QueryDef stores the SQL statement that tells Access how to retrieve data. The data remains in your tables. The QueryDef simply contains the instructions for selecting, filtering, sorting, joining, or otherwise working with that data.
To create a saved QueryDef in VBA, your code should create a QueryDef through the current database, provide it with a name, and supply the SQL statement it should use. For a simple customer query, the SQL might instruct Access to select the customer ID, first name, and last name from a customer table. Once the code runs, the saved query will appear in the Navigation Pane alongside queries that were created manually.
The saved query behaves like any other Access query. You can open it in Datasheet view to see the results, open it in Design view, use it as the record source for a form or report, or use it as the source for another query.
The main advantage is not that VBA can create a query once. You could always create a fixed query manually. The advantage is that VBA can change the structure of the query based on the situation.
For example, your code could let users choose which fields they want to include in a customer list. If one user selects name and email, the code can build a query that selects those fields. If another user selects name and phone number, the code can build a different query. The program can also add optional filters, such as limiting customers to a particular state, or add sorting, such as placing customers in last-name order.
A QueryDef is especially helpful when the actual SQL structure needs to change. This is different from a parameter query.
A parameter query is useful when the query structure stays the same but the values change. For example, a saved query might always return customers from a selected state, customers created after a selected date, or orders for a selected customer. The fields, tables, and general query structure do not change. Only the values supplied to the query are different each time it runs.
A QueryDef is more appropriate when code needs to change which fields appear in the SELECT list, whether a WHERE condition exists, how results are sorted, which tables are joined, or other structural parts of the SQL statement. Parameters are generally for values. QueryDefs are useful when the query definition itself must be built or changed.
When creating saved QueryDefs, be aware that Access will not allow two database objects with the same name. If the query already exists and your code attempts to create it again with the same name, Access will raise an error. Your code should therefore decide how to handle an existing query. It might delete the old query before creating the new one, or open the existing QueryDef and replace its SQL statement.
You can also create temporary QueryDefs that exist only while your code is running. These are useful when you need to execute a query but do not want to save a new object in the Navigation Pane. A temporary QueryDef can be used to retrieve records, run action queries, or supply data to a form without cluttering the database with many saved queries.
In practice, QueryDefs provide a flexible way to let your Access application build exactly the query it needs. They are useful for customizable lists, user-selected columns, optional search conditions, dynamic sorting, flexible reports, and other situations where creating every possible saved query manually would be impractical. Primary Topics DAO QueryDef objects, CurrentDb.CreateQueryDef, creating saved queries with VBA, dynamic SQL generation, saved versus temporary queries, QueryDefs versus parameter queries Secondary Topics SQL SELECT statements, dynamic field selection, optional WHERE clauses, ORDER BY clauses, query refresh in Navigation Pane, duplicate query-name error |
