How to check for SQL Errors

[CLSkcab]

Background:
New to SQL
Front end - MS Access 2010
Back end - MS SQL Server 2008
_______________ _______________ __

How is the best way to check if a SQL command works? For example:

zSQL = _
    "INSERT INTO [dbo_DB02001] " & _
        "(status,statusdate, buyer, supplier, mdDateTime, dWho,mdAction)" & _
        " VALUES(" & _
        "'" & Status & "'" & _
        ", '" & StatusDate & "'" & _
        ", '" & Me.fBuyer.Value & "'" & _
        ", '" & Me.fSupplier.Value & "'" & _
        ", '" & mdDateTime & "'" & _
        ", '" & gblUser & "'" & _
        ", '" & mdAction & "')"
 'Debug.Print zSQL
 DoCmd.SetWarnings False
DoCmd.RunSQL zSQL

Can I insert some code to see if the command worked or not? It would be beneficial to display an error code or something via a MsgBox.
Thanks in advanced for you help.

[twinnyfo]

The best way is to run a test of the query and then open the table you are trying to modify. If it worked, you will find the record updated properly.

[jforbes]

I use the following:

DoCmd.SetWarnings False
CurrentDb.Execute sSQL, dbFailOnError + dbSeeChanges
DoCmd.SetWarnings True

This will throw an error that you can capture or if you rather you can let it bubble up to the user.

[zmbd]

J' you do not need lines 1 and 2 in your code.
Line 2 should be altered to use an object variable instead of currentdb for example

...
set zdb = currentdb
zdb.execute...
...

The other issue with Line 2 is that we do not know how CLSkcab has connected to the SQL-Server nor how CLSkcab is interacting with the server.
Line 2 doesgive the advantage that so long as you are not using a stored-procedure or a passthru query to the SQL-Server one can use the zdb.RecordsAffe cted to return the record count.

[jforbes]

Very true Zmdb. Using SetWarning is an old habit. An old habit that has no affect on an Execute statement and really shouldn’t be in the code that I posted.

I tend to use a global variable in place of CurrentDB, but didn’t want to include it as it might cause confusion. You are right though, that if a variable is used in place of CurrentDB, it gives the ability to check the RecordCount property after running and insert, update or delete query, were as using CurrentDB is a one time instance of the database connection and will always return a 0 on the RecordCount property.

Lastly, what I was attempting to demonstrate was that by using an Execute with the dbFailOnError option an error can be raised in Access that can be handled with an error handler.