Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Modifying Microsoft Access Linked Tables from SQL Server

MSSQLTips author Tim Ford By:   |   Read Comments (2)   |   Related Tips: More > Microsoft Access Integration

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



Last Update: 5/2/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Monday, October 25, 2010 - 4:53:52 PM - Andrew Whittington Read The Tip

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?


Tuesday, May 28, 2013 - 4:40:04 AM - Gauri Read The Tip

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?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.