Automating editting a query

[fbs]

I have a query that is complex, that changes year to year and it requires editting the query and re-adding the tables and expressions, Is there a way that I can get a macro to do a find and replace of the table names and fields in the query's sql view? Or
do you have any suggestions how I might automate this process

 

[boblarson]

Actually, about the only way I see as your option, is to create a dynamic SQL query that is created and run from a Visual Basic module.

If you do that, you can at least create variables that can change depending on options you choose on a form or by some other input method.

I know that's probably not the answer you were looking for (I used to be a Macro only person), but to add a whole lot more functionality and flexibility you need to use VB.

Good luck.

BL
hth

 

[fbs]

Thanks for the info, If I could do a FIND and Replace in the SQL view of the query to find the old table name and replace with the new table name that would work out great, but I'm guessing that is not possible? I'm not sure how to accomplish what you suggested?

 

[Pat Hartman]

Seems like the real problem is that your table changes names every year. Have you ever considered changing your design so you don't need to do this?

[This message has been edited by Pat Hartman (edited 04-15-2002).]

 

[fbs]

You are absolutely right! Unfortunately this structure has been handed down to me

my concern with restructuring the table design is that it may have an impact on the active server page front end.