Access 2010 db.Execute using dbFailOnError and dbSeeChanges

thanks guys, completely forgot about the parameters.

i have a follow up question.

the code i am trying to be able to rollback if one of the queries fail is actually writing to the same table.  

The table INVENTORY_TRANS has a Identity specification field called ROWID and a primary key of TRANSACTION_ID (that is programmatically incremented (no identity seed or increment))  (not how i would have designed the table - vendors table).

The problem i am having with the following code is it stops after the first qdf.Execute.  it finally times out and falls out to the error section.
is there anyway to be able to use this rollback functionality if you are writing to the same table?

Private Sub CreateTransactions_Click()
On Error GoTo Err_CreateTransactions_Click

Dim ws As DAO.Workspace   'Current workspace (for transaction).
Dim db As DAO.DataBase    'Inside the transaction.
Dim bInTrans As Boolean   'Flag that transaction is active.
Dim strSql As String      'Action query statements.
Dim strMsg As String      'MsgBox message.
Dim qdf As DAO.QueryDef

bInTrans = True
Me.CreateTransactions.Enabled = False
Set ws = DBEngine(0)
ws.BeginTrans
Set db = ws(0)
Dim iTransID As Long
iTransID = DMax("[TRANSACTION_ID]", "dbo_INVENTORY_TRANS") + 1

   If Me.Issue_Qty > 0 Then
      Set qdf = CurrentDb.QueryDefs("Issue Materials - Issue to Work Order")
      qdf.Parameters("iTransID") = iTransID
      qdf.Execute dbFailOnError + dbSeeChanges
      iTransID = iTransID + 1
   End If
  
   If Me.MissCut > 0 Then
      Set qdf = CurrentDb.QueryDefs("Issue Materials - Adjust Out Miss Cuts")
      qdf.Parameters("iTransID") = iTransID
      qdf.Execute dbFailOnError + dbSeeChanges
      iTransID = iTransID + 1
   End If
   
   If Me.DropOffQty > 0 Then
      Set qdf = CurrentDb.QueryDefs("Issue Materials - Adjust Out DropOff")
      qdf.Parameters("iTransID") = iTransID
      qdf.Execute dbFailOnError + dbSeeChanges
      iTransID = iTransID + 1
   End If

ws.CommitTrans
bInTrans = False
Me.Completed_By = Me.Emp_ID & " - " & Me.Emp_Name & " - " & Now()
MsgBox "Transaction(s) Added for: " & Me.Job & "   Part # : " & Me.RQ_PartID

Exit_CreateTransactions_Click:
  'Step 5: Clean up
  On Error Resume Next
  Set db = Nothing
  If bInTrans Then   'Rollback if the transaction is active.
    ws.Rollback
  End If
  Set ws = Nothing
  Exit Sub

Err_CreateTransactions_Click:
    MsgBox Err.Description, vbExclamation, "Adding records failed: Error " & Err.Number & " Close and retry"
    Resume Exit_CreateTransactions_Click
End Sub

Select allOpen in new window

its like the program hangs on tying to get the next IDENTITY number for ROWID

Couple of problems.  This:

bInTrans = True
Me.CreateTransactions.Enabled = False
Set ws = DBEngine(0)
ws.BeginTrans
Set db = ws(0)

Should be:

Me.CreateTransactions.Enabled = False
Set ws = DBEngine(0)
Set db = ws(0)
ws.BeginTrans
bInTrans = True

  blnTrans needs to be moved because if you error on the next couple of statements, you'll be flagged as in a trans, but not.

This:

iTransID = DMax("[TRANSACTION_ID]", "dbo_INVENTORY_TRANS") + 1

 Is going to fail in a multi-user situation.   You either need to lock the table while your writing to it, or use a key table and lock that while your fetching the next ID.


On this:

  If bInTrans Then   'Rollback if the transaction is active.
    ws.Rollback
  End If

  You should not be rolling back on an exit.    Rollback in the error handler.

 As far as the hang, would need to see the SQL.   What you got (with the above changes), should work.

Jim.

and BTW, where is DB used?   Your making CurrentDB() calls in each statement.

I would change those to DB.

Jim.

Jim, Thanks, I agree on all the changes.

how do i lock/release a table from vba?  (i thought access was doing that for me.)

As far as the sql statement, it is append queries to the INVENTORY_TRANS table, they all work if you only run one of them.  I believe its the fact that i am not committing the append, then the 2nd append can't get the IDENTITY for ROW_ID

PARAMETERS SAWCUTID Long, EmpName Text ( 255 ), MaterialCost IEEESingle, LaborCost IEEESingle, ServiceCost IEEESingle, SiteID Text ( 255 ), iTransID Long;
INSERT INTO dbo_INVENTORY_TRANS ( TRANSACTION_ID, WORKORDER_TYPE, QTY, CREATE_DATE )
SELECT [iTransID] AS Trans, "W" AS WOType, dbo_CT_SAW_CUT_TRANSACTIONS.Issue_Qty, Now() AS Created
FROM dbo_CT_SAW_CUT_TRANSACTIONS
WHERE (((dbo_CT_SAW_CUT_TRANSACTIONS.ID)=[SAWCUTID]));

Select allOpen in new window

In essence what i would like to do is the following:

Lock the INVENTORY_TRANSACTION table (sqlserver 2008r2 linked table)
set a checkpoint
insert 3 records into the INVENTORY_TRANSACTION table (this table has an IDENTITY on the ROW_ID field)
if any fail  RollBack all of them
commit
unlock the table

can this be done from access 2010 to sqlserver 2008R2

Sorry for the delay.   I wanted to check on a few things before I answered you on this.

First, I would not lock the inventory table directly.  Instead, use a generic Key generation table and procedure.   You can use this for all your tables and it prevents anyone from having concurrency issues on the main table.  It also means you only have to lock a record rather than a entire table.   That's all covered in here:

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1
https://support.microsoft.com/en-us/kb/240317

Second (and this is what I had to check on), I would convert to ADO for this.  I looked at some things I did in the past, and everywhere I used a transaction with SQL server, I always used ADO.
 
 I'm not sure if the hang your having is related to DAO or something else.   It should work the way you have it and I don't understand why it is not.  I haven't had time to investigate like I wanted to (it's the new year!), but I didn't want to leave you hanging much longer without a response.

Jim.