Linked Server in SQL Server 2005 from ACCESS 2007

Linked Server is one of the cool features of Microsoft SQL Server especially for those who want to access remote database, remote database could be of any type SQL Server, Sybase, Access etc. you can access those databases with OLEDB provider. Let us see how can we do it?

Before you move further I would like to request my readers that please create one Access MDB file named “Testing.MDB” and create at least one table named “empDetails” in that MDB.

Now, we will link “Testing.MDB” database to SQL Server 2005.

Note: You can do it by wizard and by query but I prefer query so will show you that path only.

Below query will cover file steps to link the server.

1.) Add linked server

2.) Add linked server credential, if any

3.) Check sys.servers whether database is added as linked server

4.) List all available tables in MDB file

5.) Perform SELECT on “empDetails” table.

--add link server

exec sp_addlinkedserver

@server='LinkToAceess',

@srvproduct='AccessDatabase',

--if you have older version of access then kindly use old jet provider

--Microsoft.Jet.OLEDB.4.0

@provider='Microsoft.ACE.OLEDB.12.0',

@datasrc='C:\Testing.mdb'

GO

--add MDB's credental, if any

EXEC sp_addlinkedsrvlogin 'LinkToAceess', 'false'

--check whether 'LinkToAccess' has been added

select * from sys.servers

--list all tables available in Testing.MDB

exec sp_tables_ex 'LinkToAceess'

--perform SELECT on empDetail table.

SELECT * FROM [LinkToAceess]...empDetails

So, finally you have used Linked Server from within your SQL Server

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com