INSERT query: best method for confirming success?

kkamm
kkamm used Ask the Experts™
on
I am going through an exisiting Access 2003 application and I am trying to tighten things up to make it more stable and efficient.

Most of the database code (mostly DAO) does not do any confirmations on INSERT and DELETE operations. UPDATES are performed automatically as changes are made. I would like to be able to tell users whether or not a database operation succeeded and I would also like to have more control over transactions in general.

This leads to my question: 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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
In general, I believe it's best to use transactions.  Because especially in a large database, you'd use unnecessary resources to requery something that was deleted/updated/added.  Also, it seems it would add an enormous amount of duplicate custom code depending on the number of these types of operations.
Top Expert 2012

Commented:
>>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.
Most Valuable Expert 2014

Commented:
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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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?
Top Expert 2006
Commented:
What I tend to use is currentdb.execute specifying dbfailonerror. I always do a error check. I much prefer checking error codes instead of error handlers but each to their own, its personal preference

on error resume next
currentdb.execute "insert into ...", dbfailonerror
if err.number = 0 then
    msgbox "job done"
else
    msgbox "buggered! " & err.description
end if
Most Valuable Expert 2014

Commented:
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!

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial