Need Clarification

[prabhafriend]

I'm analyzing a Historical code by anonymous:

Dim strSQL As String
Dim qrd As QueryDef
Dim sQryName As String
Dim sTemp As String

sQryName = "temp" & Format(Time, "hh:mm:ss")
sTemp = "SELECT TOP 4000 * FROM " & strTableName & ";"
Set qrd = db.CreateQueryDef(sQryName, sTemp)
strSQL = " DELETE * FROM " & sQryName & ";"

Do
db.Execute strSQL, dbFailOnError
Loop Until db.RecordsAffected = 0

I have confusion in whether the user is deleting only the top 4000 records in strTableName or He is deleting all the records in the table but 4000 records at a time. If that's the case why can't he use "DELETE * FROM StarTableName;" Directly. Kindly explain.

[L@ja]

hi,
may this separeted deletion not blocking the tbl so long time...
if another usr or function put records to this tbl very often...
:think:

so if you have to clear this tbl time to time without block the writer routin... you may write same kind of code...

[CreganTur]

That code is... interesting to say the least.

CreateQueryDef part of the code is actually creating a query object. It's just creating a query that shows the top 4000 records.

This code really makes no logical sense. Yes, it is being used to delete 4000 records at a time, but there is no reason to create multiple query defs against the same table that all pull the top 4000 records! As soon as you close and open the query after the deletion, you'd see the top 4000 records anyway, as long as records exist in the referenced table.

I have no idea why someone would do this- it doesn't make any sense.

L@ja's suggestion is invalid in the sense that if you are performing this deletion routine while people are entering new records into the table, then you're insane because there are (as far as we know) no checks to make sure the new records aren't being deleted in this strange bulk deletion process.

This is just bad, bad, bad design. Scrap this code and find out what the actual process needs to be.