TABLE CLOSING (Microsoft Access)

Silindile Mhlongo 20 Reputation points
2026-10-26T08:29:22.8+00:00

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

Microsoft 365 and Office | Access | For education | Windows
0 comments
Answer accepted by question author
Liora 3,285 Reputation points Independent Advisor
2026-10-26T08:50:46.4566667+00:00

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. 

Was this answer helpful?

2 people found this answer helpful.

2 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Ken Sheridan 3,581 Reputation points
    2026-10-30T13:55:54.0266667+00:00

    One thing I would add to the replies you have received is that, where natural keys rather than surrogate autonumber keys are used, in most cases Cascade Updates should also be enforced in the relationships. If the value of a primary key is changed the values in any referencing tables will also be changed automatically. The image below is for a little demo database of medical prescriptions in which natural keys are used, with the exception of PatientID. Natural keys cannot be used for personal names of course, as they can legitimately be duplicated. Apart from this one exception Cascade Updates are enforced in all of the relationships:

    RelationshipsModel

    The use of natural keys in this case is partly because, for the purposes of the demo, this shows the real-world relationships better than would be the case if surrogate keys had been used, but also because, in the user interface, correlated combo box controls are used, in which the selection of a value in one control determines the available values listed in another control. The use of natural keys makes the the building of correlated controls much simpler, especially in a form in continuous forms view.

    Was this answer helpful?

    0 comments

  2. AI answer

    2026-10-26T08:30:14.76+00:00

    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
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.