Validation rules, default values, input masks & sort orders - in the form or in the table?

[GopherUK]

Hi folks,

Just wondering how the professional developers choose to do this? Should these be in the placed in the tables or in the forms? It would seem to be that tables should simply be properly constructed with the field types and relations and all of the above along with the lookups should be done in the form itself? Is that correct?

Thanks in advance,

Gopher.

 

[GopherUK]

Yeah, I might be over-thinking this a bit but with coding/data modelling in general I always try to throw out ideas to the more experienced to make sure I am on the right path.

Now I am just looking at data types, data formats, default values and indexes in the table-level and all other validation in the form level (mostly through VBA).

 

[HiTechCoach]

Hi folks,

Just wondering how the professional developers choose to do this? Should these be in the placed in the tables or in the forms?

Gopher,

All the Professionals (Access/.Net/SQL Server/etc) I know all use table level validation, defaults, required property, etc. They have the database engine enforce as much as possible. That way there is no way to get past it and "dirty" the data. They also all use form level validation to allow better a "gentler"UI. Like xenou, they do use both.

I firmly believe you should do whatever you can at every level possible to insure the data integrity. I do what is best for the users and the data integrity not what is the easiest for me to develope.

 

[GopherUK]

Gopher,

All the Professionals (Access/.Net/SQL Server/etc) I know all use table level validation, defaults, required property, etc. They have the database engine enforce as much as possible. That way there is no way to get past it and "dirty" the data. They also all use form level validation to allow better a "gentler"UI. Like xenou, they do use both.

I firmly believe you should do whatever you can at every level possible to insure the data integrity. I do what is best for the users and the data integrity not what is the easiest for me to develope.

HTC,

Thank you very much for your input. Input from experienced developers is simply invaluable to the others still with a bit to learn.

"They also all use form level validation to allow better a "gentler" UI"

Can you give me an example of this scenario in Access? One thing that drives me utterly insane with badly developed Access applications is when a field is set to required and/or has a validation rule but no validation text, leaving the user to look at awful table.field references in the resulting message box.

 

[xenou]

Also agree - Boyd has put it quite succinctly. You've probably just come up with a good example right there You can also do nice things like if a required field is blank, have the cursor focus on the control that needs input, or have the label(s) turn red or show a *required notice beside them. I'm not sure that table rules in all cases will always tell you *what* fields are the problem.

 

[GopherUK]

The idea of changing control colour on a form is a good one and one I have thought of before.

Again, thanks for the help guys. When learning a new technology, it is so important for the learned to get knowledge from genuine experienced professionals.

 

[HiTechCoach]

I also like to use color. Humans are very visual. I like to highlight the control with the focus by changing the background and border colors/style of the control. I also set all the borders of requires controls to red and make the border wider.After a required control has valid data I change the border back the normal style.

I get inspiration for some web sites that have forms to fill out, like registration and shopping check out/payments. What I find interesting is the better the UI the less you notice it. It should be helpful but not intrusive or insulting to the user. I learned a lot from this book: About Face 3: The Essentials of Interaction Design: Alan Cooper, Robert Reimann, David Cronin: 9780470084113: Amazon.com: Books

 

[GopherUK]

Talking about MS SQL... I have never used it, but have used MySQL quite extensively.

Can I ask if you guys know if SQL Server Express is a good thing to learn if I want to get to grips with MS SQL? I really know nothing about MS SQL other than knowing that it is a true file server, whereas the backend from a split Access database is not (as can't be used to run queries remotely.).

And does SQL Server Express have any real use for commercial deployment?

 

[xenou]

SQL Server Express is the same database as SQL Server. So it's perfect for learning about MSSQL. There are limitations - size of the database being a noticeable one. It's fine for commercial uses ... in fact Access 2013 is now shipping with a SQL server Express engine as it's "cloud" back end.

Access is a file based database so it actually can be run remotely, if you aren't sending data back and forth across the wire. That's dangerous - anything goes wrong, lost connection, and your db could be corrupted. But members have posted here that it's fine to have users log in remotely to a terminal service (so the DB is really running locally, and the user is only sending keystrokes). Boyd probably knows more about this than I do - I think he's posted about that actually.

Here's Boyd's article on running Access over the web:
http://www.hitechcoach.com/index.php?option=com_content&view=article&id=33:remote

 

[GopherUK]

SQL Server Express is the same database as SQL Server. So it's perfect for learning about MSSQL. There are limitations - size of the database being a noticeable one. It's fine for commercial uses ... in fact Access 2013 is now shipping with a SQL server Express engine as it's "cloud" back end.

Access is a file based database so it actually can be run remotely, if you aren't sending data back and forth across the wire. That's dangerous - anything goes wrong, lost connection, and your db could be corrupted. But members have posted here that it's fine to have users log in remotely to a terminal service (so the DB is really running locally, and the user is only sending keystrokes). Boyd probably knows more about this than I do - I think he's posted about that actually.

Here's Boyd's article on running Access over the web:
Remote access for an Access Application

Thank you.

So would I be right in thinking that deploying SQL Server Express as the "backend" (i.e. a true file server) is preferable in commercial operations when there is only a small number of users? I would imagine the number of users acceptable would be at least that of a split Access DB?

 

[xenou]

That sounds like a rhetorical question to me. Of course so. I'm very happy with Access. If I were designing something for hundreds of users then I wouldn't even consider using it, unless it were front ended to a server based database. And in between - there is grey. Access does it's job very well but it's essentially a desktop database, not to be pushed to far. All that of course, it subject to change -- Access 2013 is ready to deploy as a cloud based DB with a sql server backend - not too shabby!