Validation Rule Violation Error Trapping (1 Viewer)

[RayG]

Hi,

If I append data from one table into another using a query, I get the following message:

"Test2008...didn't add...15 records due to validation rule violations."

Fine, I can work out why this is.

The problem is that I want to incorporate the append function into a generic VBA procedure. I create the SQL and then use:

Set qdfAppendQuery = dbCurrentDatabase.CreateQueryDef("", strSQL)
qdfAppendQuery.Execute

The append fails as expected and qdfAppendQuery.RecordsAffected = 0.

The problem is it doesn't appear to generate any trappable error. How can I give the user a clue as to what has happened?

 

[DCrake]

It depends on how you want the user to see the error. If you are happy for them to see the Access default error message then instead of using the .Execute method use the DoCmd.RunSQL approach. Don't forget to turn on the Warnings prior to running the sql.

David

 

[namliam]

The append fails as expected and qdfAppendQuery.RecordsAffected = 0.

How about trapping the recordsaffected = 0??

Also instead of creating a dummy query, why not simply do
Currentdb.execute strSQL

Seems much easier?

 

[RayG]

David,

Many thanks for your prompt response. I've tried it and it'll work fine.

However, you do say "It depends.." Is there a more sophisticated approach that would allow me to generate my own messages or to provide an error log?

Thanks again, Ray

 

[DCrake]

In theory, Your intended recordset should be validated prior to any attempts to append/ update and existing table. What type of errors are you anticipating and is there any way at point of entry that precursory validation can be performed. Remember Garbage In Garbage Out (GIGI) or as I say SISO.

David

 

[RayG]

Thanks for your suggestions. I did already show the record count in a message box. The question I wanted to help the user with was 'why?'

As regards the alternative command, yes of course that is simpler. There always seems to be three ways of doing something in Access and I don't find it easy to know which is the best.

Thanks, Ray

 

[namliam]

Thanks for your suggestions. I did already show the record count in a message box. The question I wanted to help the user with was 'why?'

The way to do this is to search for common errors, like invalid date strings, null key values or something simular in seperate queries, validating all your data to the teeth before actually running your append query.

Thus preventing the garbage from entering your Append thus not needing any errors.

There always seems to be three ways of doing something in Access and I don't find it easy to know which is the best.

Which is best is usually a matter of interpertation and situation... sometimes this sometimes that

The beauti of flexibility The horror of to many options

 

[RayG]

Ah, as I said the procedure was intended to be generic and to permit records to be appended from any source table to any target table using a third 'specification' table to define the relationships between source and target fields. The specification table could also in theory include the validation rules for the target table but I think that would be overkill for my purposes. I'll stick with your first suggestion, I'm just surprised that data errors aren't logged somewhere.

Ray

 

[namliam]

I'll stick with your first suggestion, I'm just surprised that data errors aren't logged somewhere.

They are when you do a straight import to a table, you get "Tablename_importerrors" but using a query you do not get this

 

[tranchemontaigne]

Though this will not actually help the end user a whole lot, it may help you. I often capture errors as part of the error handling. Most every subroutine or function I write incorporates error handling language similar to the below:

Err_lboTasks_Click:
    MsgBox Err.Number & ": " & Err.Description
    Call fnLogError(gstrObject, "lboTasks_Click", Err.Number & ": " & Err.Description)
    Resume Exit_lboTasks_Click

in your case I would modify this fnLogError function call to capture the SQL string that failed. Depending upon how your append query call was designed, you can often capture the primary key of the record with bad data.

the complment to this function call would be the following function. Please note that gstrObject is 'global' within the context of any given module, and I use it to store the name of the module.

Public Function fnLogError(gstrObject As String, gstrCode As String, gstrMessage As String)
On Error GoTo Err_fnLogError
Dim gstrlogin As String
Dim gdtTime As Date
Dim gstrINSERT As String
Dim gstrSELECT As String
Dim gstrFROM As String
Dim gstrSQL As String
gstrlogin = fnGet_UserID
gdtTime = Now()
 
gstrINSERT = "INSERT INTO ErrorLog ( [Time], [Login], [Object], [codeString], [Message] )"
 
    gstrSELECT = "VALUES (" & Chr(34) & gdtTime & Chr(34) & ", " & Chr(34) & _
        gstrlogin & Chr(34) & ", " & Chr(34) & gstrObject & Chr(34) & ", " & Chr(34) & _
        gstrCode & Chr(34) & ", " & Chr(34) & gstrMessage & Chr(34) & ") "
 
    gstrSQL = gstrINSERT & " " & gstrSELECT '& " " & gstrFROM
 
    With DoCmd
        .SetWarnings False
        .RunSQL gstrSQL
        .SetWarnings True
    End With
 
Exit_fnLogError:
    Exit Function
 
Err_fnLogError:
    MsgBox Err.Description
    Resume Exit_fnLogError
 
End Function

As you have already figured out from this snippet, I also have a table named ErrorLog, and only one more function is needed to make this approach work

Function fnGet_UserID()
Dim user_id As String
 
  user_id = Environ("UserName")
 
  fnGet_UserID = user_id
 
End Function

I prefer writing errors into a table to simplify searches, though it is basically the same amount of effort to use an IO stream to write the error to a text file. What I have not included in this snippet are some of the find-replace functions necessary to remove semicolins, commas, double quotes, etc from strings that are passed as arguments used to the APPEND query that logs the error. These characters will cause an otherwise well formed SQL statement to fall on it's face if they appear in the wrong places
________
Launch Box Vaporizer

 

[RayG]

Thanks for your reply. I can certainly use your code to improve my error handling, the problem is though that the SQL Append, doesn't generate an error!

 

[namliam]

My suggestion is
1) Run a "regular" query to see how many records are to be expected
2) Run the append query, and see how many records affected
3) If Expected <> Affected then error message.

 

[tranchemontaigne]

Hmmmmm

the last time I had SQL code that would not run AND would not generate an error I discovered a pecurliarity within MS Access VBA. When using WILDCARDs within a query composed in "query view", you use an ASTERISK, whereas if the SQL statement is composed as a string and then executed within a VBA module, you want to use a PERCENTAGE SIGN.

Though it is uncommon to use wildcards in an APPEND query, I wonder if there is another bizarre inconsistency in syntax rules specific to the SQL statement you are trying to execute. If you post the SQL statement I'd be glad to look at it, together we may find something else peculiar to the way MS Access VBA executes SQL.

I hope this helps.
________
SUZUKI ALTO LAPIN HISTORY

 

[gemma-the-husky]

i tend to use a series of select queries to confirm whether my appends will succeed first - and not run the genuine append query until i am sure it will run properly.

however (unlike just turning warnings off) using currentdb.execute gives a generic trappable error, as a backup

note that i read somewhere that in A2007, currentdb.execute ISNT all or nothing - not sure, but if true, it makes stuff like this a bit harder to accomplish.

 

[petehilljnr]

Or - if you wanted to trap every single individual record error, you could write a procedure to run every append as a single insert statement and test after the execution of each one to see if it went in! Go on ... go crazy! (no more sugar for me)