Article ID: 213772 - View products that this article applies to.
This article was previously published under Q213772
For a Microsoft Office 97 version of this article, see 159557
Microsoft Office contains Open Database Connectivity (ODBC) drivers that you can use to access data from other programs. This article describes the different types of data source names (DSN) that you can install and use in Microsoft Office programs, specifically in Microsoft Excel.
When you install Microsoft Office, an ODBC icon that represents the ODBC Manager is installed in Control Panel. The ODBC Manager allows you to set up and configure ODBC data sources. In the ODBC Manager, you can set up and configure the following three types of DSNs:
User DSNThe User DSN is a data source that is user-specific. A User DSN is stored locally but is available only to the user who creates it. User DSNs are not used by Microsoft Query. If you use Microsoft Jet, ODBC, or Structured Query Language (SQL) commands and bypass Microsoft Query, User DSNs are required. User DSNs are stored in the Windows registry under the following key:
HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources
System DSNUnlike a User DSN, a System DSN is not user-specific. A System DSN is stored locally and is not dedicated to a particular user. Any user who logs on to a computer that has permission to access the data source can use a System DSN. Some programs, such as Microsoft SQL Server or Microsoft Internet Information Server (IIS), require a System DSN. This DSN must be created on the server where the program is located. System DSNs are stored in the Windows registry under the following key:
HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources
File DSNThe File DSN is created locally and can be shared with other users. The File DSN is file-based, which means that the .dsn file contains all the information required to connect to the data source. Note that you must install the ODBC driver locally to use a File DSN. Microsoft Query uses File DSNs, but Microsoft Jet and ODBC do not use File DSNs.
The File DSNs are stored by default in the Program Files\Common Files\Odbc\Data Sources folder. File DSNs are not stored in the Windows registry. The .dsn file is a text file that you can view in any text editor, such as Microsoft Notepad.
NOTE: When you connect to an existing data source using Microsoft Query, only the available File DSNs that are stored on that computer are displayed. Microsoft Query does not display User or System DSNs. However, you can create a File DSN that points to a System DSN.
To create a File DSN that points to a System DSN, follow these steps:
Sample Macro to Return External Data to Microsoft Excel
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:
Microsoft Certified Partners - https://partner.microsoft.com/global/30000104
Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
The following Microsoft Excel Visual Basic for Applications macro can use an existing User or System DSN to retrieve data from a database and store the data in a worksheet. The sample DSN that is used in this macro is MyDSN. It references the Microsoft Access sample database Northwind.mdb in the Program Files\Microsoft Office\Office\Samples folder. You can use MyDSN as a User or System DSN, but you cannot use it as a File DSN.
For more information about retrieving data, click Microsoft Excel Help on the Help menu, type ways to retrieve data from an external database in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Article ID: 213772 - Last Review: January 24, 2007 - Revision: 3.6