Prev: Can't outer join or append.
Next: How to attach/insert files/attachments from an outlook mailbox
From: jhrBanker on 2 Mar 2010 15:07
My Access2007 db contains 3 tables with a total of just under 5 million records
TBL1: 2,631,933
TBL2: 1,168,989
TBL3: 1,159,542
TOTAL: 4,960,464
All tables are identical, with 10 fields each. The db size is 862MB. I'm
running WinXPpro with 20gb free space.

When I try to run a Union query to combine the 3 tables in a recordset, I
receive the following error:
"The query cannot be completed. Either the size of the query result is
larger than the maximum size of a database (2GB), or there is not enough
temporary storage space on the disk to store the query result."

I created a new empty db and linked the 3 tables to it, and am still unable
to run a Union query (same error).

Any suggestions?
From: Jerry Whittle on 2 Mar 2010 15:19
Try running it as a UNION ALL instead of just a UNION. A UNION ALL doesn't
the time, effort, and disk space to eliminate duplicates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"jhrBanker" wrote:

> My Access2007 db contains 3 tables with a total of just under 5 million records
> TBL1: 2,631,933
> TBL2: 1,168,989
> TBL3: 1,159,542
> TOTAL: 4,960,464
> All tables are identical, with 10 fields each. The db size is 862MB. I'm
> running WinXPpro with 20gb free space.
>
> When I try to run a Union query to combine the 3 tables in a recordset, I
> receive the following error:
> "The query cannot be completed. Either the size of the query result is
> larger than the maximum size of a database (2GB), or there is not enough
> temporary storage space on the disk to store the query result."
>
> I created a new empty db and linked the 3 tables to it, and am still unable
> to run a Union query (same error).
>
> Any suggestions?
From: jhrBanker on 2 Mar 2010 15:38
Thanks Jerry. That did it. Muchly appreciated.

"Jerry Whittle" wrote:

> Try running it as a UNION ALL instead of just a UNION. A UNION ALL doesn't
> the time, effort, and disk space to eliminate duplicates.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "jhrBanker" wrote:
>
> > My Access2007 db contains 3 tables with a total of just under 5 million records
> > TBL1: 2,631,933
> > TBL2: 1,168,989
> > TBL3: 1,159,542
> > TOTAL: 4,960,464
> > All tables are identical, with 10 fields each. The db size is 862MB. I'm
> > running WinXPpro with 20gb free space.
> >
> > When I try to run a Union query to combine the 3 tables in a recordset, I
> > receive the following error:
> > "The query cannot be completed. Either the size of the query result is
> > larger than the maximum size of a database (2GB), or there is not enough
> > temporary storage space on the disk to store the query result."
> >
> > I created a new empty db and linked the 3 tables to it, and am still unable
> > to run a Union query (same error).
> >
> > Any suggestions?
From: De Jager on 13 Mar 2010 12:53

"jhrBanker" <jhrBanker(a)discussions.microsoft.com> wrote in message
news:7FE805F4-2975-462E-AE0A-0C15DF4631E7(a)microsoft.com...
> My Access2007 db contains 3 tables with a total of just under 5 million
> records
> TBL1: 2,631,933
> TBL2: 1,168,989
> TBL3: 1,159,542
> TOTAL: 4,960,464
> All tables are identical, with 10 fields each. The db size is 862MB. I'm
> running WinXPpro with 20gb free space.
>
> When I try to run a Union query to combine the 3 tables in a recordset, I
> receive the following error:
> "The query cannot be completed. Either the size of the query result is
> larger than the maximum size of a database (2GB), or there is not enough
> temporary storage space on the disk to store the query result."
>
> I created a new empty db and linked the 3 tables to it, and am still
> unable
> to run a Union query (same error).
>
> Any suggestions?

 | 
Pages: 1
Prev: Can't outer join or append.
Next: How to attach/insert files/attachments from an outlook mailbox