A quicker way yo delete thousands of records? (1 Viewer)

[Howlsta]

Hi Access Afficionado's,

I'm trying to knock out some records like so in VB:

sSQL = "DELETE * FROM [Attendant circumstances] WHERE (Year([Date]))LIKE " & MyYear
g_DataConnection.Execute sSQL
sSQL = "DELETE FROM [Casualty Details] WHERE [Casualty Details]![Crash Reference] NOT IN (SELECT [Crash Reference] FROM [Attendant circumstances])"
g_DataConnection.Execute sSQL
sSQL = "DELETE FROM [Vehicle Details] WHERE [Vehicle Details]![Crash Reference] NOT IN (SELECT [Crash Reference] FROM [Attendant circumstances])"
g_DataConnection.Execute sSQL
sSQL = "DELETE FROM [PNT_ACCS] WHERE [PNT_ACCS]![PointID] NOT IN (SELECT clng([Accident Key]) FROM [Attendant circumstances])"
g_DataConnection.Execute sSQL

is there a quicker way of taking out the records using VB for instance an SQL statement in a similar style to a delete query to take them all out. The guy who is going to use the program will be deleting about 30k records at a time and the above code will do it, but due to the amount of deletions needed it keeps 'not responding' is there any quicker code? It takes ages!!!!

So, to clarify, each table has an accident key; when all the records from a certain year are deleted from attendant circumstances, I delete all the orphaned records from the other tables i.e. records which have an accident key that no longer exists in attendant circumstances.

thanks,

Rich

 

[Oldsoftboss]

Just a thought, some expert help may clear this up.
If your tables are related and referencial integrity is selected I think there is a checkbox for 'cascade delete related records'.
Wouldn't this take care of all the related data in the related tables once the record in the main table is deleted ?
Dave

 

[Pat Hartman]

Some additional observations:

1. The only time you should use the Like operand is when you are supplying part of a TEXT field. So, SomeField Like "abc*" is proper usage but SomeField Like "abc" is not. Like should also never be used for numeric or date fields. It can produce erroneous results since it is intended to be used only on text fields.

2. Left joins are more efficient than sub-queries.

 

[Howlsta]

Sadly, the cascade delete is not applicable to one of the tables as the linked field is number in one and text in PointID, this might seem odd and it does to me, that's how the tables were created. That particular table is used with MapX as it contains grid references apparently it has to be text. Could do the cascade delete on the other tables, the bloke who wants to do the deletion has not got the relationships set up, but if I give him the instructions then he should be able to do it. Thing is it needs to be as easy for him as possible i.e. by just pressing a button to run the code. I've sent him the 'slow' code anyway hopefully it will be quicker on his PC.

Pat, I got rid of the LIKE and changed it to the equals operator, thanks.

Rich

 

[Howlsta]

How would the left join work do you reckon this would do it

DELETE [Casualty details].*, [Vehicle details].*, [Attendant circumstances].Date
FROM ([Attendant circumstances] LEFT JOIN [Casualty details] ON [Attendant circumstances].[Accident Key] = [Casualty details].[Accident Key]) LEFT JOIN [Vehicle details] ON [Attendant circumstances].[Accident Key] = [Vehicle details].[Accident Key]
WHERE ((([Attendant circumstances].Date)=#1/23/2001#));

because of the data type probs would this bit work if added, not sure about the CIng syntax

LEFT JOIN [pnt_accs] ON CIng([Attendant circumstances].[Accident Key]) = [pnt_accs].[PointID]

how much quicker would it be, the top bit looks alright in the query view, it seems to be selecting the correct records. Haven't got time to try it now, will have to do it tomorrow. Yes, I have to work on Xmas eve ):

BTW the tables have up to 185,000 records in them aaarrrgghh!