Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can not update Records 3197 error (Access 2003)

    I have a distributed front-end MS/Access database that users enter and update items to backend tables (on a LAN) and then add any new entries and update any changes to an Oracle table.

    The records in Oracle are updateable through the Web for a few fields that are not updateable from MS/Access. (I use OpenRecordSet and update the fields via code to prevent updating these fields by the users of the MS/Access database.)

    This is working well except for two incidences.

    My problem is that on two different occasions I have gotten this error when I try to update the items to Oracle:
    "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. (Error 3197)"

    The records are not locked by Oracle and can be updated via PL/SQL or the WEB.

    I have used the 'Compact and Repair' option and tried the Jet compact utility on both the Front-end and Backend. Neither of which worked to correct the problem.
    I dropped (deleted the links to the Oracle table and re-linked.)

    I was able to delete the items from Oracle and then Add these back from my upload process without any corrections to the data.

    Frontend has links to the backend tables and ODBC links to the Oracle tables.

    Does anyone have an idea of what might be causing this to happen and is there a way to reset the records?
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can not update Records 3197 error (Access 2003)

    Compacting the Access frontend or backend will have no effect on Oracle. My guess would be that a recordset object on one of the Oracle tables was left open.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can not update Records 3197 error (Access 2003)

    Thanks Hans,

    Here is my code. Do you see where I might have missed my drop of the open record set? (There were no errors and I have sinced moved my close to the exit area.)
    Here is the code I was using.
    Private Sub UpdtMstr()
    On Error GoTo Err_UpdtMstr

    Dim rsProd As DAO.Recordset
    Dim rsPreProd As DAO.Recordset
    Dim GSClosedStat As Long
    Dim rsProd_Open As Boolean
    Dim rsPreProd_Open As Boolean

    ' point to the two tables.
    Set rsProd = CurrentDb.OpenRecordset("LRP_ART_VII_MASTER") ' Oracle Table
    rsProd_Open = True
    Set rsPreProd = CurrentDb.OpenRecordset("qry_Changes_to_Production _Master")
    rsPreProd_Open = True

    GSClosedStat = DLookup("id", "tbl_ART_VII_Datalist", _
    "TYPE = ""GCS"" AND CODE_INITIALS = ""CLOSED""")

    If rsPreProd.EOF Then
    ' do nothing
    Else
    rsPreProd.MoveFirst
    rsProd.MoveFirst
    Do Until rsPreProd.EOF
    If rsProd!ID = rsPreProd!ID Then
    rsProd.Edit
    rsProd!UNIT = rsPreProd!UNIT

    (... Rest of fields to move to Oracle Item)

    End If
    rsProd.Update
    rsPreProd.Edit
    rsPreProd!MS_UPDATE_VERSION = rsProd!MS_UPDATE_VERSION
    rsPreProd!Changed = False
    rsPreProd.Update
    rsProd.MoveFirst
    rsPreProd.MoveNext
    Else
    rsProd.MoveNext
    If rsProd.EOF Then
    rsProd.MoveFirst
    rsPreProd.MoveNext
    End If
    End If
    Loop
    rsPreProd.Close
    rsProd.Close
    Debug.Print "UpdateMaster Closed"
    End If
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can not update Records 3197 error (Access 2003)

    The code as posted is inconsistent - there's an Else without a corresponding If, and the recordsets appear to be closed only in the Else part of the outermost If ... Else ... End If

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can not update Records 3197 error (Access 2003)

    Oops!

    I tried to remove some code and missed didn't get it all.

    I have attached a text file with the entire Function.

    This happened again last night.
    Attached Files Attached Files
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can not update Records 3197 error (Access 2003)

    I don't see anything really wrong with this code. I'd change

    Exit_UpdtMstr:
    If rsPreProd_Open = True Then
    rsPreProd.Close
    Set rsPreProd = Nothing
    End If
    If rsProd_Open = True Then
    rsProd.Close
    Set rsProd = Nothing
    End If
    Exit Sub

    to

    Exit_UpdtMstr:
    If rsPreProd_Open = True Then
    rsPreProd.Close
    End If
    Set rsPreProd = Nothing
    If rsProd_Open = True Then
    rsProd.Close
    End If
    Set rsProd = Nothing
    Exit Sub

    but that's not essential. In other words, I don't have an explanation or solution for you. Sorry.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can not update Records 3197 error (Access 2003)

    Thank you Hans. This is a real head scratcher.

    Oh and Best Wishes of the Holiday Season to you and your family.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can not update Records 3197 error (Access 2003)

    Thank you, and best wishes to you and yours too!

Posting Permissions

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