Link tables in an Access project by using the Link Table Wizard

In a Microsoft Access project (.adp) connected to a Microsoft SQL Server 7.0 or later database, you can use the Link Table Wizard to link to one or more tables in another SQL database, an Access database, or other OLE DB and ODBC data sources.

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 for the open Access project.
  2. On the File menu, point to Get External Data, and then click Link Tables.
  3. 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. 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.

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

    Link to an existing data source

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

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

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

    The wizard creates a view of each linked table.