Thursday, November 15, 2012 1:34 PM
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
Sunday, November 18, 2012 1:17 PMModerator
I would suggest you to have a look at this link
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
Monday, November 19, 2012 8:51 AMModerator
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.
TechNet Community Support