Programmatically deleting all records in a table (1 Viewer)

NebraskaUser · Oct 10, 2018

If I need to delete every record in a table, how do I programmatically do that in VBA? Here's the code I currently have, and it isn't working. It deletes one record, then errs out.

If myR.RecordCount > 0 Then
For RC = 1 To myR.RecordCount
myR.Delete
Next RC
End If

Minty · Oct 10, 2018

This will do it. Don't do it rBar !

Code:

Dim sSql as string

sSql = "Delete * from YourTable"

CurrentDb.Execute sSql

rbar : https://www.red-gate.com/simple-talk/sql/t-sql-programming/rbar-row-by-agonizing-row/

Are you sure you want to do this?

isladogs · Oct 10, 2018

CurrentDb.Execute “DELETE * FROM MyTableName”

Sent from my iPhone using Tapatalk

NebraskaUser · Oct 10, 2018

Hot dog! It worked! Thanks a million!

Gasman · Oct 10, 2018

I believe you would need to move to the next record?

Code:

myR.MoveNext

but the SQL method is obviously the easiest.

NebraskaUser said:
If I need to delete every record in a table, how do I programmatically do that in VBA? Here's the code I currently have, and it isn't working. It deletes one record, then errs out.

If myR.RecordCount > 0 Then
For RC = 1 To myR.RecordCount
myR.Delete
Next RC
End If

Cronk · Oct 10, 2018

The reason why

Code:

If myR.RecordCount > 0 Then
            For RC = 1 To myR.RecordCount
                   myR.Delete
            Next RC
    End If

only deletes one record, is that when the recordset is opened, and it has at least one record, the rccordcount is always 1. (It's necessary to move to the end of the recordset to determine the number of records in the recordset.) Consequently the loop is only executed once.
Code in posts #2 and #3 will empty the table irrespective of the number of records.

isladogs · Oct 10, 2018

NebraskaUser said:
Hot dog! It worked! Thanks a million!

You're welcome. Its also MANY times faster than using a recordset.
If you have a large table, a recordset approach could take several minutes or even hours as each record is deleted one at a time.
A simple DELETE statement takes a fraction of a second as effectively all records are deleted at once

The_Doc_Man · Oct 11, 2018

The only issue to consider after using a "DELETE * FROM ..." is that IF the table was large enough, or if you do it often enough, you might need to do a Compact & Repair every so often in the file that contained the table. Actually, make a COPY of the file first and then work on the copy. If the C&R works, then overwrite the original with the copy (i.e. still have two now identical copies) and then retire one as a backup.

If the table being deleted is relatively small, just monitor the size of the DB file and every time it reaches twice its minimum size, do another C&R cycle.

CJ_London · Oct 11, 2018

with a very large table, even using sql can take a while. An alternative is to create a new table by using transferdatabase and copy the structure only

Use a function something like this

Code:

Function truncTable(TName As String)
 'Warning - this function will remove relationships
    
    DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentProject.FullName, acTable, TName, "Tmp" & TName, True
    DoCmd.SetWarnings False ' to ignore messages about removing existing table
    DoCmd.Rename TName, acTable, "Tmp" & TName
    DoCmd.SetWarnings True
 
End Function

it does remove relationships, so if it matters, you would need code to recreate them.

Also, PK's will be reset to start from 0, this may or may not matter. If it does then you would need code to capture the last PK and reseed.

You can use copy

isladogs · Oct 11, 2018

Hi CJ
I find the DELETE SQL statement is very fast even for large tables

Testing on my large postcodes table that I always use for testing 'extreme cases', I used the following code to check the time taken to delete all 2.6 million records from a table with 45 fields The database is approx 1.6GB, almost all due to this one table

Code:

Sub EmptyPostcodesTable()

    Dim iCount As Long, jCount As Long, dblStart As Double, dblEnd As Double
    
    iCount = DCount("*", "Postcodes")
    dblStart = GetCurrentSystemTime
    
    CurrentDb.Execute "DELETE * FROM Postcodes"
    
    dblEnd = GetCurrentSystemTime
    jCount = DCount("*", "Postcodes")
    
    Debug.Print "Empty Postcodes Table"
    Debug.Print "================"
    Debug.Print "Fields in Postcodes table = " & CurrentDb.TableDefs("Postcodes").Fields.Count
    Debug.Print "Start: Records = " & iCount, "; StartTime = " & dblStart
    Debug.Print "End: Records = " & jCount, "; EndTime = " & dblEnd
    
    Debug.Print "TimeTaken = " & Round(dblEnd - dblStart, 3) & " seconds"

End Sub

NOTE The GetCurrentSystemTime function measures the number of seconds since midnight to millisecond accuracy

Results:

Empty Postcodes Table
================
Fields in Postcodes table = 45
Start: Records = 2616838 ; StartTime = 58673.86
End: Records = 0 ; EndTime = 58673.963
TimeTaken = 0.103 seconds

That seems pretty fast to me! I was doing this direct in the BE file
It would obviously take longer if it was a linked table

By comparison, repopulating the table from a backup takes almost 20 minutes!

Populate Postcodes Table
=================
Fields in Postcodes table = 45
Start: Records = 0 ; StartTime = 57366.148
End: Records = 2616838 ; EndTime = 58539.163
TimeTaken = 1173.015 seconds

The_Doc_Man · Oct 11, 2018

Probably have to repopulate an index? Might go faster if you remove the index, reload the table, and then reassert the index. I know it's not a life-threatening issue, but I recall many times out at the office where reloading a table was better (less down time) if we just killed the index first and rebuilt it later.

isladogs · Oct 11, 2018

Hi Doc
Not sure who you are responding to...

The_Doc_Man · Oct 11, 2018

You, Colin, regarding how long it took to repopulate your test table.

CJ_London · Oct 11, 2018

@Colin, Just analysing your post

to empty the table started at StartTime = 58673.86
to repopulate started as StartTime = 57366.148

i.e. 22 minutes before

So looks like you populated the table first, then deleted everything. So everything would be in a nice order, no fragmentation of data or indexes.

So would be interesting to know whether the timings would be as impressive with a 'day to day' table where data and indexes become fragmented.

isladogs · Oct 12, 2018

Doc / CJ
you both made some very valid points.
The postcodes table does contain several indexes in order to speed up searching such a massive table.

Normally I only need to repopulate the table every 3 months when new data is released by the UK Office for National Statistics. I have tried removing the indexes before repopulating and it is indeed faster but as its not needed that often I just leave it running whilst I do something else

Its effectively a read only table at other times so I don't have to worry about the 1.6GB BE database growing in between updates. There is a second & much smaller BE file with tables that are updated by user actions e.g. downloaded addresses for selected postcodes, nearby places searches etc

Yes i did populate then delete, compacted, then repeated.
For info, times were similar.
As the large postcodes BE doesn't get altered by user action, the data & indexes don't get fragmented.

Following your comments, I've just repeated the tests on a student attendance marks table with 20 fields and 1.33 million records. This is regularly updated each day so is I assume also highly fragmented. This time I emptied first then repopulated. I did each of these 3 times. The code was the same as that in my previous post

Emptying was so fast that the time was recorded as zero twice with 0.016 s in between those two.
Repopulating from a linked SQL table took 112 s then 86s then 88s. The table had only 2 indexed fields at this point

I deliberately didn't compact between tests so the db grew from 248MB to 900MB
See attached text file for results if interested

UPDATE:
I then compacted, indexed a further 6 fields and repeated the tests 3 more times
Deletion times relatively unaffected (0s / 0.164s / 0.031s)
As expected, repopulating times increased significantly (126s / 163s / 127s)
Of course, indexing increased the table size as well so the db grew from 278MB to 1.36GB

Results also attached in 2nd text file

CJ_London · Oct 12, 2018

interesting - I must admit my experience of using delete * on very large datasets has not produced those sort of results - perhaps I've been imagining it.

isladogs · Oct 12, 2018

Remember that the test tables are local.
It would of course be slower, probably much slower, if I had run the tests on linked Access or SQL tables

gemma-the-husky · Oct 12, 2018

I've found that "delete * from tblname" appears to be instantaneous. Maybe not for very big files but still very fast.

Opening a table, selecting all records and clicking delete takes a long time, so the SQL statement must be doing it differently.

isladogs · Oct 12, 2018

I've found that "delete * from tblname" appears to be instantaneous. Maybe not for very big files but still very fast.

Precisely - that's why I suggested it & indeed its what my tests have proved.

The_Doc_Man · Oct 12, 2018

gemma-the-husky said:
I've found that "delete * from tblname" appears to be instantaneous. Maybe not for very big files but still very fast.

Opening a table, selecting all records and clicking delete takes a long time, so the SQL statement must be doing it differently.

Perhaps because the "DELETE * FROM whatever;" is treated as SQL, but opening the datasheet view is actually a hidden form. Doing a select all for that form might require something so inelegant as having hidden code do a loop to check if each record was selected and then doing the deletion piecemeal, whereas SQL always does what it does wholesale.

The previous paragraph is a two-part surmise, but the part about opening a hidden form is not a guess. The part about stepping through records to check if each was selected IS a GUESS! But it fits the facts. Remember, you could have selected a subset of records via click, move cursor, shift-click, delete-key. The non-selected records would stay; the selected ones would vanish. So there has to be some interaction with that implied form that it can detect selected vs. non-selected.

Programmatically deleting all records in a table (1 Viewer)

Registered User.

AWF VIP

MVP / VIP

Registered User.

Enthusiastic Amateur

Registered User.

MVP / VIP

Immoderate Moderator

Super Moderator

MVP / VIP

Immoderate Moderator

MVP / VIP

Immoderate Moderator

Super Moderator

MVP / VIP

Attachments

Super Moderator

MVP / VIP

Super Moderator

MVP / VIP

Immoderate Moderator

Similar threads

Users who are viewing this thread