Build linked server from SQL Server 2005 (--64bits--) to MSAccess

Question

Build linked server from SQL Server 2005 (--64bits--) to MSAccess

SQL_ABD

SSCertifiable

Points: 5832
More actions
June 4, 2009 at 4:28 am

#210491

Hi,
I want to create a linked server in SQL Server 2005 Enterprise edition 64 (in production environment), to MSAccess.
I mean you can create it but it does not works.
I look for solution through the web and forums but in vain.
Thanks for any help

Viewing 7 posts - 1 through 6 (of 6 total)

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

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 1

We have a couple in a SQL Server 2005 environment. I'm going to try to attach a sample script. All we do with the linked servers is access data in queries.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

You can't do it with x64 and this is a known problem. There is no support for JET in 64 bit by Microsoft. See the following connect items:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125117

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=90562

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=123311

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Here is a documented hack that uses SQL Express 32 bit in a double hopped link:

http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 4

I stand corrected and am happy to find out about this before we have to migrate this functionality to a new 64 bit server. Does anyone know if they plan to fix this?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

Charles Hottle (6/5/2009)
I stand corrected and am happy to find out about this before we have to migrate this functionality to a new 64 bit server. Does anyone know if they plan to fix this?

There is no immediate need to fix this because Win2003 SP2 installs the x86 ODBC so you can use that instead of the x64 but the application must run x86.

Kind regards,
Gift Peddie

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Charles Hottle (6/5/2009)
I stand corrected and am happy to find out about this before we have to migrate this functionality to a new 64 bit server. Does anyone know if they plan to fix this?

They won't fix JET if that is what you are asking. The work around listed and Gift's answer are basically all you get. Its been a problem for a long time.