On the second execution of the loop the sql insert below gets stuck and I get ODBC call failed . I thought it was happening because on every insert it is selecting updated records that have not been yet commited because commit command has not been executed yet
Code:
begin trans
fo j=1 to n
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") & "#, " & interest_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"
next j
CurrentDb.Execute strSQL, dbFailOnError
commit transaction