Search code examples
sqlms-accessopenrowset

OPENROWSET equivalent in MS Access

I have an Excel file and I want to run a query direct only. In SQL Server, we can use OPENROWSET for this. From extensive digging, I found that Access can read CSVs from a query like this:

SELECT * FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\Temp\;].[MyCSV.csv]

However, I am unable to tweak the connection for reading from Excel.

Is there equivalent of OPENROWSET in MS Access?

Solution

  • For reading from an Excel file you need to use something like this:

    SELECT *
FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\foo\Book1.xlsx].[Sheet1$];