• Error in Procedure (3164) Field can't be updated

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Error in Procedure (3164) Field can't be updated

    • This topic has 15 replies, 3 voices, and was last updated 15 years ago.
    Author
    Topic
    #469025

    Hi there,

    In the process of testing a conversion from Access2007 to SQLServer2008 I am getting the following error –
    “Error in Procedure AddEndorsement (3164) Field can’t be updated”
    in the Front End from the stated procedure which adds a new transaction to a table based on previous data contained therein. When adding an Endorsement, the procedure copies the Master Policy Ref and a variety of other details pertinent to that risk and posts these along with an incremented sub policy ref field (i.e. C00782AOO/05) and the updated/additional premium amount. The table in question has an autonumbered ID field which increments when the new record is posted.

    Below is an extract of code and the point where the error occurs. I realise what I have posted here may not be that descriptive or detailed and quite vague, so please advise any additional detail I should post here.

    Thanks and regards,

    Niven

    Partial code extract from the offending procedure:-

    Code:
    [b]   ' Add Policy Record.
        Dim cmd As New adodb.Command
        Dim old As New adodb.Recordset
        
        With cmd
            .ActiveConnection = CurrentProject.Connection
            .CommandText = strQuery
            .CommandType = adCmdTable
            .Parameters.Refresh
            .Parameters("[prmTSMRef]") = strTSMRef
        End With
        
        old.CursorType = adOpenStatic
        old.Open cmd
        If old.RecordCount  1 Then
            wrk.Rollback
            old.Close
            DoCmd.Hourglass False
            MsgBox "Unable to update transaction, as transaction record count for: " & strTSMRef & " is  1", vbExclamation
            Exit Sub
        End If
        
        ' Take a copy of the old Facility Ref
        strOrigFacRef = old("Facility Ref")
        
        ' We need the incept date for the attacment date, when it's open market.
        dteIncept = old("Incept")
    
    
        Set ins = db.OpenRecordset("tblPolicy", dbOpenDynaset, dbSeeChanges)
        Dim strtest As String
        ins.AddNew
        
        ' First copy orig record.
        Dim fld As adodb.Field
        For Each fld In old.Fields
            If fld.Name  "id" Then
                ins.Fields(fld.Name).value = fld.value
    
            End If
            
        Debug.Print fld.Name; fld.value
            
        Next fld[/b]
    
     [img]http://windowssecrets.com/forums/images/smilies/excl.gif[/img] *****It gets to the the last field ok, but fails when when the loop finishes and tries to move to the next stage:-
    
     [b]   ' Set any fields different than the originals record.
        ins("Facility Ref") = strFacRef
        ins("Written") = Now()
        ins("Original LPSO No") = Null
        ins("Original LPSO Date") = Null
        ins("Comments") = ""
        ins("Status") = "W"
        ins("Premium Due Date") = Null
        ins("Incept") = dteIncept
        ins("Created") = dteNow
        ins("Updated") = dteNow
        
        ins.Update
        ins.Close
        old.Close[/b]
    Viewing 8 reply threads
    Author
    Replies
    • #1224981

      Hi there,

      In addition to the above, I have read through the “Possible Post-Migration Issues” in the SSMA help and there is a section about auto increment fields and RecordSet.AddNew in Jet, which I think relates to my problem. However, I have tried the suggested fix:-

      [indent]Recordset.Update
      Recordset.Move 0,
      Recordset.LastModified [/indent]

      and still have problems. For the following, Recordset is replaced by “Ins” as that is what is in my code.

      The first problem I think is to do with the above syntax and the 0 having a comma after it.
      The second is I get a compile error stating “Invalid use of property” error message surrounding the LastModified statement (having removed the comma).
      The third is I have tried adding Ins.Book = Ins.LastModified, but I get another error message stating “Error in Procedure: AddEndorsement (3146) ODBC–Call Failed.” before it reaches this line and occurs when leaving the Ins.AddNew statement.

      I’m a bit stumped as to what to do next, so if anyone has any ideas or solutions I would be most grateful.

      Cheers

      Niven

    • #1225142

      I’m not quite sure where your error is occurring.

      In your first post you say it gets to the last field in the loop OK then errors. On which line does the error occur?

      If you put the ins.update immediately after the Next fld does the update work OK?

      You might like to check that the fields you are setting to Null actually allow Nulls.

      Note – when you post you can use the button that looks like to insert code, keeping its format intact so it’s easier to read.

      • #1225341

        I’m not quite sure where your error is occurring.

        In your first post you say it gets to the last field in the loop OK then errors. On which line does the error occur?

        If you put the ins.update immediately after the Next fld does the update work OK?

        You might like to check that the fields you are setting to Null actually allow Nulls.

        Note – when you post you can use the button that looks like to insert code, keeping its format intact so it’s easier to read.

        Kent,

        Many thanks your reply, have edited my original post to make the code more legible.

        I’ve also added a further reply as I think the problem may lay with the IDENTITY_INSERT SQL condition.

        Regards

        Niven

        • #1225371

          Kent,

          Many thanks your reply, have edited my original post to make the code more legible.

          I’ve also added a further reply as I think the problem may lay with the IDENTITY_INSERT SQL condition.

          Regards

          Niven

          I suspect that is the source of your issue. From your description I can’t determine which field is the identity field, but if it is an autoincrement field then you may actually be trying to insert a value that duplicates an existing one. Unfortunately the error messages from ODBC often don’t identify what the real problem is, and get masked in some uninformative messages such as “An Error Occurred”

          • #1225409

            I suspect that is the source of your issue. From your description I can’t determine which field is the identity field, but if it is an autoincrement field then you may actually be trying to insert a value that duplicates an existing one. Unfortunately the error messages from ODBC often don’t identify what the real problem is, and get masked in some uninformative messages such as “An Error Occurred”

            Wendell,

            Many thanks your reply. This issue is causing me big headaches at present.

            The identity field is an autoincrement field. Is there a way of seeing what value is trying to be input into this field so as to understand whether it is a duplicate?

            My next step is going to be cutting the table down in field size, to say two or three and basically see what happens.

            Cheers

            Niven

    • #1225339

      Hi there,

      I’ve been doing some more sifting and think the problem may lay with the IDENTITY_INSERT condition.

      Having modified the original code to add in the SSMA suggested fields and subsequently received the “Error in Procedure: AddEndorsement (3146) ODBC–Call Failed.” message, I looked around and found a post which suggested the ODBC error doesn’t state the full problem and subsequently added in some additional error trapping which produced the following:-

      “544
      [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table ‘tblPolicy’ when IDENTITY_INSERT is set to OFF.”

      I investigated this and found on the Lounge the following SQLSERVER statement:-

      [indent][/indent]”SET IDENTITY_INSERT tblPolicy On”

      I’ve applied this on SQLSERVER, but am still getting the same error. Assuming the error trapping was right does anyone have any suggestions?

      The revised code with the SSMA changes falls over when executing the .Update condition.

      Code:
          Set ins = db.OpenRecordset("tblPolicy", dbOpenDynaset, dbSeeChanges)
          With ins
              .AddNew
        [i]      'added following three lines in order to cope with SQLServer updating auto-increment fields after update rather than before as Access does.[/i]
              .Update
              .Move 0
              .Bookmark = .LastModified
          
          ' First copy orig record.
          Dim fld As adodb.Field
          For Each fld In old.Fields
              If fld.Name  "id" Then
                  ins.Fields(fld.Name).value = fld.value
              End If
          Next fld
    • #1225410

      Unfortunately I almost always work with DAO, not ADO, so I’m not much help. In any event, I can’t see where you are setting the value of the identity field, but in general you don’t want to do that, as it almost always causes grief. I suspect your idea of trying just a few fields may hold the most promise.

    • #1225464

      Try inserting a record directly into the table. What happens?

      You could also try recreating the table manually. Rename the old one and create a new tblPolicy. That way you can test to see if there is something odd with the old Identy Column.

      Wendell is correct in saying the ODBC errors are worthless. He’s also right when he says that you aren’t trying to set the value in the ID field. Using a DAO AddNew should have SQL creating this value.

      • #1225521

        Try inserting a record directly into the table. What happens?

        You could also try recreating the table manually. Rename the old one and create a new tblPolicy. That way you can test to see if there is something odd with the old Identy Column.

        Wendell is correct in saying the ODBC errors are worthless. He’s also right when he says that you aren’t trying to set the value in the ID field. Using a DAO AddNew should have SQL creating this value.

        Kent & Wendell,

        Many thanks again your replies.

        This is where I’m now at.

        I have created a cut down version of tblPolicy called tblPolicy_test. This table has only two fields, ID and TSM_Ref. ID is an autoincrement field. I’ve modified the VBA code to look at this table.

        If I run the code with the SSMA Possible Post-Migration Issues Help suggestion then the app falls over with the proverbial 3146 ODBC Call failed error. The additional error trapping I put in suggests ” 544 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table ‘tblPolicy_test’ when IDENTITY_INSERT is set to OFF.”

        Running “SET IDENTITY_INSERT tblPolicy_test ON” from SQLServer doesn’t make any difference.

        Commenting out the SSMA code insertions takes me to the the next code section which is supposed to populate the TSM_Ref field. Stepping through the code I can see the value for this field appear and the code subsequently moves on to what you might think is a successful completion. From what I can see I’m not trying to update the ID field myself.

        However, although control goes back to the main form, when I try and query the table in SQL or just open in Access. Both sides hang. The activity monitor suggests that the table on boths sides is locked. I would have hoped that the update command would have either updated or failed and the table would not be in a locked state. When I have managed to regain control of both sides (i.e. closing down Access) the table has not been updated with the new record.

        Any further help/suggestions you can give i would be most grateful for.

        Have reposted the code below.

        Cheers

        Niven

        Code:
           ' Create New tblPolicy Record
            Set ins = db.OpenRecordset("tblPolicy_test", dbOpenDynaset, dbSeeChanges)
            With ins
                .AddNew
                'added following three lines in order to cope with
                'SQLServer updating auto-increment fields after update
                'rather than before as Access does.
        '        .Update
        '        .Move 0
        '        .Bookmark = .LastModified
            End With
            ' First copy orig record.
            Dim fld As adodb.Field
            For Each fld In old.Fields
                If fld.Name  "id" Then
                    If fld.Name = "TSM Ref" Then
                        ins.Fields(fld.Name).value = fld.value
                    End If
                    Debug.Print fld.Name; fld.value
                End If
            Next fld
        
                ins.Update
                ins.Close
            
                old.Close
    • #1225576

      Let’s break it down so we can find the problem

      Just try this –

      Code:
         ' Create New tblPolicy Record
          Set ins = db.OpenRecordset("tblPolicy_test", dbOpenDynaset, dbSeeChanges)
          With ins
              .AddNew
                  !TSM Ref = "Test text"
              .Update
              .Close
      End With

      Does that part work OK?

      If not then try recreating the table in SQL Server.

      • #1225587

        Let’s break it down so we can find the problem

        Just try this –

        Code:
           ' Create New tblPolicy Record
            Set ins = db.OpenRecordset("tblPolicy_test", dbOpenDynaset, dbSeeChanges)
            With ins
                .AddNew
                    !TSM Ref = "Test text"
                .Update
                .Close
        End With

        Does that part work OK?

        If not then try recreating the table in SQL Server.

        Hi Kent,

        I’ve tried your breakdown and I think I’m also having one! This is my second attempt at replying as the first decided not to upload.

        Although the code ran through without error, the table did not update with the new ref. I therefore dropped the table, recreated it, left it empty and tried again. This time the new record did seem to be appended to the table, however I ran the procedure again and the system hung. I tried querying from SQL but that hung too. It would seem that the table was in a locked state and the only way of unlocking it was to close Access. Running up Access again and interrogating tblPolicy_test showed an empty table. I.e. it hadn’t updated. I would have thought the .Update and .Close statements would do as per the tin and return the table to general use. Is there something I’m missing in my code?

        Also, when trying to exit Access via the form close and exit route I get the following error:-

        [indent]”Error 3246: Operation not supported in transactions”[/indent]

        I can close by using the command bar close buttons for both the forms and Access itself.

        A bit more background on this seems to suggest that I “may not explicitly close a Connection object while in the middle of a transaction.” which possibly suggests that the transaction didn’t commit.

        Any thoughts/suggestions on what is going on would be most appreciated.

        Cheers,

        Niven

        Table creation code as below:-

        Code:
        USE [TSM_DB2_Testv3]
        GO
        
        /****** Object:  Table [dbo].[tblPolicy_test]    Script Date: 05/26/2010 09:34:04 ******/
        SET ANSI_NULLS ON
        GO
        
        SET QUOTED_IDENTIFIER ON
        GO
        
        CREATE TABLE [dbo].[tblPolicy_test](
        	[id] [int] IDENTITY(1,1) NOT NULL,
        	[TSM Ref] [nvarchar](13) NOT NULL
        ) ON [PRIMARY]
        
        GO
    • #1225605

      This is very much a shot in the dark, but are you still using the

      Code:
      ins.Rollback

      command? That would normally be used in Transaction processing, but I don’t see anything that starts a Transaction (BeginTrans). See An ADO Transaction for an example where a Transaction is being processed.

      • #1225615

        This is very much a shot in the dark, but are you still using the

        Code:
        ins.Rollback

        command? That would normally be used in Transaction processing, but I don’t see anything that starts a Transaction (BeginTrans). See An ADO Transaction for an example where a Transaction is being processed.

        Hi Wendell,

        Your shot in the dark paid dividends. How good are you at Horse racing? I went a bit mad when reducing the amount of code to run through and inadvertantly commented out the CommitTrans statement, so of course the transaction as it were remained uncomitted, the table locked and subsequently rolled back when the db was closed.

        Well that’s solved that problem, I will now pick up on where the others arose!

        Many thanks

        Niven

    • #1225621

      Unfortunately, gambling has never paid off for me – unless I play with someone else’s money.

    • #1226034

      Wendell/Kent,

      Many thanks again your help on the above. I’m going to sign this thread off now as it’s getting rather long and I have additional problems for which I’m going to raise a new thread.

      To summarise my initial problem here (error message “Error in Procedure AddEndorsement (3164) Field can’t be updated”), the problem eventually turned out to be a recordset loop trying to populate the new SSMA_TimeStamp field in tblPolicy table.

      Microsoft’s suggestion of adding three new fields, .Update, .Move 0 and .LastModified after Recordset.AddNew, basically didn’t work as the app fell over when executing the .Update statement. I’ve left these out as the first part of the procedure seems to work ok without them and the ID field updates as I would expect.

      Cheers

      Niven

    Viewing 8 reply threads
    Reply To: Error in Procedure (3164) Field can't be updated

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: