Need help deleting large records by chunk

[praetorianprefect]

I have a table that is over 50000. When I do a delete query it takes a while, is there a way that I can delete records by 1000 at a time?

[June7]

Is there a unique identifier field such as an autonumber? Apply filter criteria.

Is deleting 50000 records a regular process - why?

[praetorianprefect]

Is there a unique identifier field such as an autonumber? Apply filter criteria.

Is deleting 50000 records a regular process - why?

It is a daily process, new records are added every day and information changes every day.

[JoeM]

I find running it from a Macro and suppressing the warnings goes much faster. I used the RunSQL action, i.e.

DELETE [TableName].* FROM [TableName];

Note that if you are deleting that many records every day, be sure to Compact and Repair your database often so the size does not get bloated and you run out of space.

[June7]

Or in VBA:

CurrentDb.Execute "DELETE FROM [TableName]"

Since entire record is deleted, there is no need to specify fields, not even with wildcard.

[praetorianprefect]

Or in VBA:

CurrentDb.Execute "DELETE FROM [TableName]"

Since entire record is deleted, there is no need to specify fields, not even with wildcard.

How would I delete 2000 records at a time?

[June7]

Is there a unique identifier? An autonumber field might serve. Something like:

intRecs = DCount("*", "tablename")
If intRecs>0 Then
dblGrps = intRecs / 2000
dblGrps = dblGrps + IIf(dblGrps - Int(dblGrps) > 0, 1, 0)
x = 1
For n = 1 to dblGrps
CurrentDb.Execute "DELETE FROM [TableName] WHERE ID BETWEEN " & x & " AND " & x + 1999
x = x + 2000
Next
End If

Really should figure out why delete takes so long. I just tested deleting over 66900 records and it was faster than a blink. Is your database split and backend on a file server?

[praetorianprefect]

Is there a unique identifier? An autonumber field might serve. Something like:

intRecs = DCount("*", "tablename")
If intRecs>0 Then
dblGrps = intRecs / 2000
dblGrps = dblGrps + IIf(dblGrps - Int(dblGrps) > 0, 1, 0)
x = 1
For n = 1 to dblGrps
CurrentDb.Execute "DELETE FROM [TableName] WHERE ID BETWEEN " & x & " AND " & x + 1999
x = x + 2000
Next
End If

Really should figure out why delete takes so long. I just tested deleting over 66900 records and it was faster than a blink. If your database split and backend on a file server?

Yes, my database is split. The backend is on sharepoint. When I manually delete, select a number of rows, it's quicker than selecting all rows and then deleting it.

[June7]

Never used SharePoint. Guess that explains why slow.

Another possible option for the delete SQL.

DELETE FROM TableName WHERE [uniqueID field] IN (SELECT TOP 2000 [uniqueID field] FROM TableName)

[praetorianprefect]

How can the code below be translated into VBA?

Code from: https://www.virtualobjectives.com.au...arge_table.htm

-- Deleting records from a large table.

DECLARE @ROWCNT INT , @NUMROWS INT, @TOPNUM INT
SET @ROWCNT = 0
SET @NUMROWS = 0
SET @TOPNUM = 100000 --<--- Set this to the number of rows to delete per batch.

WHILE 1 = 1
BEGIN
PRINT CONVERT(varchar(19), GETDATE(), 120) + ' Start'

DELETE TOP (@TOPNUM)
FROM YourTable
WHERE SomeField = xxx

SET @NUMROWS = @@ROWCOUNT
PRINT 'Deleting ' + STR(@NUMROWS) + ' records.'
SET @ROWCNT = @ROWCNT + @NUMROWS
IF @NUMROWS = 0 BREAK
PRINT CONVERT(varchar(19), GETDATE(), 120) + ' Finished.'

-- A delay will help prevent locking/blocking if required.
-- WAITFOR DELAY '00:00:03'
END
PRINT 'TOTAL: ' + STR(@ROWCNT) + ' Records Deleted.'

[June7]

Only way i know to calc a row counter is with DCount()