Linking to external data sources in an Access project

Applies to
Microsoft Office Access 2003
Microsoft Access 2002

The Link Table Wizard is a new feature of Access 2002. If your Access project is connected to a Microsoft SQL Server™ version 7.0 or later database, or to a SQL Server 2000 Desktop Engine running on your local computer, you can easily link to various OLE DB data sources, including:

  • SQL Server tables (does not require a local SQL Server installation)
  • Microsoft Access databases (.mdb — all versions)
  • Microsoft Access projects (.adp — all versions)
  • Microsoft Excel (version 3.0 and later)
  • Text files
  • HTML files
  • dBASE databases (versions 3, 4, and 5)
  • Paradox databases (versions 3.x, 4.x, 5.x, and 7.x)

To start the Link Table Wizard, point to Get External Data on the File menu, and then click Link Tables. The wizard walks you through the steps required to create the link table. When you are finished, Access creates a database view with a default four-part name in the form <server>_<database>_<owner>_<table>; for example, ACCSQL2000_pubs_dbo_authors (SQL Server) or Northwind___Suppliers (Access database).

Two types of link tables

By using the Link Table Wizard, you can define either a linked server or a Transact SQL function, both of which manage the link to the external data source and create the OLE DB connection string.

Linked server     Access creates a linked server in the local SQL Server database or database engine. A linked server is a definition of an OLE DB data source that permanently stores the connection string in your local SQL Server database or database engine. In general, a linked server provides the most functionality, including the ability to update data (using ADO, a stored procedure, or a user-defined function) if the OLE DB data source allows. Additionally, if you are linking to another SQL Server database, SQL Server optimizes join operations in most cases. However, you must be a member of either the sysadmin or setupadmin role to create a linked server.

Example: Link table connected to an external SQL Server database defined as a linked server

SELECT *
FROM [PUBSERVER].pubs.dbo.jobs jobs_1

Example: Link table connected to an external Access database defined as a linked server

SELECT *
FROM Northwind...Suppliers Suppliers_1

Transact SQL function     Access creates an OLE DB data source that uses Transact SQL rowset functions to create an ad hoc, read-only connection to the external data source. Access uses either the OPENDATASOURCE (SQL Server 2000) or OPENROWSET (SQL Server 7.0) rowset function.

Example: Link table connected to an external SQL Server database defined with the OPENDATASOURCE function

SELECT *
FROM OPENDATASOURCE ('SQLOLEDB',
'PWD=;Persist Security Info=True;UID=sa;Initial Catalog=pubs;SERVER=PUBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYCOMPUTER;Use Encryption for Data=False;Tag with column collation when possible=False').pubs.dbo.[jobs] Rowset_1

Example: Link table connected to an external Access database defined with the OPENDATASOURCE function

SELECT *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Password="";User ID=Admin;Data Source=\\NWIND\Databases\Northwind.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don''t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False')...[Suppliers] Rowset_1

For more information about linked servers, OPENDATASOURCE, and OPENROWSET, see Microsoft SQL Server Books Online.

Working with a link table

Although you can rename the link table view in the Database window, it's a good idea to use a naming scheme to remind you that the view is a link table. For example, you could rename "ACCSQL2000_pubs_dbo_authors" to "lnk_authors". However, Microsoft recommends not changing the link table in the Query Designer. If you want to work with the data by selecting columns, adding filters, creating calculated fields, or joining data, create a new query that uses the link table view by doing one of the following:

  • Add the link table as a table source in the FROM clause of an SQL SELECT statement.
  • Create the new query in the Query Designer. (On the Query menu, click Add Table, click Views, and then double-click the link table view.)

Then make your changes in the new query by using the Query Designer or SQL Text Editor.

Modify the link table connection string

There are times, however, where you may need to modify the connection string of a link table. For example, you may want to change one of the following:

  • The location of the data source, because it has moved.
  • The connection timeout, because your queries sometime take too long to execute on a congested network.
  • Extended properties, because you want to enable or disable column headers in HTML files or spreadsheets.

For more information about using connection strings, see the following:

  • The Microsoft Data Access Components (MDAC) SDK overview, on the Microsoft Developer Network (MSDN)
  • ADO provider properties and settings, on MSDN
  • "Using ADO Connection Strings" in Chapter 14 of the Microsoft Office 2000/Visual Basic Programmer's Guide
  • Setting connection string parameters in DAO, on MSDN

Modify the connection string of a link table based on a linked server

The steps you need to take to modify a connection string for a link table based on a linked server depend on what type of SQL Server installation you have on your local computer.

Procedure for a SQL Server 2000 local installation

If you have a local installation of SQL Server 2000, you can use Microsoft SQL Server Enterprise Manager to change the connection string of the linked server.

  1. Start Enterprise Manager from the Microsoft Windows® Start menu.
  2. Expand the Console tree until you find the linked server, located in the Security folder of the SQL Server.
  3. Right-click the linked server and click Properties.
  4. Click the General tab, and then change the connection string in the Provider String box.

Procedure for a SQL Server 2000 Database Engine installation

If you are using the Microsoft SQL Server 2000 Database Engine, there is no simple way to modify a linked server once you create it, because the SQL Server Enterprise Manager is not available. You need to delete the linked table and re-create it, providing the new connection string information through the wizard.

  1. To delete the link table, right-click the link table in the Database window, and then click Delete.
  2. To start the Link Table Wizard, open the File menu, point to Get External Data, and then click Link Tables.
  3. When the Link Table Wizard displays the Select Data Source dialog box, follow these steps:
    • Double-click +Connect to New Data Source.odc.
    • In the Data Connection Wizard, click Other/Advanced.
    • In the Data Link Properties dialog box, double-click the provider you want, and make additional changes to the connection string if necessary.
    • Complete the Link Table Wizard.

Modify the connection string of a link table based on Transact SQL functions

Before changing the connection string, you must make sure that Microsoft Distributed Transaction Coordinator (MSDTC) is running.

First, make sure MSDTC is running on the local computer and the remote server

MSDTC must be started on both computers because Transaction SQL requires this SQL Server service to be running in order to execute Transact SQL remotely.

  1. To open the SQL Server Service Manager window, double-click the MSSQLServer icon in the status area of the taskbar.
  2. In the Services list, select Distributed Transaction Coordinator, and then click Start/Continue.

    If you want MSDTC to automatically start each time you start Windows, select the Auto-start service when OS starts check box.

If you attempt to modify the SQL statement created by the Link Table Wizard without starting MSDTC on both computers, you see the following message:

ADO Error: MS DTC on server '<server name>' is unavailable.

Then, edit the SQL statement of the link table database view

  1. Open the link table in Design view.
  2. If the SQL pane is not displayed, point to Show Panes on the View menu, and then click SQL.
  3. Edit the connection string information as shown in the following example (changes are in bold italics):

Example: Add a connection timeout for a SQL Server OLE DB data source

SELECT *
FROM OPENDATASOURCE ('SQLOLEDB',
'PWD=;Persist Security Info=True;UID=sa;Initial Catalog=pubs;SERVER=PUBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYCOMPUTER;Use Encryption for Data=False;Tag with column collation when possible=False').pubs.dbo.[jobs] Rowset_1

Example: Change the file location in a Microsoft Jet OLE DB data source

SELECT *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Password="";User ID=Admin;Data Source=Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don''t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False')...[Suppliers] Rowset_1

Limit data retrieved at the server

If your link table retrieves a large amount of data, you probably want to limit the data retrieved at the server. Creating a new query with a WHERE clause or by using a select column list to limit the data of a link table won't limit the data on the server — only on the client. To limit the data retrieved on the server, you must use an OPENQUERY rowset function or execute a pass-through query on the remote server.

Example: Create a stored procedure that uses an OPENQUERY rowset function based on a linked server

First, get the server name from the link table view as shown in the following example (linked server name is in bold italics).

SELECT *
FROM .pubs.dbo.jobs jobs_1

Then, create and run a stored procedure that contains the pass-through query, using the linked server name and the SQL query you want to use to limit the data at the server. For example:

ALTER PROCEDURE [PUBSERVER_pubs_dbo_jobs_Passthru]
AS SELECT * FROM OPENQUERY
([PUBSERVER], 'SELECT job_id, job_desc, max_lvl FROM pubs.dbo.jobs WHERE (max_lvl > 200)' )
RETURN

Example: Modify a link table based on a Transact SQL function by adding a pass-through query

You can use a pass-through query with a link table based on an OPENROWSET function, as shown in the following example (changes are in bold italics).

Before changes:

SELECT *
FROM OPENROWSET('SQLOLEDB',
'PWD=;Persist Security Info=True;UID=sa;Initial Catalog=pubs;SERVER=PUBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYCOMPUTER;Use Encryption for Data=False;Tag with column collation when possible=False',
) Rowset_1

After changes:

SELECT *
FROM OPENROWSET('SQLOLEDB',
'PWD=;Persist Security Info=True;UID=sa;Initial Catalog=pubs;SERVER=PUBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYCOMPUTER;Use Encryption for Data=False;Tag with column collation when possible=False',
) Rowset_1

You cannot use a pass-through query with OPENDATASOURCE. You must convert the OPENDATASOURCE rowset function to an OPENROWSET function, as shown in the following example (changes are in bold italics).

Before changes:

SELECT *
FROM ('SQLOLEDB',
'PWD=;Connect Timeout=25;Persist Security Info=True;UID=sa;Initial Catalog=pubs;SERVER=PUBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYCOMPUTER;Use Encryption for Data=False;Tag with column collation when possible=False') Rowset_1

After changes:

SELECT *
FROM ('SQLOLEDB',
'PWD=;Persist Security Info=True;UID=sa;Initial Catalog=pubs;SERVER=PUBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYCOMPUTER;Use Encryption for Data=False;Tag with column collation when possible=False',
) Rowset_1

For more information about OPENQUERY and pass-through queries, see Microsoft SQL Server Books Online.

Working with spreadsheets, text files, and HTML files

If you are linking to a spreadsheet, text file, or HTML file, Microsoft recommends creating a link table that uses Transact SQL functions, because the performance is usually better than using a linked server. You should also strongly consider importing the data source on a regular basis, especially with large text and HTML files. Importing the data source will also improve performance, especially if the table you create has indexes.

However, if you must link to a spreadsheet, text file, or HTML file, be aware of several Link Table Wizard limitations:

  • When you link to a spreadsheet, text file, or HTML file, the default behavior of the Link Table Wizard assumes that the first row contains field names, not data, and uses the first row of data to make the column headers. If possible, make sure that the spreadsheet, text file, or HTML file has column headers before you set up the links.
  • The default behavior of the Link Table Wizard always uses the comma character (,) as a list separator with delimited text files. You may have changed the list separator on your computer by changing the Regional Settings in Windows Control Panel. For example, the German list separator is a semicolon character (;). If possible, convert the list separator to a comma character (,) in the text file before linking.
  • You cannot link fixed-width text files to SQL Server by using the default behavior of the Link Table Wizard. If possible, convert your text file to a delimited text file before linking.

Change connection string information to disable column headers in a spreadsheet or HTML file

For both spreadsheets and HTML files, you can change the Extended Properties keyword of the connection string.

Example: Specify that the first row of a spreadsheet is data

Change the Extended Properties keyword HDR=YES to HDR=NO as shown in the following example (changes are in bold italics).

SELECT *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Password="";User ID=Admin;Data Source=\\NWIND\ExcelSpreadsheets\Products.xls;Mode=Share Deny Write;Extended Properties=";";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don''t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False')...[Products] Rowset_1

Example: Specify that the first row of an HTML file is data

Add the Extended Properties keyword HDR=NO as shown in the following example (changes are in bold italics).

Before changes:

SELECT *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\NWIND\HTMLFiles\Products.html";User Id=Admin;Password=;Extended properties=')...[Products] Rowset_1

After changes:

SELECT *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\NWIND\HTMLFiles\Products.html";User Id=Admin;Password=;Extended properties= ')...[Products] Rowset_1

Use Schema.ini files to work around text file limitations

If you cannot make changes to a text file to work around text file limitations, you can use a Schema.ini file instead. A Schema.ini file is a text file containing entries that override default text driver settings in the Microsoft Windows registry. To use a Schema.ini file, you must:

  • Store the Schema.ini file in the same folder as the text file you are linking to.
  • Name the file Schema.ini.
  • On the first line of the Schema.ini file, type the name of the text file you are linking to, surrounded by brackets.

Example: Specify that the first row is data

[Products.txt]
ColNameHeader=False

Example: Specify a semicolon character (;) as the delimiter

[Categories.txt]
Format=Delimited(;)

Example: Specify a fixed-width file and the column widths

[Shippers.txt]
ColNameHeader=False
Format=FixedLength
Col1=ShipperID Text Width 11
Col2=CompanyName Text Width 40
Col3=Phone Text Width 24

For more information about using Schema.ini files, see the article, Schema.ini File, on the Microsoft Developer Network (MSDN).

For more information about programming in Access, visit the Office Developer Center on the Microsoft Developer Network (MSDN).