The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Question

I create Link Server in Sql 2008

EXEC sp_addlinkedserver 'ExcelShare',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\DistExcl.xls',
   NULL,
   'Excel 5.0'

if Run test connection or OPENQUERY for Link server display error

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Answer 1

Is it 64 bit or 32? 64-bit SQL Server cannot use Jet, there is no 64-bit OLEDB provider for Jet.
Currently there is no plan to write one either, but this could change based on demand.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Please take a moment to check this blog post

Dealing with Microsoft.Ace and OPENROWSET Errors

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

I noticed some confusion about ACE and JET drivers.

ACE driver for office 2010 is essentially 32-bit and 64-bit JET driver (although name was changed and it is Office only).

http://office.microsoft.com/en-us/access-help/introduction-to-the-access-2007-file-format-HA010067831.aspx

On e 64-bit server you can use only 64-bit version of ACE driver.

Answer 4

Hi Mahdy.asia,

>>error: The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

You can try to use 64-bit ACE driver as Piotr Palka mentioned, please obtain the Microsoft.ACE.OLEDB.12.0 from here: 2007 Office System Driver: Data Connectivity Components

Another option is to download a 64-bit JET driver for SQL-Server 64 bit. If you already have Office/Access 32 bit installed on your machine, you need to remove fist before installing 64-bit JET driver. Please You obtain the driver from here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

More information, please reference this thread with the same scenario as yours: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9ccf6bd6-d377-40fe-9e3b-7a5063ff3d09

---

Regards, Amber zhang

Answer 5

Hi Mahdy.asia,

>>error: The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
If you are using 64-bit SQL Server 2008, please refer my reply above, which would be help to solve your issus.
If you are using 32-bit SQL Server 2008, this error might be related to unregistered DLL based on the Jet 4.0 OLE DB Provider that you are using, please try to modify steps accordingly.
To resolve this problem, register the faulty DLLs as follows:
1. At a command prompt, change to the C:\Program Files\Common Files\System\OLE DB folder.
2. At a command prompt, type the following command:
regsvr32 Msjetoledb40.dll
regsvr32 Msjet40.dll
regsvr32 Mswstr10.dll
regsvr32 Msjter40.dll
regsvr32 Msjter40.dll
3. You should receive confirmation that the DLL is registered successfully.

The pertinent DLLs list of OLE DB provider "Microsoft.Jet.OLEDB.4.0" as below:
C:\WINNT\System32\Msjetoledb40.dll
C:\WINNT\System32\Msjet40.dll
C:\WINNT\System32\Mswstr10.dll
C:\WINNT\System32\Msjter40.dll
C:\WINNT\System32\Msjint40.dll
More information, please reference this KB: http://support.microsoft.com/kb/278604
Hope it is helpful.

---

Regards, Amber zhang