INSERT query: best method for confirming success?

>>Zones: Microsoft Access Database, SQL Server 2008<<
This question has nothing to do with SQL Server 2008, please request that the SQL Server 2008 zone be removed.

How many records are involved?
For <1000, I used recordset code and not SQL to do action queries.
I then have VERY granular control over what goes in and how.

For every large quantities of records, this is inefficient.

YMMV

It is actually for single record insertions to initialiize a new row with an auto-incrementing identity, and a few required fields, so that the row can be more extensively updated later. There are no transactional dependencies that require a multiple set of rollbacks, which is why I was wondering if a transaction would be suitable.

I have the usual error handler code block around the recordset operations. I can catch potential errors or incomplete database operations there but I was looking to specifically confirm the row was commited with the correct data and report that back to the user.

I am presuming a TRANSACTION block is a fairly expensive operation, correct?

If you are doing recordset operations like this

    Set rs = db.OpenRecordset("SELECT * FROM [tblClients] WHERE 1=2;", dbOpenDynaset, dbSeeChanges)
    With rs
        .AddNew
        ![Client Name] = StrConv(NewData, vbProperCase)
        .Update
        .Bookmark = .LastModified
        lngOwnerID = ![Client ID]
    End With
    rs.Close

Then you'll note that  lngOwnerID is returned from the operation if it is successful.
If it's not successful, it's going to throw a VBA error that you'll have to catch, or it'll slobberknock the end user.

So, if you are using recordset code, no news is good news, and bad news you have to have error-handling for!

Nick,

That is pretty much exactly what I am using right now.

It looks like the identity is key: I need that anyway to report back to the user. Error handling is already there, although I would like to catch (or test for) a specific database error, should one occur. The required fields I have are going to be validated before processing the operation so maybe catching a specific error might be overkill.

RE:

"Is using a TRANSACTION block the best way to do an operation with a confirmation or is better to just re-read the new row and base the confirmation on the success of that operation?"

In general, Yes.  BeginTrans, CommitTrans & Rollback ... this is an All or None scenario.  I use it frequently ... just like banks do when moving money around :-)

And, you can also use the RecordsAffected property of the Execute method

Public Function mFx()
On Error Goto mFx_Error:

Dim x as Long  ' optional
With CurrentDb
      .Execute "YourActionQueryName", dbFailOnError '
       ' x = .RecordsAffected   'returns the number of records processed by the action query ' optional
End With

mFx_Exit:
   Err.Clear
   ' other clean up here
   Exit Function
mFx_Error:

   ' error trap code here
   GoTo mFx_Exit

End Function

Do forget the Dots ....


More info ... for the Execute Method from Help:

"Use the RecordsAffected property of the Connection, Database, or QueryDef object to determine the number of records affected by the most recent Execute method. For example, RecordsAffected contains the number of records deleted, updated, or inserted when executing an action query. When you use the Execute method to run a query, the RecordsAffected property of the QueryDef object is set to the number of records affected."

Another bonus with the Execute method ... you annoying confirmation prompts; however if ... and unexpected error occurs, the dbFailOnError option will rollback the action.

With DoCmd.OpenQuery "SomeActionQuery" ... you need to precede this with a
DoCmd.SetWarnings False   ' which is a bad idea because it will mask errors you need to know about if the query fails for any reason.

mx