Using the Data Link Properties wizard
SQL*XL
home

This SQL*XL help page describes how the Data Link Properties wizard can be used to connect from MS Excel to your database. For SQL*XL - or any other program - to connect to your database through ADO you need to specify a connection string. The Data Link Properties wizard helps you to build a connection to your database. Once the connection string is known SQL*XL will save it and reuse it in the future without the need to go through it all again.

The process of setting up a connection to your database is explained in detail below. It contains step by step instructions for the more common database types.
SQL*XL:
Use your databases from within Excel!

Purpose:
Specify the connection settings to access a database. Choosing the different options in this Data Link Properties wizard you specify the connection details to the database. Once done SQL*XL will save these details for you in the Connection History list.

Usage:
Use Data Link Properties wizard (see below).

Data Link Properties

The wizard contains 4 sections of which you only have to concern yourself with the first two: Provider and Connection

Provider:
In the provider section you see a list of the OLE DB providers through which you can connect to databases. Lists all OLE DB providers detected on your computer.

A provider is actually nothing more than a driver (in old terminology) but the ODBC provider is the odd one out because it bundles the functionality for all possible ODBC drivers.

From the name of the provider it is usually clear which databases can be accessed through them. Note that for Access databases choose the Microsoft Jet OLE DB Provider. If no provider is available for your database choose the Microsoft OLE DB Provider for ODBC Drivers. Otherwise contact your database vendor to see if an OLE DB provider is available for their database.

Connection:
The contents of the connection section of the Data Link Properties wizard depends on which provider you choose:

What follows is a description of the contents of the Connection section for each of these Providers.

Microsoft OLE DB Provider for Oracle:
 

Microsoft OLE DB Provider for Oracle

 

Server Name:
Use the database alias as setup in your tnsnames.ora setup. Use the same database as you would use in SQL*Plus

Allow saving password:
Important! Set this when connecting to Oracle databases.

Test Connection:
Always test the connection before you continue.

Microsoft OLE DB Provider for SQL Server:
 

Microsoft OLE DB Provider for SQL Server

 

Server Name:
Select or type your server name. This is the PC name or IP address on which the database runs.

Use Windows NT Security:
Requires you to be setup as a user on the server.

Use specific Username and password:
Authentication is left to the database itself.

Select database:
Select a database to use. Leave blank to get the default database.

Test Connection:
Always test the connection before you continue.

Microsoft Jet OLE DB Provider:
 

Microsoft Jet OLE DB Provider

 

Database Name:
Type or browse to the location of your database file (*.mdb)

User Name:
If not specified the user id for an access database is Admin and has no password. If you use another username and password to enter the access database please use these details.

Test Connection:
Always test the connection before you continue.

Microsoft OLE DB Provider for ODBC Drivers:
 

Microsoft OLE DB Provider for ODBC Drivers

 

Use Data Source:
Select your Data Source Name (DSN) from the list. Use the Refresh button to rebuid the list.

If you don't have a DSN for your database yet, create one in the ODBC Data Source Administrator window.

Use Connection String:
Advice: use a DSN. It's easier. If you must you can create a connection string here or type it in.

Allow saving password:
It is suggested to always tick this although I have only found it required for Oracle databases.

Test Connection:
Always test the connection before you continue.

Microsoft OLE DB Provider for Indexing Service:
 

Microsoft OLE DB Provider for Indexing Service

 

Data Source:
"Web" seems to be the defaut Data Source.

Test Connection:
Always test the connection before you continue.

MS Project 9.0 OLE DB Provider:

 

MS Project 9.0 OLE DB Provider

 

Leave all the fields on this screen blank or at their default values. You may blank the initial catalog value. To indicate which *.mpp file to use, go to the "All" tab as shown below
 

MS Project 9.0 OLE DB Provider



Leave all the settings to their defaults except for Project Name. Select the Project Name entry and click the Edit Value button. Specify the location of your *.mpp file (in my test case the d:\test.mpp file).

After you specified the Project Name please go back to the "Connection" tab to use the test connection button.

Test Connection:
Always test the connection before you continue.
 

 

PostgreSQL OLE DB Provider:
 

PostgreSQL OLE DB Provider

Data Source:
Enter your server name or address. I used 127.0.0.1 because I run the database on the same PC as SQL*XL.

Location:
Enter  your database name. I used littest, my test database I created with the pgAdmin program provided with PostgreSQL.

Username and password:
Enter your username and password.

Test Connection:
Always test the connection before you continue.
 

 


Leave all the settings to their defaults except for Project Name. Select the Project Name entry and click the Edit Value button. Specify the location of your *.mpp file (in my test case the d:\test.mpp file).

After you specified the Project Name please go back to the "Connection" tab to use the test connection button.

Test Connection:
Always test the connection before you continue.
 

Advantage OLE DB Provider:

 

Advantage OLE DB Provider

Data Source:
Enter the directory of the database. This is a so called free table database. All files in the directory will be presented as tables.

Username and password:
Leave the username and password blank unless you were given other usernames and passwords to use.

Test Connection:
Always test the connection before you continue.

 

Active Directory : OLE DB provider for Directory Services:

 

Active Directory : OLE DB provider for Directory Services

Please note:
Irrespective your settings the connection will succeed. Entering no user details will use your current logon details. I assume this is the same as NT integrated security. I have tried to connect using NT security, my own user/pass and a nonexistent user/pass combination. All attempts gave me the same access level to AD.

Data Source:
Enter a descriptive name that you associate with the Active Directory. This will be used later as database name.

Username and password:
I suggest you enter your user name and password.

Test Connection:
Always test the connection before you continue.

 

DB2 : IBM OLE DB provider for DB2 Servers:

 

DB2 : IBM OLE DB provider for DB2 Servers

Data Source:
 Enter the name of the database you want to connect to.

Username and password:
When you leave the username and password blank, integrated NT security will be used. When you cannot connect, enter a username and password and try again.

Test Connection:
Always test the connection before you continue.

 

viksoe.dk OLE DB provider for XML:

You can obtain a copy of the freeware viksoe.dk OLE DB provider for XML at: http://www.viksoe.dk/code/xmloledb.htm
 

viksoe.dk OLE DB provider for XML

Data Source:
Enter a descriptive name as Data Source. SQL*XL will use this name as database name

Location:
Enter the full path to the xml file in the Location field

Username:
 Username seems to be ignored. You can enter anything here.

Test Connection:
Always test the connection before you continue.

 

See also: