Not Enough Space On Temporary Disk When Open/run Ms Access Append Query

S

SMAZ

Hi,

I am new to this forum.
@ I have a mdb and i linked many sql server tables in it. (TABLE_SQL)
The number of recrods in this table are in 9 figures
@ I have a table in MS access (same in which i linked MS SQL Tables
(TABLE_MDB). The number of recrods in this table are in 3 figures
@ I create a Append Query (Make table) (QUERY_APPEND)
In this query is I join the (TABLE_SQL) with (TABLE_MDB) and Inser
all records in a new MS Access
Table (NEWTABLE_MDB)
@ Whenever i ran this query it will shows an error message (Not enoug
space on temporary disk)
@ I serached on net a found article on microsoft site and impletnt it
method(1) & (3) but issue is still there.

I'll appreciate if anyone help me in this regard.

THANK
SMA
 
M

[MVP] S.Clark

Prior to running the query, I assume that Access estimates the amount of
disk space it will need to complete the entire operation. So, if you have a
hard drive with limited space, or temporary space, then it does this.

Maybe compacting will help, but if you have a larger drive, put it on that
and see if it goes away.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
M

Michel Walsh

Hi,


Jet uses temporary file, for storage, and that file is limited to 1Gig
( or 2Gig dependant of the version). Your "join" is probably too general,
and produces too much records.

You may try to run the query from within MS SQL Server, instead, using
Jet database as a linked server, rather than from within Jet, with linked
tables to MS SQL Server... if you really need such a big join... but I doubt
that is what you really want.

When you make a cross join, each record of table1 is "joinned" to each
record of table2, so if there is m1 records in table1, and m2 records in
table2, the result has m1 times m2 records. Having 1E9 records times 1E3
record, that makes 1E12 records... far too much. If you use an inner join,
the ON clause limits the "result", but that still can be large. May be you
should use, explicitly, a virtual table, like:


SELECT ...
FROM ( SELECT * FROM table1 WHERE f1=22) INNER JOIN table2 ON...

instead of


SELECT ...
FROM table1 INNER JOIN table2 ON ...
WHERE f1=22




Hoping it may help,
Vanderghast, Access MVP
 
Top