Link tables in an Access project by using the Link Table Wizard (ADP)

 Note   The information in this topic applies only to a Microsoft Access project (.adp).

In a Microsoft Access project (.adp) (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.) connected to a Microsoft SQL Server 7.0 or later database, you can use the Link Table Wizard to link (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) to one or more tables in another SQL database, an Access database, or other OLE DB and ODBC data sources (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.).

 Note    With a local installation of Microsoft SQL Server or the Microsoft SQL Server 2000 Desktop Engine, you can use the Link Table Wizard to link to data in the following applications: Microsoft Access databases .mdb (all versions), Microsoft Access projects .adp (all versions), dBASE (versions 3, 4 and 5), Paradox (versions 3.x, 4.x, 5.x, and 7.x), Microsoft Excel (version 3.0 and above), delimited text files (using the system delimiter as set in Regional Options in the Windows Control Panel), and HTML. Without a local SQL Server installation, you can only link to other SQL Server tables.

  1. Open an Access project, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open Access project.
  2. On the File menu, point to Get External Data, and then click Link Tables.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. In the first screen of the Link Table Wizard, select the type of link you want to create, either Linked SQL or Transact SQL, and then click Next.

If you select Linked SQL, Access creates a linked server which provides the most functionality, including the ability to update data if the OLE DB data source allows, and permanently stores the data source connection information in the Microsoft SQL Server database (Microsoft SQL Server database: A database that consists of tables, views, indexes, stored procedures, functions, and triggers. You can connect your database to SQL Server data by using ODBC or by creating an Access project (*.adp) file.). Also, if the SQL Server database you are linking to is on another SQL Server, in most cases, SQL Server will optimize join operations. However, you must be a member of either the sysadmin or setupadmin roles.

If you select Transact SQL, Access creates an OLE DB data source that uses Transact SQL functions to create an ad hoc read-only connection to the external data source. Access uses either the the OPENDATASOURCE (SQL Server 2000) or OPENROWSET (SQL Server 7.0) Transact SQL functions. If you are linking to spreadsheet, text, or HTML files, consider using this option because the performance is better than creating a linked server.

  1. In the Select Data Source dialog box, do one of the following:

ShowLink to an existing data source

  1. Select the data source.
  2. Click Open.

ShowCreate a new data source

  1. Click New Source.
  2. In the Data Connection Wizard dialog box, click each table that you want to link.
  3. Follow the directions in the wizard dialog box.

Access creates a connection file with the information you specified.

  1. In the Select Data Source dialog box, click Open.
  1. In the second screen of Link Table Wizard, select the tables you want to link to, and then click Next.
  2. Click Finish to complete the wizard.

The wizard creates a view (view (object): In an Access project, a type of query that is a virtual table based on an SQL SELECT statement. For example, a view might contain only 3 out of 10 available columns in a join of two tables, in order to limit access to certain data.) of each linked table.

 
 
Applies to:
Access 2003