This topic describes how to create a data link string to an Access, Oracle, or SQL Server database or to an Excel file. The Microsoft Jet 4.0 OLE-DB Provider driver is the preferred method of connecting to an Access 2000, 2002, or 2003 database. Connecting to an Access 2007 database requires the ODBC provider. Access 2000 databases can also be accessed via the ODBC provider.
Excel is capable of functioning as an OLAP database and accepting a database connection. The Microsoft Jet 4.0 OLE-DB Provider enables connecting to .xls files for the purpose of importing and exporting data. The Excel file must be open to establish a connection from Metrics Management.
How to define a data link string
1 In Database connection type, select Define connection using a data link string, as shown in Figure 7‑3.
Figure 7‑3 Choosing to create a data link string
2 To define the data link string, choose Edit.
How to define a link to an Access 2000, 2002, or 2003 database
1 In Data Link Properties, in Provider, select Microsoft Jet 4.0 OLE DB Provider, as shown in Figure 7‑4.
Figure 7‑4 Selecting a provider
2 Choose Connection. In Data Link Properties—Connection, in Specify or enter a database name, provide the path and name of the Access database to which to create the connection, as shown in Figure 7‑5. Then, choose OK.
Figure 7‑5 Providing the database name and credentials
How to define a link to an Access 2007 database
1 In Data Link Properties, in Provider, select the Microsoft OLE DB Provider for OBDC Drivers, as shown in Figure 7‑6.
Figure 7‑6 Selecting a provider
2 Choose Connection. In Data Link Properties—Connection, in Specify the source of data, select Use connection string, as shown in Figure 7‑7.
Figure 7‑7 Choosing to create a connection string
3 Choose Build.
4 In Select Data Source, as shown in Figure 7‑8, in DSN Name, choose New.
Figure 7‑8 Select Data Source
5 In Create New Data Source, select Microsoft Access Driver (*.mdb, *accdb), as shown in Figure 7‑9. Then, choose Next.
Figure 7‑9 Selecting the Microsoft Access Driver
6 In Create New Data Source, type a name for the DSN entry. For example, type Connection to Access Database, as shown in Figure 7‑10. Then, choose Next.
Figure 7‑10 Specifying the Access database
7 In Create New Data Source, as shown in Figure 7‑11, choose Finish.
Figure 7‑11 Choosing Finish
8 In OBDC Microsoft Access Setup, as shown in Figure 7‑12, choose Select.
Figure 7‑12 Choosing Select
9 In Select Database, in Directories, browse to the location containing the Access database. In Database Name, select the database connection, as shown in Figure 7‑13. Then, choose OK.
Figure 7‑13 Selecting the Access database
10 In ODBC Microsoft Access Setup, choose OK.
11 In Select Data Source, choose OK. Then, choose OK.
How to define a link to an Excel file
1 In Data Link Properties, in Provider, select the Microsoft Jet 4.0 OLE DB Provider, as shown in Figure 7‑14.
Figure 7‑14 Selecting a provider
2 Choose Connection. In Data Link Properties—Connection, in Specify or enter a database name, provide the path and name of the Excel file to which to export, as shown in Figure 7‑15.
Figure 7‑15 Providing the database name and credentials
3 Choose Advanced. In Data Link Properties—Advanced, clear the Share Deny None check box, and select ReadWrite. Advanced looks like the example in Figure 7‑16.
Figure 7‑16 Specifying the database access permissions
4 Choose All. In Data Link Properties—All, in Name, select Extended Properties, as shown in Figure 7‑17. Choose Edit Value.
Figure 7‑17 Selecting Extended Properties
5 In Property Value, enter the following parameters, as shown in Figure 7‑18, then choose OK:
Excel 8.0;hdr=no;
where
Excel 8.0 specifies the ISAM (Indexed Sequential Access Method) driver. The ISAM driver allows Excel to import and export data. When connecting to Excel, you specify which ISAM driver to open to allow for the connection. For Microsoft Excel versions 2000, XP, 2003, and 2007 specify the Excel 8.0 ISAM driver.
hdr specifies whether the Excel file contains header information. For imports, specifying "hdr=no" indicates that the first row of the Excel file does not contain header information or column names.
Figure 7‑18 Specifying parameters for the ISAM driver and header information
6 Choose OK.
How to define a link to an Oracle database
1 In the Data Link Properties dialog box, click on the Provider tab to set the correct provider. From the OLE DB Provider(s) list, select Oracle provider for OLE DB.
2 In Data Link Properties, in Provider, select the Oracle Provider for OLE DB, as shown in Figure 7‑19.
Figure 7‑19 Selecting the Oracle Provider for OLE DB
3 Choose Connection. In Data Link Properties—Connection, provide the database name and credentials, and select Allow saving password, as shown in Figure 7‑20. Then, choose OK.
Figure 7‑20 Providing the Oracle database name and credentials
How to define a link to a SQL Server database
1 In Data Link Properties, in Provider, select the Microsoft OLE DB Provider for SQL Server, as shown in Figure 7‑21.
Figure 7‑21 Selecting the Microsoft OLE DB Provider for SQL Server
2 Choose Connection. In Data Link Properties—Connection, perform the following tasks, as shown in Figure 7‑22. Then, choose OK.
Provide the server name on which the SQL Server database resides in Select or enter a server name.
In Enter information to log on to the server, either select Use Windows NT Integrated security, or select Use a specific user name and password, and provide a user name and password.
Choose Select the database on the server. Select the SQL Server database.
Figure 7‑22 Providing the database name and credentials
How to define a data link file
1 In Database connection type, select Define the connection using a data link file, as shown in Figure 7‑23.
Figure 7‑23 Choosing to create a data link file
2 Choose Browse. Then, navigate to a data link file. Alternatively, select a named path from the list. Then, type a file name. Choose Open.
3 Optionally, choose Edit. Then, make changes to the file, if necessary.
4 Choose Save.
How to test the database connection
1 In Database Connection, choose Test. If the connection succeeds, the confirmation message appears. Then, choose OK.
2 To see the database connection properties, choose Properties. Save properties information as a text file using the .ini file format for troubleshooting purposes. Choose Close.
3 Choose Save.
How to specify a connection time-out value
1 In Setup database connection, choose Options. In Options, either accept the default setting, 120 seconds or select Use this time out. Then, provide a value in seconds between 1 and 1800, as shown in the example in Figure 7‑24.
Figure 7‑24 Specifying a database connection time-out value
2 Choose Save.
How to specify security settings
1 If necessary, choose Security. Then, assign access rights for the database connection to groups and users.
2 Choose Save.
Editing a database connection
Use the following procedure to make changes to a database connection.
How to edit a database connection
1 In the Navigation Pane, choose Setup➛Database Connections.
2 In Setup Database Connections—Database Connections, select a database connection name.
3 Choose Edit or View.
4 In Setup Database Connections—Properties, select alternative properties.
Excel 8.0 specifies the ISAM (Indexed Sequential Access Method) driver. The ISAM driver allows Excel to import and export data. When connecting to Excel, you specify which ISAM driver to open to allow for the connection. For Microsoft Excel versions 2000, XP, 2003, and 2007 specify the Excel 8.0 ISAM driver.
