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]. 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]. Unfortunately, [64 bit office and 32 bit office are not compatible], 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? :