append query limits

[Guest]

I have an approx. 385MB, 2.1 million record database and want to append to it
a 61MB, 373K table and receiving error "There isn't enough disk space or
memory error".

Pentium 4, 2.99 GHZ, 1 GIG of RAM

Does anyone know if the software has been pushed beyond it's limits.

 

[John Vinson]

I have an approx. 385MB, 2.1 million record database and want to append to it
a 61MB, 373K table and receiving error "There isn't enough disk space or
memory error".

Pentium 4, 2.99 GHZ, 1 GIG of RAM

Does anyone know if the software has been pushed beyond it's limits.

Are these separate DATABASES - different .mdb files? or separate
TABLES?

Could you post the SQL of the Append query you're trying to run?

John W. Vinson[MVP]

 

[Guest]

Hi John

Sorry, these are two tables

SQL is
INSERT INTO TblStreetsMatchDA_app
SELECT tableAppendtest.*
FROM tableAppendtest, TblStreetsMatchDA_app
WITH OWNERACCESS OPTION;

Big Iv

 

[Brendan Reynolds]

You've got two table names in your FROM clause, and no join, so you're going
to get the Cartesian product - every possible combination of each record in
one table matched with each record in the other table. With 2.1 million
records in one of the tables, and 373 thousand in the other, no wonder
you're running out of space!

To resolve the problem, specify an appropriate join between the tables.

 

[John Vinson]

INSERT INTO TblStreetsMatchDA_app
SELECT tableAppendtest.*
FROM tableAppendtest, TblStreetsMatchDA_app
WITH OWNERACCESS OPTION;

This is pairing every single record in TblStreetsMatchDA_app with
every single record in tableAppendTest, and trying to append all ca.
783,300,000,000 resulting records into tableAppendtest. Somehow I
don't think this is your intent... <g>

If you want to append the records in tableAppendTest into
TblStreetsMatchDA_App, simply remove the target table from the FROM
clause:

INSERT INTO TblStreetsMatchDA_app
SELECT tableAppendtest.*
FROM tableAppendtest
WITH OWNERACCESS OPTION;

An Append query has a "source" and a "target"; it's usually not
necessary or appropriate to have the target as part of the source!

John W. Vinson[MVP]

 

[Guest]

You've got two table names in your FROM clause, and no join, so you're going
to get the Cartesian product - every possible combination of each record in
one table matched with each record in the other table. With 2.1 million
records in one of the tables, and 373 thousand in the other, no wonder
you're running out of space!

To resolve the problem, specify an appropriate join between the tables.

 

[John Vinson]

Iv, did you get your query working with Brendan's and my suggestions?

John W. Vinson[MVP]