SQL*XL online help

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

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 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 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 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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.