Make Table Query hits 2GB Limit

[Grd]

Hi,

The funny thing is that the result set is would be only 340 thousand records.
And there is lots of room in the current database. Its under 1/2 gig.

So I tried to make the table in another database to secure all 2 GB worth of
space for this table and it still compains about the 2GB limit. The machine
has 4GB of RAM and 70GB of free space so its not anything on the machine that
I can see.

The Make Table is running a Crosstab query on a 1.5 Million record table and
taking the summarized results into a table. I can run the Crosstab Query - no
problem. Thats how I can see the number of records the Make Table would
generate. Not that big so why the problem?

I've been struggline with this for a long, long time.

Any help greatly appreciated

Thanks

G

 

[John W. Vinson]

Hi,

The funny thing is that the result set is would be only 340 thousand records.
And there is lots of room in the current database. Its under 1/2 gig.

So I tried to make the table in another database to secure all 2 GB worth of
space for this table and it still compains about the 2GB limit. The machine
has 4GB of RAM and 70GB of free space so its not anything on the machine that
I can see.

The Make Table is running a Crosstab query on a 1.5 Million record table and
taking the summarized results into a table. I can run the Crosstab Query - no
problem. Thats how I can see the number of records the Make Table would
generate. Not that big so why the problem?

I've been struggline with this for a long, long time.

Any help greatly appreciated

Thanks

G

Try creating the target table first, and change the maketable query to an
Append query; Trim() any text fields, particularly calculated ones. I suspect
it's trying to blank fill to the maximum size of each text field.

John W. Vinson [MVP]

 

[Grd]

Hi John,

Thanks for the tip. I didn't to the Trim part but I did the append vs make
table and had same problem. I lost patience and moved the database to SQL
server express. This avoids the 2GB limit and I turned the Access mdb into a
project. I had to recreate my queries as store procedures and views but now
it does the queries at least 10 times faster.

So it worked out in the end

Thanks again.