Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
       ' 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
    
      *****It gets to the the last field ok, but fails when when the loop finishes and tries to move to the next stage:-
    
        ' 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

  2. #2
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:-

    Recordset.Update
    Recordset.Move 0,
    Recordset.LastModified

    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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:-

    "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
            '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
        
        ' 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

  5. #5
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kentg View Post
    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

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Niven View Post
    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"
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    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

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kentg View Post
    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

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  12. #12
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kentg View Post
    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:-

    "Error 3246: Operation not supported in transactions"

    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

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  14. #14
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    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

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Unfortunately, gambling has never paid off for me - unless I play with someone else's money.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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