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. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #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

  4. #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.

  5. #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
  •