About importing and linking data and database objects

Microsoft Access provides two choices for using data from an external data source. You can:

 Note    You cannot update or delete data that is linked to an Excel worksheet.

 Tip    Try Office 2010 In Access 2010, you can publish a database to a SharePoint server that is running Access Services, and then use the database in a browser. Read an article or try Office 2010!

You can import or link data from a variety of supported databases, programs, and file formats.

ShowShould I import or link a table?

Reasons to import data into a table

If you know that you will use your data only in Microsoft Access, you should import it. Microsoft Access generally works faster with its own tables, and, if you need to, you can modify the imported table to meet your needs just like any other table that is created in Microsoft Access.

Reasons to link data from an external table

You might want to link to tables from other Microsoft Access databases if, for example, you want to use a table from another Microsoft Access database that is shared on a network. This is particularly useful if you want to store all of your tables in one database on a network server, while keeping forms, reports, and other objects in a separate database that is copied among users of the shared database. You can easily split an existing database into two databases, called front-end and back-end databases.

If the data that you want to use in your Access database is also being updated by a program other than Microsoft Access, you should link it. Using this approach, the current methods of updating, managing, and sharing the data can remain in place, and you can use Microsoft Access to work with the data as well. For example, you can create queries, forms, and reports that use the external data, combine external data with the data in Microsoft Access tables, and even view and edit the external data while others are using it in the original program.

 Note    You cannot update or delete data that is linked to an Excel worksheet.

You might also want to link to SQL Server tables from your Access project. You can create a linked table view by using the Linked Table Wizard. These views can then be used in forms, reports, data access pages, and queries just like any other table.

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.

ShowWhat happens when importing data.

Importing data creates a copy of the information in a new table in your Access database or Access project. The source table or file is not altered in this process.

When importing data, you can't append data to existing tables (except when importing spreadsheet or text files). However, once you have imported a table, in an Access database you can perform an append query (append query: An action query that adds the records in a query's result set to the end of an existing table.) or in an Access project you can use a stored procedure (stored procedure: A precompiled collection of SQL statements and optional control-of-flow statements that is stored under a name and processed as a unit. The collection is stored in an SQL database and can be run with one call from an application.) or an append query to add the table's data to another table.

You can also import database objects other than tables, such as forms or reports, from another Access database or Access project.

ShowWhat happens when linking data.

Linking data enables you to read and, in most cases, update data in the external data source without importing. The external data source's format is not altered so that you can continue to use the file with the program that originally created it, but you can add, delete, or edit its data by using Microsoft Access as well.

 Note    You cannot update or delete data that is linked to an Excel worksheet.

Microsoft Access uses different icons to represent linked tables and tables that are stored in the current database. If you delete the icon for a linked table, you delete the link to the table, not the external table itself.

Icons for linked tables in the Database window

Callout 1 Linked DBASE table
Callout 2 Linked Paradox table
Callout 3 Linked Access table

In Microsoft Access projects, a view is created that references the linked table. The view can then be referenced in forms, reports, data access pages, and queries. The ability to insert, update, or delete records from the resulting view depends on the capabilities of the data source selected.

Linked Table View

 Note    You cannot update or delete data that is linked to an Excel worksheet.

ShowImport and link data from ODBC data sources.

You can import or link data from ODBC databases (ODBC database: A database for which an Open Database Connectivity (ODBC) driver - a driver that you can use for importing, linking to, or exporting data - is supplied.), such as Microsoft SQL Server and Visual FoxPro, as well as other programs that provide drivers compliant with ODBC (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) Level 1 to access their data files. To do this, you must be connected to the appropriate ODBC data source (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.). To connect to an ODBC data source, you must have the correct ODBC driver (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) installed and a data source name defined.

When you import a table into an Access project, Microsoft Access imports the table data, data definitions, and primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) but not its other properties (including constraints, relationships (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.), and indexes).

ShowUnsupported programs.

If you have a program whose data is not stored in one of the supported external database or file formats, but the program can export, convert, or save its data as one of these formats, then you can import that data. Additionally, when you import or link an external database format, such as dBASE or Paradox, Access usually preserves indexes. For example, you can't import or link a Microsoft Works database directly, but you can export the data from Microsoft Works to a dBASE IV (.dbf) database file and then import that data into Microsoft Access to preserve field names and indexes.

In general, most unsupported programs, even those on different operating systems, can export data to a delimited (delimited text file: A file containing data where individual field values are separated by a character, such as a comma or a tab.) or fixed-width text file (fixed-width text file: A file containing data in which each field has a fixed width.), which you can then import or link from Microsoft Access, although you won't preserve indexes.

ShowAutomating import operations.

To make frequent import operations more convenient, you can automate them by creating a macro or creating a Microsoft Visual Basic for Applications (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Windows applications and is included with several Microsoft applications.) procedure. This is useful, for example, when you import data on a regular schedule or you have unusual or complex requirements for importing data.

ShowLinked table properties.

When you open a linked table in 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.), it looks much like a regular Microsoft Access table. Although you can't change the way the linked table and its fields are defined in the external database, you can set the properties that control the way the fields appear in Microsoft Access. Changes that you make to properties for linked tables affect only how Microsoft Access handles and displays data from the linked table; no changes are made to the source table. Field properties that you can set for linked tables are listed in the following table.

Property Effect
Format Controls how data is displayed in a field
DecimalPlaces Controls the number of decimal places displayed
InputMask Creates a data input mask (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.) with separator characters and blanks to fill in
Caption Changes the name used for a field's datasheet column heading, and specifies a default name to be used as a label when adding a field to a form


You can't change other field properties for linked tables. However, to help make data entry more efficient and reliable, you can create forms that you use to add or edit data in your linked tables, and set properties for controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) that are bound to fields from your linked tables. For example, you might want to set the DefaultValue, ValidationRule, and ValidationText properties for controls in those forms.

If you link two tables from the same Access database, then any relationship (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) that was established between the tables in the other database remains in effect.

If you link tables from another Microsoft Access database, the tables use the property settings from the database they are stored in. For example, if a table has validation rules in the original database, data that you enter in the linked table must follow these rules as well. If you need to change these properties, you must open the table in the database it is stored in.

You might want to rename linked tables. Because Microsoft Access table names can contain spaces and up to 64 characters, you might want to give a linked table a more descriptive name after you link it. For example, if you link a dBASE table named SLSDATA, you could rename the linked table "Sales Data (from dBASE)." Note that this won't rename the table itself, just the name Microsoft Access uses to refer to the link to that table.

ShowAbout import/export specifications and schema.ini files.

An import/export specification contains information, such as file format, date order, or number formats, that Microsoft Access uses to import or export a fixed-width or delimited text file (delimited text file: A file containing data where individual field values are separated by a character, such as a comma or a tab.). An import/export specification is stored with the default name Filename_ImportSpec or Filename_ExportSpec in the database that you import to or export from.

You create an import/export specification by using either the Import Text Wizard or the Export Text Wizard. Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process.

Alternatively, you can use a schema.ini file in a Microsoft Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Microsoft Windows registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini.

 
 
Applies to:
Access 2003