Back to Top

Using ODBC to Create Portable Database Connections (Use for Excel (XLS), Access (MDB), and Other Data Sources)

The purpose of The Report Viewer is to allow end users to refresh database connections. For the user to be able to refresh the reports, the database connections need to exist in the location refreshed by the report.

This creates a problem for reports that use data sources reference by a file path.

The Problem

When creating a report that uses an Excel spreadsheet as a database, report authors will often use the option in SAP Crystal Reports to Create New Connection using Access/Excel (DAO).

SAP Crystal Reports Option to Create New Connection using Access/Excel (DAO)

The path to the database file may be valid and accurate on the report author's computer, but it may not be the correct path on the report users computer. And, if the report viewer cannot find the path at that location on the report user's computer, it will not be able to refresh the reports data.

The Solution

When creating a report that uses an Excel workbook as a database, add it to the report as an ODBC connection.

The first step is to create an ODBC driver that connects to the XLS database.

Step 1: Open the 32-Bit Database Administrator

As of version 4.6 of The Report Viewer, you must use 32 bit database drivers. If you are running Windows 7 x64, or another 64 bit operating system, make sure to use the 32 bit database driver administrator. This can be found on Windows 7 by searching for it, as shown in the image below:

Open the 32-bit ODBC Database Administrator

Step 2: Add a New DSN

Once the 32-bit ODBC Database Administrator is open, use the Add button to add a new DSN.

Add a new datasource name.

The "Create New Data Source" wizard will display. Select to create the appropriate type of data source. This method works great for Excel, Access, Comma/Character Separated Values, Text files, and more.

Select the type of data source to add.

Next you will need to enter information specific to your data source.

The Data Source Name must be the same on every computer.

The problem was the Excel file being referenced by the file path. This solution is to reference the Excel file by the Data Source Name. And, this Data Source Name will be stored inside the report. This means the Data Source Name must be the same on every computer that uses this report.

The path to the Excel Workbook, Access Database, CSV file, TXT file, or other database can be different on each computer.

Enter the Data Source specific information.

Now we see that the DSN is set up on the computer for use.

ODBC Data Source Administrator System DSN Now Exist

Step 3: Create Report with New Database Driver

You are now ready to create your report with your new database driver.

ODBC Data Source Administrator System DSN Now Exist

Step 4: Setup Other Computers with the same Data Source Name

Now, repeat the steps 1 and 2 for all the computers that will use this report. The database file can be in different spots. As long as the ODBC drivers DSN are the same, the report will work fine.

Read this detailed example on setting on the client systems. Also, consider the server configurations.