SQL Server 2005 - how so I create a linked server to Access 2007

Question

SQL Server 2005 - how so I create a linked server to Access 2007

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Key DBA SSCertifiable Points: 6029 More actions · Answer 1

Joy,

I believe that Linked Servers are supported in SQL Server 2005 Express. I have not created a Linked Server with a SQL Server 2005 Express instance. We only have one installation of SQL Server 2005 Express in my environment at the moment, and it is a production database. I checked the SQL Server 2005 Express Books Online (Sept 2007) on that local machine and it came up with over 500 hits on "Linked Server" with a search.

See also:

SQL Server 2005 Books Online (September 2007)

Features Supported by the Editions of SQL Server 2005

http://technet.microsoft.com/en-us/library/ms143761.aspx

The 2 biggest limitations with SQL Server 2005 Express that I have come across are the lack of the SQL Server Agent and a limitation of 4GB of data per database.

Hope This Helps,

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

jalves-761848 Grasshopper Points: 14 More actions · Answer 2

HERE IS THE SOLUTION FOR ALL OF YOU WITH THE SAME PROBLEM:

- Install the 2007 Office System Driver: Data Connectivity Components on your server.

here is the link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

- Make shure you have SQL SERVER 2005 Standard installed. IT DOESN'T WORK'S ON SQL EXPRESS. No mather what you do. It only works on the standard versions...

- Install SQL SERVER SP2

- On your Server Objects > Linked Servers > Providers, Righ Click on Microsoft.ACE.OLEDB.12.0 and select Options. Check the option Allow inprocess

- Use this T-SQL template (replacing by your own values) to create a connecto to the Linked Server:

-- ==============================================

-- Add Linked Server Access 2007 ACCDB template

-- BY JOSÉ ALVES @ CCG - UMINHO - PORTUGAL

-- ==============================================

EXEC sp_addlinkedserver

@server = N'Your Linked Server Name',

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

@srvproduct = N'Access2007',

@datasrc = N'C:\path\to\your\db.accdb'

GO

-- Set up login mapping using current user's security context

EXEC sp_addlinkedsrvlogin

@rmtsrvname = N'Your Linked Server Name',

@useself = N'TRUE',

@locallogin = NULL,

@rmtuser = N'Your Linked Server Name',

@rmtpassword = ''

GO

-- List the tables on the linked server

EXEC sp_tables_ex N'Your Linked Server Name'

GO

-- Select all the rows from table1

SELECT * FROM [Your Linked Server Name]...table1

I hope this helps you. To me it was a big headache but i figured out after many tries...

Greetings from Portugal

José Alves - CCG - Portugal

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 3

Thanks so much for the detailed posting, JALVES.

- On your Server Objects > Linked Servers > Providers, Righ Click on Microsoft.ACE.OLEDB.12.0 and select Options. Check the option Allow inprocess

I noticed that my server doesn't have an entry in Server Objects -> Linked Servers -> Providers for Microsoft.ACE.OLEDB.12.0.

Will the "2007 Office System Driver: Data Connectivity Components" create that necessary entry in Providers?

Thanks,

Simon

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 4

I've answered my own (perhaps near-sighted) question:

The 2007 Office System Driver: Data Connectivity Components does install the provider for Microsoft.ACE.OLEDB.12.0.

I can now see it in my list of Providers.

I've executed the scripts provided by Mr Alves (jalves), and am now getting the following error, when I try to query the linked server for its list of tables.

"OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Access07" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Access07"."

Can anyone shed any light on this error?

Thanks,

- Simon

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 5

Simon: Were you able to resolve the issue? I also followed Jose Alves's instructions and am getting the same error as you.

Would appreciate any help in this matter

jalves-761848 Grasshopper Points: 14 More actions · Answer 6

jalves-761848

Grasshopper

Points: 14

August 20, 2008 at 11:37 am

#860801

Hello. Check your permissions on the file...

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 7

Sorry - I was not able to resolve my issue. I ended-up taking a different approach to my problem. Good luck resolving the error!

-Simon

yweekender Newbie Points: 7 More actions · Answer 8

Hello there,

Did you get this problem resolved? I'm getting the same error when I try to connect to excel 2007 through a linked server.

ywy

brandt.marshall Grasshopper Points: 15 More actions · Answer 9

I was getting the same error as well. What fixed it was going to the Providers object and right clicking on Microsoft.ACE.OLEDB.12.0 and selecting properties then putting a check on Allow inprocess and then everything worked.

awilbournsqlcentral Old Hand Points: 363 More actions · Answer 10

According to this post http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/589ca193-3541-4c5e-965c-4c515d6b476b/[/url] is suggests it will not work with SQLExpress.

sunny.manghnani SSC Enthusiast Points: 100 More actions · Answer 11

Hello,

I have same issue, I wanted to create linked server to Access 2007.

Can this be possible on SQL Server 2005 Developer Edition with SP3 instance

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

i am getting below error

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESS".

Please help!!!

Thanks in advance

Sunny:cool:

chaitanya.dabholkar SSC Enthusiast Points: 115 More actions · Answer 12

In SQL Server, go to Providers object, right click on Microsoft.ACE.OLEDB.12.0 and select properties, then put a check on Allow inprocess.