Hi all,
I have created a union query which joins together multiple queries into one. It was working fine until I added more than 6 queries. Now I am getting an error message saying "Could not start transaction; too many transactions already nested." I looked this up and the description said that I could not nest a transaction more than 5 levels deep. This should only be a single level deep. I tried to create union queries (from only 6 tables each) and then joined those together and it worked fine but not if I try to join them using a single union query. Additionally, I looked up the maximum number of tables allowed and 6 is well below the limit of 32 (what I found). I was wondering, is this issue because one of the fields I have included is a multivalue lookup field?
Here's what my SQL code looks like.
SELECT [210-XXXX-XXX].[Product Code], [210-XXXX-XXX].DwgNo, [210-XXXX-XXX].Title, [210-XXXX-XXX].Rev
FROM [210-XXXX-XXX]
WHERE ((([210-XXXX-XXX].[Product Code])=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [270-XXXX-XXX].[Product Code], [270-XXXX-XXX].DwgNo, [270-XXXX-XXX].Title, [270-XXXX-XXX].Rev
FROM [270-XXXX-XXX]
WHERE ((([270-XXXX-XXX].[Product Code])=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [7400-XXXX].[Product Code].Value, [7400-XXXX].DwgNo, [7400-XXXX].Title, [7400-XXXX].Rev
FROM [7400-XXXX]
WHERE ((([7400-XXXX].[Product Code].Value)=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [950-XXXX].[Product Code].Value, [950-XXXX].DwgNo, [950-XXXX].Title, [950-XXXX].Rev
FROM [950-XXXX]
WHERE ((([950-XXXX].[Product Code].Value)=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [951-XXXX].[Product Code].Value, [951-XXXX].DwgNo, [951-XXXX].Title, [951-XXXX].Rev
FROM [951-XXXX]
WHERE ((([951-XXXX].[Product Code].Value)=[Forms]![Open PRF].[Product Code]))
UNION
SELECT [ALL PRODUCTS].[Product Code], [ALL PRODUCTS].[PRF Document], [ALL PRODUCTS].[PRF Description], [ALL PRODUCTS].[PRF Rev]
FROM [ALL PRODUCTS]
WHERE ((([ALL PRODUCTS].[Product Code])=[Forms]![Open PRF].[Product Code]))
UNION SELECT [ALL PRODUCTS].[Product Code], [ALL PRODUCTS].[ISR Document], [ALL PRODUCTS].[ISR Description], [ALL PRODUCTS].[ISR Rev]
FROM [ALL PRODUCTS]
WHERE ((([ALL PRODUCTS].[Product Code])=[Forms]![Open PRF].[Product Code]));
When it's written like this, it doesn't work right but if I delete any one query (or table) from the group, then it works fine. Is there a way to make a single union query without having to make the intermediate union queries? Appreciate any help that can be provided.
Cannot run Union query
