Sql-server – Using OPENDATASOURCE to insert mdb data into SQL Server

sql server

I tried inserting data from mdb file to 32 bit SQL Server using below code:

USE [master]
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE WITH OVERRIDE;
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


    USE [ImportTest]
    GO

INSERT INTO [dbo].[PROJECT_IDENTITY]
           ([Licencee_Name]
           ,[JURISDICTION])
        SELECT * FROM OPENDATASOURCE(
        'Microsoft.ACE.OLEDB.12.0',
        'Data Source="C:\\CCNTest.mdb"')...PROJECT_IDENTITY;

The Error Message I got was:

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

Any ideas on how to solve this msg?

Thanks

Best Answer

This is what worked for me: Add full permissions to Everyone on C:\Users\SQL Service account name\AppData\Local\Temp

Here's the full thread: https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null

Related Question