Configuring An Excel ODBC Data Source In Windows 7
Tanesha Prately
This article demonstrates how to connect to an ODBC data source from the Choose a Data Source or Choose a Destination page of the SQL Server Import and Export Wizard.
The ODBC drivers installed on your computer aren't listed in the drop-down list of data sources. To connect with an ODBC driver, start by selecting the .NET Framework Data Provider for ODBC as the data source on the Choose a Data Source or Choose a Destination page of the wizard. This provider acts as a wrapper around the ODBC driver.
Provide a DSN (data source name) that already exists or that you create with the ODBC Data Source Administrator applet. A DSN is the saved collection of settings required to connect to an ODBC data source.
If you provide a connection string, the Choose a Data Source or Choose a Destination page displays all the connection info that the wizard is going to use to connect to your data source, such as server and database name and authentication method. If you provide a DSN, this information isn't visible.
If you want to provide the connection information with a DSN (data source name), use the ODBC Data Source Administrator applet to find the name of the existing DSN, or to create a new DSN.
If you want to use an existing DSN for your data source, you can use any DSN that you see on the User DSN, System DSN, or File DSN tab. Check the name, then go back to the wizard and enter it in the Dsn field on the Choose a Data Source or Choose a Destination page. Skip the rest of this page and continue to the next step of the wizard.
The driver now displays one or more driver-specific screens where you enter the info needed to connect to your data source. (For the SQL Server driver, for example, there are four pages of custom settings.) After you finish, the new system DSN appears in the list.
The connection options for an ODBC driver are the same whether you're configuring your source or your destination. That is, the options you see are the same on both the Choose a Data Source and the Choose a Destination pages of the wizard.
After you click Finish, the driver that you selected displays one or more proprietary screens to gather the info it needs to connect. Typically this info includes server, login info, and database for server-based data sources, and file, format, and version for file-based data sources.
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.
Connecting to a data source means establishing communications with a DBMS to access the data. When you connect to a data source from an application through an ODBC driver, the driver makes the connection for you, either locally or across a network.
You can connect to any data source for which you have an ODBC driver. Users of your application must also have the same ODBC driver for their data source. For more information about redistributing ODBC drivers, see Redistributing ODBC Components to Your Customers.
ODBC Administrator is used to configure your data sources. You can also use ODBC Administrator after installation to add or remove data sources. When you create applications, you can either direct your users to the ODBC Administrator to let them add data sources or you can build this functionality into your application by making direct ODBC installation calls. For more information, see ODBC Administrator.
If multiple users are connected to a data source, they can change data while you are manipulating it in your recordsets. Similarly, your changes might affect other users' recordsets. For more information, see Recordset: How Recordsets Update Records (ODBC) and Transaction (ODBC).
The wizards use a default connection string to establish a connection to a data source. You use this connection to view tables and columns while you are developing your application. However, this default connection string might not be appropriate for your users' connections to the data source through your application. For example, their data source and the path to its location might be different from the one used in developing your application. In that case, you should reimplement the CRecordset::GetDefaultConnect member function in a more generic fashion and discard the wizard implementation. For example, use one of the following approaches:
Edit the connection string and remove the data source name. The framework supplies ODBC as the data source; at run time, ODBC displays a dialog box asking for the data source name and any other required connection information.
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.
If you have existing Excel workbooks with data connections to your Penelope database there may be a need (on some occasion) to update one or both of the ODBC data sources in Windows and/or data connection/s in Excel. This would be necessary for the following situations:
Once the data source is successfully connecting, open any existing Excel workbooks that use the same data source and check all Excel data connections within each workbook. To open existing connections from the Data tab click on the Connections button in the Connections section of the ribbon.
Stata's odbccommand allows you to load, write, and view data from Open DataBase Connectivity (ODBC) sources. Before you start using the odbc command in Stata, you must first set up a data source name (DSN) in the ODBC Data Source Administrator. Setting up a DSN differs slightly depending on your version of Windows.
In the ODBC Data Source Administrator, click on the Add button.
- Select the appropriate driver from the list. The driver that you need isdetermined by the data source you are trying to connect to. For example, chooseMicrosoft Access Driver (*.mdb) to read from a MS Access database.
Assign a name and select the appropriate source. The DSNis completely up to you. You will see a list of availabledatabases or, if you are using Excel, available files. Select the onesyou want to associate with the DSN you just created.
Make sure you are using the correct driver for the DSN. If you are usingStata 16 or later, you need a 64-bit driver. You should be able todownload the appropriate 64-bit database driver from your data source'sdatabase vendor. You will have to follow the driver installationinstructions for it to show up in the ODBC Data Source Administrator.
aa06259810