About copying data and databases in an Access project

There are four ways to copy data and database information in a Microsoft Access project:

• Import one or more tables.
• Export one or more tables.
• Copy a database.
• Transfer a database.

Importing and exporting data

When you import or export data (or copy and paste), the following information gets copied along with the data:

• The table name. However, if the table name already exists, Access adds a numeric suffix (for example, Customers becomes Customers1).
• The primary key of the table. If the primary key name already exists, Access tries to recreate the primary key based on the table name by adding a PK_ prefix to the primary key name (for example, PK_Customers). If that name already exists, then Access adds a numeric suffix to the name (for example, PK_Customers1).

  Note   Primary keys are not imported or exported if your Access project is connected to a SQL server version 6.5 database.

• Column name, data type, length, precision, scale, and Null value settings. User-defined data types are not imported or exported, but rather are converted to their base types.

When you import or export data (or copy and paste), the following information does not get copied along with the data:

• Extended properties, such as default values, validation text constraints, and lookup values.

Copy or transfer a database

You can copy or transfer an SQL Server version 7.0 or later database. Both these operations not only copy the data and data definitions, but, unlike import or export operations, also copy extended properties such as default values, text constraints, and lookup values.

There are different requirements for copy and transfer database operations you need to be aware of before deciding which one best fits your needs. Copying a database is generally faster than transferring a database, but copying a database has more requirements.

Requirements for copying a database

• You must disconnect all applications and users before you copy the database file.
• All windows and views except the database window must be closed.
• The current database must not be replicated.
• The source server database must be Microsoft SQL Server version 7.0 or later, or SQL Server 2000 Desktop Engine, and must be a local computer.

• The SQL Server database on the source server must be a single file database.

• You must be a member of the sysadmin role on both the source and destination SQL servers.

Requirements for transferring a database

• You must be a member of the sysadmin role on the destination server (no special role is required on the source server).

• The current SQL server connected to the Access project and the destination server you are transferring the database to must be SQL Server version 7.0 or later.

Note   Linked Servers are not transferred during a database transfer operation.

About the SQL Distributed Management Objects (SQL-DMO) version 8.0 requirement

You must have SQL Distributed Management Objects (SQL-DMO) version 8.0 installed on your local computer to import, export, copy or transfer data and databases. SQL-DMO version 8.0 is a component of SQL Server 2000, but is not automatically installed on your computer when you install Microsoft Office XP. This situation can occur if you have not installed Microsoft SQL Server 2000 or SQL Server 2000 Desktop Engine on your local computer. To resolve this, install Microsoft SQL Server 2000 or SQL Server 2000 Desktop Engine on your local computer. You can install the SQL Server 2000 Desktop Engine from the \MSDE2000 folder on the Microsoft Office XP CD-ROM.