Delete Records in Sequence 2

[tunsarod]

This going to sound so simple.. but I've just spent more than 2 hours and I haven't solved it yet.

Hope you can...

I have a table of several thousand records with an Autonumber field called RecNum.

I am trying to figure a way of deleteing say the first ( oldest) x number of records. Bearing in mind that some records will already have been deleted manually on an ad-hoc basis and not sequentially - so RecNum values are no longer contiguous.

My purpose is that I am creating a demo version of an application but I want everything except the main data table to operate as normal. With the main table I want to restrict the maximum number of records so that before the new records are imported the equivalent number of old records are automatically deleted. Of course the number of incomming records will be known before the delete process begins.

Thanks in anticipation.
Rod

 

[beetee]

Do you want to do this with queries or with VBA?

Vba shouldn't be much of a problem. What you want to do is to make your autonumber field an index.

Open a recordset using the autonumber field index, walk the table for 'n' records, and delete.

Here's a sample:

Option Compare Database
Option Explicit

Sub DeleteEm(TableName As String, RecordsToKeep As Integer)
Dim MyRst As Recordset
Set MyRst = CurrentDb.OpenRecordset("TestNightCall&quot
' use whatever you called the key on the autonumber field as the index
MyRst.Index = "PrimaryKey"
MyRst.MoveLast
MyRst.MoveFirst

' note: recordcount property not always correct
Dim RecordCount As Long
Do While Not MyRst.EOF
RecordCount = RecordCount + 1
MyRst.MoveNext
Loop

Dim RecordsToDelete As Long
RecordsToDelete = RecordCount - RecordsToKeep

MyRst.MoveFirst
Do While RecordsToDelete > 0
MyRst.Delete
RecordsToDelete = RecordsToDelete - 1
MyRst.MoveNext
Loop

MyRst.Close
Set MyRst = Nothing
End Sub

 

[tunsarod]

Hi Beetee,

and here is what I was using before my post...

Private Sub Command0_Click()
Dim RecCount As Long
Dim DelCount As Integer
Dim rst As Recordset
Dim dbs As Database

RecCount = DCount("RecNum", "DELSCOPY&quot

MsgBox "DELSCOPY contains " & RecCount & " records."

If RecCount > 500 Then
DelCount = InputBox("Enter records to remove &quot

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("DELSCOPY&quot

rst.MoveLast
rst.MoveFirst

Do While DelCount > 0

rst.Delete
rst.MoveNext
DelCount = DelCount - 1

Loop

rst.Close
Set rst = Nothing
Set dbs = Nothing

End If

End Sub

Which I thought would work as RecNum is an indexed field however while the requisite number of records were deleted they were, as if in the twighlight zone, taken apparently at random across the recordset'.

After seeing your post that alerted me to the need for rst.index "RecNum" and now the code does exactly what it says on the tin.

Thanks a lot for your help.
Rod

 

[beetee]

Yes, I agree, I think it's safest to assume you would receive records randomly if you don't specify an index.

FWIW, other people have posted a solution to generating autonumber fields that are guaranteed consecutive, using the DCount function (IIRC). Obviously, this would allow you to make an SQL delete statement.

In any case, glad to be of help.

 

[tunsarod]

Hi Beetee,

Regarding our brief conversation about deleting records in sequence. At the time I was running the code in a form's, module in the same database as the target table and it worked perfectly. But when I have the table in a back-end database and the code in the module of a front-end form I get "Error: 3251 Operation not supported on this type of object."

The error line reads: rst.Index = "RecNum"

In this case RecNum is an Autonumber field, indexed, unique and with no null values. Also as before I am using no optional qualifiers in the OpenRecordset statement. e.g.

Set rst=dbs.Openrecordset("DELSCOPY&quot

Any Ideas?

Regards Rod

 

[beetee]

Ah yes, that's a problem. If the table is a linked table, you cannot set the index property.

You therefore have two options:
1) Open the recordset from an opened database object of the back end database
2) open a query instead of a table
Dim MyRst As Recordset
Set MyRst = CurrentDb.OpenRecordset("Select * from DELSCOPY order by RecNum&quot

 

[beetee]

NOTE that you don't have to specify an index if you open a query.

 

[tunsarod]

Beetee,

Great advice and so simple when you know how.

Thanks a lot,
Rod

 

[zakrocz]

Nice one Beetee, just saved my head from exploding again!!