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

ID: Q165866

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Query 97 for Windows

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 97, 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 Microsoft Excel 97 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, 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 does 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 DSN:

   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, use the following 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 97 Information in the Registry

Microsoft Query 97 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 97 in the registry by starting Microsoft Query 97. In Microsoft Windows 95, you can locate Microsoft Query 97 by looking in the following registry key:

   HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\MSQuery

When you install Microsoft Office 97, 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 Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office Program or
               Component

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q159557
   TITLE     : XL97: Using System, User, and File Data Sources

Additional query words: XL97 xlvbmigrate

Keywords          : kbprg kbualink97 kbdta KbVBA 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: June 30, 1999