When you install Microsoft Office, an ODBC icon that
represents the ODBC Manager is installed in Control Panel. The ODBC Manager
allows you to set up and configure ODBC data sources. In the ODBC Manager, you
can set up and configure the following three types of DSNs:
- User DSN
- System DSN
- File DSN
User DSN
The User DSN is a data source that is user-specific. A User DSN
is stored locally but is available only to the user who creates it. User DSNs
are not used by Microsoft Query. If you use Microsoft Jet, ODBC, or Structured
Query Language (SQL) commands and bypass Microsoft Query, User DSNs are
required. User DSNs are stored in the Windows registry under the following
key:
HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources
System DSN
Unlike a User DSN, a System DSN is not user-specific. A System
DSN is stored locally and is not dedicated to a particular user. Any user who
logs on to a computer that has permission to access the data source can use a
System DSN. Some programs, such as Microsoft SQL Server or Microsoft Internet
Information Server (IIS), require a System DSN. This DSN must be created on the
server where the program is located. System DSNs are stored in the Windows
registry under the following key:
HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources
File DSN
The File DSN is created locally and can be shared with other
users. The File DSN is file-based, which means that the .dsn file contains all
the information required to connect to the data source. Note that you must
install the ODBC driver locally to use a File DSN. Microsoft Query uses File
DSNs, but Microsoft Jet and ODBC do not use File DSNs.
The File DSNs
are stored by default in the Program Files\Common Files\Odbc\Data Sources
folder. File DSNs are not stored in the Windows registry. The .dsn file is a
text file that you can view in any text editor, such as Microsoft
Notepad.
NOTE: When you connect to an existing data source using Microsoft
Query, only the available File DSNs that are stored on that computer are
displayed. Microsoft Query does not display User or System DSNs. However, you
can create a File DSN that points to a System DSN.
To create a File
DSN that points to a System DSN, follow these steps:
- In a text editor, such as Microsoft Notepad, type the
following two lines in a new document
[ODBC]DSN=MySysDSN
where "MySysDSN" is the name of an existing System DSN that you
installed in the ODBC tool in Control Panel. - Click Save on the File menu and type a name that includes a .dsn file name extension for
the File DSN; for example, the following is a valid name:
"DBase4.dsn"
Include the quotation marks to ensure that the .dsn file name
extension is added correctly.
You can also share a File DSN with other users. To do this,
share the folder in which the .dsn file is stored using the following steps:
- Right-click Start, and then click Explore.
- Open the folder that contains the .dsn files. By default,
this is the Program Files\Common Files\Odbc\Data Sources folder.
- Right-click the folder, and then click Sharing. On the Sharing tab, click Shared As, type the name to use for the folder in the Share Name box, and then click OK.
NOTE: Each user must install the appropriate ODBC driver (the driver
that the File DSN refers to) on the computer for the File DSN to function
properly.
Sample Macro to Return External Data to Microsoft Excel
Microsoft provides programming examples for illustration only, without warranty either
expressed or implied, including, but not limited to, the implied warranties of
merchantability and/or fitness for a particular purpose. This article assumes
that you are familiar with the programming language being demonstrated and the
tools used to create and debug procedures. Microsoft support professionals can
help explain the functionality of a particular procedure, but they will not
modify these examples to provide added functionality or construct procedures to
meet your specific needs.
If you have limited programming experience, you may
want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:
Microsoft Certified
Partners -
https://partner.microsoft.com/global/30000104Microsoft Advisory Services -
http://support.microsoft.com/gp/advisoryservice
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS The following Microsoft Excel Visual Basic for
Applications macro can use an existing User or System DSN to retrieve data from
a database and store the data in a worksheet. The sample DSN that is used in
this macro is MyDSN. It references the Microsoft Access sample database
Northwind.mdb in the Program Files\Microsoft Office\Office\Samples folder. You
can use MyDSN as a User or System DSN, but you cannot use it as a File DSN.
Sub Get_Data()
'Define SQL query string to get the CategoryName field from
'the Category table.
sqlstring = "SELECT CategoryName FROM Categories"
'Define connection string and reference File DSN.
connstring = "ODBC;DSN=MyDSN"
'Create QueryTable in worksheet beginning with cell C1.
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("C1"), Sql:=sqlstring)
.Refresh
End With
End Sub