Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    pasin is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    8

    errrot with transaction

    Hi,
    I have ms access as front end and sql server 2019 as backend .
    I need to run a transaction but the sql insert on every step is based on the result of the previous sql insert that has not been commited yet. Below code will get stuck at some point and the execution of the transaction is stopped.
    Please help how to run this kind of transaction. I need to be sure that all steps in loop are successfully inserted otherwise rollback all.
    Thanks

    Code:
    i=5
    BeginTrans
    On Error GoTo tran_Err
    For j = 1 To i - 1
     strSQL = "insert into payment_tbl ....   (select ..... from payment_tbl ) "
                                
                                
        CurrentDb.Execute strSQL, dbFailOnError
    
      Next j
        CommitTrans
    MsgBox "Success", vbOKOnly
    Exit Sub
    tran_Err:
      Rollback
      Debug.Print Err.description & " " & Err.Number
      MsgBox "Sth wrong! . Error: " & Err.description


  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    My initial take on this is that it won't work, at least in the way you have set it up.

    Let's get some basics into place - Why are you using a Transaction in the first place?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    transactions are very easy on SQL server, so I would advise to write one or more procedures on SQL server with the transactions you need. SQL is made for those things, so it's a bit crazy to try this in access when you have a SQL server as backend.

  4. #4
    pasin is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    8
    I am using transaction because I need all sql inserts to be executed or nothing. There is a loop that executes several inserts.

  5. #5
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Having had a think on, I agree with @NoellaG.

    However, I also suspect you could achieve this with a single set based insert, which would negate the need for a loop?
    Can you show us some sample data, with the starting data, and the desired outputs?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    something as:
    Code:
    create procedure [SchemaName].[ProcedureName]
    as
    begin
       set nocount on
    
       ...
       declare here your parameters
       ... 
    
       begin try
           begin tran
             ... do here your inserts ...
             commit
       end try
       begin catch
            rollback
           .... here you can add a record to an error table with the error description or send a mail about the error ...
       end catch
    
    END

  7. #7
    pasin is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    8
    Below is the sql that has to be executed during loop.
    Please notice IIF(not IsNull(pay.amt_remained), pay.amt_remained," & e & ") - princip where princip=principal_list(j). So column amt remained is calculated on every insert based on previous insert. This is why I need all or nothing

    Code:
     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"

  8. #8
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    code could look as (typed here straight into the window, so excuse me if there are some typing mistakers:
    Code:
    create procedure [SchemaName].[ProcedureName] 
        @CheckNr int --here you can give the initial value for i as a parameter when calling the procedure
    as
    begin
       set nocount on
    
       declare @j int = 1
        declare @SQL varchar(max)
      
       begin try
          begin tran
           while @j <  @CheckNr
           begin 
                set @SQL = ' insert into paymentt (loznid, amount, paiddate, .... ....  ect. '  
                             ...build here your SQL string using ' instead of " and + instead of & 
                exec sp_executesql @statement = @sql
                select @j = @j + 1
            end 
           commit
       end try
       begin catch
            rollback
           .... here you can add a record to an error table with the error description or send a mail about the error ...
       end catch
    
    END
    I suspect that for the lists you'll need to use a cursor/temp tables instead of a simple loop, but that's hard to say without seeing the app. But this should set you on your way to write the T-SQL procedure

  9. #9
    pasin is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    8
    In this case there is a problem with passing array list to a stored procedure as input parameters
    Quote Originally Posted by NoellaG View Post
    code could look as (typed here straight into the window, so excuse me if there are some typing mistakers:
    Code:
    create procedure [SchemaName].[ProcedureName] 
        @CheckNr int --here you can give the initial value for i as a parameter when calling the procedure
    as
    begin
       set nocount on
    
       declare @j int = 1
        declare @SQL varchar(max)
      
       begin try
          begin tran
           while @j <  @CheckNr
           begin 
                set @SQL = ' insert into paymentt (loznid, amount, paiddate, .... ....  ect. '  
                             ...build here your SQL string using ' instead of " and + instead of & 
                exec sp_executesql @statement = @sql
                select @j = @j + 1
            end 
           commit
       end try
       begin catch
            rollback
           .... here you can add a record to an error table with the error description or send a mail about the error ...
       end catch
    
    END
    I suspect that for the lists you'll need to use a cursor/temp tables instead of a simple loop, but that's hard to say without seeing the app. But this should set you on your way to write the T-SQL procedure

  10. #10
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    try passing a table valued parameter, or use a cursor to get the data list inside the procedure

    https://www.sqlshack.com/table-value...in-sql-server/

  11. #11
    pasin is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    8
    Yes but in my query each insert is based on a select of the previous inserted record . So in order to insert record number 2(after inserting record number one) I have to make a select of the first record which is still locked because has not been commited yet.
    All the inserts must be done in one batch


    Quote Originally Posted by NoellaG View Post
    try passing a table valued parameter, or use a cursor to get the data list inside the procedure

    https://www.sqlshack.com/table-value...in-sql-server/

  12. #12
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you are making this very complicated. The process below can work in either Access or SQL server, (easier in SQL server)


    1. Create a temporary table with all your data results in it.
    2. Check for any errors.
    3. Append the entire temp table to your table in one go.


    By your own comments you can't (and shouldn't) create another new record based on an as yet uncommitted one.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #13
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Within a transaction you can select the updated record, try this:

    Code:
     
    begin tran
        update [tablename] set [field1] = X where [field1] = Value Y
    select * from [tablename]  where [field1] = Value Y
    rollback
    
    select * from [tablename]  where [field1] = Value Y
    You will see that the result of the select within the transaction gives the new value, the second select, after the rollback, gives the old value.

  14. #14
    pasin is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    8
    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
     

  15. #15
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Yes, but the whole point is that you only launch the T-SQL procedure from access, so the whole transaction is taken care of by the SQL server, and not execute the SQL from Access VBA.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 02-10-2016, 10:00 AM
  2. Please help regarding bank transaction.
    By crackersphp94 in forum Access
    Replies: 2
    Last Post: 12-12-2015, 02:27 PM
  3. Transaction Log
    By gtimmies in forum Database Design
    Replies: 1
    Last Post: 05-27-2013, 03:27 PM
  4. Replies: 7
    Last Post: 11-14-2011, 05:59 PM
  5. SQL Transaction question
    By Mazdaspeed6 in forum Programming
    Replies: 4
    Last Post: 12-16-2010, 12:51 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums