asked
Help needed with: Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
On my laptop I have SQL Server Management Studio 2012 installed. On my c: drive I have a copy of an Access 2000 database. I have tried to connect the Access database via EXEC sp_addlinkedserver @server = N'BlendingTest', @provider = N'Microsoft.Jet.OLEDB.4.0', @srvproduct = N'OLE DB Provider for Jet', @datasrc = N'C:\Program Files\Blending\LocDB\Blending.mdb'; GO At linked servers I find “BlendingTest” however when I try to connect to the database I got the error: Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "BlendingTest". (.Net SqlClient Data Provider). I have enabled incoming port 1433 and 1434 just in case but did not solve the issue. Please help what do I wrong?
Hi there. Ports 1433 and 1434 are used for when something tries to connect to the SQL server, not when the SQL server tries to connect to something - in this case the Access DB - I suggest if you only had them enabled for this reason that you revert as there are security implications to consider to opening those ports on a network. Now, as for your question - I'd ask whether you are using the 64 bit version or 32 bit version of SQL server? You can find this out following instructions on [this page][1]. If you are connecting to the 32 bit Access file with 64 bit SQL server, you may have issues unless you install appropriate drivers - or the 32 bit version of SQL. This was covered (with some steps to make it work!) on [this MSDN forum][2]. Unfortunately, [64 bit office and 32 bit office are not compatible][3], so this isn't an easy path for you; you have to make the decision whether to change your office or SQL installation. Perhaps it is also worth upsizing the database to SQL server as a one off operation? [1]:
http://support.microsoft.com/kb/321185 [2]:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d80822d-34a8-4d12-b9ad-92abf515a973/sql-server-64-bit-connecting-to-ms-access [3]:
http://support.microsoft.com/kb/2269468
Thanks for your quick reply. I'm afraid that I have the 64 bit version installed. The only solution for me is to reinstall SQL server with the 32 bit version. The Access database will be obsoleted in a couple of weeks and I try now to retrieve data to find out if the data can be exported to a new system via conversion tables. And I know I cannot store the data one to one. Thanks for your help.
I Downgraded SQL Server Management Studio to 2008 and did the same trick. Now I can access the Access data base. The only thing I must figure out why I miss some tables in the overview. Thanks again for your help.
Glad I could help. If you can mark the answer as accepted (as it appears to have solved your problem) by clicking the tick mark next to the answer it would help others who have this issue in future.
15 People are following this question.
