Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: Update query updates just SOME fields in a record

    Hi all,

    I wonder if anyone else has ever detected this behaviour.
    I experienced it in Access 2000. The database is split. The backend is on the server, the frontend is installed on 10 PCs. Using cabled LAN for connection. Note that the backend data are not in a single file, instead are divided in different files; the frontend links to their tables.
    The oddity I have witnessed is that in some very few occasions when executing from VBA an update query only SOME fields of a record are updated while others are not.

    Here is an example:
                DoCmd.SetWarnings (False)
                DoCmd.RunSQL "UPDATE myTable1 SET myField1=0, myField2=0 WHERE IDrec=" & Me.IDRec
                DoCmd.SetWarnings (True)
    Will work almost every single time setting both fields to 0. But i have found also that at least once it failed on myFields2 while updating myField1.
    I have found the same problem on a different table and with slightly different VBA code.
    I didn't think this was possible in Access.

    Maybe related: we are experiencing problems with the network and are working on it. Sometimes we have a "Disk or network error" and the frontend can't find the backend anymore for periods from a few seconds to a few minutes. In these cases though we had whole procedures to fail. Records would either be updated or not. I have never seen though that a record was only partially updated.

    I'm writing code to check after the update but would like to know if anyone experienced the same.
    Any suggestion is welcome.

  2. #2
    Join Date
    May 2016
    Provided Answers: 4
    Hi DDD_487

    Perhaps a track to not use links between end & front database

    Private Sub Form_Load()
             Dim strPathDb As String, strDbName As String, strRsetTable As String, strBackEndLocation As String, strDBPSW As String
             strPathDb = Application.CurrentProject.Path & "\"
             strDbName = "test_be.accdb"
             strBackEndLocation = strPathDb & strDbName
             strDBPSW = ""
             Me.RecordSource = "SELECT * FROM tblTest IN'' [MS Access;PWD=" & strDBPSW & ";database=" & strBackEndLocation & "]"
         end sub

    Here the threat complet

Posting Permissions

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