Linked Server in SQL Server 2005 from ACCESS 2007

Tuesday, March 24, 2009 |

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

4 comments:

Anonymous said...

Does not work.
Gives errros message:
Msg 7302, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server ....

Ritesh Shah said...

which Access version are you using?

fd2000 said...

I couldn't get it to work on SQL 2008:
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToAceess".

fd2000 said...

Access 2003. I was able to get it to work on SQL 2005 but not on SQL 2008.