Using Excel as a Data Source

To use Excel spreadsheets with the Database Exchange control, you either configure a DSN or use a connection string directly in ODBC Connection Strings for ADO, such as:

Provider=MSDASQL.1;Persist Security Info=False;Data Source =XLS

An XLS file is actually a workbook that may contain multi-sheets, and each sheet may contain multi-tables. There are some tricks to use an Excel spreadsheet as a data source with Database Exchange control. The syntax for table name should be [SheetName$].

1. Using Unknown as the record source and [Sheet2$] as the command test.
2. Using SQL Statement as the record source, as shown here:

SELECT *FROM Employees WHERE FirstName LIKE 'AN%'

3. Using Data Table as the record source: Define a data block in your sheets. So when retrieving the schema, Database Exchange control will treat these data blocks as user defined tables and list them on the table dropdown list as shown in the illustration below. In this example, P2BData_1 and P2BData_2 are defined on Sheet1, P2BData_3 on Sheet2, and P2BData_4 on Sheet3.

Be aware that the first row of each column in a sheet is used as the column (field) name.

Note:When using Excel as your data source you can modify existing rows, but can not add rows to the worksheet.