Configuring a Linked Microsoft Access Server on SQL 2005 Server - Linked Servers on SQL 2005 Server
(Page 2 of 6 )
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=''
Next: Finding information about Linked Servers >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
/ 46 
