delete Records except the first

**gstylianou** · 06-01-2014, 05:27 AM

Hello everyone,

Anyone know how its possible to delete all records from one table except the first; Is that possible?

Regards

**Geo21** · 06-01-2014, 06:13 AM

In the private sub type KeepFirst which will perform the below function. Have in mind Order By is very important as it will sort the records accordingly. I put ID as an example. You should put the field where the minimum value will not be deleted.
Also change TableName to Actual Name of the Table.

Code:

Public Function KeepFirst() As Boolean
Dim db as DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT TableName.* FROM TableName"
strSQL =  strSQL + " ORDER BY TableName.ID"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
      If Not (.BOF And .EOF) Then
           .MoveLast
           While .RecordCount &gt; 1
                .Delete
                .MoveLast
           Wend
      End If
      .Close
End With
Set db = Nothing

End Function

**ItsMe** · 06-01-2014, 08:09 AM

You might get a little better performance using the DESC keyword in the SQL

strSQL = "SELECT TableName.* FROM TableName"
strSQL = strSQL + " ORDER BY TableName.ID"

to
strSQL = "SELECT TableName.* FROM TableName"
strSQL = strSQL + " ORDER BY TableName.ID DESC"

Then the loop would like:

Code:

      If Not (.BOF And .EOF) Then
           .MoveFirst
           While .RecordCount &gt; 1
                .Delete
                .MoveNext
           Wend
      End If

**Geo21** · 06-01-2014, 08:28 AM

Its me, with your way, is it MoveLast or MoveFirst?

**ItsMe** · 06-01-2014, 10:03 AM

Sorry, movefirst. I will edit.

**June7** · 06-01-2014, 11:14 AM

A non-VBA approach:

DELETE FROM tablename WHERE ID<>DMin("ID","tablename");

That SQL can be a query object or can run it in VBA with the CurrentDb.Execute method.

Or your first record could be determined by a date value.

**gstylianou** · 06-02-2014, 01:37 AM

Good morning from Cyprus,

Thanks all about your help. I tried the vba code of Geo21 and works fine.!

Thanks all again

**Geo21** · 06-02-2014, 03:13 AM

Kyprios, e?

delete Records except the first

Thread Tools