Microsoft Office 97 contains Open Database Connectivity (ODBC) drivers thatyou can use to access data from other programs. This article describes thedifferent types of data source names (DSN) that you can install and use inMicrosoft Office 97 programs, specifically in Microsoft Excel 97.
When you install Microsoft Office, an ODBC icon, which represents the ODBCManager, is installed in the Microsoft Windows Control Panel. The ODBCManager allows you to set up and configure ODBC data sources. In the ODBCManager, you can set up and configure the following three types of DSNs:
The User DSN is a data source that is user specific. A User DSN is storedlocally but is available only to the user who creates it. User DSNs are notused by Microsoft Query 97. If you use Microsoft Jet, ODBC, or StructuredQuery Language (SQL) commands and bypass Microsoft Query, User DSNs arerequired. User DSNs are stored in the Windows registry under the followingkey:
HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources
Unlike a User DSN, a System DSN is not user specific. A System DSN isstored locally and is not dedicated to a particular user. Any user who logson to a computer that has permission to access the data source can use aSystem DSN. Some programs, such as Microsoft SQL Server or MicrosoftInternet Information Server (IIS), require a System DSN. This DSN must becreated on the server where the program is located. System DSNs are storedin the Windows registry under the following key:
HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources
The File DSN is created locally and may be shared with other users. TheFile DSN is file based, which means that the .dsn file contains all theinformation required to connect to the data source. Note that you mustinstall the ODBC driver locally to use a File DSN. Microsoft Query usesFile DSNs but Microsoft Jet and ODBC do not use File DSNs.
The File DSNs are stored by default in the Program Files\CommonFiles\Odbc\Data Sources folder. File DSNs are not stored in the Windowsregistry. The .dsn file is a text file that you can view in any texteditor, 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 aredisplayed. 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, use the following steps:
- In a text editor, such a Notepad, type the following two lines in a new document
[ODBC] where <MySysDSN> is the name of an existing System DSN that you installed in the ODBC icon in the Control Panel.
- Click Save on the File menu and type a name that includes a .dsn file name extension for the File DSN; for example, "DBase4.dsn" is a valid name. Include the quotation marks to ensure that the .dsn file name extension is added correctly.
You can also share a File DSN with other users. To do this, share thefolder in which the .dsn file is stored using the following steps:
- Under Microsoft Windows 95 and Microsoft Windows NT 4.0, right-click the Start menu and click Explore.
- Open the folder that contains the .dsn files. By default, this is the Program Files\Common Files\Odbc\Data Sources folder.
- Right-click the folder and click Sharing. On the Sharing tab, click Shared As and type the name to use for the folder in the Share Name box. Click OK.
Note: Each user must install the appropriate ODBC driver (the driver thatthe File DSN refers to) on the computer in order for the File DSN tofunction properly.
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 usean existing User or System DSN to retrieve data from a database and storethe data in a worksheet. The sample DSN that is used in this macro isMyDSN. It references the Microsoft Access 97 sample database Northwind.mdbin the Program Files\Microsoft Office\Office\Samples folder. You can useMyDSN as a User or System DSN but you cannot use it as a File DSN.
Sub Get_Data() 'Define SQL query string to get the CategoryName field from 'the Category table. sqlstring = "SELECT CategoryName FROM Categories" 'Define connection string and reference File DSN. connstring = "ODBC;DSN=MyDSN" 'Create QueryTable in worksheet beginning with cell C1. With ActiveSheet.QueryTables.Add(Connection:=connstring, _ Destination:=Range("C1"), Sql:=sqlstring) .Refresh End With End Sub
For more information about retrieving data, click the Office Assistant,type data sources
, click Search, and then click to view "Ways to retrievedata from an external database."
NOTE: If the Assistant is hidden, click the Office Assistant button on theStandard toolbar. If Microsoft Excel Help is not installed on yourcomputer, please see the following article in the Microsoft Knowledge Base:
Office: How to Add/Remove a Single Office Program or Component