Solved sql server transaction from access (1 Viewer)

[pasin2]

Hi,
I am new to access and I am having a hard time to understand how to execute a transaction from access. When I execute below code , obviously it will fail on the first run of the loop because sql syntax is not appropriate for sql server. But instead , it continues to run and passes to the second execution of the sql insert. but this time it freezes and after a few seconds shows error odbc connection failed and the table in the database that is being updated remains locked. I don't understand why it should give this error.
I think the proper message would be transacction unsuccessful and rollback without leaving the table locked.

i=5
BeginTrans
On Error GoTo tran_Err
For j = 1 To i - 1
strSQL = "INSERT INTO paymentt (loanid, amount,paiddate, paid_interst, principal,amt_remained,dateadded,paymentnumber)" & _
                            " select 29 , " & interest_list(j) + principal_list(j) & ", #" & Format(d, "mm/dd/yyyy") & "#, " & interesi_list(j) & "," & principal_list(j) & " as princip," & _
                            "IIF(not IsNull(pay.amt_remained), pay.amt_remained," & e & ") - princip, now(), " & installment_list(j) & ", " & _
                            " FROM paymentt AS pay RIGHT JOIN (SELECT max(paymentid) AS id FROM paymentt where loanid=29)  AS max2 ON pay.paymentid=max2.id"
                            
                            
    CurrentDb.Execute strSQL, dbFailOnError

  Next j
    CommitTrans
MsgBox "Success", vbOKOnly
Exit Sub
tran_Err:
  Rollback
  Debug.Print Err.description & " " & Err.Number
  MsgBox "Unsuccessful! . Error: " & Err.description

 

[Minty]

Cross-posted with suggestions here https://www.accessforums.net/showthread.php?t=83701

@pasin2 please read here https://www.excelguru.ca/content.php?184

 

[arnelgp]

transaction on ms access applies to ms access tables only and not for SQL server tables.