Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    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.
    Last edited by kutlesh; Jun 28th, 2019 at 02:20 AM.

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,041

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

    sounds like when you Delete (clear) the Table you are not closing the Conection and Recordset
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

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

    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: ADODB.RecordSet lists only the last few rows after deletion and adding new 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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

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

    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.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width