Adding a link to Access in SQL 2008 (64bit)

Question

I am trying to add a linked server in SQL Server 2008 R2 (64bit) which points to an Access database.

Having read a number of the forum threads on this, it would appear that you cannot use the JET drivers, as I am on 64bit, but that I should be able to use Microsoft ACE OLEDB 12.0, which I have downloaded and installed, but when adding the linked server (code below), it fails as follows.

I executed the following code, which completes successfully and provides me the entry under "Linked Servers" in SQL

EXEC sp_addlinkedserver 
@server = N'myDB', 
@provider = N'Microsoft.Ace.OLEDB.12.0', 
@srvproduct = N'OLE DB Provider for ACE',
@datasrc = N'd:\data\myDB.mdb';

When I then try to expand the linked server "myDB -> Catalogues -> default -> Tables" I get a message which states:

Cannot initialize the data source of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "myDB". (Microsoft SQL Server, Error 7303)

Any ideas how I can resolve this?

I've seen a number of workarounds which are suggesting installing a 32bit version of SQL Express as a 'middle-man' for the process, but that seems a little radical and I am sure that SQL should be table to talk direct to Access in a much simpler fashion than this.

Any help would be appreciated

Answer 1

Hello,

I would suggest you to have a look at this link

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/5cdc0eb6-99f3-4618-9194-89f717847192/

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hi Sean,

What's the office version? Is it 2010? Using Office 2010 with Microsoft ACE OLEDB 12.0 is recommended.

Please open your "Control panel"--"programs and features"

Try to find "Microsoft Access database engine 2010". What's the size of this program?(If you don't have this program installed, please download it from: http://www.microsoft.com/en-us/download/details.aspx?id=13255)

In ordinary, it should be 20M+. If it is not, please reinstall it.

---

Iric Wen

TechNet Community Support