Access Export Union Query Error (The query cannot be completed)

[RosscoP]

I have a database that is used to create a data file for a customer. The database has two linked tables that are linked to tables that are both in another database (but within the same database as each other). Their structure is identical.

I have a union query set up to combine both linked tables.

I am using a Macro to export that query but after running for a short while I get the error "The query cannot be completed. Either the size of the query set....."

Does Access have a limitation on union query size? Combined, the tables are a lot of data but I'm confused as both the tables I'm combining are in the same database.

 

[vbaInet]

Not sure about the limitations in this context but I'm sure Access can churn millions of data if the tables are written with correct indexes, primary/foreign keys and the SQL statement is optimised. A UNION query in Access isn't the fastest bob out there.

Try creating the UNION query/view in the database that houses these tables, link to this query and pull the data from that UNION query.

 

[RosscoP]

That was my thinking. The database I am linking to is under 1GB so it seems odd that I'd hit a limit but I think it might be memory related?

I'll try your suggestion and see how it goes.

Thanks!

 

[RosscoP]

Not sure about the limitations in this context but I'm sure Access can churn millions of data if the tables are written with correct indexes, primary/foreign keys and the SQL statement is optimised. A UNION query in Access isn't the fastest bob out there.

Try creating the UNION query/view in the database that houses these tables, link to this query and pull the data from that UNION query.

I tried with the linked query but I got the same error.

 

[MSAccessRookie]

Would it be possible for you to display the SQL Code here? Perhaps seeing the code will allow for different observations or suggestions

 

[vbaInet]

And also try putting a LIMIT on how many records are returned using the TOP predicate.

 

[GinaWhipp]

How many rows does the query return? I would also try a Compact & Repair and then try the query again.

 

[RosscoP]

Hi All,

Thanks for the responses. The query is rather simple:

select * from Table1
union
select * from Table2

The query will present something like 2.4 million rows.

I did a compact and repair but typically the database is no more than 600KB as both tables are linked.

 

[RosscoP]

And also try putting a LIMIT on how many records are returned using the TOP predicate.

Is this to ensure that the issue is down to volume rather than an issue with the query itself?

 

[vbaInet]

Is this to ensure that the issue is down to volume rather than an issue with the query itself?

Spot on! Give that a try. Set a limit on both tables before joining.

2.4 million rows * X no. of fields * contiguous fields

Not knowing the number of fields you have and if typically most of the fields contain data, it's a lot of data to pull in.

 

[RosscoP]

I'm sure it's not the same but bear in mind both the tables I'm combining are in the same database that is about 998MB in size.

I'll give the limit a go.

 

[vbaInet]

Are these tables one of the biggest tables in the DBs by volume?

 

[RosscoP]

Spot on! Give that a try. Set a limit on both tables before joining.

How do I limit a linked table? Or do I need to create two select queries which I will then apply the union to?

 

[vbaInet]

How do I limit a linked table? Or do I need to create two select queries which I will then apply the union to?

I don't know your db setup so try do in the db itself and link to the view or like you described. However, I would imagine that with the latter Access would have pulled in all the data from both tables first before limiting it. Just try both suggestions.

 

[spikepl]

Try
Union All

(if you have distinct values anyway)

 

[RosscoP]

Try
Union All

(if you have distinct values anyway)

This has done the trick! Can you advise the difference between the use of 'ALL'?

 

[MSAccessRookie]

The main difference between UNION and UNION ALL is that UNION purges duplicate items from the resulting dataaset. Perhaps the need to purge was somehow related to your problem. Good to see you are up and running.

-- Rookie

 

[RosscoP]

Thanks it's much appreciated.

And the same to all who helped out!