Double-Checking Existing Functionality

Access will not warn us about existing dependencies if we delete an object, so we need to check on each of the assets in the database manually. Since our database is still rather unpopulated, we only have to check a few objects to be sure we haven't lost any functionality. Specifically, we need to check the queries that we built earlier. If we had a more complete database at this point, we might also have to check forms and/or reports. Let's start by looking at qryFacultyCoursesSectionsSummer18.

Step1. To open qryFacultyCoursesSectionsSummer18, in the Navigation pane,

Right-Click qryFacultyCoursesSectionsSummer18, Click Design view

The query opens. Pay close attention to the tables in the designer:

NOTE: Your tables might be arranged differently. You can rearrange them if you would like, but the appearance of the tables is inconsequential as long as the right tables are present.

We see tblSections. Momentarily, we will be deleting tblSections, so we need to replace it with our new table, tblSectionsMultiInst. This is a relatively simple process. First, let's add tblSectionsInstructors and tblSectionsMultiInst to the query.

Step2. Add tblSectionsMultiInst and tblSectionsInstructors to the query.

Before we remove tblSections, we need to modify the source table for a few fields:

We want to make sure that tblSections is not referenced, so we'll just change it to tblSectionsMultiInst.

Step3. To change the source tables, on the Query Design Grid, for SemesterYear, in the Table row,

Click tblSections, Click, Click tblSectionsMultiInst

We need to do the same thing for SectionNumber.

Step4. To change the source tables, on the Query Design Grid, for SectionNumber, in the Table row,

Click tblSections, Click, Click tblSectionsMultiInst

We're ready to remove tblSections from this query.

Step5. To remove tblSections, in the Query Designer,

Right-Click the tblSections title bar, Click Remove Table

The table is removed.

NOTE: If there were other dependencies in the query, Access wouldn't allow us to delete it.

Let's save and close the query.

Step6. Save and close qryFacultyCoursesSectionsSummer18.

NOTE: The Relationships window should still be open.

Updating the Other Queries

Before we delete the relationship, let's be sure the rest of our queries work as expected.

Step1. Update the functionality of qryCoursesSectionsAndAllFacultySummer18, qryCoursesAndSectionsSummer18, and qryCoursesAllSectionsFacultySummer18 to be sure they are not referencing tblSections.

Deleting Tables

Now, let's remove tblSections. When we do, we will remove its relationships with tblCourses and tblFaculty as well. This table is no longer needed since we've removed it from all existing database objects.

Step1. To delete the table, in the Navigation pane,

Click tblSections, press: Delete key

Access prompts us to confirm that we want to delete the table and its relationships.

Step2. To confirm the deletion,

Click, Click

The table is now deleted.

Since we don't have a tblSections anymore, we can rename tblSectionsMultiInst to tblSections to make the table names a little easier to read.

Step3. To rename tblSectionsMultiInst, in the Navigation pane,

Right-Click tblSectionsMultiInst, Click Rename, type: tblSections, press: Enter key

The table has been renamed to tblSections.