You basically have it already! In Vba you can open the query def, edit it's sql (qdf.sql) and update what you need to. I have posted solutions for dynamic queries on this forum this past week. I don't have the code at home.
No, I know of no way to use a variable as a table name directly in a query since the whole point of creating the query is so it will parse. If it does not know the table name, it cannot parse and will give an error. My method involves creating a parsed query then altering it in vba.
I have to do some extensive calculations that consist of about 10 consequtive querries. Querry 7 can be one of 3 querries, each representing an option for the entire calculations. I would like to have parameter (in a table) control which option is being calculated. I can not calculate all Options and then filter the solution I need, because the time to calculate 1 option is about 10min - so doing them all would triple my calculation time.
Right now all I have to do is execute the last querry, and all 10 previous querries are executed as needed. The problem is to calculate an other option, right now I have to replace Querry 7 with the querry of the option I want.