TABLE CLOSING

Question

I want to create relationships with referential integrity but i need to close my tables, I do not know how to close tables

Answer 1

Dear @Silindile Mhlongo,

I hope you’re having a good day 

To create a relationship with Enforce Referential Integrity, Microsoft Access requires that the tables involved are not currently open.

If you have a table open, you can close it by:

• Clicking the X on the table's tab.
• Right-clicking the table tab and selecting Close.
• Using the keyboard shortcuts Ctrl + W or Ctrl + F4 to close the active table, query, form, or report.

Please note that Access may display a prompt asking whether you want to Save, Don't Save, or Cancel any changes before the object closes. Make sure you respond to that prompt; otherwise, the table may remain open and Access will still prevent you from creating the relationship.

Also, if closing the tables does not resolve the issue, check whether any related queries, forms, or reports are still open, as these can also lock the tables and prevent changes to relationships.

Once all related objects are closed, go to: Database Tools > Relationships

Then create the relationship and select Enforce Referential Integrity.

If Access still displays an error message after everything is closed, please share the exact message.

I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.   

Looking forward to hearing back from you with any updates or additional details.  

Warm regards,  

---

If the answer is helpful, please select "Yes, accept answer". If you have any questions or new updates, please feel free to comment below. 

Answer 2

Close each open table by clicking the X on its document tab, or right-click any table tab and select Close All; then create the relationship from Database Tools > Relationships.

Close the tables

1. Save any table design changes.
2. At the top of the Access workspace, locate the tabs showing the names of your open tables.
3. Either:
   • Click the X on each table tab, or
   • Right-click a tab and select Close All.

This closes the table windows, not the tables or database itself. If document tabs are hidden, press Ctrl+F4 to close the active table. ^1^

Create the relationship

1. Select Database Tools > Relationships.
2. On Relationships Design, select Add Tables.
3. Select the tables, click Add, and then close the Add Tables dialog.
4. Drag the primary-key field from the parent table onto the matching foreign-key field in the child table.
5. In Edit Relationships, verify the selected fields.
6. Select Enforce Referential Integrity.
7. Click Create, and then save the relationship layout. ^2^

A successful one-to-many relationship displays 1 beside the parent table and ∞ beside the child table.

If Access will not enforce referential integrity

Check these requirements:

• The parent field must be a primary key or have its Indexed property set to Yes (No Duplicates).
• The child field should allow duplicate values for a one-to-many relationship.
• The related fields must have compatible data types. If the parent key is AutoNumber, the child foreign key can be Number, but their field sizes must match—normally Long Integer.
• Every existing nonblank foreign-key value in the child table must already have a matching parent-key value. Otherwise, correct or remove those orphan records first. Referential integrity prevents new orphan records and blocks deletion of parent records that still have related child records. ^2^

Enable Cascade Delete Related Records only if deleting a parent record should automatically delete all its related child records.

---

References

1. Show or hide object tabs
2. Create, edit or delete a relationship | Microsoft Support