Configuring a Linked Microsoft Access Server on SQL 2005 Server

There are certain situations in which you might have to access data that is stored in various locations. In this case, you might want to use a linked server on SQL 2005 to access data from a Microsoft Access database. This article explains how to set that up.

Introduction

When enterprise data gets scattered geographically, there is a need to access data stored in different locations to make business decisions. As a consequence of the evolutionary growth of databases, and driven by business practices, the enterprise data may reside on different kinds of databases. You might even have to deal with file based storage of legacy data.

MS SQL servers always provided remote access to servers through RPC mechanisms, but they had the limitation of being confined to invoking procedures on remote locations. A linked server (a virtual server) may be considered a more flexible way of achieving the same thing, with the added benefits of remote table access and distributed queries.

This article is about configuring a linked server on SQL 2005 to access data on a Microsoft Access database. The article describes in detail how to set up and use such a linked server on Microsoft’s latest database product, the MS SQL 2005 Server. The linked server appears in the Server Objects folder in the SQL Server 2005 Management Studio.

Linked servers can be established on an MS Access database application where you use the ODBC interface to access external data. Usage of such data are only limited by the limitations of the Access database. In MS SQL 2005 server, however, the external data is accessed through the OLEDB interface, and the limitations are those imposed by this provider.

{mospagebreak title=Linked Servers on SQL 2005 Server}

On a SQL 2005 server you can use either the stored procedures or the Management Studio to configure the linked servers. This tutorial will show both of these options. In either case, one needs to know the location of the database file on the external system.

Using Stored Procedures

SQL 2005 server makes it easy by providing you with templates. Open the Templates Explorer from the View menu item as shown in the next picture.

From the templates folder expand the node Linked Server as shown.

Double click the Add Linked Server Access MDB and you get the following template file, shown in the next picture. Using this as a template you can fashion your script. The first stored procedure, sp_addlinkedserver, adds a linked server to the Management studio. Since this database is external data, the next stored procedure, sp_addlinkedsrvlogin, creates a login from the local server you are working (SQL 2005) to the external database (MS Access database). The next, sp_tables_ex, shows all the tables in the external database. Since the Microsoft Jet OleDB provider does not support the Catalog and Schema, the query can be run against the linked server table using the syntax shown here. The keyword GO is used to submit as a batch.

 

Setting up the Linked server: an example

The MS Access 2003 database used for configuring the linked server ‘AccessDb‘ is at the following location on the hard drive: C:Documents and SettingsJayMy Documentsnwind.mdb where nwind is the example database, Northwind.mdb. The following script creates the Linked Server, ‘AccessDb‘.

exec sp_addlinkedserver	@server='AccessDb',
	@srvproduct='Access',
	@provider='Microsoft.Jet.OLEDB.4.0',
	@datasrc='C:Documents and SettingsJayMy Documents
nwind.mdb'

After you execute this procedure in the Query Editor and refresh the Linked Servers node in the Management Studio, you should be able to see this object added to the list of linked servers as shown.

The next stored procedure adds the ‘Admin’ as the user to the database. If the variable @useself, which takes the Boolean values true or false is set to true, then the local server logins will use their username and password, and the variables @rmtuser and @rmtpassword will be ignored. When it is set to false, the remote username and password will be used, which for an MS Access database is ‘Admin’ and no password. Although the template shows that NULL can be passed to the variable @rmtpassword, you will get an error passage if you run a query on this linked server (Msg 137, Level 15, State 2, Line 18 Must declare the scalar variable “@rmtpassword”). In the code shown, which works, an empty string is passed to the variable.

exec sp_addlinkedsrvlogin @rmtsrvname='AccessDb', 
	@useself='false', 
	@rmtuser='Admin', 
	@rmtpassword=''

{mospagebreak title=Finding information about Linked Servers}

Finding the login information

At times you may want to know the login mapping, and you can execute the stored procedure sp_helplinkedsrvlogin as shown, which shows the login information for all the linked servers. This stored procedure without any input value gives the login information, both local and remote, for all linked servers.

However if you want to find the information for any one linked server, you may pass an argument, which produces the result as shown.

Finding the tables in the Linked Server

At times before you run a query you may want to know what tables there are in the database. For this you would run the sp_tables_ex stored procedure, passing the Linked Server as an argument as shown with results.

Finding the columns in a table on the Linked Server

Again for scripting your query you may need the columns in a database and related information. Then you would run the sp_columns_ex query, passing the linked server and the table as arguments.

Finding Primary and Foreign Keys

Similarly you can find information about the primary and foreign keys for a table as shown in the next two pictures.

 

Finding the Indexes on a table

The following picture shows how to access indexes on a table in the linked server.

{mospagebreak title=Setting linked server options}

In addition to finding information about linked servers, you can also configure the various options for the linked server once it is added to the Linked Servers collection. The syntax for this is:

sp_serveroption [@server = ] 'server' 
      ,[@optname = ] 'option_name'     
      ,[@optvalue = ] 'option_value' ; 

A linked server has many properties that can be set. Please refer to the Book On Line to find the whole list of options. Some of the values that may be set in the Management Studio are shown in the figures that are included in this section. This next picture shows the default properties conferred by running the sp_addlinkedserver described in the beginning. The first column shows the options and the second column their values.

Now if you run the sp_serveroption as shown here, you will be changing the ‘Data Access’ option from being true to false.

The result of running the above stored procedure changes the ‘Data Access’ option as shown in the next picture.

{mospagebreak title=Running a query against the linked server}

There are two options for accessing the tables in the database and running queries against them. You may use the Transact SQL openquery() function, or use a syntax similar to the four-part name without specifying two of them.  Both methods are shown with the results in the next two pictures. The openquery() function works just like the pass-through query against the linked server. The four-part syntax is used without referring to the catalog and schema, since schema in SQL 2005 server have a different connotation than in the earlier versions and Catalog is not relevant.

Openquery(Linked_Server, ‘Query’)

 

 

Using Four Part Syntax

{mospagebreak title=Using the Management Studio to set up a Linked Server}

If you highlight the Linked Servers node and right click on the mouse, you can add a linked server by choosing the New Linked Server… ellipsis link as shown.

This pops up the dialog New Linked Server with three tabs as shown, with the General one in the default view. You must give a name to the linked server, in this case, NWIND11 (this is a copy of Northwind  from the Office 11 samples folder). From the drop-down choose the Provider as shown.

If you do not provide a product name you may generate an error as shown.

However you may give any irrelevant product name and no error is raised. This next picture shows such an example for a linked server, NWIND12.

NWID12 is configured with the default security, which is shown in the next picture. For NWIND11 the Security context was that of the external login and no mappings were made to local logins.

The Server options for the NWIND12 linked server are revealed by the Server Options tab as shown in this picture. Running a query similar to the ‘AccessDb’ returns the same results as in the case of ‘AccessDb’ (both are Northwind databases).

Summary

Using SQL 2005 Server to set up a linked server is very easy. The three part wizard can also be used for setting up the linked server. The wizard could have been designed to give more hints to the user as to what the product name should be, whether it is optional, and so on.  A drop-down for the product name which synchronizes with the provider would have been more user friendly. Book on line should be consulted for information not presented in this tutorial.

9 thoughts on “Configuring a Linked Microsoft Access Server on SQL 2005 Server

  1. Working on single databases will be mostly for small businesses and anywhere you have an enterprise then you will be facing distributed data. This basic article shows how you may set up and query a linked server. There will be more articles exploring this topic.
    As always I thank you for reading the article and I welcome your comments.

    sincerely,

    J

  2. Wonder if you have any idea why SQL Server posts a a reference to c:/windows/sytem32 in the path when the actual location is on a lan

    thanks in advance

  3. I am Jayaram Krishnaswamy. Somehow the authentication to login to leave a message under my name is not working.

    Microsoft messages may not give a hint to what is expected, nor does it goes to an anticipated point right away. I have been meaning to catalog them. Then I think, why should I, if Microsoft is not bothered. If Microsoft pays me, probably I would. Right in this tutorial you will find a place where you are allowed to enter irrelevant information and if you do not, you are penalized with a exception message. Sometimes you get stopped, because the program does not give a hint as to what you should do at this point.

    Anyway I would like to know when and how you encountered this message?

  4. I read this with great enthusiasm but was saddened because it does not have an example of Access 2007. Please update for 2007 access????

  5. Well, my error comes out in english, but I think it matches yours. The handling of an Access password seems a problem.

  6. I’m having extreme difficulties making a linked server connect to a Simply Accounting DB (which is an Access 2000 or 2003 db really).
    It’s setting the “workgroup information” that seems to be the real problem. I can’t find definitive instructions anywhere.
    I can connect through Excel to the DB using a File DSN but using that as the source for the Linked Server also fails.

    Any pointers would be gratefully received.

[gp-comments width="770" linklove="off" ]