Question

Hello Community, I trying to get Excel file Header names using as database. While executing "select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Sys1'"

  • 20 September 2022
  • 2 replies
  • 22 views

Getting Error "The Microsoft Access database engine cannot open or write to the file 'INFORMATION_SCHEMA'. It is already opened exclusively by another user, or you need permission to view and write its data."

Need suggestions how to fix this error


2 replies

i believe we cant achieve some features like deleting a columns and the scenario you have mentioned above using excel as DB, you can read/update row data not the columns

INFORMATION_SCHEMA does not work with Excel as Database.

Here is an alternative way to get the headers by connecting Excel as DB.

 

In the Connection string , update the value of HDR as NO

 

Before changes : Provider=Microsoft.ACE.OLEDB.12.0;Data Source="@ExcelPath";Extended Properties='Excel 12.0 Xml;HDR=YES';

 

After changes : Provider=Microsoft.ACE.OLEDB.12.0;Data Source="@ExcelPath";Extended Properties='Excel 12.0 Xml;HDR=NO';

 

 Execute Select query and the first row of SQL dataset will have the header values.

 

 

 

 

 

Reply