Hello everyone,
Anyone know how its possible to delete all records from one table except the first; Is that possible?
Regards
Hello everyone,
Anyone know how its possible to delete all records from one table except the first; Is that possible?
Regards
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 > 1 .Delete .MoveLast Wend End If .Close End With Set db = Nothing End Function
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 > 1 .Delete .MoveNext Wend End If
Its me, with your way, is it MoveLast or MoveFirst?
Sorry, movefirst. I will edit.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Good morning from Cyprus,
Thanks all about your help. I tried the vba code of Geo21 and works fine.!
Thanks all again
Kyprios, e?