Relation object (DAO) | Microsoft Docs
You can define a primary key either by creating a primary key index after the table is created, or by using the CONSTRAINT clause in the table. VBA code for easily creating MS Access Relations.; Author: sazakir; Updated: 11 Jun ; Section: Office Development; Chapter: Enterprise. That will likely be more involved than deleting the relationship. See this detailed example: VBA code for creating MS Access Relations.
For example, suppose you want to add an invoices table to an invoicing database.
VBA code for creating MS Access Relations
Every customer in your customers table can have many invoices in the invoices table—this is a classic one-to-many scenario. You can take the primary key from the customers table and define it as the foreign key in the invoices table, thereby establishing the proper relationship between the tables.
The following statement assumes that the tblCustomers table has already been built, and that it has a primary key defined on the CustomerID field. The statement now builds the tblInvoices table, defining its primary key on the InvoiceID field. It also builds the one-to-many relationship between the tblCustomers and tblInvoices tables by defining another CustomerID field in the tblInvoices table.
This field is defined as a foreign key that references the CustomerID field in the customers table. Now create a shipping table that will contain each customer's shipping address. Assume that there will be only one shipping record for each customer record, so you will be establishing a one-to-one relationship.
Constraints Constraints can be used to establish primary keys and referential integrity, and to restrict values that can be inserted into a field. In general, constraints can be used to preserve the integrity and consistency of the data in your database. There are two types of constraints: A single-field constraint, also known as a column-level constraint, is declared with the field itself, after the field and data type have been declared.
Use the customers table and create a single-field primary key on the CustomerID field. It is a good idea always to name your constraints.
Creating and Deleting relationships in VBA
Assume that you want to restrict or constrain the values of the first name and last name fields to be unique, meaning that there should never be a combination of first name and last name that is the same for any two records in the table. Because this is a multi-field constraint, it is declared at the table level, not the field level. The expression that defines the check constraint can be no more than 64 characters long.
Suppose that you want to check each customer's credit limit before he or she is added to the customers table.
- Relation object (DAO)
Use the Name property of all Field objects in the Fields collection of a Relation object to set or return the names of the fields in the primary key of the referenced table, or the ForeignName property settings of the Field objects to set or return the names of the fields in the foreign key of the referencing table.
If you make changes that violate the relationships established for the database, a trappable error occurs.
If you request cascading update or cascading delete operations, the Microsoft Access database engine also modifies the primary key or foreign key tables to enforce the relationships you establish.
For example, the Northwind database contains a relationship between an Orders table and a Customers table. For the Microsoft Access database engine to accept a new record in the Orders table, it searches the Customers table for a match on the CustomerID field of the Orders table.
If the Microsoft Access database engine doesn't find a match, it doesn't accept the new record, and a trappable error occurs.
Define relationships between tables using Access SQL
When you enforce referential integrity, a unique index must already exist for the key field of the referenced table. The Microsoft Access database engine automatically creates an index with the Foreign property set to act as the foreign key in the referencing table. To create a new Relation object, use the CreateRelation method. To refer to a Relation object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms: The procedure attempts to add a record with a deliberately incorrect CategoryID; this triggers the error-handling routine.
OpenRecordset "Products" ' Print a report showing all the different parts of ' the relation and where each part is stored.8. (Advanced Programming In Access 2013) Creating Relationships In SQL Server
Update On Error GoTo 0. Close End With dbsNorthwind. Errors MsgBox "Error number: It also demonstrates how creating a new Relation will also create any necessary Indexes in the foreign table the DepartmentsEmployees Index in the Employees table.