Add some sample data to two tables, one about authors and the other about books they have written. There should be a common field of author ID in both tables. Add a relationship between the tables using the primary key of authorID in the Authors table and the foreign key of authorID in the Books table. Save the database in 2002 format, close Access andthen switch to Delphi. On the first form in a new project create a menu structure as follows:
In the Forms section add 'Authors', 'Books' and 'Authors-Books'. Set the code for each item to something like 'formn.visible:=true'.
On a new form, do the following:
This sets up the ADO connection to the database on which the other parts of the application depend (for more on ADO see the relevant section of the notes on databases). The connection includes the full path name to the database. It is a good idea to store the database in the same folder as the program code and to remove all the path details except the name of the database, for example 'N:\Borland Delphi Studio Projects\MyFolder\mydata.mdb' becomes simply 'mydata.mdb'. The Delphi program will find the file as long as it is in the same folder. This will make your programs portable as there is no embedded path name; with the full path name embedded in the connection you would not be able to transfer the program to another PC without an identical address (few stand-alone PCs will have an N drive unless you set one up when you partition the disc).
Now set some properties of the components on the form:
Finally, add a Close button to the form (code: authors.visible:=false;)
See the reference section on the DBNavigator component for further details.
The DBGrid in the form above was edited by right clicking on it and selecting 'Columns Editor' from the popup menu.
To display the fields click on the third icon, at which point the list of properties is displayed:
The original width of the Name column was set to 50 characters, which is too wide for display purposes in the grid. We can change it to a smaller amount with the Width property. We shall use an alternative way of displaying the data from the Books table.
Set up a new form for the Books data and add an ADOTable component, A DataSource component and a DBNavigator component, but not a DBGrid. Set up the properties of these components as before so that the Books table can be used. Now double click the ADOTable component and then right click the small window that opens. Choose 'Add all fields' from the popup menu and then add them to the form by selecting and dragging them (use Shift-click to select more than one).
Set the Active property of the ADOTable component to True and you should see the table data, this time in separate field boxes:
This time we can use the navigator bar because only one record at a time is visible. To add a new record click on the '+' icon and fill in the blank fields. You can look at the Access database to check that new data is there.
Now we will create a form that displays related data from both tables, all the books for each author. Proceed as follows:
You should now see the data for Authors and the data for Books in their respective areas of the form and you should be able to move from author to author with the buttons on the navigator bar. The final touch is to display only the books associated with a particular author, using the relationships you established in Access.
To do this select the ADOTable2 component and set its MasterSource property to DataSource1. Note that the MasterSource property is set for the table on the many side of a relationship. In this simple example an author writes one or more books (in reality some books are written by many authors but we shall ignore that here; fiction books generally have just one author). The Authors table is the master in the relationship so Books has a MasterSource property.
Click on the ellipsis in the MasterFields property and choose the key fields that will establish the relational link between the two tables.
Select the Author and Name fields from the two lists and click the Add button and OK - this will set up the links on the form in Delphi. You should now see only those books written by the currently displayed author. (This is the point where the primary key and foreign key items should have different names; if they have the same name Delphi will not allow the link between the tables and will display a message saying that the tables are incompatible.)
Add a second navigation bar so that new books can be entered as well as authors. We can now edit each table on separate forms and also on a related form.
One further thing we can do to develop the application is to provide the means to count the number of books listed for each author. We can do this by adding a button and attaching this code to it:
procedure TForm1.Button2Click(Sender: TObject);
var total:integer;
begin
ADOTable2.DisableControls;
try
total:=0;
ADOTable2.First;
while not ADOTable2.Eof do
begin
total:=total+1;
ADOTable2.Next;
end;
edit1.Text:=inttostr(total);
finally
ADOTable2.EnableControls;
end;
end;
This code sets a counter to zero and then increments it as records are read from the table. This code shows how to iterate over the records in a table but in this case there is a simpler way to get the total number of records. The ADOTable component proves a RecordCount property, which we can set when the OnEnter event of the table is fired or when the OnClick event is fired for the DBNavigator (other events could be used to achieve the same effect).
procedure TForm1.DBGrid1Enter(Sender: TObject);
begin
edit1.Text:=inttostr(ADOTable2.RecordCount);
end;
Try using the form based on two tables. In a real project you would have to write user documentation to explain how to use the facilities you have provided. The main focus is the navigation bars, a user needs to understand how they work and what their purpose is in the application. In particular, when a new book is added to the lower table it will only be displayed if the author matches that in the table above.