Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features Connecting ODBC Data Source Administrator

Buy now

Download now

SQL*XL online help

 

SQL*XL: Excel database connection through ODBC: Create New Data Source

SQL*XL: Excel database connection through ODBC: Create New Data Source

SQL*XL
home

In this window you choose the driver for which you a re adding a data source and click Finish; a driver-specific setup dialog box will appear. Find below a list of driver-specific setup dialog boxes:

What follows is information about the different driver-specific setup dialogs.


Adaptive Server Anywhere 9.0 (Sybase ASA a.k.a. SQL Anywhere)

The setup process for SQL Anywhere consists of the following 5 steps:

Step 1 - ODBC tab:

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

 

Step 2 - Login tab:

Choose between Integrated Security or to supply a user id and password. In this example I am connecting to the demo asa database as user dba with password sql (as they are set by Sybase)

Step 3 - Database tab:

Server Name:
Type the name of the database server. Note that this is not the name of the database server machine but the database itself. To find out your database server name, connect to the data on the server to inspect its name. In this example I am connecting to the SQL Anywhere demo database asademo which is called asademo9.

Leave the other parameters at their defaults.

 

Step 4 - Network tab:

Check the TCP/IP checkbox

Leave settings at their defaults.

Step 5 - test connection:

Return to the first tab - ODBC - to use the test button to test the connection.


SQL Server
The setup process for SQL server consists of 6 steps:

Step 1:

Name:
Type a name for the DSN

Description:
Not mandatory

Server:
Server name (PC name) or ip address where the database resides. Use the dropdown or type it in.

Step 2:

Choose between NT or SQL Server authentication. For NT authentication you need to have an account setup on the server. Use Client Configuration in case there are specific requirements how to communicate to the server. You can use TCP/IP on a specific port e.g. or use named pipes.

Step 3:

Default Database:
In the list you see all the databases on the server. Use master to be able to access all databases. I recommend to be specific and chose 1 database.

Leave the other parameters at their defaults.

Step 4:

Leave settings at their defaults.
Step 5:

Use the test button to try the configuration.
Step 6:

If the test is successful you should see this message.


Oracle 7
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

SQL*Net Connect String:
Database alias or TNS name. Use the same database name as you would use in SQL*Plus.

User Id:
Your user identifier.

 


Microsoft ODBC for Oracle

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

User Name:
Your user name as you would use in SQL*Plus.

Server:
Database alias or TNS name. Use the same database name as you would use in SQL*Plus.


Microsoft Access Driver (*.mdb)

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Database:
Use Select to locate an access database (.mdb).
Use Create to create a new access database.


Microsoft Text Driver (*.txt; *.csv)
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Database:
Use Select Directory to select a directory from which text files can be opened

Note that the text files in the directory will be used as "tables". You can execute e.g. select * from readme.txt

 


Microsoft dBase Driver (*.dbf)
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Version:
Select the dBase version: 5, IV or III

Use Current Directory:
Untick this option to be able to select the database directory.

Select Directory:
Use this button to select the directory that contains the *.dbf files.

 


Microsoft Paradox Driver (*.db)
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Version:
Select the Paradox version: 5.x, 4.x or 3.x

Use Current Directory:
Untick this option to be able to select the database directory.

Select Directory:
Use this button to select the directory that contains the *.db files.

 


Microsoft Excel 4.0/3.0 Driver (*.xls)
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Version:
Select the Excel version: 4.0 or 3.0

Select Directory:
Use this button to select the directory that contains the *.xls file you wish to use. Note that this driver regards the .xls file as a table so you can execute statements like select * from my_excel_file.xls.

 


Microsoft Excel Driver (*.xls)
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Version:
Select the Excel version: 97-2000, 5.0/95

Select Workbook:
Use this button to select the .xls file you wish to use. Note that the tables that are available for querying are created in the .xls file as named ranges (Insert|Names). This in contradition to the Excel 3.0/4.0 driver.

 

MySQL Driver
 
Windows DSN Name:
Type a name for the DSN

MySQL host (name or IP):
Type the name of the host where the MySQL database resides. Usually this is your webserver address. You may also type the IP address equivalent. E.g. 123.123.123.123

MySQL database name:
Type the name of the MySQL database you wish to connect to. If you don't know this it may be set to the same name as your domain. Try mydomain if you have www.mydomain.com

User and Password:
Type the username and password to connect to the datbase. If you don't know your username it may also be set to your domain name.

Port:
Leave this value to the default unless you know that it should be a different value.

Leave all other values at their default.
 


Microsoft Visual Foxpro Databases (*.dbc)
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Database Type:
Select the Visual Foxpro Database (*.dbc) option to connect to the database

Path:
Enter the location of the *.dbc files (database) can be found. Use the browse button to select a file.

Driver:
Leave all these settings at their defaults.

 

Microsoft Visual Foxpro Tables (*.dbf)
 

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Database Type:
Select the Free Table directoty option to connect to individual *.dbf files (tables)

Path:
Enter the path where the *.dbf files (tables) can be found. Use the browse button to select a directory.

Driver:
Leave all these settings at their defaults.

 

MySQL Driver
 
Windows DSN Name:
Type a name for the DSN

MySQL host (name or IP):
Type the name of the host where the MySQL database resides. Usually this is your webserver address. You may also type the IP address equivalent. E.g. 123.123.123.123

MySQL database name:
Type the name of the MySQL database you wish to connect to. If you don't know this it may be set to the same name as your domain. Try mydomain if you have www.mydomain.com

User and Password:
Type the username and password to connect to the datbase. If you don't know your username it may also be set to your domain name.

Port:
Leave this value to the default unless you know that it should be a different value.

Leave all other values at their default.
 


PostgreSQL Driver
 

Data Source:
Type a name for the DSN

Database:
Type the name of the database you wish to connect to. By default ProgreSQL installs databases called template0 or template1. You can find out the names of your databases by using the ProgreSQL psql utility. Execute the command \l to see all the databases on the server.

Server:
Type the name of the host where the MySQL database resides. Usually this is your webserver address. You may also type the IP address equivalent. E.g. 123.123.123.123. Note that 127.0.0.1 is the current PC. I have used that since I run the database on the same PC as I use SQL*XL.

Description:
Optional.

User and Password:
Type the username and password to connect to the datbase. If you don't know your username it may also be set to your domain name.

Port:
Leave this value to the default unless you know that it should be a different value.

Leave all other values at their default.

 

Firebird / Interbase Driver
 

Data Source:
Type a name for the DSN

Driver:
Leave at its default value

Database:
Use the browse button to select the database file (*.FDB) you wish to connect to. When I installed the example database was installed to c:\program files\firebird\firebird_1_5\examples\employee.fdb but to show it in the dialog above it I moved it to the c:\databases directory.

Client:
Leave blank if the database is a local database.

Database Account:
Type the user name you wish to use. You can use the SYSDBA user

Password:
Type the password here. The default password for SYSDBA is masterkey

Leave all other values at their default.

 

Advantage Database (Advantage StreamlineSQL ODBC)

The setup process for Advantage databases consists of the following step:

Data Source Name:
Type a name for the DSN

Description:
Not mandatory

Database or Data Dictionary Path:
Browse to the database path. The directory contains the free table files e.g. .adm files.

 


IBM DB2 ODBC Driver

The setup process for DB2 databases consists of the following step:

Data Source Name:
Type the same name as  you select from the Database alias dropdown. You can use the Add button to browse for DB2 servers on your PC or on the network.

Description:
Not mandatory


Cache - Intersystems ODBC Driver

The setup process for Intersystems' Cache databases consists of the following steps:

Data Source Name:
Type the name you want to use for this database.

Data Source Description:
Not mandatory

Connection Host and Port
I have left these to the defaults as it is a local test database (127.0.0.1).

Cache Namespace
I am connecting to the SAMPLES database. To see which databases are available I used the System Management Portal that is provided in the Cache program folder. In the databases section you can see all the databases that are available.

Username and Password
Cache provides a few default users that all have SYS as password. I used the _system user and SYS as password.


QuickBooks FlexQuarters QODBC Driver

The setup process for FlexQuarters' QODBC connections to QuickBooks files consists of the following steps:

Permissions:
Before you make a connection to a new QuickBooks file, open QuickBooks and open the file. If you do not do this you will get an error at the next step: 
"The connection failed because of an error initialising the provider. [QODBC] QB Begin Session Failed. Error = 80040418, This application has not accessed this QuickBooks company data file before. The QuickBooks administrator must grant an application permission to access a QuickBooks company data file for the first time."

When a connection is attempted for the first time a dialog in QuickBooks will display:
"An application without a certificate is requesting access to the following QuickBooks company:"

Data Name:
Type the name for this database.

Company File:
Select the QBW file you want to use. 

SQL*XL ribbon in Excel