Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Unanswered: SQL Update not effective immediately?

    Hi folks

    Getting puzzled by some sort of timing problem here... using Access 2007.

    I have a form which does timed backups for me. I'd like my user-friendly front-end to be able to show a warning if a backup has failed (or any of some other conditions - not coded yet!), so a user can fetch an admin to look into it.

    In the code belonging to the backup form, I have something like...

    Code:
    If BackupOK Then
      MySQL = "UPDATE tblSettings SET tblSettings.ErrorBackupFailed = '0'"
    Else
      MySQL = "UPDATE tblSettings SET tblSettings.ErrorBackupFailed = '-1'"
    End If
    DoCmd(dot)RunSQL MySQL
    
    UpdateWarning
    The last line is a reference to a sub in a module...
    Code:
    Public Sub UpdateWarning()
    ...
    ProblemBackup = DLookup("ErrorBackupFailed", "tblSettings", "ID=1")
    tblSettings only has one record, BTW.

    On the face of it this all works ... the code in the backup form correctly updates the table, I can look at it manually and see "ErrorBackupFailed" set or cleared as expected.

    But, when the code continues to call UpdateWarning, the DLookup always returns "False", even if I've just posted "True".

    Odder still .... or perhaps not odd at all ... if I then call exactly the same UpdateWarning by a call from a button-click on another form, it will correctly read "True" if that's what I've just posted.

    I'm guessing that this is some sort of race condition - I'm reading too quickly? Something not being saved before I come to read it back?

    I did try duplicating the call to UpdateWarning in case it was something to do with the second read, but no joy - still only got "False" back within the backup form, still read correctly when called from elsewhere later.

    The backup form is not bound to tblSettings or any other table.

    Any clues greatly appreciated.

    TIA
    Last edited by CeejayDBF; 10-06-11 at 13:44. Reason: added detail

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Just a wild guess:
    Code:
    If BackupOK Then
      MySQL = "UPDATE tblSettings SET tblSettings.ErrorBackupFailed = '0';"
    Else
      MySQL = "UPDATE tblSettings SET tblSettings.ErrorBackupFailed = '-1';"
    End If
    CurrentDb.Execute MySQL, dbFailOnError
    DoEvents
    UpdateWarning
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Posts
    88
    Sinndho

    Thanks for the wild guess, but it made no difference.

    Any other ideas, anyone?

    TIA

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    One cannot win every time!

    Is tblSettings a local or an attached table?
    Have a nice day!

  5. #5
    Join Date
    Mar 2010
    Posts
    88
    Indeed not!

    tblSettings is a local table.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I cannot test this right now, but

    tblSettings.ErrorBackupFailed = '0'

    and

    tblSettings.ErrorBackupFailed = '-1'

    doesn't look right. If ErrorBackupFailed is a Boolean Field (which I'm assuming it is, since you also refer to it as being True or False) the 0 and -1 are Integers, while your syntax, with the single quotation marks, indicates that they are Text. I suspect this may be causing your problem.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Mar 2010
    Posts
    88
    Linq

    thanks for the response but that's not it ... SQL is quite happy with those quote marks, I can see that the table value is being correctly set (by looking in the datasheet view of tblSettings).

    Just for interest, I did remove the single quotes and at first appearance nothing seems to have changed - my problem isn't fixed, anyway.

    Thanks

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is really strange. Here is the code I used for testing:
    Code:
    Sub TestBackupOK()
    
    ' tblSettings is a local table defined as:
    
    '  Column Name        | Data Type
    ' --------------------+-----------------------
    '   ID                |  Number (Long Integer)
    '   ErrorBackupFailed |  Boolean (Yes/No)
    '
        Dim BackupOK As Boolean
        Dim MySQL As String
        Dim i As Integer
        
        For i = 1 To 100
            BackupOK = CBool(i Mod 2)
            If BackupOK Then
              MySQL = "UPDATE tblSettings SET tblSettings.ErrorBackupFailed = '0';"
            Else
              MySQL = "UPDATE tblSettings SET tblSettings.ErrorBackupFailed = '-1';"
            End If
            CurrentDb.Execute MySQL, dbFailOnError
            DoEvents
            UpdateWarning
        Next i
        
    End Sub
    
    Public Sub UpdateWarning()
    
        Debug.Print DLookup("ErrorBackupFailed", "tblSettings", "ID=1")
    
    End Sub
    As expected, the Immediate window displays a succession of 0 and -1 when the code is run.
    Attached Thumbnails Attached Thumbnails ScreenShot001.jpg  
    Have a nice day!

  9. #9
    Join Date
    Mar 2010
    Posts
    88
    Sinndho

    Strange indeed ... your test is a fair one. My guess is that there is something odd going on between the different forms and modules in my real application (I have many tables, forms and modules in the real thing!). So I guess I'll just have to see if I can construct a really simple model that shows the problem .... this might take a while, but if anyone has any more ideas in the meantime I'd be very happy to hear them!

  10. #10
    Join Date
    Mar 2010
    Posts
    88

    Sorted

    Well, you won't be surprised to hear that the source of the problem was a stupid error by me!

    Many thanks for suggesting some things to keep me moving and checking stuff. I did a bottom-up model and of course it worked fine. So then I went back to the real thing and started to do some hard tracing of what was going on ... eventually found that UpdateWarning had an error which I was inappropriately suppressing ... and that the error was because tblSettings doesn't have an ID field (it only has one record, why would it need one?) - so no surprise that the Dlookup failed. How or why it worked when I called it the second time, from another routine, is beyond me.

    Anyway, fixed now, so thanks to all.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome, glad you could fix it!
    Have a nice day!

Posting Permissions

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