Results 1 to 6 of 6

Thread: Database Update Issue

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Dec 2012
    Posts
    1,470

    Database Update Issue

    I have had this problem for ages and simply learned to live with it. But with all the background processing that comes with Windows 11, it is becoming more prominent. After an addition to or deletion from the database, it is refreshed with this code.
    Code:
        DataGrid1.Col = 0
        '1/2 sec time delay to allow update to finish
        Sleep 600
        Adodc1.Recordset.Requery
        DataGrid1.FirstRow = TopRow
        Adodc1.Recordset.MoveLast
    Is there a way to tell when the database has finished being updated?

    J.A. Coutts

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Database Update Issue

    Read about the options for the Requery-Method.
    especially about adAsyncExecute
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    Addicted Member ISAWHIM's Avatar
    Join Date
    Jan 2023
    Posts
    181

    Re: Database Update Issue

    Does the connection state change to closed, when it is "done"?

    Are you using any of the events?
    Adodc1.Recordset.ActiveConnection.State
    https://learn.microsoft.com/en-us/sq...l-server-ver16
    Sample Code: https://learn.microsoft.com/en-us/sq...l-server-ver16

    These are from .NET, but they might have a VB6 counterpart?!?!
    ADODC.RecordChangeComplete
    ADODC.RecordsetChangeComplete
    ADODC.MoveComplete
    https://learn.microsoft.com/en-us/do...ramework-4.8.1

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Database Update Issue

    From here: https://learn.microsoft.com/en-us/of...ery-method-ado
    If Options is set to adAsyncExecute, this operation will execute asynchronously and a RecordsetChangeComplete event will be issued when it concludes.
    Dim your Recordset WithEvents

    Untested
    Code:
    Dim WithEvents RS As RecordSet
    
    'Somewhere
    Set RS=Adodc1.Recordset
    DataGrid1.Col = 0    
    RS.Requery adAsyncExecute
    
    
    Private Sub RS_RecordChangeComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    DataGrid1.FirstRow = TopRow
    RS.MoveLast
    End Sub
    Last edited by Zvoni; Mar 22nd, 2023 at 02:25 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Dec 2012
    Posts
    1,470

    Re: Database Update Issue

    My apologies, as I may not have described the issue clearly. The problem does not occur with simple additions and deletions, but rather with transferring items to a different dataset. The Access database contains several datasets and is bound to the DataGrid. The items are added to the target dataset and then removed from the current dataset. The transfer does execute correctly, but when the DataGrid is refreshed, the deleted record is sometimes still displayed. A simple switch to view the deleted records and back again fixes it, but it is becoming more frequent now that I am on Windows 11 with all of its background processing.

    J.A. Coutts

  6. #6
    Addicted Member ISAWHIM's Avatar
    Join Date
    Jan 2023
    Posts
    181

    Re: Database Update Issue

    Quote Originally Posted by couttsj View Post
    but when the DataGrid is refreshed, the deleted record is sometimes still displayed. A simple switch to view the deleted records and back again fixes it...
    Sounds like a simple graphical issue, honestly. Are you running the program in any compatibility mode? (That will greatly interfere with the way objects on a form are "refreshed", visually.)

    Did you test to see if the actual "data" is still there? (If it is just not refreshing the "drawing of text", but actually there is physical values in those cells.)

    Do you use the forms autoreredraw setting? Could be an issue with the OCX for the DataGrid that is not getting the refresh callback, or it is just not acting on it.

    Would this help? (Maybe it is not done "updating", before YOU force a refresh.)
    Code:
    Private Sub DataGrid1_AfterUpdate()
        DataGrid1.Refresh
    End Sub
    Real desperation... maybe a DoEvents may be needed? (I'm guessing now)

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