Use an Excel workbook as a Jet data source

Expert - requires Admin permissions

Setup & Pre-Requirements

Using an Excel workbook as a data source can be a powerful tool for your Jet Reports.

Here is an example of a workbook configured for that purpose - tabs are equivalent to database tables, and columns are set up as fields:

To use the workbook as a Jet data source, we must first configure a Windows ODBC data source and then configure the Jet Excel add-in to use that data source:

Create a Windows ODBC Data Source

1. Run the ODBC Data Sources from Windows start menu.

   

   If you have 64-bit Excel, run the 64-bit ODBC. Otherwise, run the 32-bit version Need to know whether you have 64-bit or 32-bit Excel?

2. Create a System DSN using the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

   

3. Give the data source a name and then press Select Workbook... and choose the workbook you want to use as your data source

   

   

4. In Excel, go to the Jet ribbon and click Settings > Data Source Settings.

   On the Data Source Settings dialog, click Add.

   Give your data source a unique name and select the Other (Universal) database type. Click OK.

   If the Other (Universal) database type is not available, you will need to contact your regional Jet Reports representative to purchase a license for that connector type.

   

5. Click Configure on the Connection tab and choose Microsoft OLE DB Provider for ODBC Drivers on the Provider tab of the Data Link Properties dialog.

   

   

6. On the Connection tab of the Data Link Properties dialog, choose the data source name you created in the ODBC Manager:

   

7. Ensure that the correct file is listed in Enter the initial catalog to use.

   and then click Test Connection.

   

   Click OK on the test message and then click OK on the Data Link Properties dialog.

8. Click Test Connection on the Data Source Settings window. Then click OK to respond to the test message.

   Click the Set as Default button and then click OK.

   

   You are now able to use your Excel workbook as a Jet data sources.

Note: Using Excel files as data sources in Jet Reports 2019 (19.0) and later can suffer from performance degradation with multi-threading enabled. To alleviate this issue the following steps can be taken to disable multi-threading.

There are three settings related to multi-threading between Jet Reports and Excel.

Items (A) and (B) below must be disabled in tandem in order to alleviate the issue.

(A) In the Jet “Settings > App Settings” menu, under Performance,

(B) and (C) Under Excel > File > Options > Advanced, in the Formulas section (B) and the General section (C).