Modifying Microsoft Access Linked Tables from SQL Server

By:   |   Comments (2)   |   Related: > Microsoft Access Integration


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.


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 
SubCategoryName NVARCHAR(15) NOT NULL,
Description VARCHAR(50) NULL

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. 



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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, May 28, 2013 - 4:40:04 AM - Gauri Back To Top (25162)

I am new to MS Access and its concept of link tables. I have a situation where I have to select only few columns when linking tables. Can anybody help me with this please?

Monday, October 25, 2010 - 4:53:52 PM - Andrew Whittington Back To Top (10301)

Thanks for the tip on deleting the Access links to SQL, but you do not mention any difference between links to SQL table and links to SQL views.

Yet I have found a difference...

If I delete a link to a table, I can re-link again with no problem.

If I delete a link to a view, when I try to re-link, Access thinks the SQL view does not exist.

It certainly does exist, as I checked out the SQL server, but why does Acces think otherwise?

get free sql tips
agree to terms