Problem
In a previous tip we saw how easy it was to link to SQL Server tables from Microsoft Access. As is the case with all systems, how does Access manage the changes? What happens when you modify the structure of the underlying SQL Server table? What happens to the SQL Server table if you delete the linked table in Access? We will look at each of these situations in this tip.
Solution
Let’s take a look at both of those situations one step at a time. Let’s start with modifying a Linked SQL Server table’s structure. This is accomplished by navigating to the Tables tab, selecting the appropriate table and right clicking on the ‘Design View’ option on the menu.
When you attempt to modify the design of a linked table in Access you receive the following message:
This is because the design of the linked table is owned by the underlying object within SQL Server. Although the user may have been granted db_owner role rights in the underlying SQL Server database, that user is unable to make data definition changes (DDL changes) to the table. If you need to make changes to the table you will need to do so in Microsoft SQL Server and then re-link the table.
We will use the Territories table in the Northwind database as an example. The current schema for the table looks like this from a SQL Server perspective:
The design is reflected in Access as:
We will now add two columns to the SQL Server table; RegionSupervisorID (int, null) and TerritorySupervisorID (int, null) as shown below:
After the changes, the Access table will still appear as it did prior to the DDL changes:
In order to recognized the structural changes in the underlying SQL table you will need to make use of the Linked Table Manager within Microsoft Access. To launch, select Tools\Databases Utilities\Linked Table Manager from the menu bar in Access.
Once you select the table(s) you wish to update select OK. You will receive a notification that all tables were successfully updated. Then cancel out of the Linked Table Manager to return to the main Access interface.
Now, if we are to look at the linked table in Design Mode within Access you will see the two new columns we added in SQL Server.
Adding a Primary Key in Microsoft Access
One of our community members, James R. asked in the forums of the Linked SQL Server Tables tip about skipping the assignment of a primary key during the initial linking. What happens if you skipped assigning a primary key in Access and now wish to do so? The process is identical to what I just demonstrated in regards to DDL changes to the SQL Server table. Let’s take a look at the process using a table that I am going to create in the Northwind database on my SQL Server instance called SubCategories. The table will be a heap (no index) when created initially using the following script.
CREATE TABLE dbo.SubCategories
(
SubCategoryID INT IDENTITY(1,1) NOT NULL,
SubCategoryName NVARCHAR(15) NOT NULL,
Description VARCHAR(50) NULL
) ON [PRIMARY]
I’ll now link to the SubCategories table in Access and not assign a Unique Record Identifier at this time (since there is no primary key defined in the SQL Server table), using the techniques demonstrated in the initial tip on Linked SQL Server tables in Microsoft Access.
I’ll now switch back to SQL Server and add a primary key by running the following T-SQL command.
ALTER TABLE dbo.SubCategories ADD CONSTRAINT
PK_SubCategories PRIMARY KEY CLUSTERED (SubCategoryID)
WITH
(
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
Once done I will launch the Linked Table Manager and follow the same process we used for our Territories table example.
If we look at the SubCategories table in Access we’ll see that the primary key has now been set. Hope this answers your question James R. Thanks for asking it! We appreciate the community feedback.
Deleting a Linked Table In Access
Stating it simply, deleting a linked table in Microsoft Access has absolutely no effect on the underlying SQL database. Just as the user, via Microsoft Access, has no rights to make DDL changes to the underlying SQL Server database (no matter what rights have been granted to that user in the SQL Server database) they are also unable to delete the table directly in the Microsoft Access application. Deleting the table in Access only removes that linked table. It is similar in behavior to Linked Servers in Microsoft SQL Server. When you delete the Linked Server, you’re merely removing the link, not deleting the server instance.
Here is a sample screen shot to show the message from Microsoft Access:
Next Steps
- As you manage your users reporting via Microsoft Access keep this tip in mind as the SQL Server environment changes. Changing keys, adding, removing or renaming columns occur on a regular basis and do have an impact on the user community. So as you make changes, consider the Microsoft Access implications.
- Review the other tips in this series on SQL Server interaction with Microsoft Access.
- Continue to read those forums and ask questions if something in the tip is unclear. We value the feedback from you, the community.
