Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update delay with cn.Execute (VB6/Access 2K)

    I have a form where I execute changes using the cn.Execute command, then call a function that repopulates a combo box (it is an after market control that doesn't have a refresh apparently).

    Everything works, there just seems to be a delay between the changes take effect from the Update query against the database and the time the data is available to the function that requeries the ComboBox. For example, I run the following:

    cn.BeginTrans

    cn.Execute ("UPDATE tblPath SET CreateDate = " & "#" & oFile.DateCreated & "#" & ", " _
    & "Path = '" & Left$(oFile.Name, Len(oFile.Name) - 4) & "', " _
    & "StoredPath = '" & oFile.Path & "' " _
    & "WHERE MRNumber = '" & TDBMRN.Text & "' And StoredPath = '" & padQuotes(SysDefaults.DocRoot & "" & TDBMRN.Text & "" & gsFileName & ".PDF") & "'"), , adCmdText


    cn.CommitTrans

    I put in the Begin and Commit Transaction commands thinking that might speed it up. But when I run this, then run my ComboRepopulate routine, it populates the non-updated values in my combo box. If I call the function after a few seconds I get the updated data. It is an extremely small transaction against a database, updating only one record of about 200.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update delay with cn.Execute (VB6/Access 2K)

    Is the combobox using the same connection? Is the connection pooled? From your description, I would suspect not. It sounds like the write is being committed but to a cache that isn't actually written to the disk immediately.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update delay with cn.Execute (VB6/Access 2K)

    Hi Charlotte!

    Well, I was about to paste in a bunch of code and type you an "I don't see how" message - then remembered who I was talking to. I looked at the connection objects everywhere and found that I had some old connections out there that I forgot to change over. I changed everything to the same connection and It appears to be working now.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update delay with cn.Execute (VB6/Access 2K)

    Wonderful! I love taking a guess and having it come up aces. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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