Note:Â This article has done its job, and will be retiring soon. To prevent "Page not found" woes, we're removing links we know about. If you've created links to this page, please remove them, and together we'll keep the web connected.
Data in your program can come from an external data source, such as a text file, a workbook, or a database. This external data source is connected to your program through a data connection, which is a set of information that describes how to locate, log in, and access the external data source. The following sections describe how external data connections work, how to share the connection information with other applications and users, and how to make data access more secure.
Understanding the basics of data connections
The main benefit of connecting to external data is that you can periodically analyze this data without repeatedly copying it. Repeatedly copying data is an operation that can be time-consuming and error-prone.
Connection information can be stored in a workbook or a connection file, such as in an Office Data Connection (ODC) file (.odc) or in a Data Source Name (DSN) file (.dsn).
To bring external data into your program, you need access to the data. If the external data source that you want to access is not on your local computer, you may need to contact the administrator of the database for a password, user permission, or other connection information. If the data source is a database, make sure that the database is not opened in exclusive mode. If the data source is a text file or a workbook, make sure that another user does not have the text file or workbook open for exclusive access.
Many data sources also require an ODBC driver or OLE DB provider to coordinate the flow of data among your program, the connection file, and the data source.
The following diagram summarizes the key points about data connections.
1. There are a variety of data sources that you can connect to: Microsoft SQL Server, Microsoft Access, Microsoft Excel, and text files.
2. Each data source has an associated ODBC driver or OLE DB provider.
3. A connection file defines all of the information that you need to access and retrieve data from a data source.
4. Connection information is copied from a connection file into your program.
Sharing connections
Connection files are particularly useful for sharing connections on a consistent basis, making connections more discoverable, helping to improve security, and facilitating data source administration. The best way to share connection files is to put them in a secure and trusted location, such as a network folder or SharePoint library, where users can read the file, but only designated users can modify it.
You can create Office Data Connection (ODC) files (.odc) by using Excel or by using the Data Connection Wizard to connect to new data sources. An .odc file uses custom HTML and XML tags to store the connection information. You can easily view or edit the contents of the file in Excel.
You can share connection files with other users to give them the same access that you have to an external data source. Other users don't have to set up a data source to open the connection file, but they may need to install the ODBC driver or OLE DB provider that is required to access the external data on their computers.
Understanding Microsoft Data Access Components
Microsoft Data Access Components (MDAC) 2.8 is included with Windows Server 2003 and Windows XP SP2 and later. With MDAC, you can connect to and use data from a wide variety of relational and nonrelational data sources. You can connect to many different data sources by using Open Database Connectivity (ODBC) drivers or OLE DB providers. Either can be built and shipped by Microsoft or developed by various third parties. When you install Microsoft Office, additional ODBC drivers and OLE DB providers are added to your computer.
To see a complete list of OLE DB providers that are installed on your computer, display the Data Link Properties dialog box from a Data Link file, and then click the Provider tab.
To see a complete list of ODBC drivers installed on your computer, display the ODBC Database Administrator dialog box, and then click the Drivers tab.
You can also use ODBC drivers and OLE DB providers from other manufacturers to get information from sources other than Microsoft data sources, including other types of ODBC and OLE DB databases. For information about installing these ODBC drivers or OLE DB providers, check the documentation for the database or contact your database vendor.
Using ODBC to connect to data sources
The following sections describe Open Database Connectivity (ODBC) in more detail.
The ODBC architecture
In the ODBC architecture, an application (such as your program) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (such as the Microsoft SQL ODBC driver) to connect to a data source (such as a Microsoft SQL Server database).
Defining connection information
To connect to ODBC data sources, do the following:
-
Ensure that the appropriate ODBC driver is installed on the computer that contains the data source.
-
Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the Windows registry or a .dsn file, or by using a connect string in Microsoft Visual Basic code to pass the connection information directly to the ODBC Driver Manager.
To define a data source, open Control Panel, click System and Security > ODBC data sources (32-bit) or ODBC data sources (64-bit).
For more information about the different options, click the Help button in each dialog box.
Machine data sources
Machine data sources store connection information in the Windows registry on a specific computer with a user-defined name. You can use machine data sources on only the computer that they are defined on. There are two types of machine data sources — user and system. User data sources can be used by only the current user and are visible to only that user. System data sources can be used by all users on a computer and are visible to all users on the computer. A machine data source is especially useful when you want to provide added security, because it helps ensure that only users who are logged on can view a machine data source, and a machine data source cannot be copied by a remote user to another computer.
File data sources
File data sources (also called DSN files) store connection information in a text file, not in the Windows registry, and are generally more flexible to use than machine data sources. For example, you can copy a file data source to any computer that has the correct ODBC driver, so that your application can rely on consistent and accurate connection information to all of the computers that it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.
A file data source can also be unshareable. An unshareable file data source resides on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.
Using OLE DB to connect to data sources
The following sections describe Object Linking and Embedding Database (OLE DB) in more detail.
The OLE DB architecture
In the OLE DB architecture, the application that accesses the data is called a data consumer (such as Publisher), and the program that enables native access to the data is called a database provider (such as Microsoft OLE DB Provider for SQL Server).
Defining connection information
A Universal Data Link file (.udl) contains the connection information that a data consumer uses to access a data source through the OLE DB provider of that data source. You can create the connection information by doing one of the following:
-
In the Data Connection Wizard, use the Data Link Properties dialog box to define a data link for an OLE DB provider.
-
Create a blank text file with a .udl file type, and then edit the file, which displays the Data Link Properties dialog box.
Making data access more secure
When you connect to an external data source or refresh the data, it's important to be aware of potential security issues and to know what you can do about those security issues. Use the following guidelines and best practices to help secure your data.
Storing data connections in a trusted location
A data connection file often contains one or more queries to a data source. By replacing this file, a user who has malicious intent can design a query to access confidential information and distribute it to other users or perform other harmful actions. Therefore, it is important to ensure the following:
-
The connection file was written by a reliable individual.
-
The connection file is secure and that it comes from a trusted location.
To help improve security, connections to external data may be unavailable on your computer. To connect to data when you open a workbook, you must activate data connections by using the Trust Center bar or by putting the workbook in a trusted location.
For more information, see Add, remove, or change a trusted location, Add, remove, or view a trusted publisher, and View my options and settings in the Trust Center.
Using credentials in a safe manner
Accessing an external data source usually requires credentials (such as a user name and a password) that are used to authenticate the user. Make sure that these credentials are provided to you in a safe and secure manner, and that you do not inadvertently reveal these credentials to others.
Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. An example of a strong password is Y6dh!et5. An example of a weak password is House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.
It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.
Avoid saving logon information when you connect to data sources. This information may be stored as plain text in the workbook and the connection file, and a malicious user can access the information to compromise the security of the data source.
When possible, use Windows Authentication (also referred to as a trusted connection), which uses a Windows user account to connect to SQL Server. When a user connects through a Windows user account, SQL Server uses information in the Windows operating system to validate the account name and password. Before you can use Windows Authentication, a server administrator must configure the SQL Server to use this mode of authentication. If Windows Authentication is not available, avoid saving users' logon information. It is more secure for users to enter their logon information each time that they log on.
See also
Use the Data Connection Wizard to import data into Publisher
