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

This article was previously published under Q165866
This article has been archived. It is offered "as is" and will no longer be updated.
When you use a connection string that does not use a data source name (aDSN-less connection string) to connect to an external data source inMicrosoft Excel, information that is required to connect to the externaldata source is stored in the file structure of the workbook instead of inthe 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.

Why Are DSN-less Connections Important?

In earlier versions of Microsoft Excel, retrieving external data requiresthat you create a data source. The data source name and other informationthat is required to connect to the data are stored in hidden names in theworksheet. Therefore, when a workbook that contains references to externaldata is shared among multiple users, the data source name that isreferenced in the connection string must exist on each user's computer.Otherwise, you may receive an error message when you update or edit theexternal 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 sourceinformation. Instead, when you create a new data source, the connectioninformation is stored in a text file with a .dsn file name extension. Thisfile is also known as a File DSN.

Each File DSN contains an ODBC section and an optional Microsoft Officesection that may contain information, such as the default table, password,and user ID for a given data source. The following is the default folderthat 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.0Database 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 thenetwork and you map the network drive to a specific drive letter, thatdrive letter is specified in the File DSN. This behavior may cause problemsif you want to share the File DSN with multiple users across the networkand you want to maintain the File DSN on a server where all users canaccess it. To work around this problem, use either of the followingmethods.

Method 1

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

Method 2

Open the File DSN in Notepad and modify the references to the drive letterto 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, pleasesee 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 forQuery is missing in the registry key, or if path value specifies a folderthat that does not contain the file. You can reset the location forMicrosoft Query in the registry by starting Microsoft Query. InMicrosoft 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 thefollowing 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.
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
XL2000 XL97 xlvbmigrate

Article ID: 165866 - Last Review: 12/04/2015 16:38:58 - Revision: 2.3

Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Query 2000

  • kbnosurvey kbarchive kbhowto kbprogramming kbualink97 KB165866