Configuring An Excel ODBC Data Source In Windows 7

The Microsoft ODBC Data Source Administrator manages database drivers and data sources. This application is located in the Windows Control Panel under Administrative Tools. Beginning in Windows 8, the icon is named ODBC Data Sources, and on 64-bit operating systems there is a 32-bit and 64-bit version. Using the ODBC Data Source Administrator, you can add/modify/remove connections for use with the Microsoft ODBC Driver for DB2.

Configuring an Excel ODBC Data Source in Windows 7

Data sources are the databases or files accessed by a driver and are identified by a data source name (DSN). Use the ODBC Data Source Administrator to add, configure, and delete data sources from your system. The types of data sources that can be used are described in the following table.

User and system data sources are collectively known as machine data sources because they are local to a computer. Each of these data sources has a tab in the ODBC Data Source Administrator dialog box.

The data source name is used by the ODBC Data Source Administrator to name the data source within the registry or file system. This required attribute accepts a string value of up to 60 characters. The default value is an empty string.

The description is used by the ODBC Data Source Administrator to describe the data source within the registry or file system. This optional attribute accepts a string value of up to 60 characters. The default value is an empty string.

The Use this user name radio button instructs the Data Provider to use interactive sign-on security, to send a user name and password value based on information stored in the data source configuration, data consumer program, or prompted from the user at connection time. This required attribute accepts a string value in the form of a DB2 user name. The default is an empty string. The following table lists the DB2 database platform and accepted string lengths.

A data source is a source of data combined with the connection information that is required to access that data. Examples of data sources are SQL Server, Oracle RDBMS, a spreadsheet, and a text file. Examples of connection information include server location, database name, logon ID, password, and various ODBC driver options that describe how to connect to the data source. This information can be obtained from the administrator of the database to which you want to connect.

In the ODBC architecture, an application such as Access connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (for example, Microsoft SQL ODBC driver) to connect to a data source. In Access, you use ODBC data sources to connect to data sources external to Access that do not have built-in drivers.

Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the Microsoft Windows registry or a DSN file, or a connect string in Visual Basic code to pass the connection information directly to the ODBC Driver Manager.

File data sources (also called DSN files) store connection information in a text file, not the Windows registry, and are generally more flexible to use than machine data sources. For example you can copy a file data source to any computer that has the correct ODBC driver so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.

A file data source can also be unshareable. An unshareable file data source is contained on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.

This topic describes how to open the ODBC Data Source Administrator. The ODBC Data Source Administrator is a Windows component. Use the ODBC Data Source Administrator to create and manage ODBC data sources.

After you download and install the ODBC driver, add a data source name (DSN) entry to the client computer or Amazon EC2 instance. SQL client tools use this data source to connect to the Amazon Redshift database.

Enter a name for the data source. You can use any name that you want to identify the data source later when you create the connection to the cluster. For example, if you followed the Amazon Redshift Getting Started Guide, you might type exampleclusterdsn to make it easy to remember the cluster that you associate with this DSN.

On Linux and macOS X operating systems, you use an ODBC driver manager to configure the ODBC connection settings. ODBC driver managers use configuration files to define and configure ODBC data sources and drivers. The ODBC driver manager that you use depends on the operating system that you use:

Additionally, under /opt/amazon/redshiftodbc/Setup on Linux or /opt/amazon/redshift/Setup on macOS X, there are sample odbc.ini and odbcinst.ini files. You can use these files as examples for configuring the Amazon Redshift ODBC driver and the data source name (DSN).

When connecting to your data store using a data source name (DSN), configure the odbc.ini file to define DSNs. Set the properties in the odbc.ini file to create a DSN that specifies the connection information for your data store.

In Microsoft Windows, you typically set driver options when you configure a data source name (DSN). You can also set driver options in the connection string when you connect programmatically, or by adding or changing registry keys in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\your_DSN. For more information about configuring a DSN, see Install and configure the Amazon Redshift ODBC driver on Microsoft Windows. For an example of setting driver options in a connection string, see Connect to your cluster programmatically.

The second step is to configure UnixODBC with a Data Source Name (DSN) for your MariaDB Server. A DSN allows you to centrally configure all of your server's connection parameters, so that you can easily configure how to connect to your server in your environment. To configure the DSN, you can use the odbcinst tool, which can add a configuration entry for the given data source to the system's global /etc/odbc.ini file or your user's local /.odbc.ini file.

If your DSN is a user DSN (most likely) configured for a 64-bit driver, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI and delete the data source key under ODBC.INI and the entry under ODBC.INI\ODBC Data Sources:

Select the User DSN tab or the System DSN tab depending on weather you wish the SQL data to be made available to only the current user (User DSN) or all users (System DSN). Then click Add.... This will start a wizard that allows you to create a new data source.

Before connecting a third-party application to a database or cloud source through ODBC, you need to set up a data source name (DSN) for the ODBC driver in the Data Source Administrator. A 64-bit version of the Microsoft Windows operating system includes both the 64-bit and 32-bit versions of the Open Database Connectivity (ODBC) Data Source Administrator tool (odbcad32.exe):

4. Enter a name for your data source in the corresponding field. Fill in the parameters for the ODBC connection string, which is driver-specific. In most of our ODBC drivers for databases, a connection string with basic parameters requires the user to only input their server address, port number, and login credentials, since Devart ODBC drivers allow direct access to the database without involving additional client libraries.

The steps for configuring an ODBC DSN for a 32-bit driver are practically the same as for the 64-bit driver, except for the step where you select the 32-bit version of the ODBC Data Source Administrator. Running the odbcad32 command in the Command Prompt or in the Run dialog box will start the 64-bit version of the ODBC administrator on the 64-bit Windows by default, therefore your best option is to select the 32-bit version of the administrator in the search results of the Windows search box.

Open Database Connectivity (ODBC) provides a standard interface that allows one application to access many different data sources. The application's source code does not have to be recompiled for each data source. A database driver links the application to a specific data source. A database driver is a dynamic-link library that an application can invoke on demand to gain access to a particular data source. Therefore, the application can access any data source for which a database driver exists.

The Oracle ODBC Driver uses the Oracle Call Interface (OCI) client and server software to submit requests to and receive responses from the data source. Oracle Net Services communications protocol is used for communications between the OCI client and the Oracle server.

The Oracle ODBC Driver translates ODBC SQL syntax into syntax that can be used to access the data source. When the results are returned from the data source, the Oracle ODBC Driver translates them back to ODBC SQL syntax.

* The Oracle ODBC Resource data definition language (DLL) file (sqresxx.dll), where xx represents the language abbreviation, contains all pertinent language information; the default resource file used is sqresus.dll.

Oracle ODBC driver is enhanced to prefetch LONG or LONG RAW data to improve performance of ODBC applications. To do this, the maximum size of LONG data (MaxLargeData) must be set in the registry on Windows (you also must add the registry key MaxLargeData in the data source name (DSN)), and set this manually in the odbc.ini file on UNIX platforms. This enhancement improves the performance of Oracle ODBC driver up to 10 times, depending on the MaxLargeData size set by the user. The default value of MaxLargeData is 0. The maximum value for MaxLargeData that you can set is 64 KB (65536 bytes).