XL: How to Use File DSNs and DSN-less Connections (165866)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
  • Microsoft Query
This article was previously published under Q165866

SUMMARY

When you use a connection string that does not use a data source name (a DSN-less connection string) to connect to an external data source in Microsoft Excel, information that is required to connect to the external data source is stored in the file structure of the workbook instead of in the data source name. Microsoft Query 97 and later, and Microsoft Excel 97 and later use this kind of DSN-less connection; earlier versions of Microsoft Query and Microsoft Excel do not.

MORE INFORMATION

Why Are DSN-less Connections Important?

In earlier versions of Microsoft Excel, retrieving external data requires that you create a data source. The data source name and other information that is required to connect to the data are stored in hidden names in the worksheet. Therefore, when a workbook that contains references to external data is shared among multiple users, the data source name that is referenced in the connection string must exist on each user's computer. Otherwise, you may receive an error message when you update or edit the external data.

In Microsoft Excel 97 and later, once the data is returned to a worksheet, the Structured Query Language (SQL) statement and driver information that is generated from the initial data source (not the data source name) is stored with the worksheet in the file structure of the workbook. These DSN-less connections resolve the issue of maintaining data sources on several computers. All of the necessary information is stored in the workbook itself. However, a copy of the appropriate Open Database Connectivity (ODBC) driver must be installed on each computer for the queries to function.

What Does a DSN-less Connection String Look Like?

The following is an example of a DSN-less connection string: 
   DBQ=C:\TEST\QUERY FILES;DefaultDir=C:\TEST\QUERY FILES; _
   Deleted=1;Driver={Microsoft dBase Driver (*.dbf)}; _
   DriverId=277;FIL=dBase IV;PageTimeout=600;Statistics=0
Note that there are no references to the DSN keyword in the text.

More About Data Source Files

Microsoft Query version 2.0 stores data source information in the registry. Microsoft Query 97 and later do not use the registry to store data source information. Instead, when you create a new data source, the connection information is stored in a text file with a .dsn file name extension. This file is also known as a File DSN.

Each File DSN contains an ODBC section and an optional Microsoft Office section that may contain information, such as the default table, password, and user ID for a given data source. The following is the default folder that is used when you save a File DSNX 
   C:\Program Files\Common Files\ODBC\Data Sources
The following is the default folder that is used when you save a query: 
   C:\Program Files\Microsoft Office\Queries
You can specify which folders are included in the search for File DSNs, including network locations.

The following is an example File DSN that uses the Microsoft Access 7.0 Database driver (an ODBC driver included with Microsoft Office 97): 
   [ODBC]
   DSN=MS Access 7.0 Database
When you create a File DSN that references a file that is located on the network and you map the network drive to a specific drive letter, that drive letter is specified in the File DSN. This behavior may cause problems if you want to share the File DSN with multiple users across the network and you want to maintain the File DSN on a server where all users can access it. To work around this problem, use either of the following methods.

Method 1

Whenever possible, use a universal naming convention (UNC) reference instead of a mapped drive.

Method 2

Open the File DSN in Notepad and modify the references to the drive letter to use a UNC reference. Consider the following example: 
   [ODBC]
   DRIVER=Microsoft Excel Driver (*.xls)
   UID=admin
   UserCommitSync=Yes
   Threads=3
   SafeTransactions=0
   ReadOnly=1
   PageTimeout=5
   MaxScanRows=8
   MaxBufferSize=512
   ImplicitCommitSync=Yes
   FIL=excel 5.0
   DriverId=790
   DefaultDir=<drive letter>:\ 
   DBQ=<drive letter>:\<source filename>
In this example, <drive letter> is the mapped drive and <source filename> is the source data file.

Change the last two lines to the following 
   DefaultDir=\\<server name>\<share>
   DBQ=\\<server name>\<share>\<source filename>
Where <server name> is the network server, <share> is the share on the network server, and <source filename> is the source data file.

After you modify the file, all users can successfully use the File DSN.

For more information about the driver information in the File DSN, please see the Help file that is specific to the ODBC driver you are using.

You can also use the 32-bit ODBC Control Panel icon to create a File DSN. To do this, follow these steps:

  1. On the Start menu, point to Settings, and click Control Panel. Then, double-click 32bit ODBC.
  2. Click the File DSN tab.
  3. Click Add.
  4. In the Create New Data Source dialog box, click the driver for which you want to create the data source. Click Next.
  5. Enter the full path and file name for your new data source (for example, C:\Program Files\Common Files\ODBC\Data Sources\Test.dsn). Then, click Next.
  6. Click Finish.

    The ODBC Setup dialog box for the ODBC driver you selected in step 4 appears.
  7. In the dialog box, enter the appropriate information.

    NOTE: If you do not want a specific drive letter to be included in the File DSN, enter the UNC path in the Database Name box in the Select Database dialog box.
  8. Click OK.
  9. Click OK to close the ODBC Data Source Administrator dialog box.
The File DSN is available for use by Microsoft Query.

Microsoft Query Information in the Registry

Microsoft Query automatically registers itself if the path value for Query is missing in the registry key, or if path value specifies a folder that that does not contain the file. You can reset the location for Microsoft Query in the registry by starting Microsoft Query. In Microsoft Windows 95 and later, you can locate Microsoft Query by looking in the following registry key: 
   HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\MSQuery
When you install Microsoft Office 97 or later, the Setup program creates a registry key that points to the default location for DSN files. This location is stored in the following registry key: 
   HKEY_LOCAL_MACHINE\Software\ODBC\odbc.ini\ODBC File DSN\DefaultDSNDir
You can specify an alternative location for the DSN files by adding the following registry key. 
   HKEY_CURRENT_USER\Software\ODBC\odbc.ini\ODBC File DSN\DefaultDSNDir
After you create this key, the key under HKEY_LOCAL_MACHINE is ignored.

REFERENCES

For more information about retrieving data, click the Office Assistant, type data sources, click Search, and then click to view "Ways to retrieve data from an external database."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, click the article number below to view the article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component


For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

159557 XL97: Using System, User, and File Data Sources

Modification Type:MinorLast Reviewed:10/10/2006
Keywords:kbhowto kbProgramming kbualink97 KB165866
©2004 Microsoft Corporation. All rights reserved.