Field size is changed when you copy and paste a table in an Access project

In an Access project, you can copy and then paste a table that has fields that are greater than 256 characters. However, when you view the size of the fields in the resulting table, the size of the fields is reduced.

When you copy and then paste a Microsoft SQL Server table in an Access project on a computer that does not have Microsoft SQL Server 2000 or SQL Server Desktop Engine (also known as MSDE 2000) installed, the copy and paste operation uses OLEDB instead of using a bulk copy operation through Data Transformation Services (DTS).

You can see the problem that is mentioned in the "Symptoms" section when you use OLEDB to copy and paste the table.

To work around this problem, use one of the following methods:

• Method 1
  
  Perform the copy and paste operation on a computer that has SQL Server 2000, MSDE 2000, or SQL Server 2000 Client Tools installed in addition to Access 2003 or Access 2007.
• Method 2
  
  Log on to a computer that has SQL Server 2000 Client Tools installed, and then use the Import and Export Data utility of SQL Server 2000 to copy the table.
  
  For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  285829� How to Use Data Transformation Services (DTS) to Export Data from a Microsoft Access Database to an SQL Server Database

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Steps to reproduce the problem in Access 2003

1. Start SQL Query Analyzer.
2. Connect to the Northwind database on your SQL Server 2000 server.
3. Run the following code in SQL Query Analyzer:

   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestCopyPaste]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[TestCopyPaste]
   GO
   
   CREATE TABLE [dbo].[TestCopyPaste] (
   	[vc1000] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   	[vc2000] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   	[vc3000] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   	[vc4000] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   	[vc5000] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   	[vc6000] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   	[vc7000] [varchar] (7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   	[vc8000] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
   ) ON [PRIMARY]
   GO

4. Start Access 2003.
5. On the File menu, click New.
6. In the right pane, click Project using existing data.
7. In the File New Database dialog box, type TestProject in the File name text box.
8. In the Data Link Properties dialog box, provide the details of the SQL Server 2000 server that you connected to in Step 2.
9. In the Select the database on the server list, click Northwind, and then click OK.
10. In the right pane of the Project window, click the TestCopyPaste table.
11. On the Edit menu, click Copy.
12. On the Edit menu, click Paste.
13. In the Paste Table As dialog box, type TestCopyPaste2 in the Table Name text box. Before you click OK, make sure that you click the Structure and Data option, and then click OK.
14. Right-click TestCopyPaste2, and then click Design View.

Note Notice that the size of the varchar field is reduced in the new table that is named TestCopyPaste2.