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

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.
akivashapiroAsked:
Who is Participating?
 
akivashapiroConnect With a Mentor Author Commented:
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
 
Kent DyerIT Security Analyst SeniorCommented:
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
 
akivashapiroAuthor Commented:
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
 
akivashapiroAuthor Commented:
I found the solution.
0
All Courses

From novice to tech pro — start learning today.