About ODBC data sources

An ODBC data source (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.)—for example, a database and the server on which it resides—is accessed through an Open Database Connectivity (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) (ODBC) driver.

ShowWhat is a data source?

A data source is a source of data and the connection information needed to access that data. Examples of data sources are Microsoft Access, Microsoft SQL Server, Oracle RDBMS, a spreadsheet, and a text file. Examples of connection information include server location, database name, logon ID, password, and various ODBC driver options that describe how to connect to the data source.

In the ODBC architecture, an application (such as Access or a Microsoft Visual Basic program) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (for example, Microsoft SQL ODBC driver) to connect to a data source (in this case, a Microsoft SQL Server database (SQL database: A database that is based on Structured Query Language (SQL).)). In Access, you use ODBC data sources to connect to data sources external to Access that do not have built-in drivers.

To connect to these data sources, you must do the following:

  • Install the appropriate ODBC driver 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 Microsoft Windows registry or a DSN file, or a connect string in Visual Basic code to pass the connection information directly to the ODBC Driver Manager.

ShowMachine 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 only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer and system-wide services, such as Microsoft Windows services. 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 it cannot be copied by a remote user to another computer.

ShowFile data sources

File data sources (also called DSN files) store connection information in a text file, not 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 with the correct ODBC driver so that your application can rely on consistent and accurate connection information to all the computers 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.

ShowConnect strings

If you are a programmer, you can define a formatted connect string in your Microsoft Visual Basic code that specifies the connection information. Using a connect string avoids defining a machine or file DSN and passes the connection information directly to the ODBC Driver Manager. This is useful, for example, when you want to avoid requiring system administrators or users to first create a DSN, or to simplify the installation of your application. To maintain security for the connection string information in your code, help protect your code by using a MDE file or protecting your code with a password.

Security Note    

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

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.

Applies to:
Access 2003