About OLE DB and Data Links

Microsoft Access 2002 provides a native OLE DB user and programming interface that you can use to access a Microsoft Access database, a Microsoft SQL Server 2000, 7.0, or 6.5 database, and other data sources.

Microsoft Access and the OLE DB architecture

OLE DB is a component database architecture that implements efficient network and internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets. In the OLE DB architecture, the application that accesses the data is called a data consumer (for example, Microsoft Access or a Microsoft Visual Basic program), and the program that enables native access to the data is called a database provider (for example, Microsoft OLE DB Provider for SQL Server or Microsoft Jet 4.0 OLE DB Provider).

Defining connection information

A data link is the connection information that a data consumer uses to access a data source through the OLE DB provider of that data source. In the Microsoft Access user interface, you use the Data Link Properties dialog box to define a data link for an OLE DB provider in the following ways:

• To connect to a Microsoft SQL Server database when you create a Microsoft Access project by using the New command on the File menu or by using the Database Wizard.
• To redefine the connection information for an Access project by using the Connection command on the File menu. You might do this to easily switch from a test to a production database or from a primary to a secondary server.
• To redefine the connection information of an existing data access page in Page Design view by right-clicking on the field list and clicking Connection. When you first create a data access page, Microsoft Access automatically defines the connection information for you by connecting to the currently open Access database or project.
• To connect a Web page that you opened from the Open dialog box (File menu, Open) from a null database window.
• To create a PivotTable list or modify the connection to a PivotTable list.

Each OLE DB provider defines specific connection information. For example, Microsoft OLE DB Provider for SQL Server requires a server name, server location, and a user name, and you may want to define additional information, such as a password or whether you want to use Microsoft Windows NT integrated security.

In general, to define connection information in the Data Link Properties dialog box:

• Click the Provider tab, select the OLE DB provider, and then click Next to display the Connection tab to enter specific connection information for that OLE DB provider. Note that depending on how you get to the Data Link Properties dialog box, the Provider tab may not be available. Access automatically defines the provider as the currently open Access database or SQL Server database connected to the open Access project.
• Click the Advanced tab to provide additional information, such as network settings and access permissions.
• Click the All tab to define initialization properties for that OLE DB provider.

  Note   If the Access project on the client computer is connected to a SQL Server 6.5 database running Microsoft Data Access Components (MDAC) 2.5, you must delete the generated entry for the Network Library property in the All tab.

The Data Link Properties dialog box Help file (msdasc.hlp) provides additional information on using the dialog box and defining connection information for each OLE DB provider.

Creating and accessing Microsoft Data Link files

Although you typically connect to an OLE DB provider through Microsoft Access using the Data Link Properties dialog box, you may want to define connection information for an OLE DB provider in a Microsoft Data Link file (.udl) to use in a Visual Basic program and to organize and manage connection information on your computer.

To create connection information in this manner, open Microsoft Windows Explorer, choose Microsoft Data Link from the New menu, create a Microsoft Data Link file, select the file name, right-click on the file name, and then choose Properties.

OLE DB provider installation

Microsoft Jet 4.0 OLE DB Provider and Microsoft OLE DB Provider for SQL Server are installed automatically when you install Microsoft Office XP. Additional OLE DB providers are also installed, including OLE DB Provider for ODBC Drivers and OLE DB Provider for Oracle. To see a complete list of OLE DB providers installed on your computer, display the Data Link Properties dialog box from a Data Link file, and then click the Provider tab.

When you or other users display a page using Microsoft Internet Explorer 5 or later and you have a Microsoft Office XP license on your computer, if necessary, the browser automatically downloads the necessary OLE DB providers and other related files by installing Microsoft Office Web Components.

Programming OLE DB

You can also programmatically access data through an OLE DB provider in Visual Basic for Applications in Microsoft ActiveX Data Objects (ADO). This is accomplished by defining a connection string in the ConnectionString property of the Open method of the Connection object to pass the connection information to the OLE DB provider. For more information, see The Microsoft Office XP Developer's Guide.