MalpaX said:
I have a table with five columns and 2,760,000 records. I
have to purge duplicate records.
When I try to run my find duplicate query Access give the
error "not enough space on temporary disk" with code 8138.
I have 15.7 GB of free space on drive D: (where I have my
database), Windows 2002 Professional and I have already
move temp and tmp directory to drive D:.
Access temporary file at error time have 1.99 GB dimension.
On drive C: I have 3.6 GB of free space.
What have to do?
I need some patch?
I've never tried to run such a query on a table that big, but I suspect
that you've simply reached the limits of what can fit in the temporary
file, which sounds like it's limited to 2GB just as a normal Access
database is. I have some ideas of what you might do to work around
this, but I don't know if they'll work.
I think I'd try starting with a new, empty database and link to the
table in the original database. That way, the new database wouldn't
initially contain any actual data of its own; only the link to the
source table. How I'd proceed then would depend on what you need to do.
As I understand it, you're looking for duplicate records in this one
table, not between two tables. Is that right? Do records have to be
duplicates in all fields in the records, or is there a subset of fields
that should be unique?
If your purpose is to purge completely duplicate records from the
table -- so you don't need to examine the records and make any
decisions -- I'd probably run a make-table query like this in the new
database (where the linked table is known as MyTable_Linked:
SELECT DISTINCT *
INTO MyTable_Purged
FROM MyTable_Linked;
That should yield only unique records in MyTable_Purged. You could then
go back to the original database and (after making a backup copy) delete
all the records in MyTable, compact the database, and then import all
the records from MyTable_Purged in the work database.
Another possibility is that there are other fields involved besides
those you're using to determine duplicates, so you need to examine the
duplicate records to decide which ones to keep. Maybe you can create a
table in the work database containing just the duplicate key sets, using
a make-table query like this:
SELECT Field1, Field2, Field3 (and so on)
INTO MyTable_Dupes
FROM MyTable_Linked
GROUP BY Field1, Field2, Field3 (and so on)
HAVING COUNT(*) > 1;
You could then write a query inner-joining MyTable_Dupes with
MyTable_Linked on the complete set of keys, and thus examine and dispose
of the duplicate records.
