Max Records - Append Query

[LebronJames]

When I use an append query on a table of 1000 records, the new fields are added ok. When I use the same append query in table of 300000. Not all the records are brought over. I am using Access 2000. Is there an option in Access that allows me to change the Deafult Max records. Or can I program this in code?

 

[LebronJames]

Here is the query I am using:

INSERT INTO Table1
SELECT *
FROM Table2
WHERE (((Table1.field1) Not In (select Field1 from Table1)));

 

[TroyMcClure]

I'm curious about your WHERE clause--do you mean table2.field1 not in (select field1 from table1)?
--T

 

[LebronJames]

Yes I have a temp table (table2) setup identical to table1. Where the data in table2 is not found in table1 append.... this works on smaller tables but if larger than 150000 it does not work. There must be someowhere the max records can be changed..

 

[PHV]

Have you tried this ?
INSERT INTO Table1
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON Table2.field1=Table1.field1
WHERE Table1.field1Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LebronJames]

Problem is the unique field is blank or null... removed the blank or null value and append worked....