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:
- On the Start menu, point to Settings, and click Control Panel. Then, double-click 32bit ODBC.
- Click the File DSN tab.
- Click Add.
- In the Create New Data Source dialog box, click the driver for which you want to create the data source. Click Next.
- 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.
- Click Finish.
The ODBC Setup dialog box for the ODBC driver you selected in step 4
appears.
- 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.
- Click OK.
- 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.