How to Create and Modify QueryDefs Programmatically in your MS Access Database

In this episode, we'll look at how to programmatically create and modify query definitions in Microsoft Access. Being able to create and (especially) modify QueryDefs in MS Access is a nice trick that makes the coding of your application easier, and more flexible. Sometimes it is just easier and faster to change a QueryDef on the fly in your code, so that your users have a better experience in your app.
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#msaccess #vba

[@zoomingby]

FYI, any created querydef object (with a non-zero length name) is automatically appended to the querydefs collection.

[@seanmackenziedataengineering]

Thanks for sharing!

[@zoomingby]

@@seanmackenziedataengineering Quite welcome :) Lol, 1 down 1000 bits of information to make it an even exchange. Thanks for all you do. You've helped me immeasurably over the years.

[@DataisKing]

I use QueryDefs to run SQL statements so that my database users can export from Excel by filtering dropdowns on forms

[@seanmackenziedataengineering]

Cool technique!

[@RungeCarl]

Anyway you could post a sample ACCDB file to demonstrate this?

[@seanmackenziedataengineering]

@@RungeCarl Now posted on mackenziemackenzie.com/downloads

[@mahmoudbarrawi7092]

excellent Sir

[@seanmackenziedataengineering]

Many many thanks!

[@mahmoudbarrawi7092]

When I watch the program, I feel that you are capable because your style is easy and abstaining, and I learned a lot from you, and I follow all your series, and finally to you from God all the reward and from those who watch all the appreciation, admiration and gratitude

[@seanmackenziedataengineering]

@@mahmoudbarrawi7092 Very much appreciated. I'm glad you find it useful!

[@vladoz9299]

Do you have a video where the table name changes in a query definition? Say, the same query is used each year, but the table name contains the current year in its name.

[@seanmackenziedataengineering]

Good question - this is very possible and in fact, I use this strategy in some situations. You can simply change the entire SQL for the query to use your new table using the methods in this video!

[@vladoz9299]

@@seanmackenziedataengineering Yes but then I need to do string manipulation and not use the DAO engine to do it auto. more simply? We have many queries that generate a custom built tables row by row. Each of the queries need the new table name each year.

[@seanmackenziedataengineering]

@@vladoz9299 When you set the SQL for a querydef, it is a DAO querydef, so you are using DAO for the query. Maybe as an alternative, you could just generate a new querydef each time, but this will create many objects. If the data is uniform, you could try putting it all into one big table with an extra column for "query name" or something like that. Then, just select the query/report name from a drop-down and it will filter using a single query.