Not enough space on temporary disk.

[waiting to complete an OLE action]

The SQL below updates records to an access table.

It is causing the error message "Not enough space on temporary disk."

INSERT INTO NS_Pmt_Temporary ( Batch_ID, [Account Num], Line, [Item-Type],
Vendor_Nbr, [Hotel Name], [Hotel Name 2], [Pmt Start Date], [Pmt End Date],
[Date Range], [Amt Paid], [First Night], [Last Night], Nights, Comments )
SELECT No_Show_Data_Brio.[Batch Id], No_Show_Data_Brio.[Account Nbr],
No_Show_Data_Brio.[Line Item], "HOTEL" AS [Item-Type],
No_Show_Data_Brio.[Vend Id Nbr], Hotel_Name_Table.[Item Name] AS [Hotel
Name], Hotel_Name_Table.[Item Name], qPmtDateRange.[Pmt Start Date],
qPmtDateRange.[Pmt End Date], [Pmt Start Date] & " - " & [Pmt End Date] AS
[Date Range], No_Show_Data_Brio.[Paid Amt] AS [Amt Paid],
No_Show_Data_Brio.[Begin Dt] AS [First Night], No_Show_Data_Brio.[End Dt] AS
[Last Night], DateDiff("d",[Begin Dt],[End Dt]) AS Nights,
No_Show_Data_Brio.Comments2
FROM (No_Show_Data_Brio LEFT JOIN qPmtDateRange ON No_Show_Data_Brio.[Batch
Id] = qPmtDateRange.[Batch Id]) LEFT JOIN Hotel_Name_Table ON
(No_Show_Data_Brio.[Account Nbr] = Hotel_Name_Table.[Account Nbr]) AND
(No_Show_Data_Brio.[Line Item] = Hotel_Name_Table.[Line Item])
WHERE (((No_Show_Data_Brio.[Batch Id])=301911));


The help file suggest the following solutions.

* Free some space by deleting data or by removing unneeded tables, queries,
forms, macros, and modules from your database.
-The database is 600 meg it is not close the 2 gig limit and I have nothing
left to delete.

* Free some space by compressing deleted records out of your database.
-I ran the compact and repair utility on the database and it completed
successfully.

*Select fewer records. Dynaset-type, forward-only – type, and snapshot-type
Recordset objects record keys and data to the temporary disk.
-I added this parameter "WHERE (((No_Show_Data_Brio.[Batch Id])=301911)"
which reduces the number of records to 39 and the error message still
persists.

*Select a different drive for your temporary disk.
-I don't know how to do this. Can anyone tell me how to do this or suggest
any other solution to this problem?

I have also deleted files from my C:\WINXP\Temp folder as well as temporary
internet files. I have 40Gig of free space and 0% fragmentation on the local
drive.

I don't know what else to do to resolve this problem!

 

[Michel Walsh]

Make a backup.

Make a query like:


SELECT * FROM No_Show_Data_Brio WHERE (((No_Show_Data_Brio.[Batch
Id])=301911));


and save it, say, as q1. Then try:



INSERT INTO NS_Pmt_Temporary ( Batch_ID, [Account Num], Line, [Item-Type],
Vendor_Nbr, [Hotel Name], [Hotel Name 2], [Pmt Start Date], [Pmt End Date],
[Date Range], [Amt Paid], [First Night], [Last Night], Nights, Comments )
SELECT q1.[Batch Id], q1.[Account Nbr],
q1.[Line Item], "HOTEL" AS [Item-Type],
q1.[Vend Id Nbr], Hotel_Name_Table.[Item Name] AS [Hotel
Name], Hotel_Name_Table.[Item Name], qPmtDateRange.[Pmt Start Date],
qPmtDateRange.[Pmt End Date], [Pmt Start Date] & " - " & [Pmt End Date] AS
[Date Range], No_Show_Data_Brio.[Paid Amt] AS [Amt Paid],
No_Show_Data_Brio.[Begin Dt] AS [First Night], No_Show_Data_Brio.[End Dt] AS
[Last Night], DateDiff("d",[Begin Dt],[End Dt]) AS Nights,
No_Show_Data_Brio.Comments2
FROM (q1 LEFT JOIN qPmtDateRange ON q1.[Batch
Id] = qPmtDateRange.[Batch Id]) LEFT JOIN Hotel_Name_Table ON
(q1.[Account Nbr] = Hotel_Name_Table.[Account Nbr]) AND
(q1.[Line Item] = Hotel_Name_Table.[Line Item])






Vanderghast, Access MVP


"waiting to complete an OLE action"

The SQL below updates records to an access table.

It is causing the error message "Not enough space on temporary disk."

INSERT INTO NS_Pmt_Temporary ( Batch_ID, [Account Num], Line, [Item-Type],
Vendor_Nbr, [Hotel Name], [Hotel Name 2], [Pmt Start Date], [Pmt End
Date],
[Date Range], [Amt Paid], [First Night], [Last Night], Nights, Comments )
SELECT No_Show_Data_Brio.[Batch Id], No_Show_Data_Brio.[Account Nbr],
No_Show_Data_Brio.[Line Item], "HOTEL" AS [Item-Type],
No_Show_Data_Brio.[Vend Id Nbr], Hotel_Name_Table.[Item Name] AS [Hotel
Name], Hotel_Name_Table.[Item Name], qPmtDateRange.[Pmt Start Date],
qPmtDateRange.[Pmt End Date], [Pmt Start Date] & " - " & [Pmt End Date] AS
[Date Range], No_Show_Data_Brio.[Paid Amt] AS [Amt Paid],
No_Show_Data_Brio.[Begin Dt] AS [First Night], No_Show_Data_Brio.[End Dt]
AS
[Last Night], DateDiff("d",[Begin Dt],[End Dt]) AS Nights,
No_Show_Data_Brio.Comments2
FROM (No_Show_Data_Brio LEFT JOIN qPmtDateRange ON
No_Show_Data_Brio.[Batch
Id] = qPmtDateRange.[Batch Id]) LEFT JOIN Hotel_Name_Table ON
(No_Show_Data_Brio.[Account Nbr] = Hotel_Name_Table.[Account Nbr]) AND
(No_Show_Data_Brio.[Line Item] = Hotel_Name_Table.[Line Item])
WHERE (((No_Show_Data_Brio.[Batch Id])=301911));


The help file suggest the following solutions.

* Free some space by deleting data or by removing unneeded tables,
queries,
forms, macros, and modules from your database.
-The database is 600 meg it is not close the 2 gig limit and I have
nothing
left to delete.

* Free some space by compressing deleted records out of your database.
-I ran the compact and repair utility on the database and it completed
successfully.

*Select fewer records. Dynaset-type, forward-only - type, and
snapshot-type
Recordset objects record keys and data to the temporary disk.
-I added this parameter "WHERE (((No_Show_Data_Brio.[Batch Id])=301911)"
which reduces the number of records to 39 and the error message still
persists.

*Select a different drive for your temporary disk.
-I don't know how to do this. Can anyone tell me how to do this or suggest
any other solution to this problem?

I have also deleted files from my C:\WINXP\Temp folder as well as
temporary
internet files. I have 40Gig of free space and 0% fragmentation on the
local
drive.

I don't know what else to do to resolve this problem!