Delete duplicate records

[cdotec]

I have two Access 2 db’s, with identical tables in them; I need to delete matching (duplicate) records in both db's

I am using VB6 (SP4) and ADO / ADOX 2.5, I have tried joining (linking) the tables and then looping through the records, but this causes a problem when the records have null values is some fields.

i.e.

DELETE DISTINCTROW tbl1.*, lnk1.* FROM tbl1, lnk1 WHERE
(lnk1.field1 = tbl1.field1 AND lnk1.field2 = tbl1.field2 )

Any tips / ideas would be gratefully received

Thanks.
[sig][/sig]

 

[swilliams]

Do you have a primary key set up on the tables?? If you have then this should be set (automatically) not to allow null values.
If you can get around this, create a temp table in one database and fill this with the duplicate values, then delete from each table in turn where PK IN temp table, then clear temp table.

Simon [sig][/sig]

 

[MichaelRed]

Do you want to delete the duplicates from BOTH tables, or just one of them (so you only have unique records between the two tables)?

-Assuming the elatter, generte thee "FindDuplicates" query between the two tables.

-Do a delete query on the table you want to remove the (duplicated) records from, using the "FindDupliicates" query as the criteria for the delettions.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[cdotec]

Thanks Simon, Michael for your tips.

Unfortunately not quite what I need, I have a key index set on each table which I can sort by, usually made up of three or four fields.

I have approx 60 tables with about 40 fields in each, with around 60,000 records in each table (In know, I have said about upsizing to SQL server!)

The data changes each month (a new database is created), so I need to compare say April's db to May's db to find any changes, I have tried the above linking and running a SQL query but the overhead is huge - I also have the problem of null fields, example:

April May

Fld1 Fld2 Fld3 Fld1 Fld2 Fld2

Recd1 1 1 1 1 1 1
Recd2 2 1 1 2 1 2
Recd3 3 1 NULL 3 1 NULL

The key index is made up from Fld1 & Fld2

This should tell me that Recd2 has changed, but also tells me that Recd3 has changes as well!

Thank for any ideas (Other that shoot my boss!).

Steve.
[sig][/sig]

 

[swilliams]

I have just mirrored the example you gave in Access and created the query :

SELECT Table1.Fld1, Table1.Fld2, Table1.Fld3, Table2.Fld1, Table2.Fld2, Table2.Fld3
FROM Table1 INNER JOIN Table2 ON (Table1.Fld1 = Table2.Fld1) AND (Table1.Fld2 = Table2.Fld2)
WHERE Table1.Fld3<>Table2.Fld3

and this did not show record3 as changed. There is nothing complicated in this, so I don't know if I am missing something?? (field3 in each of record3 were null)

Simon [sig][/sig]