Skip to main content
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'"

  • September 20, 2022
  • 2 replies
  • 539 views
M

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

    SakthiVel
    Navigator | Tier 3
    Forum|alt.badge.img+8
    • SakthiVel
    • Navigator | Tier 3
    • September 21, 2022

    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

    Lets Connect - https://www.linkedin.com/in/sakthivel-palanisamy-08152833/
    S
    • Snehap100055
    • Cadet | Tier 2
    • September 22, 2022

    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.

     

     

     

     

     

    Terms & ConditionsCookie settingsAccessibility statement