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:
Creating a Windows ODBC Data Source
-
Run the Windows ODBC Manager.
If you have 64-bit Excel, run the 64-bit ODBC. Otherwise, run the 32-bit version -
Create a System DSN using the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
-
Give the data source a name and then press Select Workbook... and choose the workbook you want to use as your data source
-
In Excel, go to the Jet ribbon and click 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. -
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.
-
On the Connection tab of the Data Link Properties dialog, choose the data source name you created in the ODBC Manager:
-
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. -
Click Test Connection on the Data Source Settings windows. 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.
Comments