Creating a Database Connection

In this section we will describe how to define or modify a database connection for any database for which an OLE DB provider is properly installed on the user’s system. In general, the method of database connection will depend on the backend database engine and their respective supported OLE DB providers.

Using the popup menu on the Databases root-node of the Connections pane, we select the New menu item. This will open the New Database Connection dialog (see Figure 1.11 for more details).


Figure 1.11. Showing the New Database Connection dialog

The user must then select a data access method using one of the items in the Data Access Type list-box. And enter a name for the connection in the Connection Name text-box.

When the user OK-s this dialog, depending on the data access type selected different type of data access dialogs may be prompt.

In general, the Data Access Type options are as follows:

·        OLE DB Dynamic Data Access using ADO, which will prompt the Data Link Properties dialog for an OLE DB provider and further properties about the data source (see Figure 1.12),

·        OLE DB UDL Link-file using ADO, which will prompt the Select Data Link File dialog for the user to specify the location and name of an existing UDL data link file (see Figure 1.13),

·        ODBC Machine Data Source using DSN, which will prompt the Select Data Source dialog with the Machine Data Source tab active, for the user to select an ODBC machine data source name (see Figure 1.14),

·        ODBC File Data Source using DAO, which will prompt the Select Data Source dialog with the File Data Source tab active, for the user to select a data source file (see Figure 1.14).

 


Figure 1.12. Showing the Data Link Properties dialog

In the case of OLE DB Dynamic Data Access via ADO, the user is prompt with the Data Link Properties dialog to select the OLE DB Provider from the Provider tab, as shown in Figure 1.12 on the left.

For example, for MS-SQL Server the user must select the Microsoft OLE DB Provider for SQL Server item from the list of providers. For Oracle the best option is the native Oracle Provider for OLE DB. For MS-Access one may use the Microsoft Jet 4.0 OLE DB Provider, while for all other ODBC compliant databases the Microsoft OLE DB Provider for ODBC may be used.

After selecting a proper OLE DB Provider, the user may click the Next >> button which will switch the tab to Connection, to enter more details about the connection, such as: data source name / location of data / server name, server logon information, initial catalog or database name.

In the case of OLE DB UDL Link-file using ADO, it is assumed that the user wants to use an already existing UDL-file (Universal Data Link), which stores the connection information. Therefore using the Select Data Link File dialog the user may locate and open the UDL-file (see Figure 1.13 on the right).

Note that once the connection is defined in the Connections pane, the connection information is transferred from this UDL-file into the project directory for the current instance of the query builder.


Figure 1.13. Showing the Select Data Link File dialog

For the options using ODBC through a machine data source name or data source file, the user is prompt with the Select Data Source dialog (see Figure 1.14).


Figure 1.14. Showing the Select Data Source dialog

In the Select Data Source dialog the user may select an existing machine DSN or data source file, respectively from the Machine Data Source or File Data Source tabs.

Note these ODBC data access options may be used when the backend data source does not support any adequate OLE DB Provider. In general, we recommend the use of the OLE DB Dynamic Data Access or OLE DB UDL Link-file options utilizing ADO.

In all cases when the connection information is specified and the dialogs submitted, a new connection node is added to the Databases root-node.

To modify an existing connection node the user may use the Properties menu item from the popup menu obtained via the right-mouse button click method on the selected node itself. This will display the connection information dialog corresponding to the data source and data access type for the selected connection node, with which the user may modify the data source name / location of data / server name, server logon information, initial catalog or database name.