This article will explain the steps to create and using a Linked Server on an Access 2007 Database. The steps include creating a Linked Server using GUI and through T-SQL script.

The picture below displays location of the 2 Access Databases (My Documents directory) used in this exercise.

To create a Linked Server using the New Linked Server dialogs, start at the Server Objects in the Object Explorer in SSMS. Expand Server objects and on the Linked Servers, right click and select New Linked Server. (Picture below)

In the New Linked Server dialog box, type the linked server name that you would like to create and use (It is
not a must to have the actual name of the Access database, in the example we are naming
LINKEDSERVER_ACCESS. For Server type, choose Other data source, and for Provider from the drop down
list, choose Microsoft Office 12.0 Access Database Engine OLE DB Provider.

For the Product Name, type ACCESS and for the data source give the location where the access database is located. (Picture below)

After entering above mentioned details, select the Security Page in the left hand pane of the dialog box. In the security settings choose the option Be made using the login’s current security context. Click Ok and the Linked Server is created.

The picture below displays the query to update a table and select statement to retrieve the table data.

Alternately you can add a linked server using the system stored procedure sp_addlinkedserver in the Master DB. The picture below shows the parameters for the stored procedure such as servername, srvproduct, provider and the datasource. After adding the Linked Server, the next query is a select statement to select few records from the newly created Linked Server.

 Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru that let’s you know when there is a new blog post.

–Bru Medishetty