Recognizing Database Changes

If the database layout changes (field names change, data types are altered , and so forth), if the database is moved to a new location, or if you want to point your report to a different database than it was originally designed with, you need to use Crystal Reports functions to recognize these changes.

Verify Database

If the database your report is based on changes (perhaps the database designer adds new fields, deletes old fields, or gives existing fields new names or data types), your report won t automatically recognize these changes when the report is opened. Even if you refresh the report, Crystal Reports won t detect the changes, unless a specific field used in the report no longer exists in the database (it has been removed or renamed ). To detect changes to the database the report is based on, you must verify the database.

Choose Database Verify Database from the pull-down menus . If the database has not changed, you ll see a dialog box indicating so:

However, if the database has changed, you ll receive a message like this:

When you click OK at this prompt, Crystal Reports will read the database structure and make any changes to table names, field names, and data types. When you display the Field Explorer, you ll notice the changes. And if you have previously linked tables together and table structures have changed significantly, you may need to relink tables in the Database Expert, as discussed in Chapter 16. If field names have changed, you will see the Map Fields dialog box (discussed later in the chapter).

Verify on First Refresh

If the database changes and you don t verify the database thereafter, your report may show incorrect data if fields have been moved or renamed, or it may display an error message if tables have been removed or renamed. Be very careful when working with a database that may be changing. You ll want to verify the database often to catch any changes.

One way to accomplish this is to check the Verify on First Refresh check box after selecting File Report Options from the pull-down menus. When you check this menu option, Crystal Reports will verify the database the first time the report is refreshed in any given report session. The on/off state of Verify on First Refresh will be saved with the report ”if it was turned on when a particular report was saved, it will be turned on when that report is opened. If you wish to change this choice for all new reports you create in the future, you may modify the same check box on the Database tab after choosing File Options.

Using Set Datasource Location

Crystal Reports provides a single menu function for changing the connection to the database (database driver), recognizing a change to the physical location of a database or table, or changing the actual database to be used. All of these types of changes are accomplished via the Set Datasource Location function.

Choose Database Set Datasource Location from the pull-down menus. The Set Datasource Location dialog box will appear. This dialog box contains two main sections: the Current Datasource box and the Replace With box. Begin by choosing the existing database or table that you wish to alter ”you may choose either the entire database name by choosing an entry to the right of the database barrel icon, or an individual table within the database by choosing the table name to the right of a plus box.

Then, expand the appropriate category in the Replace With box to find the new data source you wish to use in place of the selected data source in the Current Datasource box. If necessary, you ll need to provide log on credentials to the new server. Choose a replacement object similar to that selected in the Current Datasource box; if you selected an entire database in the top box, select an entire database in the bottom box. If you selected an individual table above, choose a single table below. Only if you select like object types will the Update button be enabled.

When you click Update, Crystal Reports will log on to the identified replacement data source. If the database name is different on the replacement, the database will be verified (as discussed previously in this chapter), and you may see the Map Fields dialog box (discussed in the Mapping Old Fields to New Names section later in this chapter). But as long as the database names match, Crystal Reports assumes that the databases are the same and does not prompt for further input; it simply makes the change. The new data source is now displayed in the Current Data Source list, and you can click Close on the Set Datasource Location dialog box or make other data source location changes.

If your report contains one or more subreports, a separate category below the main report data source will appear in the Current Datasource box. Simply select the subreport data source as you did the main report data source and choose an alternate data source from the bottom list. All subreports that share the original data source will be directed to the new data source.

Note  

If database structures have changed significantly, you may need to re-link tables in the Database Expert for either the main report or affected subreports. Double-check links by displaying the Database Expert and clicking the Links tab.

Not only can you use these steps to point your report from one database of a particular type (SQL Server, Informix, etc.) to another of the same type, but you may also change database types in the process. For example, if you ve built a report based on a test Microsoft Access database, you can use Set Datasource Location to point the report to a production database hosted on Microsoft SQL Server.

Three special features in Set Datasource Location apply only to reports based on PC-style data source connections (such as the Access/Excel DAO connection type). They provide quick ways to identify and modify the name, local directory, or network path for a database. If necessary, click the plus sign to the left of the PC database name in the Current Datasource box. Then, expand the Properties category by clicking the plus sign next to it. You ll notice several properties for the chosen database, including the database type, physical file location, user ID, and so forth. While some of these properties are display-only, many of them can be changed. In particular, the Database Name property has several options you can choose from a pop-up menu. Right-click the Database Name property to see the three options:

Editing Database Name

The Edit option allows you to change the path to the PC-style database used in the report. If the location or name of the database has changed, or will be different for the person to whom you are sending the report, you can change the path here. Changing the database filename in this path tells Crystal Reports to point to a database with the new name.

Same As Report Option

The Same As Report function will remove any drive letter and path name from the PC-style database location. From that point forward, Crystal Reports will look for the database on the same disk drive and in the same folder as the report.

This allows you to create and save a report for distribution to other report viewers. The other viewers can place the report on any drive and in any folder that they choose. As long as the database the report is based on is in the same folder as the report, the report will be able to find it.

Converting to a UNC Name

The Convert To UNC function will change the hard-coded drive letter and path name for the PC-style database to a Uniform Naming Convention (UNC) name that can easily be found by any computer on the network, regardless of its drive mapping. UNC is a way of pointing to a file on a network disk drive without using a drive letter. Consider the following scenario:

The two PCs are connected to the same LAN server, but are using different drive letters to reach the server. If the first PC creates a report based on Accounts.MDB, the report will have the drive letter and filename H:\Accounts.MDB hard-coded into it. When the second PC opens the report, the report will fail ”the database won t be found on drive H.

To avoid this kind of problem, you can use a UNC name to replace the drive letter. A UNC name appears in the following format:

 \<Server Name>\<Share Name>\<Path and Filename> 

• Server Name is the actual name of the computer or server where the file is located (in this case, Groucho). Two backslash characters precede the server name.

• Share Name is a name that the LAN administrator has given to a particular group of shared files and folders on the server. When a PC maps a drive letter to a LAN server, the drive letter is mapped to a particular share name (in this case, Databases). A single backslash character separates the Server Name from the Share Name .

• Path and Filename are similar to path names and filenames used on a local PC hard drive, except they are located on the LAN server. In this case, the Accounts.MDB file is in the root of the Databases share name. A single backslash character separates the Share Name from the Path and Filename (and this path may contain additional backslash characters).

Based on these rules, the corresponding UNC name for the Accounts.MDB file will be as follows :

 \Groucho\Databases\Accounts.MDB 

Table Names vs. Aliases

When you first create a new report based on certain database tables, you'll notice that the actual name of the table you originally chose shows up in the Field Explorer, the Database Expert, and other places in the report. Although it may appear that Crystal Reports must refer to a database table by its physical name, this name is actually an alias assigned to the table. While the alias takes on the physical table name by default, it doesn't have to always keep that name. You have the flexibility to change the alias Crystal Reports uses to refer to this table.

To change the alias, display the Database Expert with the appropriate toolbar button or Database Database Expert menu option. Choose the table you wish to assign a new alias by clicking it in the Selected Tables box on the right side of the Database Expert. Then, press the f2 key, right-click, and choose Rename from the pop-up menu, or simply hold your mouse button down on the table name for a few seconds. The table name will be placed in edit mode. Simply type a new alias name in for the table.

 

Notice that the drive letter has been removed. Now, any PC on the network can find the file based on the UNC name ”the PC doesn t have to have a drive letter mapped to the LAN server.

Dealing with Table Name Changes

If a database designer or administrator changes the name of a table in the database, the next time you try to refresh your report, you will receive an error indicating that the table can t be found. Unfortunately, refreshing the report or verifying the database will not solve this problem ”the message will persist and the report will not print properly.

To solve this problem, perform the following simple steps:

1. Use the Set Datasource Location function as described in this chapter to connect to the new data source (this may be as simple as contracting and re-expanding the category of the Database Expert where the original database was). Select the old table name in the Current Data Source list and the new table name in the Replace With list. Click Update. This will correctly point the report to the new table name. However, the table will still be referred to by the old name in the report.

2. If you want the new name to appear throughout the report, use the Database Expert to change the alias of the old table to the new table name as described earlier in the chapter. This will change the name of the table as well, avoiding confusion as to which physical table is actually being used.