How to solve Microsoft.ACE.OLEDB.12.0 error "Unspecified error"

The reason for writing this blog is for the last 2 hours I've been battling with the below error message

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". 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)".

And finally managed to solve it!

Thought of sharing the steps I went through for others benefit.

Problem statement

When trying to use Microsoft.ACE.OLEDB.12.0 provider in a linkedserver or distributed query you'll get the above error.

Environment 

Win 7/vista 32 bit, SQL Server 2008 R2 SP! 

Steps to resolve

Check the following steps

Step 1: Check the In Process and Dynamic Provider options for the ACE provider

This can be done through T-SQL statement or through GUI

T-SQL command

Use the below command to set the properties

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




GUI




Connect to server instance through SQL Management Studio. 

Expand Server -> Server Objects -> Linked Servers -> Providers -> Microsoft.ACE.OLEDB.12.0. 

Right click choose properties.Select the Dynamic Provider and Allow inProcess properties as shown below

Step 2: Check the permissions on the Temp folder

This is needed because the provider uses the temp folder while retrieving the data. The folder can be one of the below based on whether you use a local system account or network domain account.

For network accounts, folder is 
:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
and for local system account its :\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Right click on this folder and give it read write access to the account executing the code.

Step 3: Check the MemToLeave memory area allocated

This was the step I took maximum time to understand and resolve. 
Had to go through lots of blogs before I came across this helpful article from Nathan
http://nathondalton.wordpress.com/2010/04/01/sql-memory-and-external-data/

The setting can be applied as follows
Open SQL Server Configuration Manager -> Services -> SQLServer service.
Right click and choose properties.
Go to advanced tab and append -g512; to startup parameters property and it will resolve the issue.
This brought end to my 2 hour long struggle with the issue and i was able to get data from the file as shown below.