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

[@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.

[@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.

[@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!

[@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