Tell Us: How do you validate data?

Published 24 March 08 03:07 PM

We build a lot of sample applications on the team to test stuff. Jeff Conrad is putting together a database to track the NFL season and needed a way to make it impossible to create multiple records of the same teams playing each other in the same week and same year.

He created a compound index on the table.

clip_image002You supply a name for the index in the Index Name column. You then list all the fields you want to use for this compound index in the Field Name column. It’s very important that for the additional fields, you don’t put the same (or anything) index name in the left column. Otherwise, Access thinks it’s a completely different index. You can also set a Sort Order, but I don’t see much value in doing that.

In the bottom left corner, you’ll notice that the Unique property is set to Yes. By default this is No so you need to remember to set that to Yes in order to only accept a unique compound index of those fields.

Of course you could also enforce this through table level validation or VBA/Macros on the form level. How do you solve this kind of problem in your databases? Tell us!

 

by Ryan McMinn

Comments

# Paul said on March 24, 2008 7:43 PM:

Your unique index is over-determined. The combination of HomeTeam, Year and Week is already unique without the AwayTeam. Better yet, you could have two unique constraints: HomeTeam, Year, Week and AwayTeam, Year, Week. The constraint is that each team can only play one game per week. While you're doing validation, you could add a table validation rule that HomeTeam <> AwayTeam. The real business rule that cannot quite be enforced in Access is that each team can only play one game per week. The above constraints would still allow a team to be listed as both a home team and an away team in the same week.

# Peter Schmidt said on March 24, 2008 10:06 PM:

I use the Form Error event to intercept the Index error, display a user friendly message and undo the data change. If data can be entered from multiple forms then you have to put the validation on each form.

I like enforcing validation at the table level so adding an Index Error Text property (similar to Validation Text for fields) would be useful. Whenever the Index was violated the custom message would be displayed and the data change would be undone. Certainly for simple validation, such as ensuring that a value in a lookup table is unique where you have a unique index on a single field this would work well.

# Henry Habermacher said on March 24, 2008 10:09 PM:

It would be better doing it on table level (constraint base) only as the relational database management systems should enforce the integrity of the data.

The main reason I normally don't enforce this on the table level is that I have to check it manually (with VBA/macro) in the Form_BeforeUpdate event anyway as I don't have an easy possibility else to popup a meaningful msgbox to the user and cancel the event. Tracking database errors (in the Form_ErrorEvent) is far more complicated as I don't get the exact reason why the statement failed without parsing the Jet/ODBC error messages. And doing so means the database manager had to do some work already (that failed). If I do it in the form_beforeupdate event, the database manager isn't involved yet and therefore I save ressources.

Henry, MVP Access

# ken hockley said on March 24, 2008 11:10 PM:

Creating an index field locks you into a mentality and makes it hard to test uniqueness when the rules change (as we know they will)

Using VBA allows you to build a dynamic SQL test before you save the data - doesn't pass then doesn't save

I use this one on every save I do - the example is a simple one but the SQL can become as complex as you like

 'make sure the user_name is unique

 hold_crit = "select * from t_..."

 hold_crit = hold_crit & " where user_name = '" & Me!user_name & "'"

 'add to the selection criteria here if needed

 Set curr_rs = curr_db.OpenRecordset(hold_crit)

 If curr_rs.RecordCount > 0 Then

    MsgBox "The entry already exists", 0, system_error

    curr_rs.Close

    Exit Sub

 End If

 curr_rs.Close

# Craig Alexander Morrison said on March 25, 2008 5:28 AM:

Only pre-validation is acceptable on the application.

You MUST use the database.

In Jet/ACE this is a lot less flexible than on DB2/SQLS.

Anyone using the application level to enforce uniqueness has to be 100% certain that nothing can access the database without going through it.

And to be frank nothing is 100%, so you MUST apply the constraints at the engine level.

If Jet/ACE is not up to the job then use DB2 (or SQLS or whatever).

# Markus said on March 25, 2008 9:13 AM:

There is no real control when using VBA. In a complex project there is no chance to know all the places where a VBA code checks such essential data inserts even if all is documented.  There can be a lot of mistakes on many places.

I would do a little redesign of the database. In the example it is possible that the home team plays against itself. Creating a new team-table with relation to GameID with GameID and TeamID as primary key will prevent playing a team against itself and playing twice in one week.

# DM said on March 26, 2008 6:50 PM:

Exactly what I would do Markus.

I totally agree with you!

New Comments to this post are disabled
Page view tracker