SQL Server 2012 - Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" (Microsoft SQL Server, Error: 7302)
I just installed SQL Server 2012 to test differences and make sure it is ok for my Work. I may not be a pro at SQL, but I don't see how to add a Link to Jet database on my server. I have imported Jet Databases, but when going thru the GUI to Link a JET Database I see no Provider for JET. I am running Access 2003 (I don't like the 2008+ GUI)
I searched an found nothing simular to my issue; at least nothing I understood. Nothing mentioned how to add Jet db Provider.
I ran a connection string to the Jet Database thru QA:
sp_addlinkedserver 'USGEScrub', 'Access', 'Microsoft.Jet.OLEDB.4.0', 'D:\Lead$\SupList\USGEScrub.mdb'
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "USGEScrub". (Microsoft SQL Server, Error: 7302)
- Edited by Crazyd Monday, June 25, 2012 6:55 PM
I get an error Every time I try I get (Microsoft SQL Server, Error: 7302).
It's not a permitions issue I have SQL as Local and file is Full Control to Everyone. The error for that is 3703, but I tried it's solution anyhow.
I can Connect thru 32bit ODBC and get to it, but I'm thinking It's possible that SQL Server installed as 64-bit and only checks the 64-bit ODBC when I try to connect thru an ODBC Connection. Anyhow I can't access it thru ODBC or JET.
This is the second time I'm asking due to my pure frustration after no answers the first time around without the simple version of the same question.
I can Import and even connect thru Server Explorer; I just can't add it as a Linked Server.
The connection string I tried was
@server = N'USGEdB',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'D:\Lead$\SupList\USGEScrub.mdb';
and I don't see Microsoft.ACE.OLEDB.12.0 on the list and would like to use it if that's what I need. I tried that one and got error 7303.
- Merged by Iric WenModerator Wednesday, June 27, 2012 9:39 AM duplicated
When using Microsoft.JET drivers (till office 2003) & Microsoft.ACE drivers (office 2007 & above) the version of office and SQL server must be the same. If not the query will NOT work as it is a limitation.
You can try a work-around.
Uninstall the current version of offfice.
Install AccessDatabaseEngine components (both 32-bit and 64-bit) from http://www.microsoft.com/en-us/download/details.aspx?id=13255
Install office back again
Try running your query
Hope this helps.