More of this Feature • Page 1: Data relationships • Page 3: ADO master-detail

Join the Discussion "Post your views and comments to this chapter of the free Delphi database Programming Course" Discuss!

Related Resources • free DB Course.TOC • more Database articles

   MS Access relationships
Our focus will be on the following two tables: Customers and Orders. Both tables are a part of the DBDEMOS database that comes with Delphi. Since both tables are Paradox tables, we'll use the code from the previous article to port them to our working aboutdelphi.mdb MS Access database.

Notice that when you port those tables to Access both of them have no index or primary key nor are they linked in any way in Access.

The power in a relational database management system such as MS Access comes from its ability to quickly find and bring together information stored in separate tables. In order for MS Access to work most efficiently, each table in your database should include a field or set of fields that uniquely identifies each individual record stored in the table. If two tables are liked in a relation (of any kind) we should set that relation with the MS Access.

Customers-Orders relation
To set up the relationship, you add the field or fields that make up the primary key on the "one" side of the relationship to the table on the "many" side of the relationship. In our case, you would add the CustNo field from the Customers table to the Orders table, because one customer has many orders. Note that you have to set the CustNo in Customers to be the primary key for the table.

When creating a relation between two tables MS Access provides us with the Referential Integrity feature. This feature prevents adding records to a detail table for which there is no matching record in the master table. It will also cause the key fields in the detail table to be changed when the corresponding key fields in the master are changed - this is commonly referred to as a cascading update. The second options is to enable cascading deletes. This causes the deletion of all the related records in a detail table when the corresponding record in the master table gets deleted. These events occur automatically, requiring no intervention by a Delphi application using these tables.

Now, when we have all the relations set up, we simply link few data components to create a master-detail data browsing Delphi form.

Next page > Master-detail with AdoExpress > Page 1, 2, 3

DB Course Next Chapter >>
>> New...Access Database from Delphi