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

• 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

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

   C:\Program Files\Microsoft Office\Queries

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

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>

Change the last two lines to the following

   DefaultDir=\\<server name>\<share>
   DBQ=\\<server name>\<share>\<source filename>

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.

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

   HKEY_LOCAL_MACHINE\Software\ODBC\odbc.ini\ODBC File DSN\DefaultDSNDir

   HKEY_CURRENT_USER\Software\ODBC\odbc.ini\ODBC File DSN\DefaultDSNDir

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

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