ADODB.RecordSet lists only the last few rows after deletion and adding new rows?

I have a table which has 40000 rows. I need to take all of them and run check on one particular column(filed).
I am fetching them inside a RecordSet and after I run the check, the ones which contain wrong value are copied into a separate local table inside Access 2000 environment.

Lets call this table WrongPersonID.

So I am running first, clear(DELETE all rows) the table WrongPersonID, Add(INSERT) on all those records which contain wrong preson ID, and then fetch(SELECT a page) a portion of the records in WrongPersonID into a DataGridView.

It works all well the first try. I can browse the wrong IDs with paging, i.e take first 25 rows, then next 25 rows, etc...

However, if I run all these steps again, clear table WrongPersonID, find all wrong records and add them to WrongPersonID, and then SELECT first page(25 rows), it doesn't work, I don't see the first page fetched in the DataGridView. The strange thing is, this code is fetching only the last page, so I can see the last page in my DataGridView.

As if somehow the "row pointer" points to the last part of WrongPersonID so that's why I see the last page only.

Can somebody tell me what is going on?

Also, can somebody tell me what sort of query do I send to ADODB.RecordSet to open it for inserting data? I send a "SELECT" query, but for inserting is it the most appropriate?

The code is working. I can put code snippet, but it would be too large to cover all those steps.

EDIT:

Tried MoveFirst() but get this error:

Code:

---

MoveFirst(): Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

---

sounds like when you Delete (clear) the Table you are not closing the Conection and Recordset

Turns out, I needed to use a function that exists in MS SQL server, and its called "truncate". It deletes all rows but also resets the AutoIncrement field.

I did this in Jet SQL aka SQL in Access 2000:

Code:

---

        Try
            Dim sqlString  as String

            sqlString = "DELETE FROM MyTable; "
            adodbConn.Execute(sqlString)

            sqlString = "ALTER TABLE MyTable ALTER COLUMN ID COUNTER (1, 1);"
            adodbConn.Execute(sqlString)

        Catch ex As Exception
            Dim msg As String = New StackTrace(ex).GetFrame(0).GetMethod().Name & "(): " & ex.Message
            Console.WriteLine(msg)
            Me.statusBarTB.Text = msg
        End Try

---

which deletes all rows but also resets the auto increment field ID.

Now the query finds the appropriate rows. ;)

Sounds like the real problem is that you're relying on the ID to do your paging rather than counting records.
Also those SQL commands are essentially a DROP/CREATE table... same net effect.

-tg

Yes, if I could get a certain page of the table without altering, that would be nice. But I think there is not simple way to do that in Access 2000, unless you have a column which is AutoNumber.

Actually, what you really need is a sequential number in your query so that you can page your results.
Here's one option
https://access-excel.tips/add-auto-n...-access-query/

There's multiple options here...
#7 is interesting
https://accessexperts.com/blog/2015/...ing-in-access/
NOTE - option #6 is essentially what you're doing... please read the notes of concern in there regarding bloat.

Google query - https://www.google.com/search?q=auto...tion+in+access

-tg