Database - Why is using bound controls a bad thing?

When you first start working with databases, you will probably be shown how to do it using Data Controls (eg: ADODC1, or Data1) or Data Environments, and then binding other controls (like TextBoxes) to them. These methods are known as Bound controls (as the TextBoxes etc are ‘bound’ to the database fields).

As you get more experience, you will find out (or be told by others) that Bound controls have awkward limitations, and are in fact a far from ideal method.

If you are using the standard Data Control (by default called "Data1" when you put it on a form), have you even read the help for it? The help was written way back in 1998, and explicitly states that this control should not be used - even back then it was only for backwards compatability with existing code! These days it is unsupported - which means that it is likely to fail if you try to install it on new computers (especially ones with 64bit versions of Windows).


So what should you be doing to work with databases, rather than have Bound controls? The simple answer is ADO code (or previously, DAO code), which is considered to be the 'proper' way to link to databases from 'Classic' VB (version 6 and ealier).

There are several reasons for the code option being considered to be better (and therefore getting used much more widely among professional developers), which we will now look at.

• Possibly the most important reason for code being preferred is that you have control over what happens – you decide what should be allowed, and what shouldn’t (and also when things should be allowed). For example:
  
  • if the user should only be allowed to save a record if all fields (or a certain field) have been filled in, you can easily restrict this in your save routine.
  • if the user should only be able to delete a record if a certain field has been filled in, you can add this too.
  • if you want to have different permissions for different users (for example, user A can add and edit records, but user B can also delete them), then this can be done with little fuss. (Hint: permissions would be stored in a separate table, and read in using another recordset)
  • …
  
  .
• One major issue as your programs get bigger and used by more people, is how your program deals with multiple users at the same time.
  
  If two people try to edit the same record, what should happen? Would you want the user who started editing first to have their edit saved, or the last one who pressed ‘save’? Also, should the other user be notified that their edit was overwritten (or wont be allowed, before they start “typing an essay”)?
  
  With Bound controls you have no say at all in which users edits get kept, and no message will be shown to the other user. With code, it is entirely up to you!
  .
• If your database is starting to grow quite large (or it is stored on a server which is somewhere else, so communication takes a long time), a big issue is speed. Using code you can tweak the settings in various ways to improve the speed (such as only loading a certain number of records); if you are using Bound controls, you cannot do anything.
  .
• The next issue is one of installation. You’ve got your program working nicely on your computer, and now it’s time to give it to your users… only to find out that you need to put the database in the same folder as it was on your computer – which they may not have. You also need to install the extra files for the Data Control/Data Environment, which is a source of potential problems.
  
  It is possible to correct this, but it has caused problems for several forum members before… which brings us nicely on to something which could be very important for you:
  .
• As most experienced programmers don’t use bound controls (several of us have been known to refer to them as “Evil” due to the stress that they have caused), there aren’t many people who know how to help if you have a problem (and those that do often don’t know much more than you).
  
  This means that, unfortunately, any questions you have are unlikely to get a quick answer, if you get an answer at all. Questions about ADO code on the other hand tend to be answered pretty quickly.

And now for the advantages of using bound controls:

• They help to keep things relatively simple when you are first learning to work with databases, as you do not need to write much (if any) code to get them operational.
  .
• It is a quicker to set up the form and database connection (but after you have used the code version a few times, this only saves a couple of minutes at most!

There are probably issues/advantages that I have missed, if you are aware of any then please let me know so that I can add them to the lists.


If you want to find out how to use ADO code, a good place is in the Database Development FAQ, especially the ADO Tutorial thread.