Inserting into Access table

Question

Hi

In T-SQL I am doing;

INSERT INTO OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'F:\My Path\My DB.mdb', Staff)(Title) 
SELECT        Title
FROM            Staff AS Staff_1

but am getting below 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)".

How can I make it work? I have already tried;

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 

Thanks

Regards

Answer 1

Hi @Y a h y a ,

Could you please provide any update?

I also found one good article listing all kinds of error via an OLE DB provider or linked server.

You could check this article by searching your error message and check whether it is helpful to you.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Y a h y a ,

Thank you so much for posting here in Microsoft Q&A.

You could try with below solutions:

• Solution 1: Check whether you have installed the related component like 2007 Office System Driver: Data Connectivity Components which is necessary for Microsoft OLEDB ACE 12.0 driver to work. If not, you could download and install it, then restart your machine.
• Solution 2: Check whether the user login that you are accessing has rights on SQL Server Service or not. If not you will have to give your credentials so that the SQL Service runs using your login credentials. For some reason SQL Server does not like the default MSSQLSERVER account. Switching it to a local user account resolves the issue.
• Solution 3: Install Process Monitor and give full permission to the file path which was listed in process monitor as access denied.

Reference: The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi Melissa

Office 2017 drivers are not available anymore so I have installed the Office 2010 ones.

I gave a user full admin access to SQL Server but still no luck.

I am running the query from within SSMS. Process Monitor bring up a whole bunch of stuff under Ssms.exe (attached), . Not sure which one is relevant to my issue.

Thanks53661-ssms.txt

Regards

Answer 4

Hi @Y a h y a ,

Thanks for your update.

I could not find any error or failure in your attached output.

Please also try with below steps:

One: Change your account to a local user account.

Two: Grant your account full access to the file path F:\My Path.

"Could not find installable ISAM."

Three: About above error, you could refer this doc for more details about Resolution.

INSERT INTO OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'F:\My Path\My DB.mdb', Staff)(Title)
SELECT Title
FROM Staff AS Staff_1

Four: You could check whether any correction is needed with above statement.
Please refer one example from below:

   INSERT INTO OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;Database=F:\My Path\My DB.xlxs', 'SELECT * FROM [Sheet1$]')  
 SELECT        Title  
 FROM            Staff AS Staff_1  

Or

   INSERT INTO OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;Database=F:\My Path\My DB.xlxs', [Sheetname$])  
 SELECT        Title  
 FROM            Staff AS Staff_1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.