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
–if you have older version of access then kindly use old jet provider
–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