Universal Data Link Properties

Overview

If the Data Link Properties window opens with the Connection tab selected, you should click the Provider tab at the top of the screen to start your configuration.

Provider Tab

The Provider tab allows you to select the OLE DB Provider that matches your database.

There are many cases where you have several possible options for connecting to your database. One good example of having several valid connection options is MS Access. The Jet OLE DB Provider and the OLE DB Provider for ODBC Drivers will both allow direct connection to an Access database file such as Northwind.mdb. Likewise, there is an Oracle OLE DB Provider as well an SQL OLE DB Provider , and both of those database types allow ODBC connections. In general, it is faster to use a direct OLE connection instead of ODBC. The OLE DB Provider for ODBC Drivers also requires that you separately configure an ODBC connection to your database.

Connection Tab

Once you have selected your data provider, (see previous topic) click the Next button or click the Connection tab. Shown below are the connection tabs for Microsoft OLE DB Provider for ODBC Drivers on the left and the Microsoft Jet 4.0 OLE DB Provider on the right.

On the Connection tab, you can specify where your data is located and how to connect to it.

The fields displayed on this tab depend upon the OLE DB Provider selected. For example, if you select the OLE DB Provider for Oracle, the Connection tab displays fields for the server name and login. If you select the OLE DB Provider for SQL Server, then the server name, type of authentication, and database must be furnished.

In most cases, once you have configured the Connection tab, you can click the Test Connection button to determine if the Jet Excel add-in can connect to your database. If your database connection works you will get the following message.

Test Connection Successful.

Advanced and All Tabs

Use the Advanced tab to view and set other initialization properties for your data. The Advanced tab is provider-specific and displays only the initialization properties required by the selected OLE DB provider. For more information about advanced initialization properties, refer to the documentation provided with each specific OLE DB provider.

Use the All tab to view and manually edit all OLE DB initialization properties available for your OLE DB provider. Properties can vary depending on the OLE DB provider you are using. For more information about the initialization properties, refer to the documentation provided with each specific OLE DB provider.

Setting up Northwind

The NorthWind database is a sample access database available for MS Office Access and SQL Server and is used as an example. By default in Office 2003, NorthWind is installed at C:\Program Files (x86)\Microsoft Office\Office11\SamplesNorthWind.mdb.

For later versions, please see Microsoft's documentation.

After you select the Microsoft Jet OLE DB Provider on the Provider tab of the Data Link Properties window, click Next to enter the path to the database. You will need to enter Admin as the User name and select the Blank password option. Click Test Connection and you should have access to NorthWind.