Not enough space on temporary disk error

[miss_mas]

Hi.

I'm working with a database that serves as the front end of a separate
database. The data database is 1.8 GB. The front end is 700 MB. There are
several queries and reports in this database. The queries work fine.
However, whenever I try to run a few of the reports, I get the following
error message:

"Not enough space on temporary disk".

The queries do not return more than 10,000 lines. Why would I get this error?

Additionally, I noticed that in the other reports all of the even pages are
blank? How do I get rid of these?

Any help would be appreciated.

Thanks.
ms

 

[John W. Vinson]

Hi.

I'm working with a database that serves as the front end of a separate
database. The data database is 1.8 GB. The front end is 700 MB. There are
several queries and reports in this database. The queries work fine.
However, whenever I try to run a few of the reports, I get the following
error message:

An Access database is limited to 2.0 GB so your backend is really getting
squeaky tight. Has it been Compacted lately?? Have you considered moving the
data to SQL/Server?

"Not enough space on temporary disk".

The queries do not return more than 10,000 lines. Why would I get this error?

A problem with the query... which we cannot see. If it's a query based on a
query, or if the query returns more records than that and the Report uses a
filter to cut it down to 10000 lines, you might get this. Perhaps you could
post the SQL view of the query.

Additionally, I noticed that in the other reports all of the even pages are
blank? How do I get rid of these?

This will happen if the Report is larger (even one pixel larger) than the
printable area of the page. You can use File... Page Setup to set the margins
a bit smaller, and/or open the report in design view and make sure it fits
entirely within the margins.

 

[miss_mas]

Yes, the data has been compacted recently - both the front and back end. It
has to be compacted daily so it won't crash. I just started looking at
moving to SQL Server today - so very early stages.

The problem is not the query. It's the report. The report is populated by
the query. Here is the SQL code for one of the queries that crashes.

SELECT Changes.KAM, Changes.CORE_3, Changes.CUSTOMER_MARKET,
Changes.MANAGING_OFFICE, Changes.AM AS CFR, Changes.PM, Changes.ASP, [Change
Creation Info].CHANGE_CREATED_ON, DateDiff("d",[change_created_on],Now()) AS
[Change Age], Changes.PACKAGE_IN_SCOPE, Changes.BILLABLE_TO_CUSTOMER,
Changes.PM_APPROVED, Changes.CHANGE_PRICE, Changes.CHANGES_INITIATED_BY,
Changes.REQUESTED_BY_CUSTOMER, Changes.CHANGE_TYPE,
Changes.CHANGE_DESCRIPTION, Changes.SITE_ID, Changes.PM_COMMENTS,
Changes.CHANGE_COMPLETION_DATE_AB, Changes.RSS_CREATED_ON, Changes.CALLID AS
RSS, Changes.CHANGE_NUMBER, Changes.CHANGE_SYSTEM_ID,
PACKAGES.PACKAGE_CANCELLED
FROM PACKAGES, [Change Creation Info] INNER JOIN Changes ON [Change Creation
Info].ID_CHANGE = Changes.CHANGE_SYSTEM_ID
WHERE ((([Change Creation Info].CHANGE_CREATED_ON)>#9/1/2007#) AND
((Changes.PACKAGE_IN_SCOPE) Is Not Null) AND
((Changes.BILLABLE_TO_CUSTOMER)="0") AND ((Changes.PM_APPROVED) Is Null) AND
((PACKAGES.PACKAGE_CANCELLED)="F"));

Thanks for answering my page question as well.

 

[John W. Vinson]

Yes, the data has been compacted recently - both the front and back end. It
has to be compacted daily so it won't crash. I just started looking at
moving to SQL Server today - so very early stages.

The problem is not the query. It's the report. The report is populated by
the query. Here is the SQL code for one of the queries that crashes.

SELECT Changes.KAM, Changes.CORE_3, Changes.CUSTOMER_MARKET,
Changes.MANAGING_OFFICE, Changes.AM AS CFR, Changes.PM, Changes.ASP, [Change
Creation Info].CHANGE_CREATED_ON, DateDiff("d",[change_created_on],Now()) AS
[Change Age], Changes.PACKAGE_IN_SCOPE, Changes.BILLABLE_TO_CUSTOMER,
Changes.PM_APPROVED, Changes.CHANGE_PRICE, Changes.CHANGES_INITIATED_BY,
Changes.REQUESTED_BY_CUSTOMER, Changes.CHANGE_TYPE,
Changes.CHANGE_DESCRIPTION, Changes.SITE_ID, Changes.PM_COMMENTS,
Changes.CHANGE_COMPLETION_DATE_AB, Changes.RSS_CREATED_ON, Changes.CALLID AS
RSS, Changes.CHANGE_NUMBER, Changes.CHANGE_SYSTEM_ID,
PACKAGES.PACKAGE_CANCELLED
FROM PACKAGES, [Change Creation Info] INNER JOIN Changes ON [Change Creation
Info].ID_CHANGE = Changes.CHANGE_SYSTEM_ID
WHERE ((([Change Creation Info].CHANGE_CREATED_ON)>#9/1/2007#) AND
((Changes.PACKAGE_IN_SCOPE) Is Not Null) AND
((Changes.BILLABLE_TO_CUSTOMER)="0") AND ((Changes.PM_APPROVED) Is Null) AND
((PACKAGES.PACKAGE_CANCELLED)="F"));

Thanks for answering my page question as well.

Does the error occur if you open and browse the query datasheet, or just on
opening the report? Does the report have a Filter set? Is its Sorting and
Grouping unusual? Do you have indexes on the joining and query fields - e.g.
CREATED_ON, BILLABLE_TO_CUSTOMER, PACKAGE_CANCELLED? How about on the fields
you're using in the sorting and grouping?

 

[miss_mas]

The error does not occur if I browse the datasheet, just when opening the
report. I'm not sure if it has a filter, but I don't think so. I inherited
the database. The report never opens. I get the error message when it is
running. At this point, nothing is indexed.

 

[Pete D.]

Do the reports in steps, query tables into temp tables. Then run a report
on those. Temp disk can be memory or hard drive space but simply said your
pushing the limits. Might lood at the items below for some suggestions.

http://help.alentus.com/article.aspx?id=10287&cNode=7B2X2S
http://www.access-programmers.co.uk/forums/archive/index.php/t-96471.html

 

[sgmaxmp11]

Hi.

I'm working with a database that serves as the front end of a separate
database. The data database is 1.8 GB. The front end is 700 MB. There are
several queries and reports in this database. The queries work fine.
However, whenever I try to run a few of the reports, I get the following
error message:

"Not enough space on temporary disk".

The queries do not return more than 10,000 lines. Why would I get this error?

Additionally, I noticed that in the other reports all of the even pages are
blank? How do I get rid of these?

Any help would be appreciated.

Thanks.
ms

 

[sgmaxmp11]

Hi.

I'm working with a database that serves as the front end of a separate
database. The data database is 1.8 GB. The front end is 700 MB. There are
several queries and reports in this database. The queries work fine.
However, whenever I try to run a few of the reports, I get the following
error message:

"Not enough space on temporary disk".

The queries do not return more than 10,000 lines. Why would I get this error?

Additionally, I noticed that in the other reports all of the even pages are
blank? How do I get rid of these?

Any help would be appreciated.

Thanks.
ms

 

[sgmaxmp11]

An Access database is limited to 2.0 GB so your backend is really getting
squeaky tight. Has it been Compacted lately?? Have you considered moving the
data to SQL/Server?


A problem with the query... which we cannot see. If it's a query based on a
query, or if the query returns more records than that and the Report uses a
filter to cut it down to 10000 lines, you might get this. Perhaps you could
post the SQL view of the query.


This will happen if the Report is larger (even one pixel larger) than the
printable area of the page. You can use File... Page Setup to set the margins
a bit smaller, and/or open the report in design view and make sure it fits
entirely within the margins.

 

[sgmaxmp11]

Yes, the data has been compacted recently - both the front and back end. It
has to be compacted daily so it won't crash. I just started looking at
moving to SQL Server today - so very early stages.

The problem is not the query. It's the report. The report is populated by
the query. Here is the SQL code for one of the queries that crashes.

SELECT Changes.KAM, Changes.CORE_3, Changes.CUSTOMER_MARKET,
Changes.MANAGING_OFFICE, Changes.AM AS CFR, Changes.PM, Changes.ASP, [Change
Creation Info].CHANGE_CREATED_ON, DateDiff("d",[change_created_on],Now()) AS
[Change Age], Changes.PACKAGE_IN_SCOPE, Changes.BILLABLE_TO_CUSTOMER,
Changes.PM_APPROVED, Changes.CHANGE_PRICE, Changes.CHANGES_INITIATED_BY,
Changes.REQUESTED_BY_CUSTOMER, Changes.CHANGE_TYPE,
Changes.CHANGE_DESCRIPTION, Changes.SITE_ID, Changes.PM_COMMENTS,
Changes.CHANGE_COMPLETION_DATE_AB, Changes.RSS_CREATED_ON, Changes.CALLID AS
RSS, Changes.CHANGE_NUMBER, Changes.CHANGE_SYSTEM_ID,
PACKAGES.PACKAGE_CANCELLED
FROM PACKAGES, [Change Creation Info] INNER JOIN Changes ON [Change Creation
Info].ID_CHANGE = Changes.CHANGE_SYSTEM_ID
WHERE ((([Change Creation Info].CHANGE_CREATED_ON)>#9/1/2007#) AND
((Changes.PACKAGE_IN_SCOPE) Is Not Null) AND
((Changes.BILLABLE_TO_CUSTOMER)="0") AND ((Changes.PM_APPROVED) Is Null) AND
((PACKAGES.PACKAGE_CANCELLED)="F"));

Thanks for answering my page question as well.

 

[sgmaxmp11]

The problem is in the first join in your from clause. That's a cartesian
join. You don't want to do that. (At least it's very, very unlikely.)

I don't know what your join column is on the packages table so I'll give an
example with ID_Change as the name of the column you want to match up with
[Change Creation Info].ID_CHANGE.

FROM (PACKAGES INNER JOIN [Change Creation Info] ON PACKAGES.ID_Change =
[Change Creation Info].ID_CHANGE) INNER JOIN Changes ON [Change Creation
Info].ID_CHANGE = Changes.CHANGE_SYSTEM_ID


Chris
Microsoft MVP

Yes, the data has been compacted recently - both the front and back end. It
has to be compacted daily so it won't crash. I just started looking at
moving to SQL Server today - so very early stages.

The problem is not the query. It's the report. The report is populated by
the query. Here is the SQL code for one of the queries that crashes.

SELECT Changes.KAM, Changes.CORE_3, Changes.CUSTOMER_MARKET,
Changes.MANAGING_OFFICE, Changes.AM AS CFR, Changes.PM, Changes.ASP, [Change
Creation Info].CHANGE_CREATED_ON, DateDiff("d",[change_created_on],Now()) AS
[Change Age], Changes.PACKAGE_IN_SCOPE, Changes.BILLABLE_TO_CUSTOMER,
Changes.PM_APPROVED, Changes.CHANGE_PRICE, Changes.CHANGES_INITIATED_BY,
Changes.REQUESTED_BY_CUSTOMER, Changes.CHANGE_TYPE,
Changes.CHANGE_DESCRIPTION, Changes.SITE_ID, Changes.PM_COMMENTS,
Changes.CHANGE_COMPLETION_DATE_AB, Changes.RSS_CREATED_ON, Changes.CALLID AS
RSS, Changes.CHANGE_NUMBER, Changes.CHANGE_SYSTEM_ID,
PACKAGES.PACKAGE_CANCELLED
FROM PACKAGES, [Change Creation Info] INNER JOIN Changes ON [Change Creation
Info].ID_CHANGE = Changes.CHANGE_SYSTEM_ID
WHERE ((([Change Creation Info].CHANGE_CREATED_ON)>#9/1/2007#) AND
((Changes.PACKAGE_IN_SCOPE) Is Not Null) AND
((Changes.BILLABLE_TO_CUSTOMER)="0") AND ((Changes.PM_APPROVED) Is Null) AND
((PACKAGES.PACKAGE_CANCELLED)="F"));

Thanks for answering my page question as well.

 

[sgmaxmp11]

Yes, the data has been compacted recently - both the front and back end. It
has to be compacted daily so it won't crash. I just started looking at
moving to SQL Server today - so very early stages.

The problem is not the query. It's the report. The report is populated by
the query. Here is the SQL code for one of the queries that crashes.

SELECT Changes.KAM, Changes.CORE_3, Changes.CUSTOMER_MARKET,
Changes.MANAGING_OFFICE, Changes.AM AS CFR, Changes.PM, Changes.ASP, [Change
Creation Info].CHANGE_CREATED_ON, DateDiff("d",[change_created_on],Now()) AS
[Change Age], Changes.PACKAGE_IN_SCOPE, Changes.BILLABLE_TO_CUSTOMER,
Changes.PM_APPROVED, Changes.CHANGE_PRICE, Changes.CHANGES_INITIATED_BY,
Changes.REQUESTED_BY_CUSTOMER, Changes.CHANGE_TYPE,
Changes.CHANGE_DESCRIPTION, Changes.SITE_ID, Changes.PM_COMMENTS,
Changes.CHANGE_COMPLETION_DATE_AB, Changes.RSS_CREATED_ON, Changes.CALLID AS
RSS, Changes.CHANGE_NUMBER, Changes.CHANGE_SYSTEM_ID,
PACKAGES.PACKAGE_CANCELLED
FROM PACKAGES, [Change Creation Info] INNER JOIN Changes ON [Change Creation
Info].ID_CHANGE = Changes.CHANGE_SYSTEM_ID
WHERE ((([Change Creation Info].CHANGE_CREATED_ON)>#9/1/2007#) AND
((Changes.PACKAGE_IN_SCOPE) Is Not Null) AND
((Changes.BILLABLE_TO_CUSTOMER)="0") AND ((Changes.PM_APPROVED) Is Null) AND
((PACKAGES.PACKAGE_CANCELLED)="F"));

Thanks for answering my page question as well.

Does the error occur if you open and browse the query datasheet, or just on
opening the report? Does the report have a Filter set? Is its Sorting and
Grouping unusual? Do you have indexes on the joining and query fields - e.g.
CREATED_ON, BILLABLE_TO_CUSTOMER, PACKAGE_CANCELLED? How about on the fields
you're using in the sorting and grouping?

 

[sgmaxmp11]

Firstly, make backups of the two .mdb files.
Open each .mdb and do Tools->Database Utilities-> compact and
repair Database to ensure that the databases are as small as
possible.

If the even pages in any report are blank, the usual cause is that
the design width is set too wide for the paper, and it's trying to
print the right margin onto the next page. Check your report layout
and see if it's a little too wide, sometimes only a few pixels.

Reducing the margins sometimes helps too. File->page Setup->Margins

 

[sgmaxmp11]

Do the reports in steps, query tables into temp tables. Then run a report
on those. Temp disk can be memory or hard drive space but simply said your
pushing the limits. Might lood at the items below for some suggestions.

http://help.alentus.com/article.aspx?id=10287&cNode=7B2X2S
http://www.access-programmers.co.uk/forums/archive/index.php/t-96471.html