How to create a valid OLEDB select statement in SharePoint for Excel as external data source?

While integrating external data sources into SharePoint via OLEDB the valid syntax of the select statements depends on the types of data sources used. Below are given some samples for Microsoft Excel data sources that are often used with the Layer2 Business Data List Connector for SharePoint (BDLC).

Please take a look at the following examples:

1. "SELECT * FROM [Sheet1$A:E]"   

2. "SELECT * FROM [Sales$A:E] WHERE Name='Bob'"   

Name being the column heading which will get treated as a recordset field.

How to connect to Ecxel via OleDb in detail:

MS Excel Use Case

How to connect to MS Access via OleDb in detail:
MS Access Use Case

READY TO GO NEXT STEPS?