Temporary Disk

[Bruce]

I am running Access 2002. I keep getting Not enough space on temporary disk
(Error 3183). One of the solutions given was to select a different drive for
my temporary disk. I have researched this and cannot discover how to
reallocate my temporary disk, can anyone give me some instructions, Please.

 

[John W. Vinson]

I am running Access 2002. I keep getting Not enough space on temporary disk
(Error 3183). One of the solutions given was to select a different drive for
my temporary disk. I have researched this and cannot discover how to
reallocate my temporary disk, can anyone give me some instructions, Please.

Fix the problem that's causing the error instead! What circumstances cause the
message to come up? Are you running update queries or append queries?

 

[Bruce]

I am running an append query with a sub query. Have reduced the selection to
66,000 records and still get the error message.

It runs for about an hour and a half and then delivers the error message.

I have abundanct spare space on my hard drives: C: 17 gig, D: 29 gig
(program is runnning from here) E: 39 gig. I have 22 gig of page files across
all three hard drives.

I found the temp file Access is using but not sure how to change size of
file. JET6DC.tmp.

 

[John W. Vinson]

I am running an append query with a sub query. Have reduced the selection to
66,000 records and still get the error message.

It runs for about an hour and a half and then delivers the error message.

I have abundanct spare space on my hard drives: C: 17 gig, D: 29 gig
(program is runnning from here) E: 39 gig. I have 22 gig of page files across
all three hard drives.

I found the temp file Access is using but not sure how to change size of
file. JET6DC.tmp.

Again... fix the query instead. This is a misleading error message; Access
will come out with it under all sorts of circumstances, even when temp disk
space is not really the issue!

You *can* find the location of your temp file. Open the command window
(Start... All Programs... Accessories... Command Prompt) and type

cd %temp%

You'll see where the temp directory is. It's most likely on C:.

You'll find it, and it will have plenty of room, and resetting the Temp
environmet variable to your E drive won't help, I fear!

Is your database bloating? How big is it before running the query, and how big
after the failed attempt?

Could you post the SQL of the query?

 

[Bruce]

John,

My appologies, the query is an append, not an update. I have even linked the
two main tables to limit the size of the database that is running.

 

[Bruce]

John,

I dont think my last post went through, will try to reconstruct for you.

Database is not bloating as the append is not completing. I am even running
the compact and repair after each error message.

Here is the SQL statement:

INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID, [MM 05 mst date].EquipmentID, [MM 05 mst
date].NewDate, [MM 05 mst date].FuelMiles, [MM 05 mst date].WOMiles, [MM 05
mst date].Check, IIf([MM 05 mst date].[Check]=0,(Select Top 1 [MM 04
cond].[Check] From [MM 04 cond] Where [MM 04 cond].[Check] >0 And [MM 04
cond].[EquipmentID] = [MM 05 mst date].[EquipmentID] And [MM 04
cond].[NewDate] < [MM 05 mst date].[NewDate] Order by [MM 04 cond].[NewDate]
DESC, [MM 05 mst date].[EquipmentID]),[MM 05 mst date].[Check]) AS NewCheck
FROM [MM 05 mst date], [MM 04 cond]
WHERE ((([MM 05 mst date].EquipmentID)<="CP042"));

 

[Sylvain Lafontaine]

Possibly because you have added [MM 05 mst date].[EquipmentID] in the Order
By clause of the subquery: this field has no place there because the only
table in the FROM portion of the subquery is [MM 04 cond].

A second possibility would be that even when [MM 05 mst date].[Check] <> 0
inside your IIF() call, the subquery in the IIF() function is evaluated each
time. This is because IIF() beeing a function, all of its individual
parameters must be evaluated before the function is called. So it's quite
possible that Access is running out of working space while trying to
evaluate all these subqueries.

My suggestion would be that first you remove the [MM 05 mst
date].[EquipmentID] from the Order By in the subquery and second, that you
make your append query in two step: first, make the Insert without checking
the value for [MM 05 mst date].[Check] and after that, make an update
statement to change the value of [MM 05 mst date].[Check] where it is 0 but
only after the append query (and without the IIF(), of course).

Instead of making an update, you could also make two Append: one where Check
= 0 and one with Check <> 0. This way, you could remove the IIF() statement
and compute the subquery only for the rows where Check = 0.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

John,

I dont think my last post went through, will try to reconstruct for you.

Database is not bloating as the append is not completing. I am even
running
the compact and repair after each error message.

Here is the SQL statement:

INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID, [MM 05 mst date].EquipmentID, [MM 05 mst
date].NewDate, [MM 05 mst date].FuelMiles, [MM 05 mst date].WOMiles, [MM
05
mst date].Check, IIf([MM 05 mst date].[Check]=0,(Select Top 1 [MM 04
cond].[Check] From [MM 04 cond] Where [MM 04 cond].[Check] >0 And [MM 04
cond].[EquipmentID] = [MM 05 mst date].[EquipmentID] And [MM 04
cond].[NewDate] < [MM 05 mst date].[NewDate] Order by [MM 04
cond].[NewDate]
DESC, [MM 05 mst date].[EquipmentID]),[MM 05 mst date].[Check]) AS
NewCheck
FROM [MM 05 mst date], [MM 04 cond]
WHERE ((([MM 05 mst date].EquipmentID)<="CP042"));



--
Bruce

Again... fix the query instead. This is a misleading error message;
Access
will come out with it under all sorts of circumstances, even when temp
disk
space is not really the issue!

You *can* find the location of your temp file. Open the command window
(Start... All Programs... Accessories... Command Prompt) and type

cd %temp%

You'll see where the temp directory is. It's most likely on C:.

You'll find it, and it will have plenty of room, and resetting the Temp
environmet variable to your E drive won't help, I fear!

Is your database bloating? How big is it before running the query, and
how big
after the failed attempt?

Could you post the SQL of the query?

 

[david]

Select Top 1 [MM 04 cond].[Check]
From [MM 04 cond]
Where [MM 04 cond].[Check] >0
And [MM 04 cond].[EquipmentID] = [MM 05 mst date].[EquipmentID]
And [MM 04 cond].[NewDate] < [MM 05 mst date].[NewDate]
Order by
[MM 04 cond].[NewDate] DESC, [MM 05 mst date].[EquipmentID]


"Order by" queries are always slow. And that one can't even
be sorted on an index, because the two fields are in different tables.

You could start by making sure that EquipmentID and NewDate
are indexed in both tables, but,

Better to make a temp table before you start the insert, and
put the "Top 1" values into the temp table. That is what Access
is doing anyway, but it's doing it again for every line where
[MM 05 mst date].[Check]=0

(david)

John,

I dont think my last post went through, will try to reconstruct for you.

Database is not bloating as the append is not completing. I am even
running
the compact and repair after each error message.

Here is the SQL statement:

INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID, [MM 05 mst date].EquipmentID, [MM 05 mst
date].NewDate, [MM 05 mst date].FuelMiles, [MM 05 mst date].WOMiles, [MM
05
mst date].Check, IIf([MM 05 mst date].[Check]=0,(Select Top 1 [MM 04
cond].[Check] From [MM 04 cond] Where [MM 04 cond].[Check] >0 And [MM 04
cond].[EquipmentID] = [MM 05 mst date].[EquipmentID] And [MM 04
cond].[NewDate] < [MM 05 mst date].[NewDate] Order by [MM 04
cond].[NewDate]
DESC, [MM 05 mst date].[EquipmentID]),[MM 05 mst date].[Check]) AS
NewCheck
FROM [MM 05 mst date], [MM 04 cond]
WHERE ((([MM 05 mst date].EquipmentID)<="CP042"));



--
Bruce

Again... fix the query instead. This is a misleading error message;
Access
will come out with it under all sorts of circumstances, even when temp
disk
space is not really the issue!

You *can* find the location of your temp file. Open the command window
(Start... All Programs... Accessories... Command Prompt) and type

cd %temp%

You'll see where the temp directory is. It's most likely on C:.

You'll find it, and it will have plenty of room, and resetting the Temp
environmet variable to your E drive won't help, I fear!

Is your database bloating? How big is it before running the query, and
how big
after the failed attempt?

Could you post the SQL of the query?

 

[david]

time. This is because IIF() being a function, all of its individual

parameters must be evaluated before the function is called. So

IIF is a JET SQL keyword. It does not evaluate it's parameters
before it is evaluated. You can check this by putting a divide by
zero into one side of an IIF in a query.

IIF is also a VBA function. If you test IIF in the immediate window,
you will see that VBA.IIF behaviour is different from the Jet SQL
behaviour.

You can run the JET SQL IIF (and IN) in VBA by using EVAL
?eval("IIf(1,1,1/0)")
?eval("1 IN (1,2,3)")

(david)

A second possibility would be that even when [MM 05 mst date].[Check] <> 0
inside your IIF() call, the subquery in the IIF() function is evaluated
each time. This is because IIF() beeing a function, all of its individual
parameters must be evaluated before the function is called. So it's quite
possible that Access is running out of working space while trying to
evaluate all these subqueries.

My suggestion would be that first you remove the [MM 05 mst
date].[EquipmentID] from the Order By in the subquery and second, that you
make your append query in two step: first, make the Insert without
checking the value for [MM 05 mst date].[Check] and after that, make an
update statement to change the value of [MM 05 mst date].[Check] where it
is 0 but only after the append query (and without the IIF(), of course).

Instead of making an update, you could also make two Append: one where
Check = 0 and one with Check <> 0. This way, you could remove the IIF()
statement and compute the subquery only for the rows where Check = 0.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

John,

I dont think my last post went through, will try to reconstruct for you.

Database is not bloating as the append is not completing. I am even
running
the compact and repair after each error message.

Here is the SQL statement:

INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID, [MM 05 mst date].EquipmentID, [MM 05 mst
date].NewDate, [MM 05 mst date].FuelMiles, [MM 05 mst date].WOMiles, [MM
05
mst date].Check, IIf([MM 05 mst date].[Check]=0,(Select Top 1 [MM 04
cond].[Check] From [MM 04 cond] Where [MM 04 cond].[Check] >0 And [MM 04
cond].[EquipmentID] = [MM 05 mst date].[EquipmentID] And [MM 04
cond].[NewDate] < [MM 05 mst date].[NewDate] Order by [MM 04
cond].[NewDate]
DESC, [MM 05 mst date].[EquipmentID]),[MM 05 mst date].[Check]) AS
NewCheck
FROM [MM 05 mst date], [MM 04 cond]
WHERE ((([MM 05 mst date].EquipmentID)<="CP042"));



--
Bruce

On Sat, 15 Nov 2008 15:10:00 -0800, Bruce
<[email protected]>
wrote:

I am running an append query with a sub query. Have reduced the
selection to
66,000 records and still get the error message.

It runs for about an hour and a half and then delivers the error
message.

I have abundanct spare space on my hard drives: C: 17 gig, D: 29 gig
(program is runnning from here) E: 39 gig. I have 22 gig of page files
across
all three hard drives.

I found the temp file Access is using but not sure how to change size
of
file. JET6DC.tmp.

Again... fix the query instead. This is a misleading error message;
Access
will come out with it under all sorts of circumstances, even when temp
disk
space is not really the issue!

You *can* find the location of your temp file. Open the command window
(Start... All Programs... Accessories... Command Prompt) and type

cd %temp%

You'll see where the temp directory is. It's most likely on C:.

You'll find it, and it will have plenty of room, and resetting the Temp
environmet variable to your E drive won't help, I fear!

Is your database bloating? How big is it before running the query, and
how big
after the failed attempt?

Could you post the SQL of the query?

 

[David W. Fenton]

Database is not bloating as the append is not completing.

Er, all transactions write to a temp file and take up disk space.
However, it won't necessarily show up in Windows Explorer, because
while the file is still open for writing, it will often show up as 0
bytes in size. So this doesn't mean anything at all.

And it's entirely possible that actual disk space usage has nothing
at all to do with it, it's just that it's Jet's best guess as to why
the operation can't complete.

 

[David W. Fenton]

My appologies, the query is an append, not an update. I have even
linked the two main tables to limit the size of the database that
is running.

Why do you want an ORDER BY in an append query? What difference does
it make what order the records are in the destination table? If you
want to view them in a specific order, then write query on your
destination table with the desired ORDER BY clause.

I don't think ORDER BY should ever be set in an APPEND query.

 

[Sylvain Lafontaine]

IIF is a JET SQL keyword. It does not evaluate it's parameters
before it is evaluated. You can check this by putting a divide by
zero into one side of an IIF in a query.

Not really a proof: I did make this check and all Access is doing is showing
#Error when it need to display the value of the divide by 0.

Furthermore, I did make a quick check by creating a linked table against
SQL-Server and creating a query with a subquery inside an IIF() call. The
trace from the SQL-Profiler show that the subquery is called every time;
whatever it's needed or not.

Probably that repeating this test but now with the ODBC Tracing will show
the same result even when a linked table is not used. However, I will have
to remember how to activate the ODBC tracing; something that I've not done
since many years.

IIF is also a VBA function. If you test IIF in the immediate window,
you will see that VBA.IIF behaviour is different from the Jet SQL
behaviour.

You can run the JET SQL IIF (and IN) in VBA by using EVAL
?eval("IIf(1,1,1/0)")
?eval("1 IN (1,2,3)")

(david)

A second possibility would be that even when [MM 05 mst date].[Check] <>
0 inside your IIF() call, the subquery in the IIF() function is evaluated
each time. This is because IIF() beeing a function, all of its
individual parameters must be evaluated before the function is called.
So it's quite possible that Access is running out of working space while
trying to evaluate all these subqueries.

My suggestion would be that first you remove the [MM 05 mst
date].[EquipmentID] from the Order By in the subquery and second, that
you make your append query in two step: first, make the Insert without
checking the value for [MM 05 mst date].[Check] and after that, make an
update statement to change the value of [MM 05 mst date].[Check] where it
is 0 but only after the append query (and without the IIF(), of course).

Instead of making an update, you could also make two Append: one where
Check = 0 and one with Check <> 0. This way, you could remove the IIF()
statement and compute the subquery only for the rows where Check = 0.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

John,

I dont think my last post went through, will try to reconstruct for you.

Database is not bloating as the append is not completing. I am even
running
the compact and repair after each error message.

Here is the SQL statement:

INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID, [MM 05 mst date].EquipmentID, [MM 05 mst
date].NewDate, [MM 05 mst date].FuelMiles, [MM 05 mst date].WOMiles, [MM
05
mst date].Check, IIf([MM 05 mst date].[Check]=0,(Select Top 1 [MM 04
cond].[Check] From [MM 04 cond] Where [MM 04 cond].[Check] >0 And [MM
04
cond].[EquipmentID] = [MM 05 mst date].[EquipmentID] And [MM 04
cond].[NewDate] < [MM 05 mst date].[NewDate] Order by [MM 04
cond].[NewDate]
DESC, [MM 05 mst date].[EquipmentID]),[MM 05 mst date].[Check]) AS
NewCheck
FROM [MM 05 mst date], [MM 04 cond]
WHERE ((([MM 05 mst date].EquipmentID)<="CP042"));



--
Bruce


:

On Sat, 15 Nov 2008 15:10:00 -0800, Bruce
<[email protected]>
wrote:

I am running an append query with a sub query. Have reduced the
selection to
66,000 records and still get the error message.

It runs for about an hour and a half and then delivers the error
message.

I have abundanct spare space on my hard drives: C: 17 gig, D: 29 gig
(program is runnning from here) E: 39 gig. I have 22 gig of page files
across
all three hard drives.

I found the temp file Access is using but not sure how to change size
of
file. JET6DC.tmp.

Again... fix the query instead. This is a misleading error message;
Access
will come out with it under all sorts of circumstances, even when temp
disk
space is not really the issue!

You *can* find the location of your temp file. Open the command window
(Start... All Programs... Accessories... Command Prompt) and type

cd %temp%

You'll see where the temp directory is. It's most likely on C:.

You'll find it, and it will have plenty of room, and resetting the Temp
environmet variable to your E drive won't help, I fear!

Is your database bloating? How big is it before running the query, and
how big
after the failed attempt?

Could you post the SQL of the query?

 

[John Spencer]

INSERT INTO MM ( UID, EquipmentID, NewDate, FuelMiles, WOMiles, [Check],
NewCheck )
SELECT [MM 05 mst date].UID
, [MM 05 mst date].EquipmentID
, [MM 05 mst date].NewDate
, [MM 05 mst date].FuelMiles
, [MM 05 mst date].WOMiles
, [MM 05 mst date].Check

, IIf([MM 05 mst date].[Check]=0
, (Select Top 1 [MM 04 cond].[Check]
From [MM 04 cond]
Where [MM 04 cond].[Check] >0 And
[MM 04 cond].[EquipmentID] = [MM 05 mst date].[EquipmentID]
And [MM 04 cond].[NewDate] < [MM 05 mst date].[NewDate]
Order by [MM 04 cond].[NewDate] DESC)

, [MM 05 mst date].[EquipmentID])

,[MM 05 mst date].[Check]) AS NewCheck
FROM [MM 05 mst date], [MM 04 cond]
WHERE [MM 05 mst date].EquipmentID<="CP042"

Well, first I see no reason to include MM O4 Cond table in the FROM
clause of the main query-Unless you want multiple records for each
record combination of [MM 05 mst date] and [MM 04 cond]

Next - you are missing a closing parentheses at the end of the subquery

Next - after you close the subquery there seems to be an extraneous
field reference - [MM 05 mst date].[EquipmentID]


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

 

[Bruce]

I was able to get the append query to run with no errors by indexing Check
and NewCheck. Using records selection of 134 k, the query takes about two
hours to run. It is slow but at least it is now running without getting the
error message.

Thanks to everyone for all of your help, it is most appreciated.

 

[Sylvain Lafontaine]

Glad to hear that your query is not running correctly.

However, if we take a look at your speed problem, have you tried the other
suggestions like removing the [MM 04 cond] table table in the FROM clause of
the main query as suggested by John S. and removing the [MM 05 mst
date].[EquipmentID] condition in the Order By clause of the subquery or
trying to split your append query into two append queries?

 

[Sylvain Lafontaine]

Sorry: « Glad to hear that your query is *now* running correctly. ».

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Glad to hear that your query is not running correctly.

However, if we take a look at your speed problem, have you tried the other
suggestions like removing the [MM 04 cond] table table in the FROM clause
of the main query as suggested by John S. and removing the [MM 05 mst
date].[EquipmentID] condition in the Order By clause of the subquery or
trying to split your append query into two append queries?

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

Sorry: ® Glad to hear that your query is *now* running correctly.
¯.

Yes, what you originally wrote is more like something *I'd* post.

 

[Tony Toews [MVP]]

Database is not bloating as the append is not completing.

Not necessarily. Access/Jet may be taking up the space for the
records it was unable to add. Access/Jet certainly consumes
autonumbers even if it turns around and doesn't add the records.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Bruce]

Sylvain,

Just to clarify one of your statements. I understood you to state that even
though a condition is not true, (regarding my sub query) Access will still
evaluate the condition. I sort of suspected this but had no way to verify. Is
it possible to force Access to only evaluate when a condition is true?

This was an append, I misinformed in my original statement, sorry. Did not
really understand the part where you said to make two append one where check
= 0 and one where check <> 0.

 

[Bruce]

David,

I found that if I removed the Order by statement, that the top value is not
retrieved by the sub query, so I had to put in back.