When you define a Foreign Key in a table, for example when you establish a connection between one of its columns and the primary key of another table, a relationship1:N is created between the table with the primary key and the one with the related column. It means that each record of the first table has N records of the second one connected (with N>=0), and also that each record of the second table belongs to one and only one record of the first table.
For example, look at the following relational diagram, extracted from Northwind.mdb, the Microsoft Access sample Database:
In the table Orders a Foreign Key has been defined on the column CustomerID, establishing a connection with the primary key CustomerID of the table Customers: it means that each customer can have many orders connected, while each order belongs to one and only one customer.
Let's call the table with the primary key main table of the relationship, and the table with the Foreign Key referring to that primary key secondary table of the relationship. In the example, Customers is the main table, and Orders the secondary table, with regard to the relationship that derives from the Foreign Key defined between the two columns CustomerID.
On the other hand, as you can see in the example, a table that is secondary with regard to a relationship can also be primary with regard to another one. In fact, in the table Order Details a Foreign Key has been defined on the column OrderID, establishing a connection with the primary key OrderID of the table Orders, so that each order can have many details connected, while each detail belongs to one and only one order. With regard to this relationship, Orders is now the main table, and Order Details the secondary one.
These relationships yield thus a chain of related tables (which can be extended more and more), where each table represents a level of an N-tree having the single records as nodes. Let's call the only table that is not secondary with respect to any relationship, i.e. the first table, corresponding to the 0 level of the N-tree (in the example the table Customers) main table of the chain.
Such a situation is not difficult to find at all: just consider, besides the example mentioned above, the description of a system parts, where each part can be made up in its turn by sub-parts. The main problem in implementing the management of a chain of related tables derives from the difficulty of adding new data when we want to unify more than one level in a single insertion operation.
Let's imagine, for example, that a customer makes a new order to request some products at certain conditions. In a hypothetic application we are projecting, we have decided that, after having selected the customer (that we suppose for simplicity already in the Database), we must click the button New order, which opens a form to be filled in with the order data. In the form, a button New Detail is needed, in order to add, one at a time, the different details of this order. After having added all the details and filled in the form, we can save everything clicking on the button Save Order.
Such a project seems to be quite reasonable, but at the implementation time we would meet with a difficulty: if the details are saved in the Database only when the order is saved, where should they be memorized in the meanwhile?
It is evident that we need to define an auxiliary structure where the details data can be temporarily memorized, and where to pick out when the order is saved in the Database.
The problem would become more complicate if we decided, for example, to save more than one order at a time. In this case we should move the button Save Order at the customer's level, transforming it in a button Save Orders in charge of saving in the Database the N newly inserted orders. This time a two level auxiliary structure would be necessary, in order to temporarily memorize each order with the corresponding details. More levels we want to unify, more complex the management of the needed auxiliary structure would become.
The classes ExtendedODBCRecSet and ExtendedDaoRecSet extend the functionality of the classes they derive from (respectively CBufferRecSet and CBufferDaoRecSet) to represent the intermediate element of a chain of related recordsets. In addition to the auxiliary structure, used to manage the related new records to be saved in the Database, they define the statuses an intermediate recordset can be in, due to its interaction with the other recordsets of the chain:
In order to guarantee the data consistency, each intermediate recordset takes also care of updating the recordsets that follow in the chain. In general, the operations allowed in each moment depend on the status the recordset is in, and normally concern the recordset as a chain element. For global operations, i.e. the ones concerning the chain as a whole, the classes MainODBCRecSet and MainDaoRecSet, respectively derived from ExtendedODBCRecSet and ExtendedDaoRecSet, have been defined. They represent the main recordset of the chain (i.e. the first element), which is given the ability, through member functions, of adding a new level at the end of the current chain (AddNewLevel), executing the Open method of any level of the chain (GlobalOpen), saving the auxiliary structure in the Database (Update), cancelling a pending operation (CancelUpdate), etc.
(Go immediately to the sample launch)
To see a sample about how the management of a chain of related tables is carried on, you must have the English version of Microsoft Access installed, along with the sample Database Northwind.mdb. You also need a browser supporting the downloading of an ActiveX control and its interaction through JavaScript, so if you don't have Microsoft Internet Explorer version 4.0 or later, it will be a hard try!
Since I had no intention of paying a Certification Authority (an entity in charge of issuing digital certificates) to sign my software with a trusted certificate, I created my own certificate, and signed the sample software with that. A software signature assures that:
When a certificate has not been issued by a Certification Authority, you don't know anything about its owner, so before using his software you must decide if you trust him or not (you never know, he could be ill-disposed...). Well, I hope you trust me, at least because I'm warning you...
If you want to have a look at my certificate, you can do it choosing to open the file (not to save it!) after having clicked here. Besides various technical data, you will find in it some information about the issuer (myself) and the certificate validity.
The only thing I have to warn you about, as far as the security of the sample software is concerned, is that it establishes a direct access to the Northwind.mdb Database, so you will be able to modify, add and delete its records as much as you wish. If you want to keep the original data and can't reinstall the Database, it's better for you to make a backup copy before!
If this is the first time you run this sample, first of all seek in your computer the file Northwind.mdb. If it doesn't exist, it means that you don't have the Microsoft Access sample Database installed (that is one of the system requirements), so you can't run this sample (install it before continuing!). If it exists, remember where it is, because its location will be useful to continue.
As soon as the sample is launched, a new window will try to download and install an ActiveX component. Depending on the security level configured in your browser, some warning messages against untrusted software can be shown. This is because I signed the component with a certificate which wasn't issued by a Certification Authority (see the section Software reliability). Anyway, if you want the sample to be run, you must give the authorizations you are asked for.
Now, supposing that the component has been downloaded and correctly installed (otherwise, probably the browser is not fulfilling the system requirements), a dialog box will appear, through which you will let the software localize the NorthWind.mdb Database in your computer (in this respect, have a look at the section Software security).
Since there exist two different kinds of access to the Database data (DAO and ODBC), check the following table to choose the kind you would like to try. Take into account that this choice will condition the type of dialog box appearing at the beginning of the sample, and the way the software will access the Database NorthWind.mdb, while the operations available during the sample will remain unchanged (to know what they are, look at the sample help) .
Once you have made a decision, you can launch from the bottom of the table the kind of sample you prefer.
DAO (Data Access Objects) |
ODBC (Open DataBase Connectivity) |
Database access will take place directly through the Microsoft Access engine (the Microsoft Jet Database Engine). |
Database access will take place communicating with a driver implementing for Microsoft Access the standard ODBC protocol. In order to have the communication working, you must have defined a Microsoft Access ODBC connexion to the Northwind.mdb Database. If such a connexion hasn't been defined yet, you can create it either immediately, selecting ODBC Data Sources (32 bits) from the Control Panel, or after having launched the sample, when the dialog box asks you for the connexion name. For further details check the on-line help at creation time. |
All you will have to do when the dialog box opens will just be to select the location in your computer where the file containing the Database (Northwind.mdb) is. |
All you will have to do when the dialog box opens will just be to change to the computer data sources panel, and select the name of the connexion to be used (if the connexion hasn't been defined yet, this will be the right moment to do it, clicking on the button New...). |
![]() |
![]() |
Using the classes mentioned above, I created a program with Visual C++ to access the Microsoft Access 97 sample Database (Northwind.mdb), considering just the tables chain Customers, Orders, Order Details. Then I carried out three types of tests, each with both DAO and ODBC access. For statistical interest, in addition to the Release version of the program I nearly always performed the tests with the Debug version as well.
The tests were carried out on a personal computer with a 333 MHz Pentium II MMX microprocessor, and 64 MB of RAM.
10 customers inserted (table Customers), with 10 orders each customer (table Orders), and 10 products each order (table Order Details), summing up 1,110 records.
DAO | Release | 2.470 | 1.370 | 1.320 | 1.320 sec | (average 1.620 sec) |
Debug | 3.790 | 3.020 | 3.020 | 3.020 sec | (average 3.213 sec) | |
ODBC | Release | 2.810 | 1.930 | 1.760 | 1.810 sec | (average 2.078 sec) |
Debug | 4.560 | 4.070 | 3.460 | 3.410 sec | (average 3.875 sec) |
20 customers inserted (table Customers), with 20 orders each customer (table Orders), and 20 products each order (table Order Details), summing up 8,420 records.
DAO | Release | 9.500 | 9.450 | 9.390 | 9.500 sec | (average 9.460 sec) |
Debug | 21.590 | 21.370 | 21.420 | 21.370 sec | (average 21.438 sec) | |
ODBC | Release | 17.520 | 17.790 | 17.520 | 17.800 sec | (average 17.658 sec) |
Debug | 30.420 | 30.100 | 29.770 | 30.430 sec | (average 30.180 sec) |
50 customers inserted (table Customers), with 50 orders each customer (table Orders), and 50 products each order (table Order Details), summing up 127,550 records.
DAO | Release | 3 min 3.620 sec | 2 min 43.510 sec | (average 2 min 53.565 sec) |
ODBC | Release | 8 min 44.370 sec | 7 min 35.610 sec | (average 8 min 9.990 sec) |