Solved

Need help with OPENROWSET using Microsoft.ACE.OLEDB.12.0

Posted on 2014-01-07
4
3,660 Views
Last Modified: 2014-01-12
I need help getting this OPENROWSET command to work when executing it in SQL Server 2012 64-bit under Windows Server 2008 R2, Enterprise.

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','D:\EECO\E2T2.accdb', T1)

I receive the following error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Notes:
- "Microsoft.ACE.OLEDB.12.0" does exist as a Linked Servers Provider.

- "E2T2.accdb" is a new DB created in Access 2010 64 bit. The only options under Access Options/General/Creating databases/Default file format for Blank Database are Access 2000, Access 2002 - 2003, Access 2007. I used Access 2007.
0
Comment
Question by:akivashapiro
  • 3
4 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39763798
Let's back up for a moment..  What is your end-goal?  Are you trying add/extract a ton of records from SQL?  Is this from Access/Excel/text/or other database?  Rather than using quirky OPENROWSET, I would go to a SQL Job (formerly called a DTS Package)..
0
 

Author Comment

by:akivashapiro
ID: 39763814
What I want to do is link to tables an Access DB and use them in sql join clauses in stored procedures with tables in SQL Server.
0
 

Accepted Solution

by:
akivashapiro earned 0 total points
ID: 39763913
I got it to work:

USE [master]
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','D:\E2 Test.accdb';;, [T1])

The parameter sets were required. Found the answer here (first response):
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess
0
 

Author Closing Comment

by:akivashapiro
ID: 39774512
I found the solution.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question