Query Error Message-Access 2007

[LibrarianIndiana]

I had a database of 2800 DVD' titles and 5 libraries with their individual
holdings. In Access 2000 and 2003 there were no problems. Then came Access
2007.

Now I got this error message when I try to separate the DVD's for Main (for
one report). There would be separate queries for each Branch's holdings of
DVD's.

Error Message: "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 am using the "Is Not Null" criteria. Is this still valid in 2007? Is
there another thing wrong here? Do I need to link the database to something?

Thank you.

Daniel

 

[Gina Whipp]

LibrarianIndiana,

Can you show use the SQL for the query? We don't know in what context you
are using Is Not Null.

 

[Ali Al-Ali]

When i want to append some records to my DB, I got the same error message:"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"

Did you get solution for this error??

my code is :
INSERT INTO ShareMedia ( MediaNo, DataType, Location, RackNo )
SELECT DISTINCT Kidan_North_Batch5_STOM_Import.TAPEN, Kidan_North_Batch5_STOM_Import.DataType, Kidan_North_Batch5_STOM_Import.Location, Kidan_North_Batch5_STOM_Import.RackNo
FROM ShareMedia, Kidan_North_Batch5_STOM_Import;

REgards

 

[John Spencer]

Remove ShareMedia from the FROM Clause. That is causing a cartesian join
which will generate one record for every combination of the records in
ShareMedia and Kidan_North_Batch5_STOM_Import. So if ShareMedia has 5000
records and Kidan_North_Batch5_STOM_Import has 200 records, the result would
be an attempt to add 1,000,000 records to the database. Your Distinct clause
would then attempt to filter the million records down to 2,000 unique records
in Kidan_North_Batch5_STOM_Import.

INSERT INTO ShareMedia ( MediaNo, DataType, Location, RackNo )
SELECT DISTINCT Kidan_North_Batch5_STOM_Import.TAPEN
, Kidan_North_Batch5_STOM_Import.DataType
, Kidan_North_Batch5_STOM_Import.Location
, Kidan_North_Batch5_STOM_Import.RackNo
FROM Kidan_North_Batch5_STOM_Import;



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[asasterley]

I am getting the same error message when i try to run a query : "The query cannot be completed. Either the size of the query result is larger than themaximum size of a database (2GB) or there is not enough temporary storage space on the disk to store the query result"
There are a total of four querys in the file but the forth one is the onlyquery that gives me the error message.

The code is:
SELECT qryPMsOnSchedule3.Loc, qryPMsOnSchedule3.[ORD TYPE], qryPMsOnSchedule3.SUBTYPE, qryPMsOnSchedule3.[REF NUM], qryPMsOnSchedule3.[INV DATE], qryPMsOnSchedule3.COMMENT, SDS_BMS_SERVICE_COMMENTS.SERVICE_COMMENT, qryPMsOnSchedule3.[SCHED DATE], qryPMsOnSchedule3.[LAST LABOR]
FROM qryPMsOnSchedule3 LEFT JOIN SDS_BMS_SERVICE_COMMENTS ON (qryPMsOnSchedule3.CO = SDS_BMS_SERVICE_COMMENTS.COMPANY) AND (qryPMsOnSchedule3.DIV = SDS_BMS_SERVICE_COMMENTS.DIVISION) AND (qryPMsOnSchedule3.Loc = SDS_BMS_SERVICE_COMMENTS.LOCATION) AND (qryPMsOnSchedule3.[REF NUM] = SDS_BMS_SERVICE_COMMENTS.ORDER_REFERENCE) AND (qryPMsOnSchedule3.[ORD TYPE] = SDS_BMS_SERVICE_COMMENTS.ORDER_SOURCE_TYPE) AND (qryPMsOnSchedule3.COMMENT =SDS_BMS_SERVICE_COMMENTS.COMMENT_TYPE);

Any feedback who be most helpful.

Thanks,