Microsoft KB Archive/206135

= ACC2000: Upsizing Report Incorrectly Shows Field as Varchar =

Article ID: 206135

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q206135



This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
When you use the Upsizing Wizard to upsize a table that contains a field with an Access data type of Text, the resulting upsizing report indicates that the field was upsized to a data type of varchar, when actually the field was upsized to a data type of nvarchar.



RESOLUTION
If you do not plan to use the data in other language versions of Microsoft SQL Server, you can change the Unicode data type of nvarchar to varchar. By using varchar, you can reduce disk space requirements. (See the "More Information" section later in this article.) To change the data type, follow these steps:
 * 1) Open either an existing Microsoft Access project or create a new Access project that is connected to the SQL Server database in question.
 * 2) Click Tables under Objects, and then click the table that contains the field that you want to change.
 * 3) Open the table in Design view.
 * 4) In Design view, click the data type for the field and change the data type to varchar.
 * 5) Close and save the table.



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



MORE INFORMATION
The nvarchar data type is a variable-length Unicode character data type. The length must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered.

The varchar data type is a variable-length non-Unicode character data type. The length must be a value from 1 through 8,000. Storage size is the actual length of the data entered.

Steps to Reproduce Behavior
 Create a new Access database named TestDB.  In the new database, create a new table called Test1 with the following characteristics:   Table: Test1 Field Name: CategoryID Data Type: Counter Indexed: Yes (No Duplicates)

Field Name: CategoryName Data Type: Text Save the table and open it in Datasheet view.  Enter the following sample data.

 Close the table. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.</li> Upsize the Test1 table to a new database.</li> Click Next for each step in the wizard until you see the screen that asks What application changes do you want to make?</li> Click Create a new Access client/server application, and then click Finish.</li> In the resulting report, note that the data type of CategoryID is varchar.</li> Close the report.</li> In the new TestDB.adp, open the Test1 table in Design view. Note that the data type of CategoryID is nvarchar.</li></ol>

<div class="references_section">