About OLE DB and Data Links

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).

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 (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) 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 (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) in Page Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.) by right-clicking on the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) 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 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.

Microsoft Jet 4.0 OLE DB Provider and Microsoft OLE DB Provider for SQL Server are installed automatically when you install Microsoft Office 2003. 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.01 with Service Pack 2 (SP2) or later and you have a Microsoft Office 2003 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.

You can also programmatically access data through an OLE DB provider in Visual Basic for Applications in Microsoft ActiveX Data Objects (ADO) (ActiveX Data Objects (ADO): A data access interface that communicates with OLE DB-compliant data sources to connect to, retrieve, manipulate, and update data.). 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.