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.
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).
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:
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:
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:
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:
Use Data Source:
Select your Data Source Name (DSN) from the list. Use the Refresh button to rebuid the list.
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:
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:
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
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:
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:
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:
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:
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.
Copyright (C) 1995-2007 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.
sitemap
