Renaming table names does NOT update UNION query (table reference)

[skydivetom]

Hello Experts:

I need some assistance with *tweaking* a UNION query so that a change of table name will be reflected in the UNION query.

Background:
- In this example, I have two (2) tables named [complete_LK_IMPACT] and [complete_LK_INJURY_TYPE]
- Also, I have 2 individual/separate queries as shown below:

SELECT "LK_IMPACT" AS LK_IMPACT, complete_LK_IMPACT.TARGET_TABLE, complete_LK_IMPACT.TARGET_FIELD
FROM complete_LK_IMPACT
GROUP BY "LK_IMPACT", complete_LK_IMPACT.TARGET_TABLE, complete_LK_IMPACT.TARGET_FIELD;

SELECT "INJURY_TYPE" AS INJURY_TYPE, complete_LK_INJURY_TYPE.TARGET_TABLE, complete_LK_INJURY_TYPE.TARGET_FIELD
FROM complete_LK_INJURY_TYPE
GROUP BY "INJURY_TYPE", complete_LK_INJURY_TYPE.TARGET_TABLE, complete_LK_INJURY_TYPE.TARGET_FIELD;

- Next, I also have a UNION query. The SQL is shown below:

SELECT "LK_IMPACT" AS LK_IMPACT, complete_LK_IMPACT.TARGET_TABLE, complete_LK_IMPACT.TARGET_FIELD
FROM complete_LK_IMPACT
GROUP BY "LK_IMPACT", complete_LK_IMPACT.TARGET_TABLE, complete_LK_IMPACT.TARGET_FIELD;

UNION 

SELECT "INJURY_TYPE" AS INJURY_TYPE, complete_LK_INJURY_TYPE.TARGET_TABLE, complete_LK_INJURY_TYPE.TARGET_FIELD
FROM complete_LK_INJURY_TYPE
GROUP BY "INJURY_TYPE", complete_LK_INJURY_TYPE.TARGET_TABLE, complete_LK_INJURY_TYPE.TARGET_FIELD;

Current problem:
- When I change the table names from [complete_LK_IMPACT] AND [complete_LK_INJURY_TYPE] to [LK_IMPACT] AND [LK_INJURY_TYPE], the 2 individual queries are automatically updated and continue to execute.
- However, the UNION query does not reflect the new table name and does NOT execute any longer.

My question:
- How should I modify the UNION query so that any renaming of the table names will also be updated in the UNION query?

[Gicu]

Hi Tom,

Not sure if you already figured it out as the post is marked as Solved but you can use the SQL property of the QueryDef object to edit it:

Dim sSQL as string, qdf as DAO.QueryDef
Set qdf=CurrentDb.QueryDefs("qryYourUnionQuery")
sSQL =qdf.SQL 'retrieve the existing SQL
sSQL=Replace(sSQL,"complete_","") 'update the SQL
qdf.SQL=sSQL 'save it to the query
Set qdf=Nothing

Cheers,

[skydivetom]

Vlad -- wow... that worked perfectly!! I added to code to my renaming function and voila... all is working incl. the UNION query. Sweet!

[Gicu]

Glad to hear Tom!

[Welshgasman]

Wouldn't

Select * from Query1
Union
Select * from Query2

worked just as well?

[skydivetom]

No, it would not.