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

Advertisements

8 Responses to “Linked Server in SQL Server 2005 from ACCESS 2007”

  1. Anonymous Says:

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

  2. Anonymous Says:

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

  3. Ritesh Shah Says:

    which Access version are you using?

  4. Ritesh Shah Says:

    which Access version are you using?

  5. fd2000 Says:

    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".

  6. fd2000 Says:

    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".

  7. fd2000 Says:

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

  8. fd2000 Says:

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: