This page presents information about how to connect Excel databases.


Accessing Microsoft Excel

You can connect to Excel databases using an ODBC driver, an ODBC DSN, or OleDB.

Using ODBC

Select and name a range of rows and columns in the worksheet. This will allow the software to read the information as a table. Choose one of the following naming processes for your version of Microsoft Excel:

For Microsoft Office 2007:

Right-click the selection and choose Name a Range....


For Microsoft Office 2003

In Microsoft Excel, go to Insert → Name → Define.... 

Name the selection (e.g., "itemsTable"). The Excel file is ready to be used. 

There are several methods for communicating using ODBC:

Using ODBC Microsoft Excel Driver

    • In the datasets namespace, choose the DBs tab and create a new provider by clicking Create new.
    • Select the "Odbc Data Provider" in the "Provider" data field. 
    • For the "Database" field, choose Microsoft Excel Database.
    • Click Ok.

Select Microsoft Excel Database in Database field

    • A new row is created in the data grid, click the ConnectionString column.
    • In the pop-up window, enter the path and the filename in the "Dbq" field.
    • Click the Test button to ensure that the connection is OK.

"Test" is optional.


Using ODBC with a DSN

    • Go to your computer's Control Panel and select Administrative Tools.
    • Double-click "Data Sources (ODBC)". 

    • In the "ODBC Data Source Administrator" window, click Add. You are prompted to select a driver. 
    • Select "Microsoft Excel Driver (*.xls)". 
    • Click Select Workbook and select the name of the Excel file that was previously created. 
    • Name the Data Source, (e.g., "excelDatasource"). 
    • For Write access, uncheck the "ReadOnly" checkbox. 


ODBC Data Source Administrator options

    • In the Datasets namespace, choose the "DBs" tab and create a new provider by clicking create new.
    • Under the "Odbc Data Provider" options, choose "ODBC using DSN". Then, click OK.
    • Click the ConnectionString column of the new row. Then, enter the DSN in the "DSN" field.


Enter the DSN

Using OleDB

    • In the Datasets namespace, choose the DBs tab.
    • Select the "OleDb data provider" option in the combo-box and create a new provider by clicking create new. 
    • Choose the "Microsoft Excel Database". Then, click OK.
    • Click the ConnectionString column of the new row, then enter the path and the name of the Excel (.xls) file in the "DataSource" field. 



In this section...