Results 1 to 4 of 4
  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!

    My latest issue with Access Front End to SQLServer backend conversion partly follows on from post no. 775096. Itís to do with a VBA transaction process which first adds a record to a table (tblPolicy) and then to another one (tblPolicyTransaction).

    I resolved the issue in the previous post of being able to add a new record to tblPolicy, but before the begintrans routine is committed, tblPolicy is used in a join to tblPolicyTransaction in order to update a field in tblPolicyTransaction. Following on from here itís used again in a query to extract currency conversions and other detail as part of inserting a new record into tblPolicyTransaction.

    The problem is tblPolicy is locked at the SQL end and the update and insert to tblPolicyTransaction just hang as a result. The original idea round the begintrans routine is if a problem is encountered anywhere in the process then the record postings into tblPolicy and tblPolicyTransaction get rolled back. I.e. Commit at the end, not individually.

    I could do with some advice on how best to resolve this. As a first phase of conversion, Iím trying to preserve as much of the existing front end setup as possible, but in this instance I donít whether I should look at seeing if I can convert this procedure to a stored proc and utilise say the ďtransaction isolation levelĒ facility, which I think will allow me to use the table in question whilst still in a state of having an uncommitted update (havenít tested this yet). The original Access App doesnít have a problem here as there is no record locking set.

    I don't know if it's useful at all but posted below is the procedure code along with the first sub procedure.

    Cheers

    Niven

    Code:
    Public Sub AddEndorsement()
    On Error GoTo Err_AddEndorsement
    
        Dim strFacRef As String
        Dim strOrigFacRef As String
        Dim strQuery As String
        Dim strTSMRef As String
        Dim dteIncept As Date
        Dim dteNow As Date
        Dim bInTrans As Boolean
        Dim db As DAO.Database
        Dim ins As DAO.Recordset
        Dim wrk As Workspace
        
        
        DoCmd.Hourglass True
        
        Set db = CurrentDb()
        
        dteNow = Now()
        
        ' Start of transaction
        Set wrk = DBEngine.Workspaces(0)
        wrk.BeginTrans
        bInTrans = True
    
        strTSMRef = Me.Parent![TSM Ref]
    
        ' We need a new Facility Ref.
        strFacRef = GetNextFacilityRef(strTSMRef)
    
        ' Use the lastest Signed else latest Written
        If IsPolicySigned(Me.Parent![NewPolicyEntry tblPolicy]![Facility Ref]) Then
            strQuery = "qryGetSignedForEndorsement"
        Else
            strQuery = "qryGetWrittenForEndorsement"
        End If
    
        ' 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")
        
       
    
        ' Create New tblPolicy Record
        Set ins = db.OpenRecordset("tblPolicy", dbOpenDynaset, dbSeeChanges)
        With ins
            .AddNew
        End With
    '    ' First copy orig record.
        Dim fld As adodb.Field
        For Each fld In old.Fields
            If fld.Name <> "id" And fld.Name <> "SSMA_Timestamp" Then
                    ins.Fields(fld.Name).value = fld.value
                Debug.Print fld.Name; fld.value
            End If
        Next fld
    
    
    '     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
    
        ' Reset SICRI (Sum Insured Current Record Indicator) Transaction record.
        ResetSICRIForTSMRef strTSMRef, "ENG", db                                            Ď***First Problem occurs here See subroutine at end
    
        ' Add PolicyTransaction Record.
        With cmd
            .ActiveConnection = CurrentProject.Connection
            .CommandText = "qryGetTransactionForFacRef"
            .CommandType = adCmdTable
            .Parameters.Refresh
            .Parameters("[prmFacRef]") = strOrigFacRef
        End With
    
        old.CursorType = adOpenStatic
        old.Open cmd                                                                                           Ď***Second Problem occurs here.  Code too complex to post though
        If old.RecordCount <> 1 Then
            wrk.Rollback
            old.Close
            DoCmd.Hourglass False
            MsgBox "Unable to update transaction, as transaction record count for: " & strOrigFacRef & " is <> 1", vbExclamation
            Exit Sub
        End If
    
        ' Create New tblPolicyTransaction Record
        Dim ptid As Long
        Set ins = db.OpenRecordset("tblPolicyTransaction", dbOpenDynaset, dbSeeChanges)
        With ins
            .AddNew
        'added following three lines in order to cope with
        'SQLServer updating auto-increment fields after update
        'rather then before as Access does.
    '        .Update
    '        .Move 0
    '        .Bookmark = .LastModified
        End With
    
    
        ' Save new autonumber value for other calls.
        ptid = ins("id")
    
        ' Orig CCY
        ins("Facility Ref") = strFacRef
        ins("Facility Group") = CreateNewFacilityGroup(strFacRef)
        ins("Orig CCY") = old("Orig CCY")
        ins("Sett CCY") = old("Sett CCY")
        ins("Written ROE") = old("Written ROE")
        ins("Accounting Date") = Now()
        ins("Written Line") = old("Written Line")
        ins("Gross Premium") = 0
        ins("Comm") = old("Comm")
        ins("TSM Calc") = "Y"
        ins("Transaction Type") = "Premium"
        ins("LloydsRisk Code") = old("LloydsRisk Code")
        ins("Business Type") = old("Business Type")
        ins("Survey") = old("Survey")
        ins("Consortium Split") = old("Consortium Split")
        ins("Att Date") = dteIncept
        ins("Class") = old("Class")
        ins("BSF") = old("BSF")
        ins("SICRI") = "Y"
        ins("Created") = dteNow
        ins("Updated") = dteNow
    
        ins.Update
        ins.Close
    
        ' Add Sum Insured Record
        Dim rtn As Integer
        rtn = InsertSumInsured(strTSMRef, strFacRef, old("Business Type"), old("Orig CCY"), old("Sett CCY"), Me("Sum Insured"), db)
        If rtn = 1 Then
            MsgBox "Unable to add Sum Insured, thus aborted Add Endorsment", vbExclamation
            GoTo Err_AddEndorsement
        End If
    
        ' Create OCCY/SCCY Calculations and save them to the said table.
        rtn = OCCYSCCYCalculations(ptid, "add")
        If rtn = 1 Then
            GoTo Err_AddEndorsement
        End If
    
        old.Close
    
        ' All is OK so commit transaction
        wrk.CommitTrans
        bInTrans = False
    
        ' Refresh Policy form so that endorsment appears.
        [Form_fsubPolicyEntry tblPolicy].GotoLastRecord
    
        DoCmd.Hourglass False
        
    Exit_AddEndorsement:
        Exit Sub
    
    Err_AddEndorsement:
        Dim strMsg As String
        Dim strProcedure As String
        
        
        strProcedure = "AddEndorsement"
        
        
        If bInTrans = True Then
            wrk.Rollback
        End If
        
        DoCmd.Hourglass False
    
    
        Dim errX As DAO.Error
    
        If Errors.Count > 1 Then
           For Each errX In DAO.Errors
              Debug.Print "ODBC Error"
              Debug.Print errX.Number
              Debug.Print errX.Description
           Next errX
        Else
           Debug.Print "VBA Error"
           Debug.Print Err.Number
           Debug.Print Err.Description
        End If
    
    
    
        strMsg = "Error in Procedure: " & strProcedure & vbCrLf & _
                 "(" & Err.Number & ") " & Err.Description
    
        MsgBox strMsg, vbCritical
        Resume Exit_AddEndorsement
    End Sub
    
    **************************************************************************
    Sub ResetSICRIForTSMRef(strTSMRef As String, strBusinessType As String, db As DAO.Database)
    On Error GoTo Err_ResetSICRIForTSMRef
    
        Dim strSQL As String
        
        strSQL = "UPDATE tblPolicy INNER JOIN tblPolicyTransaction ON tblPolicy.[Facility Ref] = tblPolicyTransaction.[Facility Ref] " & _
                 "SET tblPolicyTransaction.SICRI = 'N' " & _
                 "WHERE (tblPolicy.[TSM Ref]= '" & strTSMRef & "' AND tblPolicyTransaction.[Business Type]= '" & strBusinessType & "')"
        
        db.Execute strSQL, dbFailOnError
        
    Exit_ResetSICRIForTSMRef:
        Exit Sub
    
    Err_ResetSICRIForTSMRef:
        Dim strMsg As String
        Dim strProcedure As String
        strProcedure = "ResetSICRIForTSMRef"
        
        
            Dim errX As DAO.Error
    
        If Errors.Count > 1 Then
           For Each errX In DAO.Errors
              Debug.Print "ODBC Error"
              Debug.Print errX.Number
              Debug.Print errX.Description
           Next errX
        Else
           Debug.Print "VBA Error"
           Debug.Print Err.Number
           Debug.Print Err.Description
        End If
    
        
        strMsg = "Error in Procedure: " & strProcedure & vbCrLf & _
                 "(" & Err.Number & ") " & Err.Description
        
        MsgBox strMsg, vbCritical
        Resume Exit_ResetSICRIForTSMRef
    End Sub

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I've spent several days chewing on what the best strategy is here, and confess I don't have any good solutions. Does the process work if you comment out the transaction processing? These days, the speed of processing is such that you are talking about milliseconds, or even less for that to occur, and the probability of a crash or something is very small. So the biggest risk is probably two people trying to do the same thing at the same time. What are the chances of that? Probably the best solution would be to do the whole thing with a pass-through query that invokes a stored procedure, but doing your process in T-SQL will be a major challenge. Are you adept at doing a UDF with VB or C#?
    Wendell

  3. #3
    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've spent several days chewing on what the best strategy is here, and confess I don't have any good solutions. Does the process work if you comment out the transaction processing? These days, the speed of processing is such that you are talking about milliseconds, or even less for that to occur, and the probability of a crash or something is very small. So the biggest risk is probably two people trying to do the same thing at the same time. What are the chances of that? Probably the best solution would be to do the whole thing with a pass-through query that invokes a stored procedure, but doing your process in T-SQL will be a major challenge. Are you adept at doing a UDF with VB or C#?

    Hi Wendell,

    I'm afraid I know very little about VB and nothing about C#. I have to confess I'm not sure how similar VB is to VBA having never really looked at it. I do intend to get to know it at some point.

    T-SQL maybe my first port of call. I felt that I would take just about everything over to Stored Procs at some stage, but not quite this soon as I wanted to implement a staged approach.

    Also, sorry by UDF do you mean user defined form?

    I'll look at commenting out the Transaction processing to see what occurs.

    Cheers,

    Niven

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Niven View Post
    ...
    Also, sorry by UDF do you mean user defined form?

    I'll look at commenting out the Transaction processing to see what occurs.

    Cheers,

    Niven
    Actually, that is a User Defined Function in SQL Server - you can manage those in SQL Server Management Studio, or in Visual Studio current versions.
    Wendell

Posting Permissions

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