Configuring a Linked Microsoft Access Server on SQL 2005 Server - Using the Management Studio to set up a Linked Server

(Page 6 of 6 )

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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Configuring a Linked Microsoft Access Server on SQL 2005 Server

· Working on single databases will be mostly for small businesses and anywhere you...    · Wonder if you have any idea why SQL Server posts a a reference to c:/windows/sytem32...    · I am Jayaram Krishnaswamy. Somehow the authentication to login to leave a message...    · I read this with great enthusiasm but was saddened because it does not have an...    · Hi,Thanks for this article! It is exactly what I want to do. But it doesn't work...    · Well, my error comes out in english, but I think it matches yours. The handling of...    · Well, if you find something about this issue, please post and let me know ;)Quân    · I'm having extreme difficulties making a linked server connect to a Simply...    · If you are struggling to link to Access 2007 substitute Microsoft.ACE.OLEDB.12.0...   >>> Post your comment now!