Access-Database-External Data-File
Each of these sources function the same. The each use Excel's Get External Data feature as the source of data for a report. This provides the capability to use databases, web pages, text file, sql server, xml, Access, ..., as the source of data.
Click the image below to view a video on creating a report from an Access Database.
1 - File
Selecting Access, Database, External Data or File from the Source list opens the Connection setting tab (2) and establishes the source of data as a connection.
2 - Connection Setting tab
This tab is displayed whenever File is double-clicked in the Source list box. It contains all the settings necessary to establish a connection (query) as the source of data.
3 - File
Enter the full path to the file that contains the data. Click the This File button or double-click on This File in the Variables Panel to set the current file. The View File button opens the file specified in the File setting.
Default value (if left blank): this field must be populated since it is mandatory
Allowable Values: The full path to an existing file or a variable that represents the full path to an existing file.
4 - Worksheet
Click on the button and choose the worksheet that contains the connection for the report.
Default value (if left blank): this field must be populated since it is mandatory
Allowable Values: the name of a worksheet in the file specified in the File setting.
5 - Connection
This button displays a listbox of available connections in the File and Worksheet specified in the File and Worksheet settings. Choose the connection that will contains the data for the report.
Click here to learn how to create a connection.
Default value (if left blank): this field must be populated since it is mandatory
Allowable Values: the name of a connection specified in the File and Worksheet settings.
EXAMPLE: Create a report based on data stored in a CSV file. The CSV file is loaded using Excel's ability to Get External Data feature. The Query Replace a value found in a specific column.
Download EXAMPLEDownload CSV File
The configuration for this report is shown at the top fo this page.
CSV File contents.
| Report | |||
| ID | First Name | Last Name | Order |
| 1 | Jane | Doe | Milk |
| 2 | John | Doe | Eggs |
| 3 | Eleni | Smith | Milk |
| 1 | Jane | Doe | Butter |
See also