About importing and linking data and database objects

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.

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.

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 or in an Access project you can use a stored procedure 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.

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

Linked DBASE table

Linked Paradox table

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.

You can import or link data from ODBC databases, such as Microsoft SQL Server and Visual FoxPro, as well as other programs that provide drivers compliant with ODBC Level 1 to access their data files. To do this, you must be connected to the appropriate ODBC data source. To connect to an ODBC data source, you must have the correct ODBC driver installed and a data source name defined.

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 or fixed-width text file, which you can then import or link from Microsoft Access, although you won't preserve indexes.

To make frequent import operations more convenient, you can automate them by creating a macro or creating a Microsoft Visual Basic for 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.

When you open a linked table in Design view, 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.

If you link two tables from the same Access database, then any relationship 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.

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