CODEWITHSUNDEEP
sqlms-accessvba
user2395059
user2395059

Reputation: 80

Delete records if an updated record exists

I have an access database containing records pulled in from csv files which are published daily. From time to time, an existing record is updated. My current database is of the form

... ID ... Date on which the csv file was created
     1             26/05/2013
     1             27/05/2013
     2             27/05/2013
     3             26/05/2013
etc.

How do I get Access to delete the older record in VBA?

Upvotes: 1

Views: 816

Answers (3)

HansUp
HansUp

Reputation: 97101

Create a DMax expression which returns the most recent creation_date for each ID.

DMax("creation_date", "YourTable", "ID=" & <current ID>)

Then use that expression in a SELECT query to identify the rows which you want discarded.

SELECT
    y.ID,
    y.creation_date,
    DMax("creation_date", "YourTable", "ID=" & y.ID)
FROM YourTable AS y;

Once you have the SELECT query working correctly, use its working DMax expression in a DELETE query.

DELETE FROM YourTable
WHERE creation_date < DMax("creation_date", "YourTable", "ID=" & [ID]);

Upvotes: 2

Tom Collins
Tom Collins

Reputation: 4069

If you want to delete all the older records with duplicate IDs at once, you can just execute this SQL.

However, it will require that you have a primary or unique field. I added a field called RecID that's just an autonumber field.

strSQL = ""
strSQL = strSQL & "DELETE " & vbCrLf
strSQL = strSQL & "  t.recid, " & vbCrLf
strSQL = strSQL & "  t.* " & vbCrLf
strSQL = strSQL & "FROM t " & vbCrLf
strSQL = strSQL & "WHERE  t.recid NOT IN (SELECT Last(t.recid) AS LastOfRecID " & vbCrLf
strSQL = strSQL & "                       FROM   t " & vbCrLf
strSQL = strSQL & "                       GROUP  BY t.id)"

Then you just do a Currentdb.Execute(strSQL)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Have you tried using a delete with a where clause?

delete from t
     where exists (select 1 from t t2 where t2.id > t.id)

I'm not absolutely positive that Access lets you select and delete from the same table. If not, then you can put the ids in a temporary table and delete using that.

To be honest, I'm not thrilled with the idea of deleting the records directly. Try creating a new table with the records you want:

select *
from t
where exists (select 1 from t t2 where t2.id > t.id)

This should work, assuming you have an index on id. Then delete the original table and replace it with the new one after you have validated that the right records are in it.

Upvotes: 1

Related Questions