ACC2002: When You Paste Table Structure in ADP, Extended Properties Are Not Included in New Table (287494)

MORE INFORMATION

The design of a table in an Access project includes, for every column, the following properties:

• Column Name, which identifies the column
• Data Type, which specifies the type of data to be stored in the column, such as integer or character
• Length, which specifies the maximum size of a data item in that column, such as 4 digits for integer or 50 characters for character data
• Allow Nulls, which specifies whether or not the table will permit records to be saved that have no data (NULL) in that column

In addition to these basic properties, each column can have a variety of extended properties, which depend on the type of data being stored. The extended properties are grouped as Columns properties and Lookup properties. Columns properties affect the way in which the data is stored and presented, and Lookup properties affect the way in which the data is obtained.

The Columns properties include the following:

• Default Value
• Precision
• Scale
• Identity
• Identity Seed
• Identity Increment
• Is RowGuid
• Formula
• Collation
• Format
• Decimal Places
• Input Mask

However, not all Columns properties apply to all columns. For example, Decimal Places is not a relevant property in a column that stores character data.

The Lookup properties include the following:

• Display Control
• Row Source Type
• Row Source
• Bound Column
• Column Count
• Column Heads
• Column Widths
• List Rows
• List Width
• Limit To List

Lookup properties are primarily used to determine the characteristics of a list that allows the user to select rather than type data to be entered in the column.

When you use the Copy and Paste commands to duplicate a table in the Database window of an Access database, the extended properties of the table's fields (that is, Columns and Lookup properties) are copied along with the rest of the structure. The extended properties are not copied in this way in an Access project, but you are able to duplicate them by copying from the table's Design view.

To do this, follow these steps after you perform the steps in the "Steps to Reproduce the Problem" section of this article:

1. Open the table TestCustOrders in Design view.
2. Drag to select the three rows containing the column definitions, and then click Copy on the toolbar.
3. Minimize the TestCustOrders window, and then click New in the Project window.
4. In the new table's Design view, click the first row header, and then click Paste on the toolbar.
5. Click the individual rows and notice that their extended properties have been copied.

NOTE: Although the extended properties of the table can be copied in this way, the Primary Key property cannot be. It is necessary for you to manually select the Primary Key (in this case, the column OrdID).

Steps to Reproduce the Problem

1. Start Access, and then open any project (*.adp).
2. In the Database window, click Tables under Objects, and then click New.
3. Create columns with the following properties:

      Column Name       Data Type            Length     Allow Nulls
      -----------       ---------            ------     -----------
      OrdID             uniqueidentifier       16           No
      CustID            char                   50           No
      Value             money                   8           Yes
   					

4. Click the OrdID row, and then click Primary Key on the toolbar.
5. Click the CustID row, and then type the following properties on the extended properties Lookup tab:

      Display Control           Combo Box
      Row Source Type           Tables/Views/Functions
      Row Source                SELECT CustomerID FROM Customers
   					

6. Click the Value row, and then type the following property on the extended properties Columns tab:

      Default value             100
   					

7. On the File menu, click Save, and then type the name TestCustOrders.
8. Open the table in Datasheet view, enter three rows of data of your choice, and then close the table.
9. In the Database window, right-click the TestCustOrders table, click Copy on the shortcut menu, right-click the Project window, and then click Paste on the shortcut menu.
10. In the Paste Table As dialog box, type the table name TestCustOrdersBU, and then click OK.
11. Open TestCustOrdersBU in Design view, and then notice that the extended properties have not been copied.